CODEWITHSUNDEEP
c#sql-serverado.net
Mohammad hossein
Mohammad hossein

Reputation: 255

how to get null values in sqldatareader

I get a row from table and some field of this row are null. How can I get these fields with SqlDataReader?

//so_type , so_read are null able 

while (reader.Read())
{
    s.id = (Int64)reader["so_id"];
    s.senderid = (Int64)reader["so_senderid"];
    s.body = (string)reader["so_body"];
    s.date = (string)reader["so_date"];
    s.isread = (bool)reader["so_read"];
    s.type = (int)reader["so_type"];
}

Upvotes: 1

Views: 1597

Answers (2)

Dustin Kingen
Dustin Kingen

Reputation: 21245

You can take advantage of C# generics to write a helper method which extracts the value or default for each column that can be null.

private static T GetValue<T>(object value)
{
    return value == DBNull.Value ? default(T) : (T) value;
}

while (reader.Read())
{
    s.id       = GetValue<long>(reader["so_id"]);
    s.senderid = GetValue<long>(reader["so_senderid"]);
    s.body     = GetValue<string>(reader["so_body"]);
    s.date     = GetValue<string>(reader["so_date"]);
    s.isread   = GetValue<bool>(reader["so_read"]);
    s.type     = GetValue<int>(reader["so_type"]);
}

This also assumes the value is already the correct type.

A more type safe implementation would be:

private static T GetValue<T>(object value)
{
    return value == DBNull.Value
           ? default(T)
           : (T) Convert.ChangeType(value, typeof(T));
}

Upvotes: 2

Michael
Michael

Reputation: 658

You can use the IsDBNull method. Something like that:

int ordinal_so_type=reader.GetOrdinal("so_type");
//...
while(reader.Read()==true)
{
  //reading other columns here
  if (reader.IsDBNull(ordinal_so_type)==false)
  {
    s.type=reader.GetInt32(ordinal_so_type);
  }
  else
  {
    //do whatever you like if the so_type column is null
  }
}

Upvotes: 1

Related Questions