Search code examples
sql-serverguidstrongly-typed-datasettableadapternull-uniqueidentifier

Handling null GUIDs in SQL Server and strongly typed datasets

I have a table in SQL server:

Categories
--------------
CategoryID (uniqueidentifier)
ParentCategoryID (uniqueidentifier) allow nulls

ParentCategoryID is meant to hold a value in CategoryID to indicate which category is the parent. If it has no parent (i.e. it's a top category) then ParentCategoryID should be null.

I'm using strongly typed datasets (table adapters) and for the properties for ParentCategoryID it does not allow it to be null. I've tried to change the properties for the field in the typed dataset but it says trying to make a guid "empty" or "nothing" is not valid. The only option is to throw an exception on null. This results in an error:

The value for column 'ParentCategoryID ' in table 'Categories' is DBNull.

Is this how it is, or is there a way to handle null GUID/uniqueidentifiers when using typed datasets?

Solution

  • If you have used the Visual Studio generators and everything is detected properly for your table, then a nullable column will generate the following on your strongly typed DataRow:

    1. A public property named for the table Column name ("ParentCategoryID")
    2. A public method that detects a null entry ("bool IsParentCategoryIDNull()")
    3. A public method that "nulls" the entry ("void SetParentCategoryIDNull()")

    Given that your strongly typed table is named "My" (Generates MyDataTable and MyDataRow), your DataSet is named MyDataSetType, and the instance is named myDataSet:

    MyDataSetType.MyRow row = myDataSet.My.NewMyRow();
row.ParentCategoryID = Guid.Empty; //OPTION 1: explicitly set GUID
row.SetParentCategoryIDNull(); //OPTION 2: explicitly set Null
myDataSet.My.AddMyRow(row);

    You can also look at the implementation of SetParentCategoryID to see what is used to do a "nulling".

    Further, to detect a "null guid":

    if (row.IsParentCategoryIDNull())
{
  //Do something spectacular
}

    So now you have three different types of values to represent state:

    1. null entry in database/dataset (no parent category)
    2. non-null entry in database (parent category, presumably)
    3. non-null entry in database that is the empty (Guid.Empty) guid (???)

    When i first ran into this problem, I thought Guid.Empty should have been used to represent a null entry in the database, but that would have required custom handling of the guid type. Using the wrapper functions, the strongly typed dataset can provide consistent handling of any number of nullable columns based on struct-style types.