OmegaMan's Musings

I recently replied to a post in the MSDN C# General forum (one of the best places to get help if you have C# question) where the user was getting data from a DataReader and needed help with null values being returned. What I ended up discovering was differences from old way of getting data from a database in .Net and the new way. Such as the highways above both go to the same location and essentially do the same thing, but the contrast is so much different. Same holds true for .Net Linq and the DataReader.

In the post I recommend the use of the null-coalescing operator to handle the null returned from the database. Erroneously I forgot that the DataReader returns a DBNull which cannot be handled by the null-coalescing operator. GRRR

My mistake was based in the fact that I have become used to the Linq Data Context returning a INullable<T> when dealing with null fields in a database. The the null-coalescing operator can handle that to a tee.

I created my test code and realized that it demonstrated the following

1. Linq does so much for the developer that what previously took around six plus lines of code boils down to roughly three. It manages so much in the background for the user that it allows for quicker coding to get at data.
2. The design of Linq handles Nulls differently, or at lease presents them to the end user from the old way processing. Using the null-coalescing operator as shown is a great help.

Here is the example code, first up is Linq and the target operation is to display values from a nullable field in the database called Description. When enumerating over the data rows, if that field happens to be null it reports it or shows the actual data. Next up is the older way of doing it and notice how the user has to handle reader. Note one could check for DBNull in that situation, so the example may not be what you are looking to do in a real system.

GTCDataContext gtc = new GTCDataContext();

var items = gtc.Location_US;

foreach ( var it in items )
   Console.WriteLine( it.Description ?? "not Defined" );

string queryString = "SELECT Description FROM dbo.Location_US;";

using ( SqlConnection connection = new SqlConnection( gtc.Connection.ConnectionString  ))
{
   SqlCommand command = new SqlCommand( queryString, connection );    connection.Open();

   SqlDataReader reader = command.ExecuteReader();

   // Call Read before accessing data.
   while ( reader.Read() )
   {
      Console.WriteLine( (string.IsNullOrEmpty(reader[0].ToString()) == false) ?  reader[0] : "not defined again" );
   }

    // Call Close when done reading.
    reader.Close();
}

Here is the result of the run:

Florida

not Defined

Colorado

California

not Defined

not Defined

not Defined

not Defined

—————————————–

Florida

not defined again

Colorado

California

not defined again

not defined again

not defined again

not defined again