Working with null values in the .NET Framework

August 14th, 2006

With C# 2.0 comes the introduction of the nullable type as a complete and integrated solution for the nullability issue on all forms of value types.

By utilizing nullable types, we can address the nullability requirements presented on both sides of the fence. Data publishers need no longer maintain generic interfaces to allow support for nulls or handle null values and pass back symbolic representations in such instances. We can cast the results of ExecuteScalar to an int? and redefine the property signature to return int?. On the consuming side, the programmer can now use the HasValue property of int? to determine whether the returning value from the database is indeed null.

C# nullable type is essentially a structure that combines a value of the underlying type with a boolean null indicator. Nullable types possess a default constructor which accepts as an argument, an instance of the underlying type of that nullable. An instance of a nullable type has two public read-only properties: HasValue, of type bool, and Value, of the nullable type’s underlying type. HasValue is true for a non-null instance and false for a null instance. When HasValue is true, the Value property returns the contained value. When HasValue is false, an attempt to access the Value property throws an exception.

Nullable types are constructed using the ? type modifier. This token is placed immediately after the value type being defined as nullable. For instance, if we were trying to define a uint in its nullable form, we would apply the ? after making the token uint?. The type specified before the ? modifier in a nullable type is called the underlying type of the nullable type.
Any value type can be an underlying type. And a nullable indicator may also be applied to any struct. You also have the ability to create your own user defined null value types, and you do not have to do any extra work because any struct or enum you create will automatically have a nullable version of itself that can be utilized anywhere you need it. Once you have your user defined nullable type defined, you may use it as you would use any user defined type.
What folks are saying on the net…

There was some concern that precision is lost between SqlDecimal and Decimal (which isn’t an issue for Credentialing Manager). And some confusion regarding remoting/web services support (nullable types are supported, sqltypes are not). And there was also a lot of discussion about conversions, bool? and lifted operators. Its quirky so we’ll have to be aware of that (stuff like the compiler will prevent you from implicitly converting an object of a nullable type to its underlying type and a null added to a value will result in a null).

In regards to the Infragistic controls, they have a Nullable property so we should be all set. The Microsoft developers envisioned that a DBNull would go up all the way into the UI. On the databinding itself there’s a null replacement value in the advance section of the databinding property for any control. For example, if a database field is a null string, you may want to represent that as “select something” or “type here” in a text box. If that value still is there when the push is done it will actually convert it back to null, so you get the two way null support all the way back and forth.

Microsoft is committed to the success of these nullable types and it has been well tested, but we’ll be pioneering their use in our application.

Null vs. DBNull:
Do not confuse the notion of a null reference in an object-oriented programming language with a DBNull object. In C#, a null reference means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column. Sadly, a null reference does not equate to a DBNull. So, the code below will fail:

int? _addUserNumber;
_addUserNumber = Convert.DBNull;

The statement below will also throw an InvalidCastException error if the data returned from the dataabse is a DBNull:

int? _addUserNumber;
_addUserNumber = (int?)data["add_user_nbr"];

Nullable Types Quick Overview:

The default value of a nullable type is an instance for which the HasValue property is false and the Value property is undefined. The default value is also known as the null value of the nullable type. An implicit conversion exists from the null literal to any nullable type, and this conversion produces the null value of the type.

private int? _addUserNumber;
_addUserNumber = (int?)data["add_user_nbr"];

But be aware that the code above will fail with a Conversion error in the case that the value returned from the database is DBNull.

One advantage is that you can evaluate a variable against a null directly.

if (_addUserNumber == null) {...}
if (_addUserNumber.HasValue) {...}
if (Convert.IsDBNull._addUserNumber) {...}

And a nullable type can be used in the same way that a regular value type can be used. This means you can assign a standard integer to a nullable integer and vice-versa:

int? nFirst = null;
int Second = 2;
nFirst = Second; // Valid
nFirst = 123; // Valid
Second = nFirst; // Also valid

Conversions between the nullable and standard version of a given value type vary based on direction. Converting from standard to nullable is always implicit whereas conversions from nullable back to standard is always explicit.

nFirst = null; // Valid
Second = nFirst; // Exception, Second is nonnullable.

Lifted operators permit the predefined and user defined operators that work on the standard value types to also work on the nullable versions of those types. But be aware that the results may not be what you would expect:

int ValA = 10;
int? ValB = null;
int? ValC = ValA * ValB; //ValC = null

int ValA = 10;
int? ValB = null;
int ValC = ValA * ValB; // ValC not nullable, exception thrown

NullableTypes vs. SqlTypes

Q: When and where to use NullableTypes and where to use System.Data.SqlTypes instead?

A: Use NullableTypes in the Business Layer: in the code that implements the application business logic (business entities, functions, services and business processes) and in public interfaces (nullable parameters, return type and properties). Use NullableTypes also in the Presentation Layer: in the code that implements the user interaction dialog (navigation, logic and presentation).

Here NullableTypes do not just shift the problem of handling nulls with built-in value-types elsewhere, they do completely solve it in an elegant OO fashion that make code more readable and maintainable.

In the Business Layer and in the Presentation Layer, NullableTypes are a valid general purpose solution: they are more reliable and efficient than every custom solution.

Do prefer System.Data.SqlTypes (or the types provided by the .NET Data Provider in use, as the structures in System.Data.OracleClient or types in Oracle.DataAccess.Types) in the Data Layer: in the code that move data from the database (with a DataAdapter or with a DataCommand) to the memory (DataSet, variables, Array, etc.) and vice versa.
This is because System.Data.SqlTypes (and types provided by other .NET Data Providers) are isomorphic with the database types while NullableTypes (as built-in types) are not.

Q: Why shouldn’t I use System.Data.SqlTypes everywhere?

A: You can, but you can also do better with NullableTypes because System.Data.SqlTypes (and types provided by other .NET Data Providers) do:
– not work with .NET Remoting
– not work with ASP.NET Web Services
– depends on SqlServer or other databases (it is not a good design to couple the Presentation Layer and the Business Layer with a specific database)

While NullableTypes:
– do work with .NET Remoting
– do work with ASP.NET Web Services (in beta now)
– are database agnostic
– have the NullConvertClass that can be used to seamlessly integrate NullableTypes with Web Server controls and WinForms controls
– have the DbNullConvert class that converts NullableTypes values to in-memory database values (Command Parameters, DataReader values, DataSet column values) and vice versa

Entry Filed under: C# and .NET

1 Comment Add your own

  • 1. Richard  |  March 23rd, 2012 at 3:56 am

    DBNULL : The DBNull class represents a nonexistent value. The DBNull type is a singleton class, which means only one DBNull object exists. The DBNull.Value member represents the sole DBNull object.

    You can use System.DBNull.Value for find out the variable has a dbnull or not

    if (ds.Tables[0].Rows[i].ItemArray[0] == System.DBNull.Value)
    {
    MessageBox.Show(“DBNULL exist in the field “);
    }

    full source code :

    http://net-informations.com/csprj/ado.net/cs-dbnull.htm

    Richard

Leave a Comment

Required

Required, hidden

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


Pages

Tweets

If you are going to write a swear, spell the damn thing correctly, dammit! ...or is it damnit? damn it? ....aw heck.

Last week from Liana Leahy's Twitter via Twitter Web App

Meta