SQL数据读取器-处理空列值。

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.

我使用SQLdatareader从数据库构建POCOs。除非在数据库中遇到null值，否则代码是有效的。例如，如果数据库中的FirstName列包含空值，则抛出异常。

employee.FirstName = sqlreader.GetString(indexFirstName);

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

在这种情况下处理null值的最好方法是什么?

21 个解决方案

#1

You need to check for IsDBNull:

您需要检查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.

您应该使用as操作符与??运营商为默认值。值类型需要被读取为nullable，并且给定一个默认值。

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.

作为操作符处理转换，包括检查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:

对于一个字符串，您可以简单地转换对象版本(使用数组操作符访问)并使用null字符串结束:

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

对于整数，如果将其转换为可空整数，则可以使用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.

IsDbNull(int)通常比使用GetSqlDateTime等方法要慢得多，然后与DBNull.Value进行比较。为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:

一种方法是检查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.

我认为在使用列名称的datareader中返回行时，没有空列值。

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

如果你datareader(“columnName”).ToString();它总是给你一个可以是空字符串(字符串)的值。如果你需要比较的话，空着。

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

我会用下面的方法，不用太担心:

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

#7

reader.IsDbNull(ColumnIndex) works as many answers says.

isdbnull (ColumnIndex)可以像许多答案一样工作。

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 }

#8

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

这个解决方案不依赖于供应商，并且使用SQL、OleDB和MySQL阅读器:

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

#9

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

我要做的是用适当的东西替换SELECT语句中的null值。

SELECT ISNULL(firstname, '') FROM people

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

这里，我用一个空字符串替换所有null。在那种情况下，你的代码不会出错。

#10

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

在尝试读取它之前，检查sqlreader.IsDBNull(indexFirstName)。

#11

I think you would want to use:

我想你会想用:

SqlReader.IsDBNull(indexFirstName)

#12

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

您可以编写一个泛型函数来检查Null，并在它为Null时包含默认值。在阅读Datareader时调用它。

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

When reading the Datareader use

读取Datareader使用时。

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

#13

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

我们使用一系列静态方法从数据读取器中提取所有值。因此，在本例中，我们将调用DBUtils.GetString(sqlreader(indexFirstName))创建静态/共享方法的好处是，您不必一次又一次地进行相同的检查。

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

静态方法(s)将包含用于检查nulls的代码(请参阅本页面上的其他答案)。

#14

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，声明变量为。

DateTime? variable;

#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

字符串只是返回“”，而不是0作为“”是空字符串。

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.

我正在使用下面列出的代码来处理一个Excel表中的空单元格，它被读入一个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...

将缺省值(当null)替换为适合每个属性类型的值…

#19

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

这个方法依赖于indexFirstName，它应该是从零开始的列序数。

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

you can ever check for this as well

你也可以检查一下。

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