Today is not only outsourced, but also run to the Hongqiao airport to help implement. It is a pity to come here again as if there is nothing to do. :) Just can write blog.
Just a few days to write database operations more, it is time to write about DBNull things.
DBNull in dotnet is a separate type of system.dbnull. It has only one value DBNull.Value. DBNull directly inherits Object, so DBNull is not a string, not int, nor 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 as object in the form of dotnet data.
For DataRow, its row[column] returns a value that is never null, or is the value of a type that is specific to column. Or it's DBNull. So Row[column]. ToString () This writing will never happen to NullReferenceException in ToString.
DBNull realized the IConvertible. However, except that ToString is normal, other toxxx will throw errors that cannot be converted.
In the return value of the executescalar of IDbCommand (Oledbcommand,sqlcommand ...), the situation can be analyzed as follows:
Select 1 so the object returned is 1
Select null This returns the DBNull.Value
Select IsNull (null,1) returns 1
Select top 0 ID from table1 The value returned is null
The value returned by Select IsNull (id,0) from table1 where 1=0 is null
The ExecuteScalar rule here is to return the first column, the first row of data. If the first row of the first column is not empty, then executescalar directly corresponds to the dotnet value. If there is a first row, but the first column is empty, then the return is DBNull. If there is no line, then ExecuteScalar returns null
That's the rule. One of the easy mistakes here is to confuse ExecuteScalar return dbnull with NULL, for example:
String Username=cmd. ExecuteScalar (). ToString ();
Unless you think that after the execution of CMD, there must be at least one row of data, otherwise there will be errors.
Or the Select ID from usertable where username= @name such an SQL statement, and if no records are found, ExecuteScalar returns NULL, so don't
int Userid=convert.toint32 (cmd. ExecuteScalar ());
Or you can write SQL statements like this: Select IsNull (id,0) from usertable where Username= @name
but int userid=convert.toint32 (CMD). ExecuteScalar ()); There will still be an error, because when the above statement is not valid, it is still not returning any rows.
For IDbDataParameter (Oleddbparameter,sqlparameter.) Value, if NULL, is not specified on behalf of the parameter, or it represents default. If DBNull.Value, represents null in SQL
So, if you want to call the stored procedure, which has parameters @val nvarchar = "AABB",
So cmd. parameters["@val"]. Value=null represents the use of this default "AABB"
and CMD. parameters["@val"]. The value=dbnull.value represents using null to pass to @val
You can use Convert.isdbnull to determine whether a value is DBNull. Note that convert.isdbnull (NULL) is false.
Note: All of the above SQL statements refer to SQLSERVER2000. I'm not sure if the other databases are the same behavior.
(write here first, think again)
