Replacing Null Values in a Strongly Typed DataSet

[ Team LiB ] 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.
Nội dung trích xuất từ tài liệu:
Replacing Null Values in a Strongly Typed DataSet[ Team LiB ]Recipe 2.19 Replacing Null Values in a Strongly Typed DataSetProblemWhen a column in a database has a null value, you want the value in the DataSet to be astring indicating that no value is available.SolutionUse 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 type=xs:int /> The sample code creates a strongly typed DataSet based on the Categories table inNorthwind. The user specifies whether the one based on the default or annotated schemafile is used. In either case, data is loaded into the DataSet. A row is added to theCategories table with a Description value of null. The data in the table is written to thetext box on the form to demonstrate the effect of the schema annotation on null columnvalues.The C# code is shown in Example 2-26.Example 2-26. File: TypedDataSetNullsForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Text;using System.Data;using System.Data.SqlClient;// Table name constantsprivate 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 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 + \t + row.CategoryName + \t + description + Environment.NewLine); }}resultTextBox.Text = result.ToString( );DiscussionAnnotations to XSD schemas used to generate strongly typed DataSet objects arediscussed in detail in Recipe 2.18.[ Team LiB ]