SqlDataReader Contd.. GetOrdinal(), IsDBNull()

In a previous post "ADO.Net - SqlDataReader" I have given an intro to the SQL Data Reader. Here will go through some usages in the data reader. In the Below code I use some built in methods to iterate through the items returned by the reader.

            using (var connection = new SqlConnection(@"Password=****;Persist Security Info=True;User ID=**;Initial Catalog=****;Data Source=****;Min Pool Size=100;"))
            {
                var command = new SqlCommand("SELECT contact_fax, firstname, lastname, phone from employess where empid = 'E1290'", connection);
             
                connection.Open();
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    // display middle name only if not null
                    if (!reader.IsDBNull(reader.GetOrdinal("firstname")))
                        Console.Write(" {0}", reader.GetString(reader.GetOrdinal("firstname")));
                }
                connection.Close();
            }

Here, the IsDBNull() method is used to check if the column contains null's. It accepts an integer and returns true or false based on the column value availability.

The GetOrdinal() method returns the position of the column in the table retrieved by the data reader. Columns are numbered starting from zero. As the firstname column is the second column specified in my select statement, the method returns 1.

If the column is not available this method fires a IndexOutOfRangeException.

And now you combine these two methods together. Specify the column name in the GetOrdinal(). If the column contains a non-null value the condition becomes true, control goes in and prints the column value.