The difference between DBNull and null

DBNull class

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

Namespaces: System

Assembly: mscorlib (in mscorlib.dll)

The DBNull class represents a value that does not exist. For example, in a table in a database, a column in a row might not contain any data. That is, the column is considered not to exist at all, not just the value. A DBNull object that represents a column that does not exist. In addition, COM Interop uses the DBNull class to differentiate between VT_NULL variables (indicating nonexistent values) and VT_EMPTY variables (indicating unspecified values).

The DBNull type is a separate class, which means that only one DBNull object exists. DBNull::. The Value member represents a unique DBNull object. DBNull::. Value can be used to explicitly assign a nonexistent value to a database field, but most Ado.net data providers automatically assign DBNull values when the field does not have a valid value. You can determine whether the field value is a DBNull value by passing the value retrieved from the database field to the DBNull.Value.Equals method. However, some languages and database objects provide methods that make it easier to determine whether a database field value is DBNull:. Value. These methods include the IsDBNull function of Visual Basic, Convert::. IsDBNull method, DataTableReader::. IsDBNull methods and IDataRecord::. IsDBNull method.

Do not confuse a nullnothingnullptrnull reference in an object-oriented programming language (Nothing in Visual Basic) with a DBNull object. In an object-oriented programming language, the Nullnothingnullptrnull reference (Nothing in Visual Basic) indicates that there is no reference to an object. DBNull represents an uninitialized variable or a database column that does not exist.

Note

Example

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

Usage in C #

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;

}

Beginner database Programming We may have some questions about "null value", for example, all the data in a new table created by programming is displayed as , manually adding and deleting the text and then becoming blank; A string-type field that is not filled with value, but not equal to ""; Use Ado.net to get the value from the database, every encounter has an error ... This requires our correct understanding. NET, and several different "null values" in SQL Server.

1, the real null value, that is, "no input value," can appear in most types of fields (if there are no other constraints), SQL Server is represented as NULL, shown as , manually in SQL Server Enterprise Manager, the method entered is by ctrl+0. It's in. NET corresponds to System.DBNull.Value. In the T-SQL command, determine if a value is null, and use "is null" instead of "= null"; There is a isnull function to handle null values, It replaces null with the specified value. Null values obtained from the database by Ado.net cannot be automatically converted to an empty string or nothing, and must be detected manually: If you get System.DBNull.Value, you assign nothing to the data object or any other custom meaningful value.

2, the empty string (0-length string), only appears in the field of string type (such as nvarchar), is represented as "in SQL Server", is displayed as blank, and empties a cell when entered manually in SQL Server Enterprise Manager. It's in. NET corresponds to System.String.Empty, which is our commonly used "". There is no difference between handling an empty string in a T-SQL command and handling a generic string. The empty string obtained from the database with Ado.net is no different from the normal string.

DBNull Introduction

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 class (DataRow, etc.) where the data is stored is stored in the form of object (dotnet). 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:

The object returned by select 1 is 1 select null so that the return of DBNull.Value Select IsNull (null,1) is 1 select top 0 IDs from table1 So the value returned is NUL L 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 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 DBNull is returned. If none of the rows is available, 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 the Value of IDbDataParameter (oleddbparameter,sqlparameter), if NULL, either the parameter is not specified, or the default is represented. If it is DBNull.Value, Represents the null in SQL

So, if you want to call the stored procedure, which has parameters @val nvarchar = "AABB", then 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.

Add: DBNull refers to "null" in the database, not "null" in the CLR.

As many beginners think the following is the same:

Cmd. parameters["@payment_type"]. Value = "";

Cmd. parameters["@payment_type"]. Value = System.DBNull.Value;

"" represents an empty string, and System.DBNull.Value represents null in the database, with no data. Just like the teacher said, "0" is not nothing.