Using the SQLDataAdapter Part 2

Using the SQLDataAdapter and the SQLXMLAdapter in a VB.Net Application – Part 2

By Bill Wunder

In the Part one of this article we compared the difference between a joined table T-SQL query result set and a “shaped” XML query result set. Then we began the process of creating a VB.Net application to exploit that “shaped” XML query result set. We looked at creating a SQLDataAdapter including defining a DataSet class, instantiating, and even populating an instance of that DataSet. We even considered why XML data can be problematic when using a SQLDataAdapter. We examined the .xsd file VB.Net creates for a DataSet and considered a few ways to modify the .xsd file using the available Visual Studio Net tools. In this second part, we’ll turn our attention to using SQLXML 3.0 to populate an .xsd definition that includes complex data types with a single query, examine how to load the SQLXML query into an existing VB.Net DataSet, and see how easy it is to access the query results from the in-memory and disconnected DataSet.

To start this exploration we need to add a reference to SQLXML 3.0 to our project so we can use the SQLXMLAdapter to populate the DataSet. To add the reference, go to the Solution Explorer, right click on References in the project and select “Add Reference…” to open the Add Reference dialog. Browse down to the Microsoft.Data.SQLXML component on the .Net tab, double click the entry to select it and hit "OK". Be careful! The list is pretty long, seems to get longer every day, and there are a few entries that look an awful lot like SQLXML 3.0 but aren't the same at all. If you don’t find it in the list it’s probably not installed on the machine. To install SQLXML3.0 go to the download page at http://www.microsoft.com/sql, select the SQLXML 3.0 SP2 link and select “Open”. Once you do find it and add it to the project, the project will know all about SQLXML. For your part, you may want to review the SQLXML 3.0 Documentation file that was placed on your machine when SQLXML was installed. This will be your key to getting the most from the tool set now and in the future.

Next we need a function that will access the database using SQLXML and populate our DataSet. I’ll give you a simple function to accomplish this but please note that if your DataSet class is named anything other than DataClass1 – which should be the default name created if you followed along in the previous article – you’ll need to update the code below to use the correct name in your project. Right Click on the Form and select View Code and paste the following function in below the text “Windows Form Designer Generated Code”:

Private Function LoadData()

Dim sSQLServer As String

Dim sConnectionString As String

Dim sCommandString As String

Dim sFileName As String

Dim strmFile As System.IO.FileStream

Dim cmd As Microsoft.Data.SqlXml.SqlXmlCommand

Dim adapter As Microsoft.Data.SqlXml.SqlXmlAdapter

' to keep it simple we'll hardcode the SQL Server name

' and the database name in the connection string

Try

sConnectionString = "provider=sqloledb" & _

";data provider=sqloledb" & _

";Server=BILLXP" & _

";database=XMLtest" & _

";Integrated Security=SSPI"

' or for a SQL authenticated connection

' sConnectionString = "provider=sqloledb" & _

' ";data provider=sqloledb" & _

' ";Server=BILLXP" & _

' ";database=XMLtest" & _

' ";User ID=sa" & _

' ";Password=strongpassword"

' now define the sql command

sCommandString = "Select r.Name " & _

", m.Name " & _

", m.IsOrganic " & _

", m.IsVegan " & _

"from dbo.TB_Restaurant r " & _

"inner join dbo.TB_MenuItem m " & _

"on r.id = m.RestaurantId " & _

"For XML AUTO"

' connect to the SQL Server

cmd = New Microsoft.Data.SqlXml.SqlXmlCommand(sConnectionString)

cmd.RootTag = "ArchiveConfiguration"

cmd.OutputEncoding = "utf-8"

cmd.CommandText = sCommandString

cmd.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.Sql

adapter = New Microsoft.Data.SqlXml.SqlXmlAdapter(cmd)

' populate the DataSet

DataSet1.Clear()

adapter.Fill(DataSet1)

' dump the data to the "Output" window

For Each dt As DataTable In DataSet1.Tables

If dt.TableName = "r" Then

For Each dr As DataRow In dt.Rows

Debug.WriteLine(dr("Name"))

Next dr

End If

Next dt

For Each dt As DataTable In DataSet1.Tables

If dt.TableName = "m" Then

For Each dr As DataRow In dt.Rows

Debug.WriteLine(dr("Name") & vbTab & _

dr("IsOrganic") & vbTab & _

dr("IsVegan"))

Next dr

End If

Next dt

LoadData = True

Catch exSQL As SqlClient.SqlException

System.Windows.Forms.MessageBox.Show("(LoadData) SQL Error: " & _

exSQL.Number & " " & _

exSQL.Message, "XML Test")

End Try

End Function

Remember, make sure the name of your DataSet class replaces the name DataSet1 where it occurs in this routine if your DataSet class is not named DataSet1. It’s also worth mentioning that as you get familiar with using namespaces in VB.Net you can avoid fully qualified namespaces such as I use in this example by using the Imports statement to declare namespaces in your code. See the Imports documentation in the Visual Studio documentation for more on this topic.

In order to get the function to run without obscuring our DataAdapter focused discussion with something like data grids or adding other Windows Forms processing to this example, the next thing we want to do is to click the + box in front of the text “Windows Form Designer Generated Code” to expand the system generated code. Look it over to see everything Visual Studio auto generated when you added the “Windows Application” form to your project. At the beginning of this code you’ll find the "New" subroutine. As a rule, about the only place you’ll ever want to make a change to the auto generated code is to add initialization code to this subroutine and then only after the call to InitializeComponent. An example would be if your application needs to list the SQL Servers available on the network in a drop down box, you could load that list here. This is also a good place to add a call to a class or other test code when you want the windows application be act as a stub for testing some functionality in preliminary development efforts. Here, I’m going to use Sub New to execute the LoadData Function and output the debug information it produces. To do this all we need to do is add a call to the LoadData Function in the space that allows you to add initialization code. While it’s not exactly the same thing, this will cause the function to run when the application starts in a similar fashion to the load event familiar to VB6 users. You could also use the form’s load event but I can save a couple lines of code by using the initialization routine in this example.

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

addà LoadData()

End Sub

At this point you should be able to execute the VB.Net application by hitting F5 and the empty form will load to your desktop. Then if you look at Visual Studio .Net 2003 Output window you should see the results of the query as read from the in-memory DataSet and dumped using the debug.writeln statements.

And there you have it, an example of the easiest way of designing and populating a DataSet from a SQL Server using the DataAdapter wizard and an example of a code based population of an existing DataSet using SQLXML extensions. One thing you might be interested to know is that this example will run against a SQL Server 2000 and against Yukon beta1. That’s worth mentioning because you now know that just by reading this article you are on your way to being able to use some of those new features coming in Yukon.

Bill