DataView Object Reference | Microsoft ADO.NET (Core Reference) (PRO-Developer)

Creating Strongly Typed DataSet Objects

So how do you create a strongly typed DataSet class? You can choose one of two basic approaches. One involves writing code and using a command-line tool that's part of the .NET Framework SDK. The other approach, which is much simpler, relies on the Visual Studio .NET development environment and doesn't require you to open a Command window.

The Hard Way

The .NET Framework SDK includes a command-line utility called the XML Schema Definition Tool, which helps you generate class files based on XML schema (.xsd) files. You can use this utility in conjunction with the DataSet object's WriteXmlSchema method to translate your DataSet into a strongly typed DataSet class.

Using the DataSet Object's WriteXmlSchema Method

In Chapter 6, when we looked at how to create DataSet objects in the Visual Studio .NET development environment, you saw that Visual Studio .NET added a file to your project with an .xsd extension. This file contains schema information (tables, columns, constraints, and relationships) for your DataSet. You can create this file programmatically using the DataSet object's WriteXmlSchema method.

The WriteXmlSchema method is overloaded to accept a Stream object, a TextWriter object, an XmlWriter object, or a filename as a string. The following code snippet builds a DataSet using columns from the Customers and Orders tables in the sample Northwind database. It also adds a DataRelation between the two DataTable objects before writing the schema for the DataSet to a file.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _          "FROM Customers" Dim daCustomers As New OleDbDataAdapter(strSQL, cn) strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _          "FROM Orders" Dim daOrders As New OleDbDataAdapter(strSQL, cn) Dim ds As New DataSet() ds.DataSetName = "Chapter9" cn.Open() daCustomers.FillSchema(ds, SchemaType.Source, "Customers") daOrders.FillSchema(ds, SchemaType.Source, "Orders") cn.Close() ds.Relations.Add("CustomersOrders", _                  ds.Tables("Customers").Columns("CustomerID"), _                  ds.Tables("Orders").Columns("CustomerID")) ds.WriteXmlSchema("C:\Chapter9.XSD")

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +          "FROM Customers"; OleDbDataAdapter daCustomers = new OleDbDataAdapter(strSQL, strConn); strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " + _          "FROM Orders"; OleDbDataAdapter daOrders = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); ds.DataSetName = "Chapter9"; cn.Open(); daCustomers.FillSchema(ds, SchemaType.Source, "Customers"); daOrders.FillSchema(ds, SchemaType.Source, "Orders"); cn.Close(); ds.Relations.Add("CustomersOrders",                   ds.Tables["Customers"].Columns["CustomerID"],                   ds.Tables["Orders"].Columns["CustomerID"]); ds.WriteXmlSchema("C:\\Chapter9.XSD");


	The preceding code snippet uses the FillSchema method of the DataAdapter object. I generally advise developers to avoid using this method in their applications whenever possible. The purpose of the code snippet is to generate an .xsd file that contains DataSet schema information, so I consider it to be "design-time" code, which is exactly the type of scenario for which the method was created.

Using the XML Schema Definition Tool

The XML Schema Definition Tool is simply a file in the bin directory called XSD.exe. The tool can generate class files based on XML schema (.xsd or .xdr) files. It can also create XML schema files from libraries (.dll) and executables (.exe).

In the previous code snippet, we saved the schema for a DataSet to an .xsd file. Now let's use the XML Schema Definition Tool to generate a class file based on this file. Open a Command window and type the following:


	To create a Command window, choose Programs, Accessories, Command Prompt from the Start menu. Or, you can choose Run from the Start menu and then type cmd.exe.

Visual Basic .NET

C:\>XSD Chapter9.XSD /d /l:VB

Visual C# .NET

C:\>XSD Chapter9.XSD /d


	You can either enter the full path to the XSD.exe file or add the .NET Framework SDK's bin directory to your system's Path environment variable. You'll also need to supply the path to your XML schema file.

The first parameter is the path to the XML schema file. The second parameter indicates that the class we want to create is derived from the DataSet class. The Visual Basic .NET example uses a third parameter to specify the language for the output file. By default, the tool generates Visual C# .NET class files.

The XML Schema Definition Tool also offers other options that are documented in the .NET Framework SDK. You can type XSD /? to list the available options in the Command window.

Now you can simply add your new class file to your project, and you can create an instance of your new strongly typed DataSet class, as shown in the following code snippet:

Visual Basic .NET

Dim ds As New Chapter9()

Visual C# .NET

Chapter9 ds = new Chapter9();


	The name of your class is based on the DataSetName property of the DataSet used to generate your .xsd file. In the code snippet that generated the .xsd file, the DataSetName property is set to Chapter9, which is also the name of our new strongly typed DataSet class.

The Easy Way

Creating a strongly typed DataSet class in Visual Studio .NET is much simpler than the approach you saw in the previous example. There's no code to write and, best of all, no Command window.

To demonstrate how much easier it is to create a strongly typed DataSet class in Visual Studio .NET, let's build the same strongly typed DataSet class that we built in the previous section using code and the Command window. Like the previous DataSet, the new DataSet will include two DataTable objects and a DataRelation.

First, create a new Microsoft Windows application in your language of choice. Then, using the Data tab of the Toolbox, add two OleDbDataAdapter objects to your Windows form. In the Data Adapter Configuration Wizard, point both OleDbDataAdapter objects to your favorite Northwind database. Enter the following SQL statements on the Generate SQL Statement page of the wizard for your DataAdapter objects:

SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders

Right-click on the designer and choose Generate Dataset from the shortcut menu. In the dialog box that appears, shown in Figure 9-1, type Chapter9 as the DataSet name and choose OK.

Figure 9-1

Generating a new strongly typed DataSet

In one swift step, you've created both the DataSet schema file and the strongly typed DataSet class. The only difference between this DataSet and the one we created earlier is that this one does not have a DataRelation defined. Yet.

In the Solution Explorer window, double-click on the schema file. Right-click on the Orders table in the XML Schema Designer and choose Add, New Relation from the shortcut menu. The Edit Relation dialog box will open, as shown in Figure 9-2. Accept the defaults, and then click OK.

Figure 9-2

Adding a DataRelation to your DataSet

That's it. No muss, no fuss. No oily residue. You can now create instances of your new strongly typed DataSet class in code, as you saw earlier, or at design time using the DataSet item on the Data tab of the Toolbox.

When you generated your DataSet, Visual Studio .NET went through the following steps to create your new strongly typed DataSet class:

Created a new instance of the DataSet class.
Called the FillSchema method of all of the DataAdapter objects selected in the Generate Dataset dialog box to add schema information to the new DataSet.
Called the WriteXmlSchema method of the DataSet.
Added the .xsd file to the project.
Used the XML Schema Definition Tool to generate the strongly typed DataSet class based on the .xsd file.
Added the new class file to the project.

Where Is the Class File?

But where is the class file for the strongly typed DataSet? If you look closely, you'll see a toolbar at the top of the Solution Explorer window. One of the toolbar buttons has an icon showing multiple files. If you move your mouse over the button, the ToolTip will say "Show All Files." Click this toolbar button to see all of the hidden files in your project in a tree view.

The schema file for your DataSet (Chapter9.xsd) will have two files associated with it. The first will be the class file for your strongly typed DataSet—either Chapter9.vb or Chapter9.cs, depending on your language of choice. The second associated file will have an .xsx extension; this is just a text file that contains settings for the layout of your DataSet in the XML Schema Designer.

The class file actually contains many classes. There's the main class, which is derived from DataSet. This class exposes the two DataTable objects—Customers and Orders, each of which returns a class derived from DataTable. Each of these classes (CustomersDataTable and OrdersDataTable) exposes a default Item property that returns a table-specific class derived from DataRow, as shown here:

Visual Basic .NET

Dim ds As New Chapter9() OleDbDataAdapter1.Fill(ds) Dim tblCustomers As Chapter9.CustomersDataTable = ds.Customers Dim rowCustomer As Chapter9.CustomersRow = tblCustomers(0)

Visual C# .NET

Chapter9 ds = new Chapter9(); OleDbDataAdapter1.Fill(ds); Chapter9.CustomersDataTable tblCustomers = ds.Customers; Chapter9.CustomersRow rowCustomer = tblCustomers[0];