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)?
For working with database ANSI SQL null values, use System. Data. SqlTypes nulls rather than Nullable. For more information on working with CLR value nullable types in Visual Basic see Nullable Value Types, and for C# see Nullable value types.
Use of NULL Values in C#Any type is known as nullable if you can assign a value or null to this variable it means that the type will have no value. In C# all reference types like string are of a nullable type, but the other types like int32 are not nullable type. A nullable type has two members, HasValue.
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.
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);
}
}
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With