.NODE

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: TypedDataSetsCategoriesDS_AnnotatedNull.xsd



xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"

attributeFormDefault="qualified" elementFormDefault="qualified">
 
 
 
 
 
 
 
 
 
codegen:nullValue=
 "- no description available -" />


 
 
 
 
 
 
 
 
 

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 + "	" + row.CategoryName + "	" +
 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 IsNull 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 + "	" + row.CategoryName + "	" +
 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.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax





ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton
Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net