Identification of "null" in. NET and SQL Server (difference between dbnull and null) [conversion]

For new database programming, we may have some questions about "null". For example, if all the data in a new table is displayed as <null>, after manually adding and deleting text, it becomes blank again. A string-type field is not explicitly filled in, but not equal to ""; ADO is used. net value from the database, an error occurs every time there is a <null> ...... This requires us to correctly understand several different "null values" in. NET and SQL Server ".

1. The true null value, that is, "No input value", can appear in most types of fields (if there are no other constraints). It is expressed as null in SQL Server, displayed as <null>. In the SQL Server Enterprise Manager, press Ctrl + 0. It corresponds to system. dbnull. Value in. net. In the T-SQL command, to determine whether a value is null, use "is null" instead of "= NULL"; to handle a null value, there is an isnull function that replaces null with the specified value. Use ADO. the null value obtained from the database cannot be automatically converted to a null string or a nothing. You must manually check the value if system is obtained. dbnull. value is assigned to the Data Object nothing or other custom meaningful values.

2. A null string (zero-length string) appears only in a string type (such as nvarchar) field. It is expressed as ''in SQL Server and displayed as blank, manually enter a cell in the SQL Server Enterprise Manager. It corresponds to system. String. Empty in. net, which is commonly used "". Processing empty strings in T-SQL commands is no different from processing general strings. The Null String obtained from the database using ADO. NET is no different from the general string.

About dbnull

Dbnull in DOTNET is a separate type of system. dbnull. It has only one value, dbnull. value. Dbnull directly inherits the object, so dbnull is not a string, not an int, or datetime...

But why does dbnull indicate a string, number, or date in the database? The reason is that the classes (such as datarow) used by DOTNET to store the data are in the form of objects. For datarow, the value returned by its row [column] is never null, or it is a specific value of the column type. Or dbnull. Therefore, the row [column]. tostring () Statement will never cause nullreferenceexception in tostring.

Dbnull implements iconvertible. However, except that tostring is normal, other toxxx will throw an error that cannot be converted.

In the returned values of executescalar of idbcommand (oledbcommand, sqlcommand...), we can analyze the situation as follows:

The returned object in select 1 is 1.
In this case, select null returns dbnull. value.
Select isnull (null, 1) returns 1
The return value of select top 0 ID from Table1 is null.
Select isnull (ID, 0) from Table1 where 1 = 0 the returned value is null.

Here, the executescalar rule is to return the data of the first column and the first row. If the first row of the first column is not empty, executescalar directly corresponds to the DOTNET value. If the first row exists but the first column is empty, dbnull is returned. If no row exists, executescalar returns NULL.

The rule is like this. One easy mistake here is to confuse executescalar's returned dbnull with null, for example:

String username = cmd. executescalar (). tostring ();

Unless you think that there must be at least one row of data after executing the command, an error will occur here.

Or select ID from usertable where username = @ name. If no record is found, executescalar returns NULL, so never

Int userid = convert. toint32 (CMD. executescalar ());

Or you will write the SQL statement: Select isnull (ID, 0) from usertable where username = @ name

However, int userid = convert. toint32 (CMD. executescalar (); still has an error. Because the preceding statement is not valid, no rows are returned.

For the value of idbdataparameter (oleddbparameter, sqlparameter ..), if it is null, it indicates that this parameter is not specified or it represents default. If it is dbnull. value, it indicates null in SQL

Therefore, if you want to call the stored procedure, there is the parameter @ Val nvarchar (20) = "AABB ",
Then cmd. Parameters ["@ Val"]. value = NULL indicates that the default "AABB" is used"
Cmd. Parameters ["@ Val"]. value = dbnull. value indicates that null is used to pass to @ Val.

You can use convert. isdbnull to determine whether a value is dbnull. Note that convert. isdbnull (null) is false.

Supplement: dbnull refers to "null" in the database, rather than "null" in the CLR ".

For example, many beginners think the following is the same:

Cmd. Parameters ["@ payment_type"]. value = "";

Cmd. Parameters ["@ payment_type"]. value = system. dbnull. value;

 

"" Indicates a Null String, while system. dbnull. value indicates null in the database, with no data. Just as the teacher said "0" is not none.