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);
If you've opened a table and you want to clear an existing value to NULL, click on the value, and press Ctrl + 0 .
The rule for assigning NULL values to variables or table columns is simple: Use keyword "NULL" directly as normal values. Specificly, "NULL" can be used in SET statements to assign NULL values to variables. "NULL" can be used in SET clauses in UPDATE statements.
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With