Generating a Typed DataSet

for RuBoard

Generating a Typed DataSet

As mentioned earlier, you must first generate some valid XML for the DataSet you intend to create. Well, you must have a clue of what you need in this DataSet before you can start this process. Your coding needs dictate this. If you need only a portion of the Customers data, it is a pretty small DataSet definition. Figure 20.1 illustrates the Customers table in the Northwind database. We will use all or portions of the Customer data in our coding example. Listing 20.1 is the associated XML Schema Definition for Customers (compliant with the XML Schema definition language standards).

Figure 20.1. The Customers table.

Listing 20.1 The XML Schema File for Customers DataSet

 <?xml version="1.0" encoding="utf-8"?> <xs:schema id="CustDataSet" xmlns=""     xmlns:xs="http://www.w3.org/2001/XMLSchema"     xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="CustDataSet" msdata:IsDataSet="true">     <xs:complexType>       <xs:choice maxOccurs="unbounded">         <xs:element name="Customers">           <xs:complexType>             <xs:sequence>               <xs:element name="CustomerID" type="xs:string" minOccurs="0" />               <xs:element name="CompanyName" type="xs:string" minOccurs="0" />               <xs:element name="ContactName" type="xs:string" minOccurs="0" />               <xs:element name="ContactTitle" type="xs:string" minOccurs="0" />               <xs:element name="Address" type="xs:string" minOccurs="0" />               <xs:element name="City" type="xs:string" minOccurs="0" />               <xs:element name="Region" type="xs:string" minOccurs="0" />               <xs:element name="PostalCode" type="xs:string" minOccurs="0" />               <xs:element name="Country" type="xs:string" minOccurs="0" />               <xs:element name="Phone" type="xs:string" minOccurs="0" />               <xs:element name="Fax" type="xs:string" minOccurs="0" />             </xs:sequence>           </xs:complexType>         </xs:element>       </xs:choice>     </xs:complexType>   </xs:element> </xs:schema> 

If you need a much more complex DataSet that includes perhaps Customers (all elements) and their associated orders (all elements), the XML coding is a bit longer. Figure 20.2 shows the relationship that must be traversed from the Customers table to the Orders table. This also translates into a potential complex XML schema definition that contains both customers and orders. Remember, it must allow you to traverse a relationship (from parent customers to their child orders) using one DataSet (as seen in Listing 20.2).

Figure 20.2. The Customers and Orders table relationships.

Listing 20.2 The XML Schema File for Customers and Orders DataSet

 <?xml version="1.0" encoding="utf-8"?> <xs:schema id="CustDataSetO" xmlns=""     xmlns:xs="http://www.w3.org/2001/XMLSchema"     xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="CustDataSetO" msdata:IsDataSet="true">     <xs:complexType>       <xs:choice maxOccurs="unbounded">         <xs:element name="Customers">           <xs:complexType>             <xs:sequence>               <xs:element name="CustomerID" type="xs:string" minOccurs="0" />               <xs:element name="CompanyName" type="xs:string" minOccurs="0" />               <xs:element name="ContactName" type="xs:string" minOccurs="0" />               <xs:element name="ContactTitle" type="xs:string" minOccurs="0"/>               <xs:element name="Address" type="xs:string" minOccurs="0" />               <xs:element name="City" type="xs:string" minOccurs="0" />               <xs:element name="Region" type="xs:string" minOccurs="0" />               <xs:element name="PostalCode" type="xs:string" minOccurs="0"/>               <xs:element name="Country" type="xs:string" minOccurs="0" />               <xs:element name="Phone" type="xs:string" minOccurs="0" />               <xs:element name="Fax" type="xs:string" minOccurs="0" />             </xs:sequence>           </xs:complexType>         </xs:element>         <xs:element name="Orders">           <xs:complexType>             <xs:sequence>               <xs:element name="OrderID" type="xs:int" minOccurs="0" />               <xs:element name="CustomerID" type="xs:string" minOccurs="0" />               <xs:element name="EmployeeID" type="xs:int" minOccurs="0" />               <xs:element name="OrderDate" type="xs:dateTime" minOccurs="0" />               <xs:element name="RequiredDate" type="xs:dateTime" minOccurs="0"/>               <xs:element name="ShippedDate" type="xs:dateTime" minOccurs="0" />               <xs:element name="ShipVia" type="xs:int" minOccurs="0" />               <xs:element name="Freight" type="xs:decimal" minOccurs="0" />               <xs:element name="ShipName" type="xs:string" minOccurs="0" />               <xs:element name="ShipAddress" type="xs:string" minOccurs="0"/>               <xs:element name="ShipCity" type="xs:string" minOccurs="0" />               <xs:element name="ShipRegion" type="xs:string" minOccurs="0" />               <xs:element name="ShipPostalCode" type="xs:string" minOccurs="0"/>               <xs:element name="ShipCountry" type="xs:string" minOccurs="0" />             </xs:sequence>           </xs:complexType>         </xs:element>       </xs:choice>     </xs:complexType>   </xs:element> </xs:schema> 

A quick-and-dirty way to generate the XML schema file definition for a DataSet is to utilize the WriteXMLSchema method of the DataSet and just generate it out to a physical file using the System.IO.StreamWriter object. The VB code example in Listing 20.3 defines what the DataSet should contain (Customers table data, all elements), fills the DataSet , and then writes out the XML schema for the DataSet into a physical file named Customers.xsd. Always use the "xsd" file type for XML Schema files.

Listing 20.3 Generating the XML Schema File Definition (20xmlsch.vb)

 dbConnection = new SqlConnection("server=localhost;        Trusted_Connection=yes;database=northwind")       dbSqlDataAdapter = new SqlDataAdapter         ("select * from customers", dbConnection)       CustDataSet = new DataSet()       dbSqlDataAdapter.Fill(CustDataSet,"Customers")       Dim xmlsch As System.IO.StreamWriter =               New System.IO.StreamWriter("Customers.xsd")       CustDataSet.WriteXmlSchema(xmlsch)       xmlsch.Close() 

If you wanted your DataSet to also include the Orders data (as seen in Figure 20.2), you could just create another sqlDataAdapter that defines the SELECT statement for the Orders table and fill this into your DataSet as well (as shown here):

 SqlConnection("server=localhost;Trusted_Connection=yes;database=northwind")       dbSqlDataAdapter1 = new SqlDataAdapter               ("select * from customers", dbConnection)       dbSqlDataAdapter2 = new SqlDataAdapter               ("select * from orders", dbConnection)       CustODataSet = new DataSet()       dbSqlDataAdapter1.Fill(CustODataSet,"Customers")       dbSqlDataAdapter2.Fill(CustODataSet,"Orders")       Dim xmlsch As System.IO.StreamWriter =              New System.IO.StreamWriter("CustomerO.xsd")       CustODataSet.WriteXmlSchema(xmlsch)       xmlsch.Close() 

After the XML Schema file is created, you can use this with the XSD tool to generate the typed DataSet . Open up the physical file that was generated and make sure that you have the desired schema id name ( <xs:schema id= "CustDataSet" ) along with its associated element name value ( <xs:element name="CustDataSet" ) as you can see in Listing 20.4.

Listing 20.4 Customer DataSet XML for generating a Typed DataSet (Customers.xsd)

 <?xml version="1.0" encoding="utf-8"?> <xs:  schema id="CustDataSet"  xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:  element name="CustDataSet"  msdata:IsDataSet="true">     <xs:complexType>       <xs:choice maxOccurs="unbounded">         <xs:element name="Customers">           <xs:complexType> 

Using XSD.exe: The XML Schema Definition Tool

The XSD.exe (XML Schema Definition) tool is supplied to you in the .NET Framework SDK. The XSD tool can generate XML schema or Common Language Runtime classes from XDR, XML, and XSD files, or from classes in a runtime assembly. You will be providing the XML Schema file as input (Customers.xsd in this example) along with a few directives. The file extensions drive the XSD tool logic. So if you specify (and provide) an XML file, XSD.exe will infer a schema from the data in the file and produce an associated schema file (.xsd). If you specify (and provide) an XSD file (schema file), XSD.exe will generate source code for runtime objects that correspond to the XML Schema. For our purpose the important directives are:

• /d[ataset] ” Instructs XSD.exe to generate a typed DataSet .

• / l[anguage] ” Instructs XSD.exe on what language to use (VB or CS or JS). Default language is CS (C#).

• / n(amespace]: namespace ” Specifies the runtime namespace for the generated types. The default namespace is Schemas .

The output (results) of the XSD.exe command will be the corresponding typed DataSet class code for the language specified. An example of generating the typed DataSet class code from our Customers.xsd XML Schema file would be (from the DOS prompt):

 D:>  xsd.exe /d /l:VB customers.xsd /n:XSDSchema.Northwind  Microsoft (R) Xml Schemas/DataTypes support utility [Microsoft (R) .NET Framework, Version 1.0.3617.0] Copyright (C) Microsoft Corporation 1998-2001. All rights reserved. Writing file 'D:\ADOSAMPLE\customers.vb'. 

The resulting Customers.VB source code is quite extensive , and it is recommended that this code should not be changed. Here are the first few lines from the code:

 '------------------------------------------------------------------------------ ' <autogenerated> '     This code was generated by a tool. '     Runtime Version: 1.0.3617.0 '     Changes to this file may cause incorrect behavior and will be lost if '     the code is regenerated. ' </autogenerated> '------------------------------------------------------------------------------ Option Strict Off Option Explicit On Imports System Imports System.Data Imports System.Runtime.Serialization Imports System.Xml 'This source code was auto-generated by xsd, Version=1.0.3617.0.  Namespace XSDSchema.Northwind  <Serializable(),  _      System.ComponentModel.DesignerCategoryAttribute("code"),  _      System.Diagnostics.DebuggerStepThrough(),  _      System.ComponentModel.ToolboxItem(true)>  _  Public Class CustDataSet   Inherits DataSet  Private tableCustomers As CustomersDataTable . . . 

Use the /t:library directive when compiling this code so that a corresponding library (.dll) is generated for later use. Following our example, you would specify (at the DOS prompt):

 D:>  vbc.exe  /t:Library customers.vb  /r:System.dll  /r:System.Data.dll   /r:System.Xml.dll  Microsoft (R) Visual Basic .NET Compiler version 7.00.9447 for Microsoft (R) .NET Framework version 1.00.3617 Copyright (C) Microsoft Corporation 1987-2001. All rights reserved. 

Now the typed DataSet is available to use in your code by including the namespace (Imports for VB, Using for C#) and the appropriate /r: reference for the .dll (library). The following is an example of compiling a sample VB program followed by some sample code lines of that VB program:

 D:>  vbc.exe 20usetyped.vb /r:System.dll /r:System.Data.dll /r:System.Xml.dll   /r:customers.dll  Microsoft (R) Visual Basic .NET Compiler version 7.00.9447 for Microsoft (R) .NET Framework version 1.00.3617 Copyright (C) Microsoft Corporation 1987-2001. All rights reserved. 

The preceding example compiled the VB program code shown in Listing 20.5.

Listing 20.5 Visual Basic Program Code ”Typed DataSet s (20UseTyped.vb)

 Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.VisualBasic  Imports XSDSchema.Northwind  namespace HowTo.ADONET24.Samples public class usetypedDS   public shared sub Main()     Dim mytypedds as usetypedDS = new usetypedDS()     mytypedds.Run()   end sub   public sub Run()     try       Dim CSDataSet As CustDataSet = New CustDataSet()       Dim CustomerAdapter As SqlDataAdapter =        New SqlDataAdapter("SELECT CustomerID, CompanyName, ContactName " &               "FROM Customers WHERE CustomerID like 'B%' " &               "ORDER BY         CustomerID",               "server=localhost;Trusted_Connection=yes;database=northwind;")       CustomerAdapter.Fill(CSDataSet, "Customers")       Dim CustRow As CustDataSet.CustomersRow       For each CustRow in CSDataSet.Customers           Console.Writeline("Customers Selected: " + CustRow.ContactName)       Next       Console.WriteLine ("Much simpler code")     catch e as Exception       ' Display the error.       Console.WriteLine(e.ToString())     end try   end sub end class end namespace 

Looking at the preceding code, you can readily see that using the typed DataSet has simplified the code and has improved how the code reads as well. Specifically, it's now much easier to reference the CustDataSet (the typed DataSet we created):

 Dim CSDataSet As CustDataSet = New CustDataSet() 

Also we can use all of the inherited methods and properties directly:

 Dim CustRow As CustDataSet.  CustomersRow  

and

 Console.Writeline("Customers Selected: " + CustRow.  ContactName  ) 

Now the hard work of generating the typed DataSet has been done and it can be used over and over accordingly . Go ahead and execute the sample program to verify its capability.

Using Annotations with a Typed DataSet

Please note that default reference names are given to the objects in the DataSet , such as CustomersRow for the DataRow object name and Customers for the DataRowCollection object name. This might be fine, but things can be made even simpler and clearer by using annotations with a typed DataSet . Plus, it's not very hard.

Annotations will allow you to modify the names of the elements in your typed DataSet without modifying the underlying schema.

Our coding preference is to be able to reference the DataRow object name as just "Customer" (instead of CustomerRow ) and the DataRowCollection object name as "Customers". This will make coding even simpler.

The following code is part of an annotated version of the original customers.xsd XML Schema file we used in generating our typed DataSet . To use annotations, you must include a special xmlns reference in your XML Schema file ( xmlns: codegen ="urn:schemas-microsoft-com:xml-msprop" ) . Then, each annotation uses the codegen= in its specification.

 <?xml version="1.0" encoding="utf-8"?> <xs:schema id="CustDataSet"  xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"  xmlns=""     xmlns:xs="http://www.w3.org/2001/XMLSchema"     xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="CustDataSet" msdata:IsDataSet="true">     <xs:complexType>       <xs:choice maxOccurs="unbounded">         <xs:element name="Customers"  codegen:typedName="Customer"   codegen:typedPlural="Customers">  <xs:complexType>             <xs:sequence>               <xs:element name="CustomerID"                   type="xs:string" minOccurs="0" /> 

Then we just repeat the process of generating this as a typed DataSet as we did earlier. The VB code (20UsedTyped.vb) that we used earlier can now be coded as follows :

(20UsedTypedA.vb)

 Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.VisualBasic Imports XSDSchema.Northwind namespace HowTo.ADONET24.Samples public class usetypedDSA   public shared sub Main()     Dim mytypedds as usetypedDSA = new usetypedDSA()     mytypedds.Run()   end sub   public sub Run()     try       Dim CSDataSet As CustDataSet = New CustDataSet()       Dim CustomerAdapter As SqlDataAdapter =       New SqlDataAdapter("SELECT * FROM Customers " &         "WHERE CustomerID like 'B%' ORDER BY CustomerID",         "server=localhost;Trusted_Connection=yes;database=north wind;")       CustomerAdapter.Fill(CSDataSet, "Customers")       Dim Customer As  CustDataSet.Customer  For each Customer in CSDataSet.Customers           Console.Writeline("Customers Selected: " + Customer.ContactName)       Next       Console.WriteLine ("Much more readable code")     catch e as Exception       ' Display the error.       Console.WriteLine(e.ToString())     end try   end sub end class end namespace 

And, adding new customer code is as easy as

 Dim NewCust As CustDataSet.Customer = CSDataSet.Customers.NewCustomer() NewCust.CustomerID = "BERTU" NewCust.CompanyName = "Bertucci's Pizza" CSDataSet.Customers.AddCustomer(newCust) 

Updating customer data is as easy as

 CSDataSet.Customers("BERTU").ContactName = "Donald Renato" 

for RuBoard