通过DBNull。数据库的值和空文本框值[duplicate]

This question already has an answer here:

这个问题已经有了答案:

How do I Parameterize a null string with DBNull.Value clearly and quickly 8 answers
如何用DBNull参数化一个空字符串。价值清楚且快速地回答8个问题。

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

这些字段中的任何一个都可以为空。问题是当它们为空时，我接收到的过程XXX需要@FirstName，但没有提供

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 object because ternary statement requires both value to be the same type.

但这在我看来很混乱，尤其是对象转换，因为三元语句要求两个值都是相同的类型。

Why is empty string or null string not treated NULL in the database? If I have to convert this to DBNull.Value is there a cleaner way? Saving the value as empty string in the database could have helped but query for NULL in the database will get messy too

为什么空字符串或空字符串在数据库中没有被处理为空?如果我要把它转换成DBNull。有更干净的方法吗?将值保存为数据库中的空字符串可能会有所帮助，但在数据库中查询NULL也会变得很麻烦

Please give your advice on common practices or something close to that.

请就常见的做法或类似的事情给出你的建议。

3 个解决方案

#1

First, there are 2 more handy overloads:

首先，有两个更方便的重载:

command.Parameters.Add("@name").Value = value;

或

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

或

public static object DbNullIfNullOrEmpty(this string str)
{
    return !String.IsNullOrEmpty(str) ? str : (object)DBNull.Value;
}

#2

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

#3

You can default the parameters in the stored procedure, making them optional.

您可以在存储过程中默认这些参数，使它们成为可选的。

create procedure XXX
(
   @FirstName nvarchar(50) = null,
   @LastName nvarchar(50) = null,
   ...
)

#1