Taking a Look at ADO.NET s Behind-the-Scenes Use of XML

Taking a Look at ADO.NET’s Behind-the-Scenes Use of XML

To represent (describe) the data stored in a DataSet, ADO.NET makes extensive use of XML. In fact, each time a client program queries a database, the database returns the contents of the DataSet object using XML. The GetXML web service in Listing 7.8 provides the DatabaseXML method that queries the Duwamish7vb database Publishers table. The method then returns the query result (a data set) as a text string that contains the XML tags:

string DatabaseXML()

Listing 7.8 GetXML.asmx.vb

Imports System.Web.Services Imports System.Data.SqlClient <WebService(Namespace:="http://tempuri.org/")> _ Public Class Service1     Inherits System.Web.Services.WebService #Region " Web Services Designer Generated Code "   ' Generated code not shown #End Region <WebMethod()> Public Function DatabaseXML() As String     Try        Dim Connection As New SqlConnection("Initial Catalog=" & _           "Duwamish7vb;Data Source=(local);User ID=sa;password=;")        Connection.Open()        Dim Query As String = "SELECT * From Publishers"        Dim DataSetObj As New DataSet()        Dim Adapter As SqlDataAdapter = New _            SqlDataAdapter(Query, Connection)        Dim CmdBuilder As SqlCommandBuilder = New _            SqlCommandBuilder(Adapter)        Adapter.Fill(DataSetObj)        Connection.Close()        DatabaseXML = DataSetObj.GetXml()      Catch Ex As Exception         Throw Ex      End Try End Function End Class

To create the GetXML web service, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click ASP.NET Web Service. Finally, within the Location field, specify the folder within which you want to store the program and the program name GetXML. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the service’s components.

3. Select the View menu Code option. Visual Studio .NET will display the program’s source code.

The web service performs many of the same steps you have seen throughout this chapter’s programs to connect to and query a database. Then, the code uses the DataSet object’s GetXml method to retrieve a string that contains XML data that represents the query’s result. The method then returns the string to the caller.

Next, the Visual Basic .NET program in Listing 7.9, ShowDataSetXML.vb, uses the GetXML web service to query the Duwamish7vb database. The program then displays the query’s XML result within a text box, as shown in Figure 7.7.

To create the ShowDatSetXML.vb program, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

   
   Figure 7.7: ADO.NET uses XML to describe database objects.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name ShowDataSetXML. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls.

3. Using the Toolbox, drag and drop the button and text box previously shown in Figure 7.7 onto the form.

4. Select the Project menu Add Web Reference option. Visual Studio .NET will display the Add Web Reference dialog box.

5. Within the Address field, type localhost/GetXML/Service1.asmx?WSDL and press Enter. The dialog box will load the file’s contents. Click the Add Reference button.

6. Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.9.

Listing 7.9 ShowDataSetXML.vb

Private Sub Button1_Click(ByVal sender As System.Object, _ ÄByVal e As System.EventArgs) Handles Button1.Click    Dim WebServiceObject As New localhost.Service1()    Try       TextBox1.Text = WebServiceObject.DatabaseXML()    Catch Ex As Exception       TextBox1.Text = "Exception in Web service: " & Ex.Message    End Try End Sub

As you can see, the program’s processing is quite straightforward. The code simply calls the GetXML web service DatabaseXML method to retrieve the XML data, assigning the method’s result to a text box.

Building a DataSet Using XML-Based Data

Just as ADO.NET can generate XML statements that correspond to a DataSet object, it can also create a DataSet object from XML-based data. For example, assume that you have the XML-based basketball-team data stored in the file Basketball.xml in Listing 7.10.

Listing 7.10 Basketball.xml

<DataSet> <Table>   <Team>Sonics</Team> </Table> <Table>   <City>Phoenix</City>   <Team>Suns</Team> </Table> <Table>   <City>New York</City>   <Team>Knicks</Team> </Table> <Table>   <City>Los Angelos</City>   <Team>Lakers</Team> </Table> <Table>   <City>Miami</City>   <Team>Heat</Team> </Table> <Table>   <City>Detroit</City>   <Team>Pistons</Team> </Table> </DataSet>

The XML file uses the <DataSet> and </DataSet> tags to group <Table> and </Table> entries that define the rows that will make up a table. Within each row, the file uses the <City> and <Team> tags to define two columns of data.

Using the DataSet object’s ReadXML method, you can use the file’s contents to construct a DataSet object. The Visual Basic .NET program in Listing 7.11, BuildDataset.vb, reads the contents of the Basketball.xml file and uses the file’s contents to create a DataSet object. The program then uses the DataSet object to display data within a form, as shown in Figure 7.8. You must place the Basketball.xml file within the program’s folder. To create the BuildDataset.vb program, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name BuildDataSet. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls.

   
   Figure 7.8: Building a DataSet object from an XML file

3. Using the Toolbox, drag and drop the button and text boxes shown in Figure 7.8 onto the form.

4. Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.11.

Listing 7.11 BuildDataset.vb

Private Sub Button1_Click(ByVal sender As System.Object, _ ÄByVal e As System.EventArgs) Handles Button1.Click    Try      Dim DataSetObj As New DataSet()      DataSetObj.ReadXml("Basketball.xml")      Dim I As Integer      TextBox1.Text = ""      TextBox2.Text = ""      For I = 0 To DataSetObj.Tables(0).Rows.Count - 1         TextBox1.Text = TextBox1.Text & _            DataSetObj.Tables(0).Rows(I).Item("City") & vbCrLf         TextBox2.Text = TextBox2.Text & _            DataSetObj.Tables(0).Rows(I).Item("Team") & vbCrLf      Next    Catch Ex As Exception      MessageBox.Show(Ex.Message)    End Try End Sub

The program uses the DataSet object’s ReadXml method to read the Basketball.xml file. The ReadXml method will use the file’s XML entries to build a DataSet object. Then, the program uses a For loop to move through the records the new DataSet object contains, displaying the city and basketball team names.

The ability to create DataSet objects from XML content provides developers with a very convenient way to store data on the fly. The FileDataSet web service in Listing 7.12, for example, provides the BuildXMLFile method, which programs can use to create and store data:

integer BuildXMLFile(ByVal string Filename, ByVal string XMLData)

The BuildXMLFile method receives two parameters. The first specifies the name of the XML file the web service is to create. The second parameter is a string that contains data in an XML format. To create the FileDataSet web service, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click ASP.NET Web Service. Finally, within the Location field, specify the folder within which you want to store the program and the program name FileDataSet. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the service’s components.

3. Select the View menu Code option. Visual Studio .NET will display the program’s source code.

Listing 7.12 FileDataSet.asmx.vb

Imports System.Web.Services Imports System.IO <WebService(Namespace := "http://tempuri.org/")> _ Public Class Service1     Inherits System.Web.Services.WebService #Region " Web Services Designer Generated Code "   ' Generated code not shown. #End Region <WebMethod()> Public Function BuildXMLFile(ByVal Filename As String, _ Ä  ByVal Data As String) As Integer      Try        Dim XMLFile As New StreamWriter(Filename)        XMLFile.Write(Data)        XMLFile.Close()        BuildXMLFile = 1      Catch Ex As Exception        Throw Ex      End Try     End Function End Class

For security reasons, services restrict the files a remote user can create on the server itself. To use the FileDataSet service, you might have to assign security settings to a specific folder within which users can create a file. In Chapter 8, “Authenticating Users within .NET Services,” you will learn how to restrict access to your web services to specific users. By authenticating the user who is calling the FileDataSet web service, you can direct the service to create the file within a specific folder, for which you have set permissions that let the user create files.

The Visual Basic .NET program in Listing 7.13, StoreMemo.vb, displays a form that prompts the user to enter information that corresponds to an idea or information the user wants to store, as shown in Figure 7.9.

Figure 7.9: Prompting the user for data the program will store using a web service that builds DataSet objects on the fly

As you can see, the StoreMemo.vb program lets the user store the information within a new file or append the data to an existing file. To create the StoreMemo.vb program, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name StoreMemo. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls (label, buttons, and text box).

3. Using the Toolbox, drag and drop the button, labels, and text boxes previously shown in Figure 7.9 onto the form.

4. Select the Project menu Add Web Reference option. Visual Studio .NET will display the Add Web Reference dialog box.

5. Within the Address field, type localhost/FileDataSet/Service1.asmx?WSDL and press Enter. The dialog box will load the file’s contents. Click the Add Reference button.

6. Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.13.

Listing 7.13 StoreMemo.vb

Private Sub Button1_Click(ByVal sender As System.Object, _ Ä ByVal e As System.EventArgs) Handles Button1.Click   Dim Data As String   Try     If (TextBox3.Text.Length > 0) Then      If (TextBox2.Text.Length > 0) Then        If (TextBox1.Text.Length = 0) Then           TextBox1.Text = "No subject"        End If        Data = "<DataSet><Table>" & _               "<Subject>" & TextBox1.Text & "</Subject>" & _               "<Memo>" & TextBox3.Text & "</Memo>" & _               "</Table></DataSet>"        Dim WSObject As New localhost.Service1()        WSObject.BuildXMLFile(TextBox2.Text, Data)        MessageBox.Show("Memo stored")      End If     End If   Catch Ex As Exception    MessageBox.Show(Ex.Message)   End Try End Sub

As you can see, the form prompts the user for a target filename and information about a memo. When the user submits the data, the program groups the data within tags that create XML based data.

Retrieving a DataSet from an XML-Based File

In the previous section, you learned how to use a web service to create a file on the server. In a similar way, the RetrieveDataSet web service in Listing 7.14 lets a client program retrieve data stored in a file by the FileDataSet web service. The web service provides the BuildDataSet method that receives the name of the XML file that contains the corresponding data. Then, the method creates a DataSet object that contains the corresponding data. The method returns a DataSet object to the program, which the program can query:

DataSet BuildDataSet(ByVal Filename As String)

To create the RetrieveDataSet web service, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click ASP.NET Web Service. Finally, within the Location field, specify the folder within which you want to store the program and the program name RetrieveDataSet. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the service’s components.

3. Select the View menu Code option. Visual Studio .NET will display the program’s source code.

Listing 7.14 RetrieveDataSet.asmx.vb

Imports System.Web.Services Imports System.Data.SqlClient <WebService(Namespace := "http://tempuri.org/")> _ Public Class Service1     Inherits System.Web.Services.WebService #Region " Web Services Designer Generated Code "    ' Generated code not shown. #End Region  <WebMethod()> Public Function BuildDataSet(ByVal Filename As String) _ Ä  As DataSet    Dim DataSetObj As New DataSet()    Try      DataSetObj.ReadXml(Filename)      BuildDataSet = DataSetObj    Catch Ex As Exception      Throw Ex    End Try  End Function End Class

As you can see, the service’s processing is quite straightforward. The code creates a DataSet object and then uses the ReadXML method to read the contents of the file the caller passes to the method. The ReadXML method returns a DataSet object, which the BuildDataSet method returns to the caller. Ideally, a production-quality service would perform additional testing to ensure that the caller has specified an XML file, that the caller has permission to use the file, and so on.

The Visual Basic .NET program in Listing 7.15, UseDataSet.vb, uses the RetrieveData web service program to retrieve data stored in an XML file by the StoreMemo.vb program just discussed. When you run the program, it will display a form that prompts you for the corresponding filename. After you specify a filename and click Submit, the program will use the RetrieveDataSet web service to retrieve the corresponding DataSet. The program will display the DataSet object’s fields and data within a text box, as shown in Figure 7.10.

Figure 7.10: Retrieving a DataSet object from a web service

To create the UseDataSet.vb program, perform these steps:

1. Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.

2. Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name UseDataSet. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls (label, buttons, and text box).

3. Using the Toolbox, drag and drop the button and text box previously shown in Figure 7.10 onto the form.

4. Select the Project menu Add Web Reference option. Visual Studio .NET will display the Add Web Reference dialog box.

5. Within the Address field, type localhost/RetrieveDataSet/Service1.asmx?WSDL and press Enter. The dialog box will load the file’s contents. Click the Add Reference button.

6. Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.15.

Listing 7.15 UseDataSet.vb

Private Sub Button1_Click(ByVal sender As System.Object, _ Ä ByVal e As System.EventArgs) Handles Button1.Click    Dim DataSetObj As DataSet    Try      If (TextBox1.Text.Length > 0) Then        Dim WSObject As New localhost.Service1()        DataSetObj = WSObject.BuildDataSet(TextBox1.Text)        Dim I As Integer        TextBox2.Text = TextBox2.Text & _           DataSetObj.Tables(0).Rows(I).Item("Subject") & vbCrLf        TextBox2.Text = TextBox2.Text & _           DataSetObj.Tables(0).Rows(I).Item("Memo") & vbCrLf      End If    Catch Ex As Exception      MessageBox.Show(Ex.Message)    End Try End Sub

As you can see, the program assigns the DataSet object returned by the BuildDataSet method to a program variable. Then, the program uses the object to display the values of the Subject and Memo columns within the table. In this case, the program takes advantage of knowledge you have about the structure of the database the FileDataSet web service creates in order to reference the Subject and Memo columns. If a program does not have such knowledge about a DataSet, the program can query the DataSet for information about each column.