SQL COALESCE UPDATE in C# function

Question

I have a function in C# that I want to update my database. This is connected to a windows form with textboxes. I want to ensure that if someone wants to UPDATE only one or two values, the rest doesn't get replaced with empty strings, but rather retains what is already there.

sqlStr = "UPDATE [CRONUS Sverige AB$Employee] SET [First Name] = COALESCE(@param2, [First Name]), [Last Name] = COALESCE(@param3, [Last Name]), " +
    "[Address] = COALESCE(@param4, [Address]), [E-Mail] = COALESCE(@param5, [E-Mail]), [Social Security No_] = COALESCE(@param6, [Social Security No_]) WHERE [No_] = @param1";

sqlCom = new SqlCommand(sqlStr, con);
sqlCom.Parameters.AddWithValue("@param1", id);
sqlCom.Parameters.AddWithValue("@param2", fName);
sqlCom.Parameters.AddWithValue("@param3", lName);
sqlCom.Parameters.AddWithValue("@param4", adress);
sqlCom.Parameters.AddWithValue("@param5", email);
sqlCom.Parameters.AddWithValue("@param6", ssn);
sqlCom.ExecuteNonQuery();

The problem is that if I now try to input a null value in for example fName, I get this error:

System.Data.SqlClient.SqlException (0x80131904): The parameterized query '(@param1 nvarchar(2),@param2 nvarchar(4000),@param3 nvarchar(11)' expects the parameter '@param2', which was not supplied.

Any ideas about how to solve this? I'm very grateful for any and all help :).

Answer 1

The issue here is a null value in C# is not the same thing as a NULL value in SQL Server. One option here might be to use the null coalescing operator ?? when adding the parameters, e.g.

sqlStr = "UPDATE [CRONUS Sverige AB$Employee] SET [First Name] = COALESCE(@param2, [First Name]), [Last Name] = COALESCE(@param3, [Last Name]), " +
    "[Address] = COALESCE(@param4, [Address]), [E-Mail] = COALESCE(@param5, [E-Mail]), [Social Security No_] = COALESCE(@param6, [Social Security No_]) WHERE [No_] = @param1";

sqlCom = new SqlCommand(sqlStr, con);
sqlCom.Parameters.AddWithValue("@param1", ((object)id) ?? DBNull.Value);
sqlCom.Parameters.AddWithValue("@param2", ((object)fName) ?? DBNull.Value);
sqlCom.Parameters.AddWithValue("@param3", ((object)lName) ?? DBNull.Value);
sqlCom.Parameters.AddWithValue("@param4", ((object)adress) ?? DBNull.Value);
sqlCom.Parameters.AddWithValue("@param5", ((object)email) ?? DBNull.Value);
sqlCom.Parameters.AddWithValue("@param6", ((object)ssn) ?? DBNull.Value);
sqlCom.ExecuteNonQuery();

The modified logic above now will map a null value in C# to an actual NULL value in SQL Server.