C# 将 DBNull.Value 和 Empty 文本框值传递给数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12444115/
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
Passing DBNull.Value and Empty textbox value to database
提问by codingbiz
I have some textboxes on my page which can be empty because they are optional and I have this DAL code
我的页面上有一些文本框可以为空，因为它们是可选的，而且我有这个 DAL 代码
parameters.Add(new SqlParameter("@FirstName", FirstName));
parameters.Add(new SqlParameter("@LastName", LastName));
parameters.Add(new SqlParameter("@DisplayName", DisplayName));
parameters.Add(new SqlParameter("@BirthDate", BirthDate));
parameters.Add(new SqlParameter("@Gender", Gender));
Any of those fields can be empty. The problem is when they are empty I receive Procedure XXX requires @FirstName which was not supplied
这些字段中的任何一个都可以为空。问题是当它们为空时我收到Procedure XXX requires @FirstName which was not supplied
Then I changed my code to
然后我将代码更改为
parameters.Add(new SqlParameter("@FirstName", String.IsNullOrEmpty(FirstName) ? DBNull.Value : (object)FirstName));
parameters.Add(new SqlParameter("@LastName", String.IsNullOrEmpty(LastName) ? DBNull.Value : (object) LastName));
parameters.Add(new SqlParameter("@DisplayName", String.IsNullOrEmpty(DisplayName) ? DBNull.Value : (object) DisplayName));
parameters.Add(new SqlParameter("@BirthDate", BirthDate.HasValue ? (object)BirthDate.Value : DBNull.Value));
parameters.Add(new SqlParameter("@Gender", String.IsNullOrEmpty(Gender) ? DBNull.Value : (object) Gender));
But this looks messy to me especially the casting to objectbecause ternary statement requires both value to be the same type.
但这对我来说看起来很混乱，尤其是转换为 toobject因为三元语句要求两个值都是相同的类型。
Why is empty string or null string not treated NULLin the database? If I have to convert this to DBNull.Valueis there a cleaner way? Saving the value as empty string in the database could have helped but query for NULLin the database will get messy too
为什么NULL在数据库中没有处理空字符串或空字符串？如果我必须将其转换DBNull.Value为有更清洁的方法吗？将值保存为数据库中的空字符串可能会有所帮助，但NULL在数据库中查询也会变得混乱
Please give your advice on common practices or something close to that.
请就常见做法或类似做法提出您的建议。
采纳答案by abatishchev
First, there are 2 more handy overloads:
首先，还有 2 个更方便的重载：
command.Parameters.Add("@name").Value = value;
or
或者
command.Parameters.AddWithValue("@name", value);
Personally I use the following extension method:
我个人使用以下扩展方法：
public static object DbNullIfNull(this object obj)
{
return obj != null ? obj : DBNull.Value;
}
command.Parameters.AddWithValue("@name", value.DbNullIfNull());
or
或者
public static object DbNullIfNullOrEmpty(this string str)
{
return !String.IsNullOrEmpty(str) ? str : (object)DBNull.Value;
}
回答by Mufaka
You can default the parameters in the stored procedure, making them optional.
您可以默认存储过程中的参数，使它们成为可选的。
create procedure XXX
(
@FirstName nvarchar(50) = null,
@LastName nvarchar(50) = null,
...
)
回答by naveen
A little re-factoring might make code less messy. Try this
一些重构可能会使代码不那么混乱。尝试这个
dbParams.Add(SetDBNullIfEmpty("@FirstName", FirstName));
dbParams.Add(SetDBNullIfEmpty("@LastName", LastName));
dbParams.Add(SetDBNullIfEmpty("@DisplayName", DisplayName));
dbParams.Add(SetDBNullIfEmpty("@BirthDate", BirthDate));
dbParams.Add(SetDBNullIfEmpty("@Gender", Gender));
private SqlParameter SetDBNullIfEmpty(string parmName, string parmValue)
{
return new SqlParameter(parmName, String.IsNullOrEmpty(parmValue) ? DBNull.Value : (object)parmValue));
}
