Solution of SQL parameter passing in null value to report error in C

null in C # is different from NULL in SQL, and NULL in SQL is represented by C # as DBNull. Value.

Note: The SQL parameter cannot accept the null value of C #. If null is passed in, an error will be reported.

Let's look at an example:

 SqlCommand cmd=new SqlCommand("Insert into Student values(@StuName,@StuAge)" ,conn);
 cmd.parameters.add("@StuName" ,stuName);
 cmd.parameters.add("@StuAge" ,stuAge);
 cmd.ExecuteNonQuery();

There is nothing wrong with the above code. In fact, when the value of stuName or stuAge is null, an exception will be thrown. Then how to solve it?

Solution: Pass in DBNull. Value when the value of stuName or stuAge is null. Below, write a static general method in the public class to judge the passed parameter value. If it is null, it will return DBNull. Value, otherwise it will return the original value.

public static object SqlNull(object obj)
 {
  if(obj == null)
  {
   return DBNull.Value;
  }
  else
  {
   return obj;
  }  
 }

The code after calling the above method is as follows:

 SqlCommand cmd=new SqlCommand("Insert into Student values(@StuName,@StuAge)" ,conn);
 cmd.parameters.add("@StuName" ,SqlNull(stuName));
 cmd.parameters.add("@StuAge" ,SqlNull(stuAge));
 cmd.ExecuteNonQuery();

In addition, if the parameter value comes from the value of a control (such as a text box), the parameter value passed in will not be null (because the value of the control will not be null, and even if there is no value, it will be ""). If you want to realize that the data table field value is NULL when the control value is "" (such as no input characters in the text box), you only need to modify the SqlNull method:

 public static object SqlNull(object obj)
 {
   if(obj == null || obj.ToString() == "")
   {
    return DBNull.Value;
   }
   else
   {
    return obj;
  }  
 }

Extension:

SQL parameters can also be passed to parameter groups, as follows:

 SqlParameter[] parm = new SqlParameter[]
 {
  new SqlParameter("@StuName", SqlNull(stuName)),
  new SqlParameter("@StuAge", SqlNull(stuAge)) 
 }
 if(parm != null)
 {
  cmd.Parameters.AddRange(parm);  
 }
 cmd.ExecuteNonQuery();

Note: The parameter values in new SqlParameter (parameter name, parameter value) also do not accept null value, and the parm parameter group does not accept null and if (parm! = null) cannot be judged less.