SQL parameter passed in NULL in C # out of error and how to workaround

Null in C # is not the same as null in SQL, and Null in SQL is represented by C # as DBNull.Value.

NOTE: TheSQL parameter cannot accept the null value of C #, and passing NULL will cause an error .

Let's look at 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 ();

There is no problem with the code above, in fact, when the value of Stuname or stuage is null, an exception is thrown. How to solve it?
Solution: When the value of Stuname or stuage is null, the DBNull.Value is passed in. In the public class, write a static general method to determine the value of the passed parameter, NULL to return DBNull.Value, otherwise return the original value.

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 above 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 value of the parameter originates from the value of the control (such as a text box), the passed parameter value is not NULL (because the value of the control is not NULL, even if no value is ""), and if you want to implement a value of "" (such as a text box with no input characters), the Data table field value Just make a slight change to the Sqlnull method:

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    }    one  }

Extended:

The SQL parameter can also be passed to the parameter group as follows:

1 sqlparameter[] parm = new sqlparameter[] 2 {3   new SqlParameter ("@StuName", Sqlnull (Stuname)), 4   new Sqlparame ter ("@StuAge", Sqlnull (Stuage))   5} 6 if (parm! = null) 7 {8   cmd. Parameters.addrange (parm);    9}10 cmd. ExecuteNonQuery ();

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