While NULL is a standard feature and business-as-usual in the SQL world, and likewise null objects are common in the C# world, the concept of NULL and null are completely different things and the translation is easily forgotten. This is particularly important when correlating a data-aware C# object to a SQL database.
Assigning NULL to a C# SqlCommand.SqlParameter, where the column mes_route_step_id allows NULL values:
private static readonly string SQL_Insert =
"INSERT INTO aca_wip_tracked_steps ( "
+ "route_step_name "
+ ",mes_route_step_id "
+ ",aca_wip_tracked_stepgroup_id "
+ ") "
+ " OUTPUT INSERTED.aca_wip_tracked_step_id "
+ " VALUES "
+ "( "
+ "@route_step_name "
+ ",@mes_route_step_id "
+ ",@aca_wip_tracked_stepgroup_id "
+ ") ";SqlCommand sc = new SqlCommand();
sc.CommandText = SQL_Insert;
sc.Parameters.Add("@route_step_name", System.Data.SqlDbType.NVarChar, 255).Value
= _route_step_name;
sc.Parameters.Add("@mes_route_step_id", System.Data.SqlDbType.UniqueIdentifier).Value
= _mes_route_step_id.Equals(String.Empty)
? System.Data.SqlTypes.SqlGuid.Null
: new Guid(_mes_route_step_id);
sc.Parameters.Add("@aca_wip_tracked_stepgroup_id", System.Data.SqlDbType.UniqueIdentifier).Value
= _aca_wip_tracked_stepgroup_id.Equals(String.Empty)
? System.Data.SqlTypes.SqlGuid.Null
: new Guid(_aca_wip_tracked_stepgroup_id);
Assigning NULL to the Datatable that correlates to the database:
if (flagInsertingNewRecord)
{
DataRow row = dt.NewRow();
row["aca_wip_tracked_step_id"] = insertedID;
row["route_step_name"] = route_step_name;
if (mes_route_step_id.Equals(String.Empty))
row["mes_route_step_id"] = System.DBNull.Value;
else
row["mes_route_step_id"] = mes_route_step_id;
row["aca_wip_tracked_stepgroup_id"] = lblAcaWipTrackedStepGroupId.Text;
dt.Rows.Add(row);
}
else
{
for (int row = 0; row < dt.Rows.Count; row++)
{
if (dt.Rows[row]["aca_wip_tracked_step_id"].ToString().Equals(insertedID))
{
dt.Rows[row]["route_step_name"] = route_step_name;
if (mes_route_step_id.Equals(String.Empty))
dt.Rows[row]["mes_route_step_id"] = System.DBNull.Value;
else
dt.Rows[row]["mes_route_step_id"] = mes_route_step_id;
dt.Rows[row]["aca_wip_tracked_stepgroup_id"] = lblAcaWipTrackedStepGroupId.Text;
break; // early exit of for loop.
}
}
}