C# SQL 数据读取器 - 处理空列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1772025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Data Reader - handling Null column values
提问by DenaliHardtail
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 从数据库构建 POCO。除非在数据库中遇到空值，否则代码会起作用。例如，如果数据库中的 FirstName 列包含空值，则会引发异常。
employee.FirstName = sqlreader.GetString(indexFirstName);
What is the best way to handle null values in this situation?
在这种情况下处理空值的最佳方法是什么？
采纳答案by marc_s
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:
我将这些东西包装到扩展方法中，如果列确实是，则倾向于返回默认值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 nullvalue again.
并且您再也不必担心异常或null值。
回答by Michael Todd
One way to do it is to check for db nulls:
一种方法是检查数据库空值：
employee.FirstName = (sqlreader.IsDBNull(indexFirstName)
? ""
: sqlreader.GetString(indexFirstName));
回答by Sonny Boy
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).
静态方法将包含检查空值的代码（请参阅此页面上的其他答案）。
回答by alex
What I tend to do is replace the null values in the SELECT statement with something appropriate.
我倾向于做的是用适当的东西替换 SELECT 语句中的空值。
SELECT ISNULL(firstname, '') FROM people
Here I replace every null with a blank string. Your code won't throw in error in that case.
在这里，我用一个空字符串替换了每个空值。在这种情况下，您的代码不会出错。
回答by Charles Bretana
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...
根据每个属性类型替换默认值（当为空时）...
回答by CesarGon
Check sqlreader.IsDBNull(indexFirstName)before you try to read it.
sqlreader.IsDBNull(indexFirstName)在尝试阅读之前先检查一下。
回答by bytebender
I think you would want to use:
我想你会想使用：
SqlReader.IsDBNull(indexFirstName)
回答by ZXX
IsDbNull(int)is usually much slower than using methods like GetSqlDateTimeand 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]);
回答by stevehipwell
You should use the asoperator combined with the ??operator for default values. Value types will need to be read as nullable and given a default.
您应该将as运算符与??默认值的运算符结合使用。值类型需要被读取为可为空并给出默认值。
employee.FirstName = sqlreader[indexFirstName] as string;
employee.Age = sqlreader[indexAge] as int? ?? default(int);
The asoperator handles the casting including the check for DBNull.
该as运营商处理的铸造，包括支票的DBNull。
回答by scooby
you can ever check for this as well
你也可以检查这个
if(null !=x && x.HasRows)
{ ....}
