How do you handle Nullable type with SqlDataRecord

Question

I am parsing XML (LINQ to XML) and I am using a nullable type (int? and decimal?) in cases where the element / attribute is empty. However, when building my collection to pass to the DB (Using TVP) I don't know how to handle the cases where the value is actually null. I can't pass a null into the SqlDataRecord SetInt32 or SetDecimal and I don't want to set to zero....I actually want it to be null.

Telling me no overload for int?

Count below is a nullable type (int? Count)

SqlDataRecord rec = new SqlDataRecord(
              new SqlMetaData("Name", SqlDbType.VarChar, 50),
              new SqlMetaData("Type", SqlDbType.VarChar, 50),
              new SqlMetaData("Count", SqlDbType.Int));

   rec.SetString(0, dm.Name);
   rec.SetString(1, dm.Type);
   rec.SetString(2, dm.Count);

Any ideas how to handle this without passing zero (maintaining the null)?

Answer 1

Extension method:

static class SqlDataRecordExtensions
{
    static void SetNullableInt32(this SqlDataRecord rec, int index, Int32? value)
    {
        if (value.HasValue)
            rec.SetInt32(index, value.GetValueOrDefault());
        else
            rec.SetDBNull(index);
    }
}

or, to use SetSqlInt32 as suggested by D Stanley:

static class SqlDataRecordExtensions
{
    static void SetNullableInt32(this SqlDataRecord rec, int index, Int32? value)
    {
        rec.SetSqlInt32(index, value.HasValue ? value.GetValueOrDefault() : SqlInt32.Null);
        //                                      ^^^^^^^^^^^^^^^^^^^^^^^^^
        //                                      You can leave out the cast to (SqlInt32),
        //                                      because the conversion is implicit
    }
}

Note, 9 Dec 2013: Returning to this answer because of a comment, I noticed a small opportunity for improvement, based on Eric Lippert's series on nullable micro-optimizations, which can be found at http://ericlippert.com/2012/12/20/nullable-micro-optimizations-part-one/.

In brief, while the Value property requires less typing and is therefore arguably more optimal for the programmer, it has to throw an exception if HasValue is false. On the other hand, the GetValueOrDefault() method is a simple field access. Because of this, GetValueOrDefault() requires fewer instructions and is more likely to be inlined, so it is more optimal for the compiler and the processor.

Answer 2

As @phoog recommended, extension methods for different types:

public static class ExtensionSqlDataRecord
{
    public static void SetDateTime(this SqlDataRecord record, int ordinal, DateTime? value)
    {
        if (value != null)
        {
            record.SetDateTime(ordinal, (DateTime)value);
        }
        else
        {
            record.SetDBNull(ordinal);
        }
    }

    public static void SetInt32(this SqlDataRecord record, int ordinal, int? value)
    {
        if (value != null)
        {
            record.SetInt32(ordinal, (int)value);
        }
        else
        {
            record.SetDBNull(ordinal);
        }
    }

    public static void SetByte(this SqlDataRecord record, int ordinal, byte? value)
    {
        if (value != null)
        {
            record.SetByte(ordinal, (byte)value);
        }
        else
        {
            record.SetDBNull(ordinal);
        }
    }

    public static void SetDecimal(this SqlDataRecord record, int ordinal, decimal? value)
    {
        if (value != null)
        {
            record.SetDecimal(ordinal, (decimal)value);
        }
        else
        {
            record.SetDBNull(ordinal);
        }
    }

    public static void SetBoolean(this SqlDataRecord record, int ordinal, bool? value)
    {
        if (value != null)
        {
            record.SetBoolean(ordinal, (bool)value);
        }
        else
        {
            record.SetDBNull(ordinal);
        }
    }
}

Answer 3

I've never worked with SqlDataRecord, but when using DataTable and DataRow, or when using parameterized queries, I specify null using DBNull.Value.

With SqlDataRecord, it looks like you can use the SetDBNull method.