How to make optional parameter in parameterizaed query

Question

In my code bellow I use simple parameterized query.
It has a lot parameters and some can be NULL and some when null complain about FK.
How can I fix this to have optional parameters but without using stored procedure.
To be clear I want when parameter is null to avoid trying to update that field.

conn.Open();

                string sql = @"UPDATE UserProfile
                               SET FirstName = @p_FirstName
                                  ,LastName = @p_LastName
                                  ,Gender = @p_Gender
                                  ,DateOfBirth = @p_DateOfBirth
                                  ,CityId = @p_CityId
                                  ,MartialStatusId = @p_MartialStatusId
                                  ,ProfileImageId = @p_ProfileImageId
                             WHERE UserId = @p_UserId";

                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {                    
                    cmd.Parameters.AddWithValue("@p_FirstName", userProfile.FirstName);
                    cmd.Parameters.AddWithValue("@p_LastName", userProfile.LastName);
                    cmd.Parameters.AddWithValue("@p_Gender", userProfile.Gender);
                    cmd.Parameters.AddWithValue("@p_DateOfBirth", userProfile.DateOfBirth);
                    cmd.Parameters.AddWithValue("@p_CityId", userProfile.CityId);
                    cmd.Parameters.AddWithValue("@p_MartialStatusId", userProfile.MartialStatusId);
                    cmd.Parameters.AddWithValue("@p_ProfileImageId", userProfile.ProfileImageId);
                    cmd.Parameters.AddWithValue("@p_UserId", userProfile.UserId);

                    cmd.ExecuteNonQuery();

                }

Answer 1

You can construct your SQL so those optional parameters are never in it when they don't need to be:

var sql = new StringBuilder();
sql.Append(@"UPDATE UserProfile
               SET "); 

if(addFirstName) // <-- some boolean condition here
  sql.Append("FirstName = @p_FirstName, ");

// ... etc...

sql.Append(" WHERE UserId = @p_UserId");

Same with the actual parameters - don't add what you don't want to add.