Mar 22 2011

Using Nullable Types to Handle Possible NULL Values From Database

By: Brian Dobberteen

I’d like to describe a techinque that I’ve come up with to handle the ever-present issue of the easiest (best?) way to handle building an object from a database query that may or may not contain NULL values. I feel compelled to post this because I haven’t seen this approach detailed elsewhere online.

First, let’s create a simple class to demonstrate our needs:

Note the use of nullable value types on lines 5 and 7 above. For those not familiar with the concept of nullable types, have a look at: Using Nullable Types – MSDN.

In our class, we are anticipating that there may be NULL values stored for a Person’s Age and Last Modified Date.

Simple enough. But how to best build a Person object from a SqlDataReader? I’ve seen tons of different approaches, such as:

This approach certainly isn’t that bad, and seems to be a very common method for handling NULLs via nullable types.

But we’re not content with something that’s only ‘not bad,’ are we? Of course not.

Let’s try another way:

Check out lines 9 and 11 – we are using the ‘as’ operator rather than the clunky ternary, resulting in code that, IMHO, is more concise, clear, and therefore easier to read and maintain.

I’m surprised that I haven’t seen others doing this… I know I’m not the only one to have stumbled across this approach!

Mar 18 2011

Getting Value of NEWSEQUENTIALID() on Insert

By: Brian Dobberteen

Recently, I was writing a stored procedure that, at one point, performs an insert into a table that has a GUID (uniqueidentifier) primary key. The default value for this column is set to NEWSEQUENTIALID().

Further along in the stored procedure, I needed the value of this newly-inserted GUID to use in another INSERT statement. Fortunately, T-SQL provides us with precisely what we need – the OUTPUT clause.

A quick demo:

The table DDL:

Example portion of sproc:

Thanks to the bit highlighted above on lines 7 and 15, we now have a reference to the newly inserted GUID and can use it as needed.

Additionally, the use of newsequentialid() here lessens the impact of using a GUID clustered index.