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

What is the best way to handle null values in this situation?

回答1:

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.

回答2:

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.

回答3:

For a string you can simply cast the object version (accessed using the array operator) and wind up with a null string for nulls:

employee.FirstName = (string)sqlreader[indexFirstName];

or

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;

回答4:

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

回答5:

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

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

回答6:

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

回答7:

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

回答8:

reader.IsDbNull(ColumnIndex) 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 }

回答9:

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.

回答10:

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

回答11:

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\"]);
                         }

回答12:

I think you would want to use:

SqlReader.IsDBNull(indexFirstName)

回答13:

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;

回答14:

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

回答15:

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

回答16:

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

回答17:

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

回答18:

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...

回答19:

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\"];
}

回答20:

You may use the conditional operator:

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

回答21:

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

回答22:

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\")

回答23:

you can ever check for this as well

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