Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes: Strongly Typed DataSets – Part 12

Posted by Ravi Varma Thumati on November 19, 2009

Strongly typed DataSets are a collection of classes that inherit from the DataSet, DataTable, and DataRow classes, and provide additional properties, methods, and events based on the DataSet schema. These methods, properties, and events allow, among other things, named properties and methods to be used to retrieve column values, access parent and child records, find rows, and handle null column values.

It is important to remember that strongly typed DataSets are in fact just a collection of classes. Because the strongly typed DataSet classes inherit from DataSet, DataTable, and DataRow, all the functionality present in those classes can be used just as it is for the untyped classes. The development environment just provides a way to automatically generate consistent and efficient strongly typed DataSet classes.

Using a strongly typed DataSet has a number of advantages over using untyped DataSet:

  • Schema information is contained within the strongly typed DataSet. This results in an improvement in performance over retrieving schema information at runtime. Of course, the schema of an untyped DataSetcan also be defined programmatically.
  • Programming is more intuitive, and code is easier to maintain. Table and field names are accessed through properties rather than indexer arguments. The Visual Studio .NET IDE provides autocomplete functionality for these names. As an example, the following code demonstrates accessing data using both an untyped and strongly typed Northwind DataSet:
  • Type mismatch errors and errors resulting from either misspelled or out-of-bounds indexer arguments to retrieve tables and columns can be detected during compilation rather than at runtime.

·                // untyped

·                String categoryName =

·                   (String)dsNorthwind.Tables[“Categories”].Rows[0][“CategoryName”];

·

·                // strongly typed

String categoryName = dsNorthwind.Categories[0].CategoryName;

There are some drawbacks to using strongly typed DataSet objects:

  • Using typed classes adds some overhead to code execution. If the strongly typed functionality isn’t required, application performance can be improved slightly using an untyped DataSet.
  • The strongly typed DataSet will need to be regenerated when the data structure changes. Applications using the strongly typed DataSet will need to be rebuilt using a reference to the new strongly typed DataSet. This can be especially significant in a multitier application or distributed where any clients that use the strongly typed DataSetsDataSet was used.will have to be rebuilt using a reference to the updated version, even those that would not otherwise have been affected by the change if an untyped

This chapter discusses how to create strongly typed DataSets and describes some of their added functionality. Examples that demonstrate how to use the extended functionality are presented.

Creating a Strongly Typed DataSet

There are three ways a strongly typed DataSet class can be generated. The easiest method is to drop one or more DataAdapter objects from the Data tab in the Visual Studio .NET Toolbox onto a design surface such as a form or a component. Configure each DataAdapter to select data from one table. Right-click on the design surface and select Generate DataSet. Provide a name for the DataSet, select the tables to be included, and generate the new strongly typed DataSet. To relate the two tables, double-click on the XSD file for the new DataSet in the Solution Explorer window to open it. Right-click on the child table in XSD schema designer, select Add/New Relation… from the shortcut menu, and complete the dialog. Instances of the strongly typed DataSet can now be created programmatically or by using the DataSet object from the Data tab in the Visual Studio.NET Toolbox.

The other two methods are more involved, and both require an XSD schema file, which can be generated in a number of ways, e.g., using Visual Studio IDE tools, third-party tools, or the DataSet WriteXmlSchema( ) method. The following example shows a utility that uses the WriteXmlSchema( ) method to create an XSD schema based on the Categories and Products tables in the Northwind database:

String connString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

SqlDataAdapter daCategories = new SqlDataAdapter(

“SELECT * FROM Categories”, connString);

SqlDataAdapter daProducts = new SqlDataAdapter(

“SELECT * FROM Products”, connString);

SqlDataAdapter daOrders = new SqlDataAdapter(

“SELECT * FROM Orders”, connString);

SqlDataAdapter daOrderDetails = new SqlDataAdapter(

“SELECT * FROM [Order Details]”, connString);

DataSet ds = new DataSet(“Northwind”);

// load the schema information for the tables into the DataSet

daCategories.FillSchema(ds, SchemaType.Mapped, “Categories”);

daProducts.FillSchema(ds, SchemaType.Mapped, “Products”);

daOrders.FillSchema(ds, SchemaType.Mapped, “Orders”);

daOrderDetails.FillSchema(ds, SchemaType.Mapped, “Order Details”);

// add the relations

ds.Relations.Add(“Categories_Products”,

ds.Tables[“Categories”].Columns[“CategoryID”],

ds.Tables[“Products”].Columns[“CategoryID”]);

ds.Relations.Add(“Orders_OrderDetails”,

ds.Tables[“Orders”].Columns[“OrderID”],

ds.Tables[“Order Details”].Columns[“OrderID”]);

ds.Relations.Add(“Products_OrderDetails”,

ds.Tables[“Products”].Columns[“ProductID”],

ds.Tables[“Order Details”].Columns[“ProductID”]);

// output the XSD schema

ds.WriteXmlSchema(@”c:\Northwind.xsd”);

The following code is a partial listing of the XSD schema for the Categories and Products tables in the Northwind database. Note that the msdata namespace is defined to add Microsoft-specific extensions, including the read-only and auto-increment column properties.

<?xml version=”1.0″ standalone=”yes”?>

<xs:schema xmlns=””

xmlns:xs=”http://www.w3.org/2001/XMLSchema&#8221;

xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”>

<xs:element msdata:IsDataSet=”true”>

<xs:complexType>

<xs:choice maxOccurs=”unbounded”>

<xs:element>

<xs:complexType>

<xs:sequence>

<xs:element msdata:ReadOnly=”true”

msdata:AutoIncrement=”true” />

<xs:element>

<xs:simpleType>

<xs:restriction base=”xs:string”>

<xs:maxLength value=”15″ />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element minOccurs=”0″>

<xs:simpleType>

<xs:restriction base=”xs:string”>

<xs:maxLength value=”1073741823″ />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element

minOccurs=”0″ />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element>

<!– Product definition omitted. –>

</xs:element>

</xs:choice>

</xs:complexType>

<xs:unique msdata:PrimaryKey=”true”>

<xs:selector xpath=”.//Categories” />

<xs:field xpath=”CategoryID” />

</xs:unique>

<xs:unique

msdata:ConstraintName=”Constraint1″ msdata:PrimaryKey=”true”>

<xs:selector xpath=”.//Products” />

<xs:field xpath=”ProductID” />

</xs:unique>

<xs:keyref refer=”Constraint1″>

<xs:selector xpath=”.//Products” />

<xs:field xpath=”CategoryID” />

</xs:keyref>

</xs:element>

</xs:schema>

From this schema, a strongly typed DataSet can be created using Visual Studio .NET or the XML Schema Definition Tool.

To create a strongly typed DataSet from the XSD schema using Visual Studio .NET, right-click on the project in the Solution Explorer window, choose Add / Existing Item… from the shortcut menu, and select the XSD file to add it to the project. Double-click on the XSD file to open it in the designer window. Right-click on the designer window and select Generate DataSet. To see the strongly typed DataSet file in the Solution Explorer window, select Show All Files from the Project menu. The strongly typed DataSet class Northwind.cs lists as a child of the Northwind.xsd node.

The second way to create a strongly typed DataSet from an XSD schema is to use the XML Schema Definition Tool (XSD.EXE) found in the .NET Framework SDK bin directory. To generate the class file from Northwind.xsd, issue the following command from the command prompt:

xsd Northwind.xsd /d /l:CS

The /d switch specifies that source code for a DataSet should be created, while the /l switch specifies that the utility should use the C# language, which is the default if not specified. The XML Schema Definition Tool offers additional options that are documented in the .NET Framework SDK documentation.

The resulting class file for the strongly typed DataSet is named using the DataSet name in the XSD schema and an extension specifying the language. In this case, the file is named Northwind.cs because the DataSet was named Northwind when it was constructed. The strongly typed DataSet is ready to be added to a project.

Discussion of Underlying Classes

As mentioned before, the strongly typed DataSet is simply a collection of classes extending the functionality of the untyped DataSet. Specifically, three classes are generated for each table in the DataSet: one for each DataTable, DataRow, and DataRowChangeEvent. This section provides a brief overview of the classes generated and discusses the more commonly used methods, properties, and events of those classes.

A class called TableNameDataTable is created for each table in the DataSet. This class inherits from DataTable and implements the IEnumerable interface. Table 13-1 lists the commonly used methods of this class specific to strongly typed DataSet objects.

Table 13-1. TableNameDataTable methods
Method Description
AddTableNameRow( ) Adds a row to the table. The method has two overloads and takes an argument of either a TableNameRow object or a set of arguments, one for each of the column values.
FindByPrimaryKeyField1PrimaryKeyFieldN( ) Takes N arguments that are the values of the primary key fields of the row to find. Returns a reference to a TableNameRow object.
NewTableNameRow( ) Takes no arguments and returns a reference to a new TableNameRow object with the same schema as the table to be used for adding new rows to the table in the strongly typed DataSet.

A class called TableNameRow is created for the DataRow in each table. This class inherits from DataRow. The class also exposes a property for each column in the table with the same name as the column. Table 13-2 lists the commonly used methods of this class.

Table 13-2. TableNameRow Class methods
Method Description
Typed Accessor For each column, a typed accessor to set and get the value of a column that is a property with the same name as the underlying column.
IsColumnNameNull( ) Returns a Boolean value indicating whether the value of the field is null.
SetColumnNameNull( ) Sets the value of the underlying field to DBNull.
GetChildTableNameRows( ) Returns an array of ChildTableNameRow objects comprising the child rows.
ParentTableNameRow( ) Returns an object of type ParentTableNameRow providing access to the parent row.

Finally, a class called TableNameRowChangeEvent is created for each table in the DataSet. This class inherits from EventArgs. Table 13-3 lists the method of this class.

Table 13-3. TableNameChangeEvent method
Property Description
TableNameRow Returns a reference to a TableNameRow object representing the row that caused the event to be raised.

Adding a Row

As with untyped DataSet objects, there are two ways to add a new row to a strongly typed DataSet. The first uses the NewTableNameRow( ) method of the strongly typed DataSet to return a reference to a TableNameRow object. The accessor properties are then used to assign values to the columns of the new row. Finally, the AddTableNameRow( ) method adds the new row to the DataTable. The following example demonstrates this method:

// strongly typed DataSet called Northwind containing the Orders table

Northwind.OrdersDataTable ordersTable = new Northwind.OrdersDataTable();

// create a new row object

Northwind.OrdersRow ordersRow = ordersTable.NewOrdersRow();

// use property accessors to set the column values for the row

ordersRow.CustomerID = “VINET”;

ordersRow.EmployeeID = 1;

// … set the rest of the fields

// add the row to the table

ordersTable.AddOrdersRow(ordersRow);

The following code sample shows how the same thing can be accomplished with an untyped DataSet:

DataTable ordersTable = new DataTable(“Orders”);

// … code to define or retrieve the schema for the DataTable

DataRow ordersRow = ordersTable.NewRow();

ordersRow[“CustomerID”] = “VINET”;

ordersRow[“EmployeeID”] = 1;

// … set the rest of the fields

ordersTable.Rows.Add(ordersRow);

The second way to add a new row to a strongly typed DataSet is to use the AddTableNameRow( ) method. This method allows a row to be added to the DataSet using a single statement similar to the Add( ) method of the DataRowCollection when dealing with untyped DataSet objects. The main difference, aside from the simpler syntax, is that the method is strongly typed, allowing parameter data type errors to be caught at compilation time. The following example illustrates the second method for adding a row:

// strongly typed DataSet

Northwind.OrdersDataTable ordersTable = new Northwind.OrdersDataTable();

// add the row to the table

ordersTable.AddOrdersRow(“VINET”, 1, … );

Again, the following code sample shows how the same thing can be accomplished using an untyped DataSet:

// untyped DataSet

DataTable ordersTable = new DataTable(“Orders”);

// … code to define or retrieve the schema for the DataTable

// add the row to the table

ordersTable.Rows.Add(new Object[] {“VINET”, 1, …});

Editing a Row

Editing a row in a strongly typed DataSet is nearly the same as editing a row in an untyped DataSet. The only real difference is that columns for the rows are accessed using properties of the strongly typed DataSet. The following example illustrates editing data using a strongly typed DataSet:

// strongly typed DataSet called Northwind containing the Orders table

Northwind.OrdersDataTable ordersTable = new Northwind.OrdersDataTable();

// … code to add new rows to, or Fill the Orders table

// modify the employee ID for the first Order

Northwind.OrdersRow ordersRow = ordersTable[0];

ordersRow.EmployeeID = 2;

This example shows editing the same data using an untyped DataSet:

// untyped DataSet containing the Orders table

DataSet ds = new DataSet(“Northwind”);

DataTable ordersTable = ds.Tables.Add(“Orders”);

// … code to define or retrieve the schema for the Orders table

// … code to add new rows to, or Fill the Orders table

// modify the employee ID for the first Order

DataRow ordersRow = ordersTable.Rows[0];

ordersRow[“EmployeeID”] = 2;

Finding a Row

The strongly typed DataSet has a FindBy( ) method to locate a row in a DataTable based on the primary key. The method accepts one argument for each of the columns that make up the primary key. The method has named arguments, making it easier to work with than similar untyped DataSet code. Additionally, the arguments are typed, allowing mismatch errors to be caught at compilation time rather than at runtime. The following example demonstrates using the strongly typed DataSet FindBy( ) method and also shows comparable code using an untyped DataSet.

// strongly typed DataSet called Northwind containing the Orders table

Northwind.Order_DetailsDataTable table =

new Northwind.Order_DetailsDataTable();

// … code to add new rows to, or Fill the Orders table

// locate the row based on its primary key value

Northwind.Order_DetailsRow orderDetailsRow

= table.FindByOrderIDProductID(10248, 11);

if(orderDetailsRow != null)

{

// … code to process the row

}

This example shows comparable code using an untyped DataSet:

// untyped DataSet containing the Orders table

DataSet ds = new DataSet(“Northwind”);

DataTable ordersTable = ds.Tables.Add(“Orders”);

// … code to define or retrieve the schema for the DataTable

// … code to add new rows to, or Fill the Orders table

// locate the row based on its primary key value

DataRow orderDetailsRow  =  ordersTable.Find(new Object[] {10248, 11});

if(orderDetailsRow != null)

{

// … code to process the row

}

Null Data

The strongly typed DataSet adds two methods for each column, IsColumnNameNull( ) and SetColumnNameNull( ), to make it easier and more intuitive to work with null values in DataRow columns. IsColumnNameNull( ) returns a Boolean value indicating whether the underlying field value is null, while SetColumnNameNull( ) sets the value of the underlying field to DBNull. The following sample demonstrates using the strongly typed DataSet null handling methods:

// strongly typed DataSet called Northwind containing the Orders table

Northwind.OrdersDataTable ordersTable = new Northwind.OrdersDataTable();

// … code to add new rows to, or Fill the Orders table

// check whether the CustomerID of the first row is null

Northwind.OrdersRow ordersRow = ordersTable[0];

if(ordersRow.IsCustomerIDNull())

{

// … code to handle the null CustomerID condition

}

// set the EmployeeID to null

ordersRow.SetEmployeeIDNull();

This example shows comparable code using an untyped DataSet:

// untyped DataSet containing the Orders table

DataSet ds = new DataSet(“Northwind”);

DataTable ordersTable = ds.Tables.Add(“Orders”);

// … code to define or retrieve the schema for the DataTable

// … code to add new rows to, or Fill the Orders table

// check whether the CustomerID of the first row is null

DataRow ordersRow = ordersTable.Rows[0];

if(ordersRow.IsNull(“CustomerID”))

{

// … code to handle the null CustomerID condition

}

// set the EmployeeID to null

ordersRow[“EmployeeID”] = Convert.DBNull;

Navigating Hierarchical Data

The strongly typed DataSet provides two methods for each DataRelation, TableNameRow( ) and TableNameRows( ), to facilitate navigating records in parent child relationships. These methods are similar to the GetParentRow( ) and GetChildRows( ) methods in the untyped DataSet. The TableNameRow( ) method is defined for the child table and retrieves the parent row for a DataRow. The TableNameRows( ) method is defined for the parent table in a relationship and retrieves the child rows for a DataRow.

The strongly typed DataSet methods encapsulate the DataRelations defined within the DataSet so a reference to the DataRelation or the name of the DataRelation isn’t required when navigating the hierarchy of records. The following sample demonstrates using the strongly typed DataSet methods to navigate a hierarchy of records:

// strongly typed DataSet called Northwind containing Orders table and

// OrderDetails table, related through the OrderID field

Northwind ds = new Northwind();

// … code to fill the Orders and Order Details tables in the DataSet

foreach(Northwind.OrdersRow ordersRow in ds.Orders)

{

// iterate the collection of order details for the order through

// the GetOrderDetailsRow accessor

foreach(Northwind.Order_DetailsRow orderDetailsRow in

ordersRow.GetOrder_DetailsRows())

{

// get the CustomerID from the parent row, through the

// OrdersRow property of the child row

String customerId = orderDetailsRow.OrdersRow.CustomerID;

// get the ProductID

Int32 productId = orderDetailsRow.ProductID;

}

}

This example shows comparable code using an untyped DataSet:

// untyped DataSet containing Orders table and

// OrderDetails table, related through the OrderID field

DataSet ds = new DataSet();

// … code to define or retrieve the schema for the Orders and

// [Order Details] tables schemas including creating DataRelation objects

// … code to add new rows to the Orders and [Order Details] tables

foreach(DataRow ordersRow in ds.Tables[“Orders”].Rows)

{

foreach(DataRow orderDetailsRow in

ordersRow.GetChildRows(“Order_OrderDetails”))

{

// get the CustomerID from the parent row

String customerId  =

orderDetailsRow.GetParentRow(“Order_OrderDetails”)

[“CustomerID”].ToString();

// get the ProductID

Int32 productId = (Int32)orderDetailsRow[“ProductID”];

}

}

Annotations

By default, strongly typed DataSet classes use names from the underlying tables and columns in the data source to generate the names of methods, properties, and events. Annotations allow the names of elements in the typed DataSet to be changed without modifying the underlying schema. The names can be made more meaningful, making the strongly typed DataSet easier to use and the code using it more readable.

To use annotations, the codegen namespace declaration must be added to the XSD file. This can be placed directly after the msdata namespace declaration at the beginning of the file:

<?xml version=”1.0″ standalone=”yes”?>

<xs:schema xmlns=””

xmlns:xs=”http://www.w3.org/2001/XMLSchema&#8221;

xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”

xmlns:codegen=”urn:schemas-microsoft-com:xml-msprop”>

Annotations can be used to change the names of elements in the strongly typed DataSet. Using the Categories table in Northwind as an example, the default schema results in a DataRow name of CategoriesRow and a DataRowCollection name of Categories in the strongly typed DataSet. Here’s an excerpt from the default schema:

<xs:element>

Specifying the typedName and the typedPlural in the schema element changes the name of the DataRow to Category and the name of the DataRowCollection to Categorys. Here’s the new XSD schema:

<xs:element

codegen:typedName=”Category” codegen:typedPlural=”Categorys”>

Annotations can also change the names of methods that navigate relationships. Here’s the automatically generated code relating Categories to Products:

<xs:keyref refer=”Constraint1″>

<xs:selector xpath=”.//Products” />

<xs:field xpath=”CategoryID” />

</xs:keyref>

Adding the typedParent and typedChildren attributes to the relationship allows the method Category.Products( ) to be used instead of the default Category.GetProductsRows( ) to retrieve the child records for a product:

<xs:keyref refer=”Constraint1″

codegen:typedParent=”Categories” codegen:typedChildren=”Products”>

<xs:selector xpath=”.//Products” />

<xs:field xpath=”CategoryID” />

</xs:keyref>

Annotations can also control the way null values in the underlying data source are handled. The default schema element for the Description field of the Categories table in Northwind is a null value as shown here:

<xs:element minOccurs=”0″ />

By specifying a nullValue annotation, the default value for the Description field in the typed DataSet will be an empty string when the value for the Description is null in the underlying data source. Here’s the annotated schema element for the field:

<xs:element minOccurs=”0″

codegen:nullValue=”” />

This section introduced the use of annotations with strongly typed DataSet objects.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: