I got tired of writing the following code:
我厌倦了写下面的代码:
/* Commenting out irrelevant parts
public string MiddleName;
public void Save(){
SqlCommand = new SqlCommand();
// blah blah...boring INSERT statement with params etc go here. */
if(MiddleName==null){
myCmd.Parameters.Add("@MiddleName", DBNull.Value);
}
else{
myCmd.Parameters.Add("@MiddleName", MiddleName);
}
/*
// more boring code to save to DB.
}*/
So, I wrote this:
所以,我写这个:
public static object DBNullValueorStringIfNotNull(string value)
{
object o;
if (value == null)
{
o = DBNull.Value;
}
else
{
o = value;
}
return o;
}
// which would be called like:
myCmd.Parameters.Add("@MiddleName", DBNullValueorStringIfNotNull(MiddleName));
If this is a good way to go about doing this then what would you suggest as the method name? DBNullValueorStringIfNotNull is a bit verbose and confusing.
如果这是一种很好的方法,那么您认为方法名是什么?DBNullValueorStringIfNotNull有点冗长和混乱。
I'm also open to ways to alleviate this problem entirely. I'd LOVE to do this:
我也有办法完全缓解这个问题。我很乐意这样做:
myCmd.Parameters.Add("@MiddleName", MiddleName==null ? DBNull.Value : MiddleName);
but that won't work because the "Operator '??' cannot be applied to operands of type 'string and 'System.DBNull'".
但这行不通,因为“操作者”?'不能应用于'string和'System.DBNull'类型的操作数。
I've got C# 3.5 and SQL Server 2005 at my disposal if it matters.
如果有问题的话,我有c# 3.5和SQL Server 2005。
8 个解决方案
#1
50
Cast either of your values to object
and it will compile.
将你的任何一个值转换为对象,它将被编译。
myCmd.Parameters.Add("@MiddleName", MiddleName==null ? (object)DBNull.Value : MiddleName);
#2
15
You can avoid the explicit cast to object
using SqlString.Null
instead of DBNull.Value
:
您可以避免使用SqlString对对象进行显式转换。Null代替DBNull.Value:
MiddleName ?? SqlString.Null
There are corresponding types for int, datetime, and so forth. Here's a code snippet with a couple more examples:
int、datetime等等都有相应的类型。这里有一个代码片段,还有几个例子:
cmd.Parameters.AddWithValue("@StartDate", StartDate ?? SqlDateTime.Null);
cmd.Parameters.AddWithValue("@EndDate", EndDate ?? SqlDateTime.Null);
cmd.Parameters.AddWithValue("@Month", Month ?? SqlInt16.Null);
cmd.Parameters.AddWithValue("@FormatID", FormatID ?? SqlInt32.Null);
cmd.Parameters.AddWithValue("@Email", Email ?? SqlString.Null);
cmd.Parameters.AddWithValue("@ZIP", ZIP ?? SqlBoolean.Null);
#3
11
Personally this is what I would do with an extension method (make sure this goes into a static class)
就我个人而言,这就是我对扩展方法所做的(确保它进入一个静态类)
public static object GetStringOrDBNull(this string obj)
{
return string.IsNullOrEmpty(obj) ? DBNull.Value : (object) obj
}
Then you'd have
然后你会
myCmd.Parameters.Add("@MiddleName", MiddleName.GetStringOrDBNull());
#4
6
myCmd.Parameters.Add("@MiddleName", MiddleName ?? (object)DBNull.Value);
#5
2
@David Thanks for your suggestion. The following method works great!
谢谢你的建议。下面的方法非常有用!
MiddleName ?? (object)DBNull.Value
#6
1
Yeap, we'd all love to do myCmd.Parameters.Add("@MiddleName", MiddleName ?? DBNull.Value);
. Or better still, have the freakin' SqlClient layer understand that CLR null
should be mapped to DBNull.Value
when adding a parameter. Unfortunately the .Net type system closes the first alternative, and the implementation of SqlClient closes the second.
是的,我们都喜欢做mycmd .参数。Add(“@MiddleName MiddleName ? ?DBNull.Value);。或者更好的是,让freakin' SqlClient层理解CLR null应该映射到DBNull。添加参数时的值。不幸的是。net类型系统关闭了第一个选项,SqlClient的实现关闭了第二个选项。
I'd go with a well known function name, like Coalesce or IsNull. Any DB developer will recognize what they do in an instant, from the name alone.
我将使用一个众所周知的函数名,比如Coalesce或IsNull。任何DB开发人员都能一眼就认出他们所做的事情。
#7
1
I'd rather give you two totally different suggestions:
我宁愿给你两个完全不同的建议:
-
Use an ORM. There are plenty of non-intrusive ORM tools.
使用ORM。有大量的非侵入性ORM工具。
-
Write your own wrapper for building commands, with a cleaner interface. Something like:
为构建命令编写自己的包装器,使用更简洁的接口。喜欢的东西:
public class MyCommandRunner { private SqlCommand cmd; public MyCommandRunner(string commandText) { cmd = new SqlCommand(commandText); } public void AddParameter(string name, string value) { if (value == null) cmd.Parameters.Add(name, DBNull.Value); else cmd.Parameters.Add(name, value); } // ... more AddParameter overloads }
If you rename your AddParameter
methods to just Add
, you can use it in a very slick way:
如果您将AddParameter方法重命名为Add,您可以非常巧妙地使用它:
var cmd = new MyCommand("INSERT ...")
{
{ "@Param1", null },
{ "@Param2", p2 }
};
#8
1
I would suggest using nullable properties instead of public fields and an 'AddParameter' method (don't know if this code is optimized or correct, just off the top of my head):
我建议使用nullable属性,而不是public字段和“AddParameter”方法(不知道这段代码是否经过了优化或正确,我不知道):
private string m_MiddleName;
public string MiddleName
{
get { return m_MiddleName; }
set { m_MiddleName = value; }
}
.
.
.
public static void AddParameter(SQLCommand cmd, string parameterName, SQLDataType dataType, object value)
{
SQLParameter param = cmd.Parameters.Add(parameterName, dataType);
if (value is string) { // include other non-nullable datatypes
if (value == null) {
param.value = DBNull.Value;
} else {
param.value = value;
}
} else {
// nullable data types
// UPDATE: HasValue is for nullable, not object type
if (value.HasValue) // {{{=====================================================
{
param.value = value;
} else
{
param.value = DBNull.Value;
}
}
}
.
.
.
AddParameter(cmd, "@MiddleName", SqlDbType.VarChar, MiddleName);
#1
50
Cast either of your values to object
and it will compile.
将你的任何一个值转换为对象,它将被编译。
myCmd.Parameters.Add("@MiddleName", MiddleName==null ? (object)DBNull.Value : MiddleName);
#2
15
You can avoid the explicit cast to object
using SqlString.Null
instead of DBNull.Value
:
您可以避免使用SqlString对对象进行显式转换。Null代替DBNull.Value:
MiddleName ?? SqlString.Null
There are corresponding types for int, datetime, and so forth. Here's a code snippet with a couple more examples:
int、datetime等等都有相应的类型。这里有一个代码片段,还有几个例子:
cmd.Parameters.AddWithValue("@StartDate", StartDate ?? SqlDateTime.Null);
cmd.Parameters.AddWithValue("@EndDate", EndDate ?? SqlDateTime.Null);
cmd.Parameters.AddWithValue("@Month", Month ?? SqlInt16.Null);
cmd.Parameters.AddWithValue("@FormatID", FormatID ?? SqlInt32.Null);
cmd.Parameters.AddWithValue("@Email", Email ?? SqlString.Null);
cmd.Parameters.AddWithValue("@ZIP", ZIP ?? SqlBoolean.Null);
#3
11
Personally this is what I would do with an extension method (make sure this goes into a static class)
就我个人而言,这就是我对扩展方法所做的(确保它进入一个静态类)
public static object GetStringOrDBNull(this string obj)
{
return string.IsNullOrEmpty(obj) ? DBNull.Value : (object) obj
}
Then you'd have
然后你会
myCmd.Parameters.Add("@MiddleName", MiddleName.GetStringOrDBNull());
#4
6
myCmd.Parameters.Add("@MiddleName", MiddleName ?? (object)DBNull.Value);
#5
2
@David Thanks for your suggestion. The following method works great!
谢谢你的建议。下面的方法非常有用!
MiddleName ?? (object)DBNull.Value
#6
1
Yeap, we'd all love to do myCmd.Parameters.Add("@MiddleName", MiddleName ?? DBNull.Value);
. Or better still, have the freakin' SqlClient layer understand that CLR null
should be mapped to DBNull.Value
when adding a parameter. Unfortunately the .Net type system closes the first alternative, and the implementation of SqlClient closes the second.
是的,我们都喜欢做mycmd .参数。Add(“@MiddleName MiddleName ? ?DBNull.Value);。或者更好的是,让freakin' SqlClient层理解CLR null应该映射到DBNull。添加参数时的值。不幸的是。net类型系统关闭了第一个选项,SqlClient的实现关闭了第二个选项。
I'd go with a well known function name, like Coalesce or IsNull. Any DB developer will recognize what they do in an instant, from the name alone.
我将使用一个众所周知的函数名,比如Coalesce或IsNull。任何DB开发人员都能一眼就认出他们所做的事情。
#7
1
I'd rather give you two totally different suggestions:
我宁愿给你两个完全不同的建议:
-
Use an ORM. There are plenty of non-intrusive ORM tools.
使用ORM。有大量的非侵入性ORM工具。
-
Write your own wrapper for building commands, with a cleaner interface. Something like:
为构建命令编写自己的包装器,使用更简洁的接口。喜欢的东西:
public class MyCommandRunner { private SqlCommand cmd; public MyCommandRunner(string commandText) { cmd = new SqlCommand(commandText); } public void AddParameter(string name, string value) { if (value == null) cmd.Parameters.Add(name, DBNull.Value); else cmd.Parameters.Add(name, value); } // ... more AddParameter overloads }
If you rename your AddParameter
methods to just Add
, you can use it in a very slick way:
如果您将AddParameter方法重命名为Add,您可以非常巧妙地使用它:
var cmd = new MyCommand("INSERT ...")
{
{ "@Param1", null },
{ "@Param2", p2 }
};
#8
1
I would suggest using nullable properties instead of public fields and an 'AddParameter' method (don't know if this code is optimized or correct, just off the top of my head):
我建议使用nullable属性,而不是public字段和“AddParameter”方法(不知道这段代码是否经过了优化或正确,我不知道):
private string m_MiddleName;
public string MiddleName
{
get { return m_MiddleName; }
set { m_MiddleName = value; }
}
.
.
.
public static void AddParameter(SQLCommand cmd, string parameterName, SQLDataType dataType, object value)
{
SQLParameter param = cmd.Parameters.Add(parameterName, dataType);
if (value is string) { // include other non-nullable datatypes
if (value == null) {
param.value = DBNull.Value;
} else {
param.value = value;
}
} else {
// nullable data types
// UPDATE: HasValue is for nullable, not object type
if (value.HasValue) // {{{=====================================================
{
param.value = value;
} else
{
param.value = DBNull.Value;
}
}
}
.
.
.
AddParameter(cmd, "@MiddleName", SqlDbType.VarChar, MiddleName);