CODEWITHSUNDEEP
c#sqldatareader
DenaliHardtail
DenaliHardtail

Reputation: 28296

SQL Data Reader - handling Null column values

I'm using a SQLdatareader to build POCOs from a database. The code works except when it encounters a null value in the database. For example, if the FirstName column in the database contains a null value, an exception is thrown.

employee.FirstName = sqlreader.GetString(indexFirstName);

How can I handle null values in this situation?

Upvotes: 379

Views: 521323

Answers (30)

Pasindu Jay
Pasindu Jay

Reputation: 4510

if(reader.IsDBNull(ColumnIndex)) {// logic} works as many answers says.

And I want to mention if you working with column names, just comparing types may be more comfortable.

if(reader["TeacherImage"].GetType() == typeof(DBNull)) { //logic }

Upvotes: 27

iCollect.it Ltd
iCollect.it Ltd

Reputation: 93551

employee.FirstName = sqlreader[indexFirstName] as string;

For integers, if you cast to a nullable int, you can use GetValueOrDefault()

employee.Age = (sqlreader[indexAge] as int?).GetValueOrDefault();

or the null-coalescing operator (??).

employee.Age = (sqlreader[indexAge] as int?) ?? 0;

Upvotes: 39

Conner
Conner

Reputation: 361

I did my best to reimplement the Field method from DataTable. https://learn.microsoft.com/en-us/dotnet/api/system.data.datarowextensions.field

It will throw if you try to convert a DBNull.Value to a non-nullable type. Otherwise it will convert DBNull.Value to null.

I haven't fully tested it.

public static T Field<T>(this SqlDataReader sqlDataReader, string columnName)
{
    int columnIndex = sqlDataReader.GetOrdinal(columnName);
    if (sqlDataReader.IsDBNull(columnIndex))
    {
        if (default(T) != null)
        {
            throw new InvalidCastException("Cannot convert DBNULL value to type " + typeof(T).Name);
        }
        else
        {
            return default(T);
        }
    }
    else
    {
        return sqlDataReader.GetFieldValue<T>(columnIndex);
    }
}

Usage:

string fname = sqlDataReader.Field<string>("FirstName");
int? age = sqlDataReader.Field<int?>("Age");
int yearsOfExperience = sqlDataReader.Field<int?>("YearsEx") ?? 0;

Upvotes: 2

Alison Niu
Alison Niu

Reputation: 74

in c# 7.0 we can do :

var a = reader["ERateCode"] as string;
var b = reader["ERateLift"] as int?;
var c = reader["Id"] as int?;

so it will keep null value if it is.

Upvotes: 2

Kaido
Kaido

Reputation: 3921

None of these was quite what i wanted:

 public static T GetFieldValueOrDefault<T>(this SqlDataReader reader, string name)
 {
     int index = reader.GetOrdinal(name);
     T value = reader.IsDBNull(index) ? default(T) : reader.GetFieldValue<T>(index);
     return value;
 }

Upvotes: 4

user9604262
user9604262

Reputation:

neat one-liner:

    while (dataReader.Read())
{
    employee.FirstName = (!dataReader.IsDBNull(dataReader.GetOrdinal("FirstName"))) ? dataReader["FirstName"].ToString() : "";
}

Upvotes: 1

Rhys Jones
Rhys Jones

Reputation: 5498

There are a lot of answers here with useful info (and some wrong info) spread about, I'd like to bring it all together.

The short answer to the question is to check for DBNull - almost everyone agrees on this bit :)

Rather than using a helper method to read nullable values per SQL data type a generic method allows us to address this with a lot less code. However, you can't have a single generic method for both nullable value types and reference types, this is discussed at length in Nullable type as a generic parameter possible? and C# generic type constraint for everything nullable.

So, following on from the answers from @ZXX and @getpsyched we end up with this, 2 methods for getting nullable values and I've added a 3rd for non-null values (it completes the set based on method naming).

public static T? GetNullableValueType<T>(this SqlDataReader sqlDataReader, string columnName) where T : struct
{
    int columnOrdinal = sqlDataReader.GetOrdinal(columnName);
    return sqlDataReader.IsDBNull(columnOrdinal) ? (T?)null : sqlDataReader.GetFieldValue<T>(columnOrdinal);
}

public static T GetNullableReferenceType<T>(this SqlDataReader sqlDataReader, string columnName) where T : class
{
    int columnOrdinal = sqlDataReader.GetOrdinal(columnName);
    return sqlDataReader.IsDBNull(columnOrdinal) ? null : sqlDataReader.GetFieldValue<T>(columnOrdinal);
}

public static T GetNonNullValue<T>(this SqlDataReader sqlDataReader, string columnName)
{
    int columnOrdinal = sqlDataReader.GetOrdinal(columnName);
    return sqlDataReader.GetFieldValue<T>(columnOrdinal);
}

I usually use column names, alter these if you use column indexes. Based on these method names I can tell whether I'm expecting the data to be nullable or not, quite useful when looking at code written a long time ago.

Tips;

  • Not having nullable columns in the database avoids this issue. If you have control over the database then columns should be non-null by default and only nullable where necessary.
  • Don't cast database values with the C# 'as' operator because if the cast is wrong it will silently return null.
  • Using a default value expression will change database nulls to non-null values for value types like int, datetime, bit etc.

Lastly, whilst testing the above methods across all SQL Server data types I discovered you can't directly get a char[] from a SqlDataReader, if you want a char[] you will have to get a string and use ToCharArray().

Upvotes: 3

Frank Hagenson
Frank Hagenson

Reputation: 1023

Convert handles DbNull sensibly.

employee.FirstName = Convert.ToString(sqlreader.GetValue(indexFirstName));

Upvotes: 0

Raghav
Raghav

Reputation: 9630

Here is helper class which others can use if they need based on @marc_s answer:

public static class SQLDataReaderExtensions
    {
        public static int SafeGetInt(this SqlDataReader dataReader, string fieldName)
        {
            int fieldIndex = dataReader.GetOrdinal(fieldName);
            return dataReader.IsDBNull(fieldIndex) ? 0 : dataReader.GetInt32(fieldIndex);
        }

        public static int? SafeGetNullableInt(this SqlDataReader dataReader, string fieldName)
        {
            int fieldIndex = dataReader.GetOrdinal(fieldName);
            return dataReader.GetValue(fieldIndex) as int?;
        }

        public static string SafeGetString(this SqlDataReader dataReader, string fieldName)
        {
            int fieldIndex = dataReader.GetOrdinal(fieldName);
            return dataReader.IsDBNull(fieldIndex) ? string.Empty : dataReader.GetString(fieldIndex);
        }

        public static DateTime? SafeGetNullableDateTime(this SqlDataReader dataReader, string fieldName)
        {
            int fieldIndex = dataReader.GetOrdinal(fieldName);
            return dataReader.GetValue(fieldIndex) as DateTime?;
        }

        public static bool SafeGetBoolean(this SqlDataReader dataReader, string fieldName)
        {
            return SafeGetBoolean(dataReader, fieldName, false);
        }

        public static bool SafeGetBoolean(this SqlDataReader dataReader, string fieldName, bool defaultValue)
        {
            int fieldIndex = dataReader.GetOrdinal(fieldName);
            return dataReader.IsDBNull(fieldIndex) ? defaultValue : dataReader.GetBoolean(fieldIndex);
        }
    }

Upvotes: 2

Project Mayhem
Project Mayhem

Reputation: 529

By influencing from getpsyched's answer, I created a generic method which checks column value by its name

public static T SafeGet<T>(this System.Data.SqlClient.SqlDataReader reader, string nameOfColumn)
{
  var indexOfColumn = reader.GetOrdinal(nameOfColumn);
  return reader.IsDBNull(indexOfColumn) ? default(T) : reader.GetFieldValue<T>(indexOfColumn);
}

Usage: 

var myVariable = SafeGet<string>(reader, "NameOfColumn")

Upvotes: 11

getpsyched
getpsyched

Reputation: 157

As an addition to the answer by marc_s, you can use a more generic extension method to get values from the SqlDataReader:

public static T SafeGet<T>(this SqlDataReader reader, int col)
    {
        return reader.IsDBNull(col) ? default(T) : reader.GetFieldValue<T>(col);
    }

Upvotes: 6

Ahmed Kamal
Ahmed Kamal

Reputation: 3000

Old question but maybe someone still need an answer

in real i worked around this issue like that

For int :

public static object GatDataInt(string Query, string Column)
    {
        SqlConnection DBConn = new SqlConnection(ConnectionString);
        if (DBConn.State == ConnectionState.Closed)
            DBConn.Open();
        SqlCommand CMD = new SqlCommand(Query, DBConn);
        SqlDataReader RDR = CMD.ExecuteReader();
        if (RDR.Read())
        {
            var Result = RDR[Column];
            RDR.Close();
            DBConn.Close();
            return Result;
        }
        return 0;
    }

the same for string just return "" instead of 0 as "" is empty string

so you can use it like 

int TotalPoints = GatDataInt(QueryToGetTotalPoints, TotalPointColumn) as int?;

and

string Email = GatDatastring(QueryToGetEmail, EmailColumn) as string;

very flexible so you can insert any query to read any column and it'll never return with error

Upvotes: 1

Usman Ali
Usman Ali

Reputation: 818

how to about creating helper methods

For String

private static string MyStringConverter(object o)
    {
        if (o == DBNull.Value || o == null)
            return "";

        return o.ToString();
    }

Usage

MyStringConverter(read["indexStringValue"])

For Int

 private static int MyIntonverter(object o)
    {
        if (o == DBNull.Value || o == null)
            return 0;

        return Convert.ToInt32(o);
    }

Usage

MyIntonverter(read["indexIntValue"])

For Date

private static DateTime? MyDateConverter(object o)
    {
        return (o == DBNull.Value || o == null) ? (DateTime?)null : Convert.ToDateTime(o);
    }

Usage

MyDateConverter(read["indexDateValue"])

Note: for DateTime declare varialbe as

DateTime? variable;

Upvotes: 4

Panayot Minkov
Panayot Minkov

Reputation: 21

You may use the conditional operator:

employee.FirstName = sqlreader["indexFirstName"] != DBNull.Value ? sqlreader[indexFirstName].ToString() : "";

Upvotes: 2

Vijai
Vijai

Reputation: 2509

You can write a Generic function to check Null and include default value when it is NULL. Call this when reading Datareader

public T CheckNull<T>(object obj)
        {
            return (obj == DBNull.Value ? default(T) : (T)obj);
        }

When reading the Datareader use

                        while (dr.Read())
                        {
                            tblBPN_InTrRecon Bpn = new tblBPN_InTrRecon();
                            Bpn.BPN_Date = CheckNull<DateTime?>(dr["BPN_Date"]);
                            Bpn.Cust_Backorder_Qty = CheckNull<int?>(dr["Cust_Backorder_Qty"]);
                            Bpn.Cust_Min = CheckNull<int?>(dr["Cust_Min"]);
                         }

Upvotes: 16

marc_s
marc_s

Reputation: 754220

You need to check for IsDBNull:

if(!SqlReader.IsDBNull(indexFirstName))
{
  employee.FirstName = sqlreader.GetString(indexFirstName);
}

That's your only reliable way to detect and handle this situation.

I wrapped those things into extension methods and tend to return a default value if the column is indeed null:

public static string SafeGetString(this SqlDataReader reader, int colIndex)
{
   if(!reader.IsDBNull(colIndex))
       return reader.GetString(colIndex);
   return string.Empty;
}

Now you can call it like this:

employee.FirstName = SqlReader.SafeGetString(indexFirstName);

and you'll never have to worry about an exception or a null value again.

Upvotes: 603

Summer-Time
Summer-Time

Reputation: 1874

This Solution is less vendor-dependent and works with an SQL, OleDB, and MySQL Reader:

public static string GetStringSafe(this IDataReader reader, int colIndex)
{
    return GetStringSafe(reader, colIndex, string.Empty);
}

public static string GetStringSafe(this IDataReader reader, int colIndex, string defaultValue)
{
    if (!reader.IsDBNull(colIndex))
        return reader.GetString(colIndex);
    else
        return defaultValue;
}

public static string GetStringSafe(this IDataReader reader, string indexName)
{
    return GetStringSafe(reader, reader.GetOrdinal(indexName));
}

public static string GetStringSafe(this IDataReader reader, string indexName, string defaultValue)
{
    return GetStringSafe(reader, reader.GetOrdinal(indexName), defaultValue);
}

Upvotes: 12

Ogglas
Ogglas

Reputation: 69928

This method is dependent on indexFirstName which should be the zero-based column ordinal.

if(!sqlReader.IsDBNull(indexFirstName))
{
  employee.FirstName = sqlreader.GetString(indexFirstName);
}

If you don't know the column index but wan't to check a name you can use this extension method instead:

public static class DataRecordExtensions
{
    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i=0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;
        }
        return false;
    }
}

And use the method like this:

if(sqlReader.HasColumn("FirstName"))
{
  employee.FirstName = sqlreader["FirstName"];
}

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146409

and / or use ternary operator with assignment:

employee.FirstName = rdr.IsDBNull(indexFirstName))? 
                     String.Empty: rdr.GetString(indexFirstName);

replace the default (when null) value as appropriate for each property type...

Upvotes: 1

ZXX
ZXX

Reputation: 4762

IsDbNull(int) is usually much slower than using methods like GetSqlDateTime and then comparing to DBNull.Value. Try these extension methods for SqlDataReader.

public static T Def<T>(this SqlDataReader r, int ord)
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return default(T);
    return ((INullable)t).IsNull ? default(T) : (T)t;
}

public static T? Val<T>(this SqlDataReader r, int ord) where T:struct
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return null;
    return ((INullable)t).IsNull ? (T?)null : (T)t;
}

public static T Ref<T>(this SqlDataReader r, int ord) where T : class
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return null;
    return ((INullable)t).IsNull ? null : (T)t;
}

Use them like this:

var dd = r.Val<DateTime>(ords[4]);
var ii = r.Def<int>(ords[0]);
int nn = r.Def<int>(ords[0]);

Upvotes: 27

xux
xux

Reputation: 11

private static void Render(IList<ListData> list, IDataReader reader)
        {
            while (reader.Read())
            {

                listData.DownUrl = (reader.GetSchemaTable().Columns["DownUrl"] != null) ? Convert.ToString(reader["DownUrl"]) : null;
                //没有这一列时，让其等于null
                list.Add(listData);
            }
            reader.Close();
        }

Upvotes: 1

el bayames
el bayames

Reputation: 187

I don't think there's a NULL column value, when rows are returned within a datareader using the column name.

If you do datareader["columnName"].ToString(); it will always give you a value that can be a empty string (String.Empty if you need to compare).

I would use the following and wouldn't worry too much:

employee.FirstName = sqlreader["columnNameForFirstName"].ToString();

Upvotes: 16

Tequila
Tequila

Reputation: 864

I am using the code listed below to handle null cells in an Excel sheet that is read in to a datatable.

if (!reader.IsDBNull(2))
{
   row["Oracle"] = (string)reader[2];
}

Upvotes: 1

scooby
scooby

Reputation: 603

you can ever check for this as well

if(null !=x && x.HasRows)
{ ....}

Upvotes: -3

alex
alex

Reputation: 953

What I tend to do is replace the null values in the SELECT statement with something appropriate.

SELECT ISNULL(firstname, '') FROM people

Here I replace every null with a blank string. Your code won't throw in error in that case.

Upvotes: 9

stevehipwell
stevehipwell

Reputation: 57468

You should use the as operator combined with the ?? operator for default values. Value types will need to be read as nullable and given a default.

employee.FirstName = sqlreader[indexFirstName] as string;
employee.Age = sqlreader[indexAge] as int? ?? default(int);

The as operator handles the casting including the check for DBNull.

Upvotes: 250

Michael Todd
Michael Todd

Reputation: 17041

One way to do it is to check for db nulls:

employee.FirstName = (sqlreader.IsDBNull(indexFirstName) 
    ? ""
    : sqlreader.GetString(indexFirstName));

Upvotes: 13

bytebender
bytebender

Reputation: 7491

I think you would want to use:

SqlReader.IsDBNull(indexFirstName)

Upvotes: 4

CesarGon
CesarGon

Reputation: 15325

Check sqlreader.IsDBNull(indexFirstName) before you try to read it.

Upvotes: 7

Sonny Boy
Sonny Boy

Reputation: 8016

We use a series of static methods to pull all of the values out of our data readers. So in this case we'd be calling DBUtils.GetString(sqlreader(indexFirstName)) The benefit of creating static/shared methods is that you don't have to do the same checks over and over and over...

The static method(s) would contain code to check for nulls (see other answers on this page).

Upvotes: 2

Related Questions