. NET has parameterized queries that require parameters but do not provide an error for this parameter

1, the source of the problem

　In. NET or C #, we generally execute SQL statements, and we recommend using parameterized queries to avoid SQL injection attacks, but when I use parameterized queries

The following error has occurred, as detailed below:

Figure one This is the code that writes the SQL statement parameterized query

Figure 2 This is the SQL statement that MSSQL executes

2, the cause of the problem

The reason for this error is that in a parameterized query, there are several parameters with a value of NULL, so there is a 2 error.

Why is this so??

Although the value of the parameter is NULL, the incoming database must become DBNull.Value
Because this null is C #, DBNull.Value is the null in the database
Then why did you make an error?
Because you are an incoming parameter ah, if you assign a value of NULL to the parameter @nickname, it is equivalent to no assignment because of CMD. Parameters[nickname]. The default value of value is null, not dbnull.value.

3, how to solve the problem ( the following is not the only method )

 public  object  checkisnull (object   obj)//This method determines whether the parameter to be passed is null, if NULL, the return value Dbnll.value, the primary user network database adds or updates data                { if   (Obj==null) {             return   DBNull.Value;  else   {
    return   obj; }} 
 
  

  public Object Checkisdbnull (object obj)    //This method reads data from the database and returns null {if the data in the database is DBNull.Value.             Convert.isdbnull (obj))            {                return null;            }            else            {                return obj;            }        }    

The difference between DBNULL and null

Null is an invalid object reference in. Net.

DBNull is a class. DBNull.Value is the only instance of it. It refers to the value in. NET when the data in the database is empty (<NULL>).

Null indicates that an object's pointer is invalid, that is, the object is an empty object.

DBNull.Value indicates that the value of an object in the database is empty, or uninitialized, that the DBNull.Value object is a pointer to a valid object.

DBNull in Dotnet is a single type of system.dbnull. It has only one value DBNull.Value. DBNull directly inherits Object, so DBNull is not a string, not an int, or a DateTime ...

But why DBNull can represent strings, numbers, or dates in a database? The reason is that the classes that store the data (DataRow, and so on) are stored in the form of object dotnet the data.

for a DataRow, the value returned by its row[column] is never null , or it is the value of the type of column that is specific. Or it's DBNull. So Row[column]. The ToString () notation will never occur at ToString, but it is possible to throw out the NullReferenceException exception.

DBNull implements the IConvertible. However, except that ToString is normal, other toxxx will throw an error that cannot be converted.

In the return value of the executescalar of IDbCommand (Oledbcommand,sqlcommand ...), the situation can be analyzed like this:

Select 1 Returns an object that is 1

Select NULL to return the DBNull.Value

Select IsNull (null,1) returned the 1

Select top 0 ID from table1 This returns a value of NULL

Select IsNull (id,0) from table1 where 1=0 the value returned is null

The ExecuteScalar rule here is to return the first column, the first row of data. If none of the rows are there, then ExecuteScalar returns NULL. If there is a first row, but the first column is empty, then the return is DBNull. If the first row of the first column is not empty, then the executescalar directly corresponds to the value of the dotnet.

This is the rule. One of the easiest mistakes here is to confuse ExecuteScalar return dbnull with NULL, for example:

String Username=cmd. ExecuteScalar (). ToString ();

Unless you think that CMD executes, there must be at least one row of data, otherwise there will be an error.

or select ID from usertable where [email protected] Such an SQL statement, if the record is not found, then ExecuteScalar will return null, so do not

int Userid=convert.toint32 (cmd. ExecuteScalar ());

Or you would write SQL statements like this: Select IsNull (id,0) from usertable where [email protected]

but int userid=convert.toint32 (CMD). ExecuteScalar ()); Will still go wrong, because when the above statement is not true, no rows are returned.

For IDbDataParameter (Oleddbparameter,sqlparameter.) Value, If NULL, indicates that the parameter is not specified or represents default. If DBNull.Value, represents null in SQL

So, if you're going to call a stored procedure with a default parameter @val nvarchar = "C",

So cmd. parameters["@val"]. The Value=null delegate uses this default "C"

and CMD. parameters["@val"]. The Value=dbnull.value delegate uses null to pass to @val

You can use Convert.isdbnull to determine whether a value is DBNull. Note that convert.isdbnull (null) is false, which means that null is not equal to DBNull.Value.

. NET has parameterized queries that require parameters but do not provide an error for this parameter