传递和的DBNull.Value空文本框的值与数据库 [英] Passing DBNull.Value and Empty textbox value to database

问题描述

我有我的网页一些文本框可以是空的，因为它们是可选的，我有这个DAL code

I have some textboxes on my page which can be empty because they are optional and I have this DAL code

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

所有这些领域中可以为空。问题是，当它们是空的，我收到程序XXX需要@firstName但未提供

然后，我改变了我的code到

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.

为什么空字符串或空字符串不及时治疗 NULL 数据库？如果非要将其转换为的DBNull.Value 有一个更清洁的方式？保存值在数据库空字符串可以帮助，但在数据库中查询 NULL 将变得混乱太

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

请上常见的做法或一些接近，让你的意见。

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

推荐答案

首先，有2更加得心应手重载：

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

这篇关于传递和的DBNull.Value空文本框的值与数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！