Difference between DBNull and Null

DBNull class

Indicates a value that does not exist. This class cannot be inherited.

Namespace: System

Assembly: mscorlib (in mscorlib. dll)

The DBNull class indicates a non-existent value. For example, in a database table, a column in a row may not contain any data. That is, this column is regarded as nonexistent, not just a value. A DBNull object indicating a column that does not exist. In addition, COM interoperability uses the DBNull class to differentiate VT_NULL variables (indicating nonexistent values) and VT_EMPTY variables (indicating unspecified values ).

DBNull is a separate class, which means that only one DBNull object exists. DBNull:. Value indicates a unique DBNull object. DBNull:. Value can be used to explicitly allocate nonexistent values to database fields. However, most ADO. NET data providers automatically allocate DBNull values when fields do not have valid values. You can pass the Value retrieved from the database field to the DBNull. Value. Equals method to determine whether the field Value is DBNull. However, some languages and database objects provide some methods to determine whether the database field value is DBNull ::. value. these methods include the IsDBNull function of Visual Basic, Convert ::. isDBNull method, DataTableReader ::. isDBNull method and IDataRecord ::. isDBNull method.

Do not confuse the nullNothingnullptrnull reference (Nothing in Visual Basic) concept with the DBNull object. In an object-oriented programming language, nullNothingnullptrnull reference (Nothing in Visual Basic) indicates that there is no reference to an object. DBNull indicates uninitialized variables or non-existent database columns.

Remarks

Example

The following example calls the DBNull. Value. Equals method to determine whether the database field in the contact database has a valid Value. If a valid value exists, the field value is appended to the string output in the tag.

C # usage

Private void OutputLabels (DataTable dt)

{

String label;

// Iterate rows of table

Foreach (DataRow row in dt. Rows)

{

Int labelLen;

Label = String. Empty;

Label + = AddFieldValue (label, row, "Title ");

Label + = AddFieldValue (label, row, "FirstName ");

Label + = AddFieldValue (label, row, "MiddleInitial ");

Label + = AddFieldValue (label, row, "LastName ");

Label + = AddFieldValue (label, row, "Suffix ");

Label + = "/n ";

Label + = AddFieldValue (label, row, "Address1 ");

Label + = AddFieldValue (label, row, "AptNo ");

Label + = "/n ";

LabelLen = label. Length;

Label + = AddFieldValue (label, row, "Address2 ");

If (label. Length! = LabelLen)

Label + = "/n ";

Label + = AddFieldValue (label, row, "City ");

Label + = AddFieldValue (label, row, "State ");

Label + = AddFieldValue (label, row, "Zip ");

Console. WriteLine (label );

Console. WriteLine ();

}

}

Private string AddFieldValue (string label, DataRow row,

String fieldName)

{

If (! DBNull. Value. Equals (row [fieldName])

Return (string) row [fieldName] + "";

Else

Return String. Empty;

}

For new database programming, we may have some questions about "null". For example, all the data in a new table is displayed , Manually add and delete text and then become blank; a string type field, clearly not filled in, but not equal to ""; with ADO.. ...... This requires a correct understanding. . 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 and displayed In the SQL server Enterprise Manager, press Ctrl + 0. NET corresponds to System. DBNull. value. in the T-SQL command, to determine whether a value is null, to use "is null" instead of "= null"; to handle a null value there is an ISNULL function, it 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 is in. NET corresponds to System. String. Empty, which is commonly used "". There is no difference between processing null strings in T-SQL commands and 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 only has 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. so row [column]. toString () 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 IDbCommand (OleDbCommand, SqlCommand ...) In the returned values of ExecuteScalar, the situation can be analyzed as follows:

The returned object in select 1 is 1 select null, and DBNull is returned. value select isnull (null, 1) returns 1 select top 0 id from table1. The returned Value 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 indicates DEFAULT. If it is DBNull. Value, it indicates NULL in SQL

Therefore, if you want to call the stored procedure with the parameter @ val nvarchar (20) = "AABB", then cmd. parameters ["@ val"]. value = null indicates the default "AABB" and 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.