mercoledì 7 novembre 2012

Delete Commad with Null Parameter Value

Sometimes we need to make where conditions with null value.
If we use Command Paramenters the first thing we make is:

       SqlCommand.CommandText = "DELETE table WHERE field1 = @param1";

       SqlCommand.Parameters.Add(new SqlParameter("@param1"DBNull.Value)

Your problem is that you cannot use the '=' operator in SQL for null value.  
Instead, you need to generate different SQL for this case.
See the following example:

public void DeleteYourRecord(SqlConnection con, string param1, string param2, string param3)
            SqlCommand com = new SqlCommand();
            com.Connection = con;

            com.Connection = con;
            com.CommandText = "DELETE table" +
                              "WHERE field1 = @param1 AND field2 = @param2 ";

            com.Parameters.Add(new SqlParameter("@param1", param1));
            com.Parameters.Add(new SqlParameter("@param2", param2));

            if (param3.Trim() == "")
                com.CommandText += " AND field3 is null";
                com.CommandText += " AND field3 = @param3";
                com.Parameters.Add(new SqlParameter("@param3", param3));


My Two Cents...