13.1 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. These attributes are described in more detail later in Appendix B.

<?xml version="1.0" standalone="yes"?>
<xs:schema id="Northwind" xmlns="" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="Nortdwind" 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">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="15" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Description" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="1073741823" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Picture" type="xs:base64Binary" 
                  minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Products">

        <!-- Product definition omitted. -->

        </xs:element>
      </xs:choice>
    </xs:complexType>
    <xs:unique name="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//Categories" />
      <xs:field xpath="CategoryID" />
    </xs:unique>
    <xs:unique name="Products_Constraint1"
        msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//Products" />
      <xs:field xpath="ProductID" />
    </xs:unique>
    <xs:keyref name="Categories_Products" 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.

13.1.1 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.
FindByPrimaryKeyField1 ... PrimaryKeyFieldN( )	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.