DataReader - Handling Null Values

[HLeyes]

I am using a SQL Server database and C#. When retrieving data with a datareader, there are some fields that are null. What is the best way to handle a null value? In VB.Net, there was a single line test that would return an empty value if it was null, otherwise the non-null value would return. Here's some of the datareader code:

            var dr = myCMD.ExecuteReader();
            while (dr.Read())
            {
                ColumnItem colItem = new ColumnItem();
                colItem.TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
                colItem.TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
                colItem.TABLE_NAME = dr["TABLE_NAME"].ToString();
                colItem.COLUMN_NAME = dr["COLUMN_NAME"].ToString();
                colItem.ORDINAL_POSITION = Convert.ToInt32(dr["ORDINAL_POSITION"]);
                colItem.COLUMN_DEFAULT = dr["COLUMN_DEFAULT"].ToString();
                colItem.IS_NULLABLE = dr["IS_NULLABLE"].ToString();
                colItem.DATA_TYPE = dr["DATA_TYPE"].ToString();
                colItem.CHARACTER_MAXIMUM_LENGTH = Convert.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"]);
                colItem.CHARACTER_OCTET_LENGTH = Convert.ToInt32(dr["CHARACTER_OCTET_LENGTH"]);

Thanks in advance for any help and examples!

HLeyes

 

[jmcilhinney]

You do pretty much exactly the same thing in C# as you would do in VB. C# has had a ternary operator from the get-go, while the If operator was only added to VB fairly recently to provide equivalent functionality. In VB you would do this:

myString = If(myDataReader.IsDBNull(columnIndex1), CStr(Nothing), myDataReader.GetString(columnIndex1))
myNullableInteger = If(myDataReader.IsDBNull(columnIndex2), DirectCast(Nothing, Integer?), myDataReader.GetInt32(columnIndex2))

The equivalent C# code is this:

myString = myDataReader.IsDBNull(columnIndex1) ? (string) null : myDataReader.GetString(columnIndex1);
myNullableInteger = myDataReader.IsDBNull(columnIndex2) ? (int?) null : myDataReader.GetInt32(columnIndex2);

As you can see, there's really not much difference.

By the way, if you want to be able to use column names in your code then you can pass them to GetOrdinal to get the index that IsDBNull and methods like GetString and GetInt32 require.

 

[jmcilhinney]

You might also like to check out this code that I wrote some time ago to make accessing nullable data from a data reader a bit cleaner:

Nullable Data Extension Methods

As you can see, that code was written for VB but you could write equivalent C# by using the same type of conversion as I used above. Alternatively, you could compile the VB source into a DLL and then reference that in any VB or C# project.