Handling Null types from SQL in C#

Question

I am using the SqlDataReader to get values from an SQL table and assign them to their respective variables. But in my table, int and double type columns have null values. So when I try to read a null int and assign it to an int variable, it crashes.

Here is how the values are being assigned to:

public int ID { get; set; }
public int Map { get; set; }
public int TypeID { get; set; }

And this is where they are being read:

while (objSqlDataReader.Read())
{
    data= new data();
    emissiondata.ID = (int)objSqlDataReader["EFID"];
    emissiondata.Map = (int)objSqlDataReader["EFMappingID"];
    emissiondata.TypeID =(SqlInt32)objSqlDataReader["MobileTypeID"];

So if any of these is null, even when i'm debugging it, it crashes and doesn't continue. How do I handle the null values in SQL and how do I assign empty values to my int if it is null?

Michael Buen · Accepted Answer · 2012-05-04 15:25:35Z

DBNull.Value works on DataTable rows, I'm not sure with SqlDataReader:

bool isNull = objSqlDataReader.GetOrdinal("EFID") == DBNull.Value;

If you use SqlDataReader's GetSqlInt32,GetSqlDateTime, etc for example, they are null-aware out-of-the-box.

Instead of unboxing:

emissiondata.ID = (int)objSqlDataReader["EFID"];

You do this instead:

SqlInt32 emissionId = objSqlDataReader.GetSqlInt32(emissionIdOrdinal);

Then you can test for null directly:

if (emissionId.IsNull) ...

However, those methods need the column's ordinal to access value. You can setup those ordinals before your loop

int emissionIdOrdinal = rdr.GetOrdinal("EFID");

But if you really want C#'s null, you make a helper function for it instead:

public static int? ToNullable(this SqlInt32 value)
{
    return value.IsNull ? (int?) null : value.Value;
}

To access it with your nullable variable:

int? emissionId = objSqlDataReader.GetSqlInt32(emissionIdOrdinal).ToNullable();

You can now test null directly:

if (emissionId == null)

An advice, try to change your class' properties to nullable types, precluding the need for you to use another variable:

public int? ID { get; set; }
public int? Map { get; set; }
public int? TypeID { get; set; }

Final code:

data= new data();
emissiondata.ID = objSqlDataReader.GetSqlInt32(emissionIdOrdinal).ToNullable();
emissiondata.Map = objSqlDataReader.GetSqlInt32(emissionMapOrdinal).ToNullable();

Emperor Orionii · Accepted Answer · 2012-05-04 08:39:03Z

2

Can't test it now but you can try with int?, the nullable int. It's a shorthand for Nullable<Int32>. If objSqlDataReader can be cast to int?, change your fields to int? and add extra logic to property setter if you have to do something when value is null.

answered May 4, 2012 at 8:39

Emperor Orionii

7511 gold badge10 silver badges24 bronze badges

Comments

qxotk · Accepted Answer · 2016-07-14 16:59:03Z

2

I realize this is old, but if you are needing this answer after 2016-07-14: When SQL Type is int, null Use int? as the data type.

int? is a "nullable" int - so when SQL returns NULL for a column, the property value receives the value null.

answered Jul 14, 2016 at 16:59

qxotk

2,4215 gold badges25 silver badges40 bronze badges

Comments

Mead · Accepted Answer · 2012-05-06 03:17:17Z

So, to find a possible answer I look at the documentation for the class you're talking about: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx There's a IsDBNull method. Not having C# around, this should work:

while (objSqlDataReader.Read())
{
   data= new data();
   if (!objSqlDataReader.IsDBNull(objSqlDataReader.GetOrdinal("EFID")))
   {
      emissiondata.ID = (int)objSqlDataReader["EFID"];
   } else {
      // Whatever you want to do when it is null
   }
   if (!objSqlDataReader.IsDBNull(objSqlDataReader.GetOrdinal("EFMappingID")))
   {
      emissiondata.Map = (int)objSqlDataReader["EFMappingID"];
   } else {
      // Whatever you want to do when it is null
   }
   if (!objSqlDataReader.IsDBNull(objSqlDataReader.GetOrdinal("MobileTypeID")))
   {
      emissiondata.TypeID = (int)objSqlDataReader["MobileTypeID"];
   } else {
      // Whatever you want to do when it is null
   }
}

Marc · Accepted Answer · 2012-05-04 00:51:19Z

0

You could do this in the query itself. In Oracle, the function is NVL(). Looks like you're using MS Sql Server, which uses:

ISNULL(YOUR_FIELD,0)

Usage and syntax for other RDBMS is here: http://www.w3schools.com/sql/sql_isnull.asp

answered May 4, 2012 at 0:51

Marc

11.6k2 gold badges40 silver badges48 bronze badges

6 Comments

Tanny Fgh Over a year ago

will this assign my variable as empty or as 0 ?

Tanny Fgh Over a year ago

i was looking to keep it blank if it is null

Marc Over a year ago

@Tanny Fgh, I'm not sure what programming language you're using, but you typically can't cast a null to an int primitive.

Marc Over a year ago

@TannyFgh, if you need it to be blank, you need to be working with a string data type. It's simply not going to work the way you're casting a null to an int. int is a number. Always.

Tanny Fgh Over a year ago

Well I tried ISNULL(YOUR_FIELD,0) in my query , it still doesnt assign 0 to the variable . It crashes

|

Collectives™ on Stack Overflow

Handling Null types from SQL in C#

5 Answers 5

Comments

Comments

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related