In C #, the SQL parameter is passed into the null value and the error solution is displayed,

In C #, the SQL parameter is passed into the null value and the error solution is displayed,

The null in C # is different from the NULL in SQL. The NULL in SQL is represented by C # As DBNull. Value.

Note: SQL parameters cannot accept the null value of C #. If null is input, an error is returned.

The following is an example:

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

When the above code is used to check the token, an exception is thrown when the stuName or stuAge value is null. How can this problem be solved?
Solution: When the stuName or stuAge Value is null, pass in DBNull. Value. Next we will write a static generic method in the public class to judge the passed parameter Value. If it is null, DBNull. Value is returned; otherwise, the original Value is returned.

 1 public static object SqlNull(object obj) 2 { 3   if(obj == null) 4   { 5     return DBNull.Value; 6   } 7   else 8   { 9     return obj;10   }    11 }

The code after calling the preceding method is as follows:

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

In addition, if the parameter value comes from the value of a control (such as a text box), the input parameter value is not null (because the value of the control is not null, even if there is no value, it is ""), if you want to set the field value of the control to "" (for example, if no character is entered in the text box) to NULL, you only need to modify the SqlNull method slightly:

 1 public static object SqlNull(object obj) 2 { 3    if(obj == null || obj.ToString() == "") 4    { 5      return DBNull.Value; 6    } 7    else 8    { 9      return obj;10    }    11  }

Extension:

You can also set parameters for passing SQL parameters as follows:

 1 SqlParameter[] parm = new SqlParameter[] 2 { 3   new SqlParameter("@StuName", SqlNull(stuName)), 4   new SqlParameter("@StuAge", SqlNull(stuAge))   5 } 6 if(parm != null) 7 { 8   cmd.Parameters.AddRange(parm);    9 }10 cmd.ExecuteNonQuery(); 

Note: The parameter values in new SqlParameter (parameter name, parameter value) do not accept null values, and parm parameter groups do not accept null, if (parm! = Null.