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)?

phoog · Accepted Answer · 2015-02-17 08:53:19Z

20

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.

edited Feb 17, 2015 at 8:53

answered Dec 6, 2011 at 19:16

phoog

43.2k6 gold badges84 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Richard Collette Over a year ago

SetValue(ordinal,object) handles nullables and null values with a little bit of overhead for type and value length checking. The performance difference may not be noticeable.

phoog Over a year ago

@RichardCollette Thanks for the comment; I didn't think of that. I suspect you're right that the performance difference is probably insignificant for most applications. I do tend to prefer stronger typing on philosophical grounds, though. Your comment also inspires me to add a small optimization I didn't think of two years ago.

Phil Cooper Over a year ago

Not sure if the framework has changed but it's SetDBNull in version 4 (note the 'B' case).

phoog Over a year ago

@PhilCooper What is SetDBNull in version 4? What 'B' case?

Phil Cooper Over a year ago

@phoog SetDbNull is SetDBNull see here: SqlDataRecord.SetDBNull

|

Jeff Ogata · Accepted Answer · 2011-12-06 19:08:08Z

4

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.

answered Dec 6, 2011 at 19:08

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

Comments

Andrea · Accepted Answer · 2019-11-11 14:51:11Z

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);
        }
    }
}

D Stanley · Accepted Answer · 2011-12-06 19:13:42Z

0

Should use SetSqlInt32 instead of SetString. Try

rec.SetSqlInt32(2, (dm.Count.HasValue ? new SqlInt32(dm.Count) : SqlInt32.Null) );

answered Dec 6, 2011 at 19:13

D Stanley

153k12 gold badges189 silver badges257 bronze badges

2 Comments

phoog Over a year ago

That would have to be rec.SetSqlInt32(2, (dm.Count.HasValue ? new SqlInt32(dm.Count.Value) : SqlInt32.Null) );

phoog Over a year ago

Further, as I realized when editing my answer, the implicit conversion from int to SqlInt32 means you can do this: rec.SetSqlInt32(2, dm.Count.HasValue ? dm.Count.Value : SqlInt32.Null);

MethodMan · Accepted Answer · 2011-12-06 19:45:12Z

0

//Ive found this to work the best for me when checking or dealing with nullable types //in my example I am checking values stored in a String[] array

sqlcmdUpdateRecord.Parameters.AddWithValue("@strSomeValue", (strRecord[(int) 
DataFields.SomeDataField] as object) ?? DBNull.Value);

answered Dec 6, 2011 at 19:45

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Comments

Nish · Accepted Answer · 2019-01-25 11:04:47Z

0

maybe you can try the below.

if(dm.Count == null){
  rec.SetDBNull(2)
}
else{
 rec.SetString(2, dm.Count);
}

answered Jan 25, 2019 at 11:04

Nish

1711 silver badge7 bronze badges

Collectives™ on Stack Overflow

How do you handle Nullable type with SqlDataRecord

6 Answers 6

6 Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related