Chapter: XML and Web Services : Building XML-Based Applications : Leveraging XML in Visual Studio .NET

XML Within ADO.NET

XML is at the core of the functionality provided by the ADO.NET architecture. It allows developers to more easily access data in a distributed/disconnected environment.

XML Within ADO.NET

XML is at the core of the functionality provided by the ADO.NET architecture. It allows developers to more easily access data in a distributed/disconnected environment. The XML support within the ADO.NET architecture is immense, and the majority of it can be found within the functionality provided and included with the DataSet class.

Data sets can be created directly from XML documents or streams, allowing a hierarchi-cal set of data to be loaded and accessed in a relational manner. The data sets can then create XML documents representing the data in a hierarchical fashion and use XML as a transport mechanism to allow data sets to be easily transported via HTTP. In fact, if, for example, a Web service returns a DataSet object, for anything other than another .NET application, this will be returned as a DiffGram, which is explained later in this chapter. However, if a .NET application uses that same Web service, .NET is smart enough to “dehydrate” the DataSet object into XML for transport via HTTP and then “rehydrate” the DiffGram into a DataSet object on the other side, thereby simplifying data access and allowing the developer to concentrate on more important tasks, such as what to do with that data once it’s returned.

The support for XML within ADO.NET doesn’t stop there. In fact, it gets better. A developer can create “typed” data sets using XSD schemas to create a mapping between the data store and the data set. ADO.NET then uses these maps when accessing and updating information in the data store.

Typed Data Sets

Typed data sets within ADO.NET provide a sort of string-typing mechanism for data access. By creating this string typing, you can access the tables and columns within the data set by name rather than through collection-based methods. Another added benefit to using typed data sets is the ability of Intellisense to provide autocomplete features for tables and columns contained in the data set.

So, how do you create a typed data set? To illustrate this, we’ll use the Northwind data-base included in SQL Server 2000 to create a sample typed data set for the Customers table. This example assumes you’ve either created a new project in VS .NET or have opened an existing one for which you’d like to create a typed data set. Once there, go to the Solution Explorer, right-click the main project solution item, and then select Add, Add New Item. This will bring up the Add New Item dialog box, as shown in Figure 17.1.

Select the Data Set item on the right side of the dialog box and enter a name for the XSD schema. For this example, we’ll use the filename CustomersDataSet.xsd. Once you have entered the filename, click the Open button. This will take you back to the design area shown in Figure 17.2.

Now, you need to open a connection to your data store. To accomplish this, from the main menu, select Tools, Connect To Database, which will bring up the Data Link Properties dialog box shown in Figure 17.3.

Using this dialog box, you will need to enter your connection information, such as the instance of SQL Server to connect to, how to log in to the data store, and what database to open. For our purposes, we’ll use the Northwind database. Once, you’ve entered the information needed to open the connection, you can test the connection before commit-ting the information by clicking the Test Connection button. Once you’re confident of the connection information, you can open the connection by clicking the OK button.

From here, you can navigate through Server Explorer to drag and drop items from your data store into the data set design area. For the purposes of our demonstration, select the Customers table and drag and drop it into the design area for the data set. Once you’ve completed this, your design area should look similar to Figure 17.4.

You can now move this graphical item around on the design area, add or remove other tables, add or remove columns from the Customers table, and change the data types for each column. Once you have modified the data set definition, you can view the XML generated for it by clicking the XML button in the design area. Your screen should now appear similar to Figure 17.5.

The actual XSD schema generated is shown in Listing 17.1.

LISTING 17.1 CustomersDataSet.xsd Contains the Information to Create a Typed Data Set for the Customers Table

<?xml version=”1.0” encoding=”utf-8” ?> <xsd:schema id=”CustomersDataSet” targetNamespace=

➥ ”http://tempuri.org/Customers.xsd” ➥ elementFormDefault=”qualified”

➥ xmlns=”http://tempuri.org/Customers.xsd”

➥ xmlns:xsd=”http://www.w3.org/2001/XMLSchema”

➥ xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”> <xsd:element name=”Customers” msdata:IsDataSet=”true”>

<xsd:complexType>

<xsd:choice maxOccurs=”unbounded”> <xsd:element name=”Customers”>

<xsd:complexType>

<xsd:sequence>

<xsd:element name=”CustomerID” type=”xsd:string” /> <xsd:element name=”CompanyName” type=”xsd:string”

➥ minOccurs=”0” />

<xsd:element name=”ContactName” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”ContactTitle” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”Address” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”City” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”Region” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”PostalCode” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”Country” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”Phone” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”Fax” type=”xsd:string” ➥ minOccurs=”0” />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:choice>

</xsd:complexType>

<xsd:unique name=”CustomersKey1” msdata:PrimaryKey=”true”> <xsd:selector xpath=”.//Customers” />

<xsd:field xpath=”CustomerID” /> </xsd:unique>

</xsd:element>

</xsd:schema>

Now that we’ve modified and adjusted the data set’s XSD schema, we can generate the typed data set by selecting (assuming you installed Visual Studio .NET in its default location) Start, Programs, Microsoft Visual Studio .NET 7.0, Visual Studio .NET Tools, Visual Studio .NET Command Prompt. This will bring up the Visual Studio .NET com-mand prompt. By default, this will show up in the root directory. You will need to change this to your project’s directory. In this example, we’ll use C:\Documents and Settings\

Travis.TRAVISNOTEBOOK\My Documents\Visual Studio Projects\DevConADODemo\.

Once you’re in your project directory, you can run a command-line program called xsd.exe to create your typed data set. The basic syntax for this program is shown here:

xsd /d /l:Language SchemaFileName [/n:NamespaceName]

The /d directive indicates that the program should create a typed data set. The /l direc-tive specifies the language to use—for instance, /l:CS indicates that the data set should be generated in C#. Next, SchemaFileName is the name for your XSD schema file, and the /n directive allows you to specify a namespace to be generated for the typed data set, which will allow you to include that namespace within your applications. Figure 17.6 shows the command prompt for Visual Studio .NET with the full xsd command entered.

Once this command has successfully executed, it will create a file with the same name, CustomersDataSet, with a .cs extension for C#. We can then go back into the Visual Studio .NET Solution Explorer, right-click the project, and then select Add, Add Existing Item. This will bring up the Add Existing Item dialog box. From here, you can select the file generated by the xsd.exe program and click the Open button to add it to the project, as shown in Figure 17.7.

This will add the file to your project, and you can then begin to use the newly created typed data set in your application. If you decide to generate a namespace, using the /n option, for your typed data set, as was done in the example, you can include it in your application by referencing that namespace as shown in the following code for C#:

using CustomersDataSet;

Here’s the code for VB .NET:

Imports CustomersDataSet

Now you can create strongly typed variables based on your data set—in this example, CustomersDataSet. As mentioned before, one of the biggest benefits to using typed data sets is the use of Intellisense when accessing members from your typed data set, as shown in Figure 17.8.

From Figure 17.8, you can see that we can directly access the Customers data table via a property on the CustomersDataSet object itself. In data sets that are not typed, you had to access the data table from the Tables collection on the DataSet object.

The following C# code demonstrates how to create the typed data set and sets the captions for the CustomerID and CompanyName columns:

string cConn = “Data Source=TRAVISNOTEBOOK\\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”; System.Data.SqlClient.SqlConnection oConn = new ➥ System.Data.SqlClient.SqlConnection(cConn);

oConn.Open();

System.Data.SqlClient.SqlDataAdapter oDA = new

➥ System.Data.SqlClient.SqlDataAdapter(

➥ ”Select * From Customers”, oConn);

CustomersDataSet.CustomersDataSet oDS = new

➥ CustomersDataSet.CustomersDataSet();

oDA.Fill(oDS,”Customers”);

oConn.Close();

oDS.Customers.CustomerIDColumn.Caption = “Customer Id”;

oDS.Customers.CompanyNameColumn.Caption = “Company Name”;

Here’s the code for VB .NET:

Dim cConn As string = “Data Source=

➥ TRAVISNOTEBOOK\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”

Dim oConn As System.Data.SqlClient.SqlConnection = new

➥ System.Data.SqlClient.SqlConnection(cConn)

oConn.Open();

Dim oDA As System.Data.SqlClient.SqlDataAdapter = new

➥ System.Data.SqlClient.SqlDataAdapter( ➥ ”Select * From Customers”, oConn)

Dim oDS As CustomersDataSet.CustomersDataSet = new ➥ CustomersDataSet.CustomersDataSet()

oDA.Fill(oDS,”Customers”)

oConn.Close()

oDS.Customers.CustomerIDColumn.Caption = “Customer Id”

oDS.Customers.CompanyNameColumn.Caption = “Company Name”

As you can see from the preceding code, we create the connection string to our SQL Server Northwind database, create the SqlDataAdapter object, create the CustomersDataSet object, use the SqlDataAdapter object to fill the typed CustomersDataSet object, close the connection to the database, and then set the column captions.

Loading an XML Document into a Data Set

XML is starting to play a bigger and bigger role in today’s business world, and as a result, today’s applications need to be able to perform extraordinary tasks based on XML documents or even XML fragments. Microsoft recognizes the importance of XML within today’s industries and has attempted to integrate support for XML within its newest tech-nologies, including Visual Studio .NET and the various .NET Enterprise server applica-tions that will be discussed in Chapter 18, “Using XML in the .NET Enterprise Servers.”

Because XML is the key technology that enables data sets to function, it’s really a rather trivial task to load an XML document into a DataSet object. Why would you ever need to do this? Sometimes it may be easier to access an XML document in a relational man-ner rather than a hierarchical one. For these cases, Microsoft has included the ability to load XML documents directly into DataSet objects by calling the ReadXml() method. This method accepts two parameters. The first parameter is the XML source, and a sec-ond (optional) parameter indicates how the XML document should be loaded into the

DataSet object.

The following C# code demonstrates a simple example of loading an XML file called

Customers1.xml into a DataSet object:

System.Data.DataSet oDS = new System.Data.DataSet(); oDS.ReadXml(“http://localhost/sql2000/template/customers1.xml”);

Here’s the code for VB .NET:

Dim oDS As System.Data.DataSet = new System.Data.DataSet(); oDS.ReadXml(“http://localhost/sql2000/template/customers1.xml”);

Notice that the second parameter isn’t used in the preceding examples. By not explicitly passing an optional, second parameter, we leave it up to .NET to decide the best way to create the DataSet object from the given XML document. Also, the first parameter itself can be an XML stream, an XML document, or an XmlReader object because the ReadXml() method is overloaded. This gives you, the developer, added flexibility in application design by allowing you to load XML into a data set from multiple sources.

Writing an XML Document from a DataSet Object

Just as you can load an XML document into a DataSet object to represent hierarchical data as relational data, you can create an XML document from a DataSet object, thereby allowing yourself to represent relational data in a hierarchical fashion. The XML can be retrieved from the data set as a string, an XML file, an XML stream, or an XMLWriter object, depending on whether you call the GetXml method or the WriteXml method. The GetXml method always returns an XML string, whereas the WriteXml method allows you to specify whether you want to create a file, write the XML to a stream, or write it to an XMLWriter object. It just depends on which output you desire. If you want the XML as a string, you can call the GetXml() method on the DataSet object, as shown in the follow-ing C# code:

string cConn = “Data Source=TRAVISNOTEBOOK\\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”; System.Data.SqlClient.SqlConnection oConn = new ➥ System.Data.SqlClient.SqlConnection(cConn);

oConn.Open();

System.Data.SqlClient.SqlDataAdapter oDA = new

➥ System.Data.SqlClient.SqlDataAdapter( ➥ ”Select * From Customers”, oConn);

System.Data.DataSet oDS = new System.Data.DataSet();

oDA.Fill(oDS,”Customers”);

oConn.Close();

string cXml;

cXml = oDS.GetXml();

Here’s the code for VB .NET:

Dim cConn As string = “Data Source=

➥ TRAVISNOTEBOOK\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”

Dim oConn As System.Data.SqlClient.SqlConnection = new ➥ System.Data.SqlClient.SqlConnection(cConn)

oConn.Open();

Dim oDA As System.Data.SqlClient.SqlDataAdapter = new

➥ System.Data.SqlClient.SqlDataAdapter( ➥ ”Select * From Customers”, oConn)

Dim oDS As System.Data.DataSet = new System.Data.DataSet()

oDA.Fill(oDS,”Customers”)

oConn.Close()

Dim cXml As string cXml = oDS.GetXml()

From these examples, you can see that we create a connection string, open a connection to the database, create a SqlDataAdapter object, use the SqlDataAdapter object to fill the data set, close the connection to the database, and finally get the XML string for the data set. However, there is one limitation to this method: The GetXml() method only returns an XML string that represents the data within the data set, not the XML schema for it. To retrieve the XML schema, call the GetXmlSchema()method on the DataSet object, as shown in the following C# code:

string cConn = “Data Source=TRAVISNOTEBOOK\\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”; System.Data.SqlClient.SqlConnection oConn = new ➥ System.Data.SqlClient.SqlConnection(cConn);

oConn.Open();

System.Data.SqlClient.SqlDataAdapter oDA = new

➥ System.Data.SqlClient.SqlDataAdapter( ➥ ”Select * From Customers”, oConn);

System.Data.DataSet oDS = new System.Data.DataSet();

oDA.Fill(oDS,”Customers”);

oConn.Close();

string cXmlSchema;

cXmlSchema = oDS.GetXmlSchema();

Here’s the code for VB .NET:

Dim cConn As string = “Data Source=

➥ TRAVISNOTEBOOK\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”

Dim oConn As System.Data.SqlClient.SqlConnection = new ➥ System.Data.SqlClient.SqlConnection(cConn)

oConn.Open();

Dim oDA As System.Data.SqlClient.SqlDataAdapter = new

➥ System.Data.SqlClient.SqlDataAdapter( ➥ ”Select * From Customers”, oConn)

Dim oDS As System.Data.DataSet = new System.Data.DataSet()

oDA.Fill(oDS,”Customers”)

oConn.Close()

Dim cXmlSchema As string cXmlSchema = oDS.GetXmlSchema()

These examples are the same as the ones for returning the XML string representation, except that instead of calling the GetXml() method to return the XML string for the data in the data set, we call the GetXmlSchema() method to return the XML schema for the data in the data set.

Persisting a DataSet as an XML File

Sometimes, however, you may wish to create an actual XML file with both the data and the schema information in it at the same time, or you may even wish to reduce the num-ber of steps required to create an XML document file. In this case, you can call the WriteXml() method to immediately create an XML document file containing just the data, the data and the schema information, or a third type of XML document used exten-sively by ADO.NET—a DiffGram.

The following C# code demonstrates how to write an XML document with only the data based upon the data in the dataset:

string cConn = “Data Source=TRAVISNOTEBOOK\\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”; System.Data.SqlClient.SqlConnection oConn = new ➥ System.Data.SqlClient.SqlConnection(cConn);

oConn.Open();

System.Data.SqlClient.SqlDataAdapter oDA = new

➥ System.Data.SqlClient.SqlDataAdapter(

➥ ”Select * From Customers Where CustomerID Like ➥ ’A%’”, oConn);

System.Data.DataSet oDS = new System.Data.DataSet();

oDA.Fill(oDS,”Customers”);

oConn.Close();

oDS.WriteXml(“c:\Customers.xml”,

➥ System.Data.XmlWriteMode.IgnoreSchema);

Here’s the code for VB .NET:

Dim cConn As string = “Data Source=

➥ TRAVISNOTEBOOK\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”

Dim oConn As System.Data.SqlClient.SqlConnection = new

➥ System.Data.SqlClient.SqlConnection(cConn)

oConn.Open();

Dim oDA As System.Data.SqlClient.SqlDataAdapter = new

➥ System.Data.SqlClient.SqlDataAdapter(

➥ ”Select * From Customers Where CustomerID Like ➥ ’A%’”, oConn)

Dim oDS As System.Data.DataSet = new System.Data.DataSet()

oDA.Fill(oDS,”Customers”)

oConn.Close()

oDS.WriteXml(“c:\Customers.xml”,

➥ System.Data.XmlWriteMode.IgnoreSchema)

The preceding examples create an output file called Customers.xml in the root directory of C, as shown in Listing 17.2.

LISTING 17.2 Customers.xml Contains the XML Output from Calling WriteXml on the Data Set Without Including Schema Information

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

<CustomerID>ALFKI</CustomerID> <CompanyName>Alfreds Futterkiste

➥ </CompanyName>

<ContactName>Maria Anders</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>Obere Str. 57</Address> <City>Test</City>

<Country>Germany</Country> <Phone>030-0074321</Phone> <Fax>030-0076545</Fax>

</Customers>

<CustomerID>ANATR</CustomerID>

<CompanyName>Ana Trujillo Emparedados y helados ➥ </CompanyName>

<ContactName>Ana Trujillo</ContactName> <ContactTitle>Owner</ContactTitle> <Address>Avda. de la Constitución 2222</Address> <City>México D.F.</City> <PostalCode>05021</PostalCode> <Country>Mexico</Country>

</Customers>

<CustomerID>ANTON</CustomerID> <CompanyName>Antonio Moreno Taquería

➥ </CompanyName>

LISTING 17.2 continued

<ContactName>Antonio Moreno</ContactName> <ContactTitle>Owner</ContactTitle> <Address>Mataderos 2312</Address> <City>México D.F.</City> <PostalCode>05023</PostalCode> <Country>Mexico</Country>

<CustomerID>AROUT</CustomerID> <CompanyName>Around the Horn</CompanyName> <ContactName>Thomas Hardy</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>120 Hanover Sq.</Address> <City>London</City>

</Customers>

</NewDataSet>

The following C# code shows you how to create an XML file from a data set that includes the data and the schema information:

string cConn = “Data Source=TRAVISNOTEBOOK\\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”; System.Data.SqlClient.SqlConnection oConn = new ➥ System.Data.SqlClient.SqlConnection(cConn);

oConn.Open();

System.Data.SqlClient.SqlDataAdapter oDA = new

➥ System.Data.SqlClient.SqlDataAdapter(

➥ ”Select * From Customers Where CustomerID Like ➥ ’A%’”, oConn);

System.Data.DataSet oDS = new System.Data.DataSet();

oDA.Fill(oDS,”Customers”);

oConn.Close();

oDS.WriteXml(“c:\CustomersWithSchema.xml”, ➥ System.Data.XmlWriteMode.WriteSchema);

Here’s the code for VB .NET:

Dim cConn As string = “Data Source=

➥ TRAVISNOTEBOOK\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”

Dim oConn As System.Data.SqlClient.SqlConnection = new ➥ System.Data.SqlClient.SqlConnection(cConn)

oConn.Open();

Dim oDA As System.Data.SqlClient.SqlDataAdapter = new

➥ System.Data.SqlClient.SqlDataAdapter(

➥ ”Select * From Customers Where CustomerID Like ➥ ’A%’”, oConn)

Dim oDS As System.Data.DataSet = new System.Data.DataSet()

oDA.Fill(oDS,”Customers”)

oConn.Close()

oDS.WriteXml(“c:\CustomersWithSchema.xml”, ➥ System.Data.XmlWriteMode.WriteSchema)

The preceding examples create an XML file in the root directory of C called CustomersWithSchema.xml that includes the data and the schema information for it, as shown in Listing 17.3.

LISTING 17.3 CustomersWithSchema.xml Contains the Data and the Schema Information for the Data Set

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

<xsd:schema id=”NewDataSet” targetNamespace=”” xmlns=””

➥ xmlns:xsd=”http://www.w3.org/2001/XMLSchema”

➥ xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”> <xsd:element name=”NewDataSet” msdata:IsDataSet=”true”>

<xsd:complexType>

<xsd:choice maxOccurs=”unbounded”> <xsd:element name=”Customers”>

<xsd:complexType>

<xsd:sequence>

<xsd:element name=”CustomerID” type=”xsd:string” ➥ minOccurs=”0” />

<xsd:element name=”CompanyName” type=”xsd:string” ➥ minOccurs=”0” />