C# 将 NULL 值分配给 SqlParameter 的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13981281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best method of assigning NULL value to SqlParameter
提问by NealR
I have a number of optional input parameters I am using in a C# class method. Since the optional syntax creates a value of '0' when the parameter is not used, the SQL insert command I call in the method winds up inserting as such. However, I need the command to insert a NULL value instead of a 0 when the parameter is not being used. What is the best way to accomplish this without using a large amount of 'if' statements?
我在 C# 类方法中使用了许多可选的输入参数。由于可选语法在不使用参数时创建值 '0'，因此我在该方法中调用的 SQL 插入命令最终会插入。但是，当不使用参数时，我需要该命令插入一个 NULL 值而不是 0。在不使用大量“if”语句的情况下完成此操作的最佳方法是什么？
Below is the code I am referring to. Is there syntax/a command of some kind that will allow me to specify a NULL value in the SqlParameter declaration?
下面是我所指的代码。是否有某种语法/命令允许我在 SqlParameter 声明中指定一个 NULL 值？
public int batchInsert
(
int id,
int outcome,
int input = 0,
int add = 0,
int update = 0,
int delete = 0,
int errors = 0,
int warnings = 0
)
{
string sts;
if (outcome == 0)
{
sts = "S";
}
else if (outcome == 1)
{
sts = "W";
}
else
{
sts = "E";
}
SqlConnection sqlConn = new SqlConnection(this.connString);
SqlParameter runId = new SqlParameter("@runId", id);
SqlParameter endTime = new SqlParameter("@endTime", DateTime.Now);
SqlParameter status = new SqlParameter("@status", sts);
SqlParameter sqlInput = new SqlParameter("@itemsRead", input);
SqlParameter sqlAdd = new SqlParameter("@add", add);
SqlParameter sqlUpdate = new SqlParameter("@update", update);
SqlParameter sqlDelete = new SqlParameter("@delete", delete);
SqlParameter sqlError = new SqlParameter("@errors", errors);
SqlParameter sqlWarning = new SqlParameter("@warnings", warnings);
SqlParameter result = new SqlParameter("@outcome", results[outcome]);
SqlCommand sqlComm = new SqlCommand(insertCommand(), sqlConn);
采纳答案by Eric Petroelje
Yes, for the value of the parameter, just use DBNull.Value. For example:
是的，对于参数的值，只需使用DBNull.Value. 例如：
SqlParameter sqlError =
new SqlParameter("@errors", errors == 0 ? (object)DBNull.Value : errors);
Or write a little helper:
或者写一个小帮手：
private object ValueOrDBNullIfZero(int val) {
if ( val == 0 ) return DBNull.Value;
return val;
}
Then:
然后：
SqlParameter sqlError =
new SqlParameter("@errors", ValueOrDBNullIfZero(errors));
回答by moribvndvs
You'd need to check each parameter value and use DBNull.Valuesend null. We have an extension method off objectto make this a little easier.
您需要检查每个参数值并使用DBNull.Value发送空值。我们有一个扩展方法 offobject使这更容易一些。
public static class ObjectExtensions
{
public static object OptionalParam<T>(this T value, T optionalValue = default(T))
{
return Equals(value,optionalValue) ? (object)DBNull.Value : value;
}
}
Usage:
用法：
var sqlInput = new SqlParameter("@itemsRead", input.OptionalParam());
Or if you want to consider a non-default, arbitrary value as the default value:
或者，如果您想考虑一个非默认的任意值作为默认值：
var sqlInput = new SqlParameter("@itemsRead", input.OptionalParam(-1));
回答by Dillie-O
Consider using the Nullable(T)structure available. It'll let you only set values if you have them, and your SQL Command objects will recognize the nullable value and process accordingly with no hassle on your end.
考虑使用可用的Nullable(T)结构。如果您拥有它们，它只会让您设置值，并且您的 SQL 命令对象将识别可为空的值并进行相应的处理，而您最终不会遇到麻烦。
回答by kavali rakesh
with small helper class we can create extenction method to add DBNull.Value to sqlparameter
使用小助手类，我们可以创建扩展方法将 DBNull.Value 添加到 sqlparameter
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
namespace System.Data.SqlClient
{
public static class ExtensionMethods
{
public static SqlParameter AddParameter(this SqlParameterCollection parms, SqlParameter param)
{
if (param.Value == null)
{
param.Value = DBNull.Value;
return parms.Add(param);
}
else
{
return parms.Add(param);
}
}
}
usage
用法
SqlParameter _FraudPackageID = new SqlParameter("@FraudPackageID", System.Data.SqlDbType.BigInt);
_FraudPackageID.Value = FraudPackageID;
_FraudPackageID.Direction = System.Data.ParameterDirection.Input;
_cmd.Parameters.AddParameter(_FraudPackageID);
if you assign or pass null as value to sqlparameter this extenction method will automatically converts it to DBNull and reassign to the sqlparameter.
如果您将 null 作为值分配或传递给 sqlparameter，则此扩展方法将自动将其转换为 DBNull 并重新分配给 sqlparameter。
so no need to worry about what the values we are passing dynamically.
所以无需担心我们动态传递的值是什么。
回答by GoAntonio
short syntax!
简短的语法！
cmd.Parameters.Add(new SqlParameter{SqlValue=username ?? (object)DBNull.Value,ParameterName="usuario" } );
回答by Parsa
This is the easiest way to assign a Null value to sql parameter
这是为 sql 参数分配 Null 值的最简单方法
command.Parameters.AddWithValue("@Comment", string.IsNullOrEmpty(comment) ? (object)DBNull.Value : comment);
回答by daniele3004
Another clear way to set a null datetime parameter when necessary
必要时设置空日期时间参数的另一种明确方法
if(obj.myDate == DateTime.MinValue)
{
aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = DBNull.Value;
}
else
{
aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = obj.myDate ;
}
