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?
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?
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);
Yes, for the value of the parameter, just use DBNull.Value
. For example:
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));