将 DBNull.Value 和 Empty 文本框值传递给数据库

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

本文介绍了将 DBNull.Value 和 Empty 文本框值传递给数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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 文本框值传递给数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:将 DBNull.Value 和 Empty 文本框值传递给数据库

基础教程推荐