Recipe 2.19 Replacing Null Values in a Strongly Typed DataSet

Problem

When a column in a database has a null value, you want the value in the DataSet to be a string indicating that no value is available.

Solution

Use annotations in the XML schema to control the handling of null values.

The sample uses one XSD file:

CategoriesDS_AnnotatedNull.xsd

The schema used to generate the strongly typed DataSet. The schema is annotated so the null Description values are replaced with the string "- no description available -". The annotations are marked in bold in Example 2-25.

Example 2-25. File: TypedDataSets\CategoriesDS_AnnotatedNull.xsd

<?xml version="1.0" standalone="yes" ?>
<xs:schema id="CategoriesDS_AnnotatedNull"
    targetNamespace=
    "http://www.tempuri.org/CategoriesDS_AnnotatedNull.xsd"
    xmlns:mstns="http://www.tempuri.org/CategoriesDS_AnnotatedNull.xsd"
    xmlns="http://www.tempuri.org/CategoriesDS_AnnotatedNull.xsd"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
    xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"
    attributeFormDefault="qualified" elementFormDefault="qualified">
    <xs:element name="CategoriesDS_AnnotatedNull" msdata:IsDataSet="true">
        <xs:complexType>
            <xs:choice maxOccurs="unbounded">
                <xs:element name="Categories">
                    <xs:complexType>
                        <xs:sequence>
                        <xs:element
                        name="CategoryID"
                        msdata:ReadOnly="true"
                        msdata:AutoIncrement="true"
                        type="xs:int" />
                        <xs:element
                        name="CategoryName"
                        type="xs:string" />
                        <xs:element
                        name="Description"
                        type="xs:string"
                        minOccurs="0"
                        codegen:nullValue=
                        "- no description available -" />
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:choice>
        </xs:complexType>
        <xs:unique name="Constraint1" msdata:PrimaryKey="true">
            <xs:selector xpath=".//mstns:Categories" />
            <xs:field xpath="mstns:CategoryID" />
        </xs:unique>
    </xs:element>
</xs:schema>

The sample code creates a strongly typed DataSet based on the Categories table in Northwind. The user specifies whether the one based on the default or annotated schema file is used. In either case, data is loaded into the DataSet. A row is added to the Categories table with a Description value of null. The data in the table is written to the text box on the form to demonstrate the effect of the schema annotation on null column values.

The C# code is shown in Example 2-26.

Example 2-26. File: TypedDataSetNullsForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String CATEGORIES_TABLE = "Categories";

//  . . . 

StringBuilder result = new StringBuilder( );

// Create the DataAdapter.
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Categories",
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

if (annotatedRadioButton.Checked)
{
    // Create the typed DataSet without null annotation.
    CategoriesDS_AnnotatedNull ds = new CategoriesDS_AnnotatedNull( );
    // Fill the Categories table within DataSet.
    da.Fill(ds, CATEGORIES_TABLE);

    // Add a row with a null Description.
    ds.Categories.AddCategoriesRow("New Category", null);

    result.Append("Annotated Nulls" + Environment.NewLine +
        Environment.NewLine);
    // Iterate over the rows collection and display columns.
    foreach(CategoriesDS_AnnotatedNull.CategoriesRow row in ds.Categories)
    {
        // Get the Description.
        String description = row.Description;

        // Note that the null Description is replaced.
        result.Append(row.CategoryID + "\t" + row.CategoryName + "\t" +
            description + Environment.NewLine);
    }
}
else
{
    // Create the typed DataSet annotated for nulls.
    CategoriesDS ds = new CategoriesDS( );
    da.Fill(ds, CATEGORIES_TABLE);

    // Add a row with a null Description.
    ds.Categories.AddCategoriesRow("New Category", null);

    result.Append("Default" + Environment.NewLine + Environment.NewLine);
    // Iterate over the rows collection and display columns.
    foreach(CategoriesDS.CategoriesRow row in ds.Categories)
    {
        // Use Is<field>Null method or StrongTypingException will 
        // result when null row.Description is accessed.
        String description = row.IsDescriptionNull( ) ?
            "NULL" : row.Description;

        // Note that the null Description is not replaced.
        result.Append(row.CategoryID + "\t" + row.CategoryName + "\t" +
            description + Environment.NewLine);
    }
}

resultTextBox.Text = result.ToString( );

Discussion

Annotations to XSD schemas used to generate strongly typed DataSet objects are discussed in detail in Recipe 2.18.