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

问题描述

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

Any of those fields can be empty. The problem is when they are empty I receive 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 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

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

解决方案

First, there are 2 more handy overloads:

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

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