What is the best practice for handling null int/char from a SQL Database?

Question

I have a database that hold's a user's optional profile. In the profile I have strings, char (for M or F) and ints.

I ran into an issue where I try to put the sex of the user into the property of my Profile object, and the application crashes because it doesn't know how to handle a returned null value.

I've tried casting the data to the appropriate type

char sex = (char)dt.Rows[0]["Sex"];

Which didn't fix my problem. I then tried changing the types to Nullable and Nullable and get conversion issues all the same. My current solution that I was able to find is the following:

object.sex = null;  
if(dt.Rows[0]["Sex"] != DBNull.Value)
      object.sex = (char)dt.Rows[0]["Sex"];
object.WorkExt = null;
if(dt.Rows[0]["WorkExt"] != DBNull.Value)
      object.WorkExt = (int)dt.Rows[0]["WorkExt"];

Is there a simpler or better way to do this? Or am I pretty much on the right track?

Answer 1

I would do it pretty much like you did. I would write a function for it:

Something that does:

object.sex = handle(dt.Rows[0]["Sex"]);

And in handle you do the ==DBNull.Value check.

Answer 2

A decent discussion on this is at http://stackoverflow.com/questions/221582/most-efficient-way-to-check-for-dbnull-and-then-assign-to-a-variable.

Answer 3

Is dt an ADO.Net 2 data table? Can you not do something like:

if(dt.Rows[0].IsSexNull()) {} else {}

? Also, assuming you have control over your database, would it not make more sense to use a bit, rather than a string?

Answer 4

nullable types were designed just for this purpose! use 'as char?' instead of '(char?)'

class Foo {
    char? sex;
}
Foo object;

object.sex = dt.Rows[0]["Sex"] as char?;

Answer 5

rotard's answer (use Is<ColumnName>Null()) only works for typed data sets.

For untyped data sets, you have to use one of the patterns in the following code. If this code isn't definitive, let me know and I'll edit it until it is. This is an extremely common question that there should really be only one right answer to.

using System.
using System.Data;

class Program
{
    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("test", typeof (char));
        dt.Columns["test"].AllowDBNull = true;

        DataRow dr = dt.Rows.Add();
        char? test;

        try
        {
            test = (char?)dr["test"];
        }
        catch (InvalidCastException)
        {
            Console.WriteLine("Simply casting to a nullable type doesn't work.");
        }

        test  = dr.Field<char?>("test");
        if (test == null)
        {
            Console.WriteLine("The Field extension method in .NET 3.5 converts System.DBNull to null.");                
        }

        test = (dr["test"] is DBNull) ? null : (char?) dr["test"];
        if (test == null)
        {
            Console.WriteLine("Before .NET 3.5, you have to check the type of the column's value.");
        }

        test = (dr["test"] == DBNull.Value) ? null : (char?) dr["test"];
        if (test == null)
        {
            Console.WriteLine("Comparing the field's value to DBNull.Value is very marginally faster, but takes a bit more code.");
        }

        // now let's put the data back

        try
        {
            dr["test"] = test;
        }
        catch (ArgumentException)
        {
            Console.WriteLine("You can't set nullable columns to null.");
        }

        dr.SetField("test", test);
        if (dr["test"] is DBNull)
        {
            Console.WriteLine("Again, in .NET 3.5 extension methods make this relatively easy.");
        }

        dr["test"] = (object)test ?? DBNull.Value;
        if (dr["test"] is DBNull)
        {
            Console.WriteLine("Before .NET 3.5, you can use the null coalescing operator, but note the awful cast required.");
        }


        Console.ReadLine();
    }
}

Answer 6

how about:

    internal static T CastTo<T>(object value)
    {
        return value != DBNull.Value ? (T)value : default(T);
    }

and then use it like:

        return new EquipmentDetails(
            CastTo<int>(reader["ID"]),
            CastTo<int>(reader["CategoryID"]),
            CastTo<string>(reader["Description"]));

etc...