Building the Data Set and XML Viewer Project

Create a new Windows project, and call it SqlXMLExample. On the default form, add a tab control. We will be adding a data grid to the first tab and text boxes to the next two tabs so that we can have different views of the same data. To create the rest of the project, follow these steps:

Right-click the tab control, and select Properties. In the Properties window, click the button with three dots in the TabPages property to display the TabPage Collection Editor, shown in Figure 11-6. The editor permits us to modify the appearance of each of the tab objects in the tab collection. Add two additional tabs by clicking the Add button. Change the Text property of the TabPage1 tab to Data, as shown in Figure 11-6, the TabPage2 tab to XML, and the TabPage3 tab to XML Schema.

Figure 11-6
Use the TabPage Collection Editor to add tabs and set their properties.
Add a data grid control to the first tab. Set the Dock property of the data grid to Fill. This setting will expand the control to fill the entire container of the tab, as shown in Figure 11-7. Change the name of the control to dgDataGrid.
Add two buttons to the form as shown in Figure 11-7. Name the first one btnRetrieve, and set its Text property to &Retrieve Data. Name the second button btnUpdate, and set its Text property to &Update Datasource.

Figure 11-7
The data grid control fills the tab control.
Click the second tab, and add a text box control. Do the same thing for the third tab. Table 11-1 lists the properties to set for the form and its controls.

Table 11-1 Properties to Set for SqlXMLExample
Control	Property	Value
Form	Text	Datasets and XML
Tab control	Name	tcTabControl
	TabCollection	Add tabs as described in step 1
Data grid on tab 1	Name	dgDataGrid
	Dock	Fill
Text box on tab 2	Name	txtXML
	Dock	Fill
	MultiLine	True
	ScrollBars	Both
Text box on tab 3	Name	txtXMLSchema
	Dock	Fill
	MultiLine	True
	ScrollBars	Both
Button	Name	btnRetrieve
	Text	&Retrieve Data
Button	Name	btnUpdate
	Text	&Update Datasource

Adding the Connection, Data Adapter, and DataSet Objects

I won't spend a lot of time on this aspect of building the program because I covered it in depth in the last chapter. I'll only discuss the modifications to the objects.

Add a SqlConnection object to your program. Right-click the SqlConnection object and select Properties. In the drop-down list for the ConnectionString property, select the Northwind.dbo connection we built in Chapter 10.
Add a SqlDataAdapter object to your program. The Data Adapter Configuration Wizard starts when you drop the object on the form; click Next on the Welcome screen. The Northwind.dbo connection will be selected on the next screen, so simply keep that and click Next. Leave the Use SQL Statements option selected, and then click Next. The SQL statement will be different from the example in Chapter 10. Add "SELECT CategoryID, CategoryName, Description FROM Categories" in the text box, as shown in Figure 11-8. (We don't select all the records in this example because a link to a picture object is also stored in the Categories table.)

Figure 11-8
This SQL statement selects the fields we want.

Click Next, and the wizard will do its magic. Then click Finish to complete the configuration of the data adapter.
Right-click the form, and then select Generate Dataset. Keep the default settings in the dialog box, and then click OK. This will add the object to the program. Simply by using the wizards, all of the internal mechanisms to connect to the database are in place. We now just have to add a few lines of code to wire everything together.

Adding Code to Our Program

At the top of the form class, add the namespaces that we will use for our SQL connection, data adapter, and data set. We also need to add a class-level Boolean variable that will be set to True when data is placed into our data grid. Unfortunately, there is no easy way to clear the grid manually, so we will simply use the Boolean variable to skip the code that updates the grid if it has already been filled. Simple, yet effective. Of course, we could disable the Retrieve button after the data has been added, thus preventing the user from double or triple populating the grid with the same data. Here's the code to add:

Imports System Imports System.Data.SqlClient Public Class Form1 Inherits System.Windows.Forms.Form Dim bDataAdded As Boolean = False

Now add the following code to the btnRetrieve Click event handler. By now, this code should be second nature to you.

Private Sub btnRetrieve_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btmRetrieve.Click If bDataAdded = True Then Exit Sub Try SqlDataAdapter1.Fill(DataSet11, "Categories") With dgDataGrid .CaptionText = "Examining XML" .DataSource = DataSet11 .AllowSorting = True .AlternatingBackColor = System.Drawing.Color.Bisque .SetDataBinding(DataSet11, "Categories") End With '-- Update the XML tab txtXML.Text = DataSet11.GetXml '-- Update the XML Schema tab txtXMLSchema.Text = DataSet11.GetXmlSchema bDataAdded = True Catch ex As Exception Console.WriteLine(ex.ToString()) End TryEnd Sub

Now add this code to the btnUpdate Click event handler.

Private Sub btnUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click Try dgDataGrid.Update() SqlDataAdapter1.Update(DataSet11) Catch ex As SystemException MessageBox.Show(ex.Message) End TryEnd Sub

That's all there is to it. If you are so inclined, examine the regions of code in the form and spend a few minutes reviewing the code the wizard added for us. Contrast that with the few lines that we had to add to wire everything together. This example is a good illustration of just how productive programmers will be using Visual Basic .NET. Run the program, and examine the output placed in the XML and XML Schema tabs.

How It Works

As you now know, the SqlDataAdapter object manages getting data to and from the database by using the appropriate Transact-SQL statements against the data source. Recall that the SqlClient classes are optimized for Microsoft SQL Server 7 and later. Therefore, the SqlDataAdapter object is used in conjunction with the SqlConnection and SqlCommand objects to increase performance when connecting to a SQL Server database. Because there's a chance that our database won't be available, we want to put the code that accesses it (in the btnRetrieve Click event handler) in a structured Try block.

Try SqlDataAdapter1.Fill(DataSet11, "Categories") With dgDataGrid .CaptionText = "Examining XML" .DataSource = DataSet11 .AllowSorting = True .AlternatingBackColor = System.Drawing.Color.Bisque .SetDataBinding(DataSet11, "Categories") End With

Next, we set a few properties of the data grid and then bind it to our data set. The only new property here is CaptionText, which places a header on the data grid—a nice detail that you should consider using in your production code.

Generating XML from Our Data Set

With Visual Basic .NET, displaying the XML representation and the XML schema of the data contained in our data set is pretty trivial. Simply set the Text property of each text box to the appropriate and self-describing methods of our DataSet object.

'-- Update the XML tab txtXML.Text = dsDataSet.GetXml '-- Update the XML Schema tab txtSchema.Text = dsDataSet.GetXmlSchema

If everything in the Catch block works as expected, we set the form-level variable to True so that if the user clicks the Retrieve button again, the code simply exits the procedure.

bDataAdded = True

Now, if everything works as advertised, the data grid is populated with records. In the unlikely event of a water landing—or an exception—the code will jump to the Catch block and be displayed, preventing our program from crashing.

Catch ex As Exception Console.WriteLine(ex.ToString()) End Try

Updating the Data Source

When you work with a disconnected data set, updates require two stages: you first place new data in the data set, and then, after the user is finished editing the fields, you reconnect and send the data back from the data set to the source database. The data adapter can perform this second step with its Update method, which examines every record in the specified data table in the data set and, if a record has changed, sends the appropriate UPDATE, INSERT, or DELETE command to the database. Calling the Update method of the SqlDataAdapter object does all of this for us automatically using its existing connection object.

Private Sub btnUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click Try dgDataGrid.Update() SqlDataAdapter1.Update(DataSet11) Catch ex As SystemException MessageBox.Show(ex.Message) End Try End Sub

In our example, we accomplished the first step in the update process by using the data grid control. If your application is concerned only with the data set—for example, after updating the data set you simply send its contents via XML to another application—you're finished at this point.

However, if you are updating the original data source with changes, you have to send the changes from the data set to the original data source. That is, you must explicitly update the data source by using the Update method, as we do in the preceding code.

XML in .NET

XML was developed by an XML working group (originally known as the SGML Editorial Review Board) formed under the auspices of the World Wide Web Consortium (W3C) in 1996. Since then, XML has rapidly grown in acceptance as a standard for digital information markup. XML allows information (data, metadata, documentation, and resources of any kind) to be expressed in a structured, flexible, and easily parsible manner. XML also allows for content-based tagging of any information resource, and consequently it allows for powerful, focused, and efficient contents-based search and retrieval of information.

While you might be familiar with HTML, XML is very different. Even though both use tags enclosed in brackets (<>), HTML is used as a markup to describe how to show data on a browser. For example, you might do something like <B>This is in Bold</B>. However, HTML knows nothing about what it is displaying. XML, on the other hand, is used to represent and describe the data it contains. XML is designed to deliver structured content over the Web and, with a schema (XSD), to fully describe itself to the recipient.

Unlike HTML, XML allows users to structure and define the information in their documents. And while HTML has a finite collection of tags, XML allows users to create their own tags to meet their requirements, hence the extensibility. XML is a core technology substrate in .NET. All parts of the .NET Framework (ASP.NET, Web services, and so on) use XML as their native data representation format. The .NET Framework XML classes are also tightly coupled with managed data access in ADO.NET. If you have worked with data sources in the past, you know that traditionally there have always been different programming models for working with relational versus hierarchical data. By placing our data in XML, .NET breaks that tradition by offering a more deeply integrated programming model for all types of data.