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

By Bill Wunder

 

While I was bringing Bill Wunder’s DDL Archive Utility into the VB.Net world, one of my goals was to learn as much about the new feature tools I expect to be working with as possible as the software upgrade procession marches on. Of course VB.Net itself was more than enough to try to wrap my mind around, still I wanted to be sure I at least glanced at the  SQLXML 3.0  add in. I’m glad I took the time to look it over, because it seems to me I was actually able to use SQLXML to add some efficiencies to my code even though I’m getting a clear message that XML is less efficient in the literature. For example, from the MSXML 4.0 SDK developers Guide:

XML isn't appropriate for every situation, however. XML documents tend to be more verbose than the binary formats they replace. They take up more network bandwidth and storage space, or require more processor time for compression. XML parsing can be slower than parsing highly optimized binary formats and can require more memory. However, careful application design can avoid some of these problems.

(Authors Note: 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”. To install the MSXML 4.0 SDK go to MSXML 4.0.)

 

In the VB6 version of Bill Wunder's DDL Archive Utility, configuration was managed by a set of configuration (.INI) files stored along with the SQL scripts in the file system hierarchy. I used one .INI file for server wide configuration settings and one .INI to indicate which databases to include in the archive operation. While migrating to VB.Net I wanted to use the database rather than .INI files scattered around the file system, improve the granularity a bit at the database level and I wanted to get away from the need to parse the .INI files into an array in the application. In fact, my array parsing code would be broken with VB.NET so I was going to need to rewrite it no matter what. I normalized the .INI files into a classic parent-child relationship to achieve these objectives.

 

To load the parent-child data into a VB6 application using ADO I would probably have chosen to query the parent table then make another round trip back to the SQL Server to get the child rows, or more preferably, I could execute both queries in a single stored procedure saving a network round trip and then deal with two record sets. Of course I’d have to keep my connection open until I was done with the data else get back into the business of loading arrays so I could disconnect. With VB.Net I would have many more options. All these new options would allow me to easily load the results into an ADO.Net DataSet so I wouldn’t need to stay connected or traverse the result sets or load values into local working variables or arrays.

 

Using a plain vanilla ADO.Net DataAdapter I could execute a query or stored procedure that would provide the results I needed from the two tables into a single result set, but I noticed there would be quite a bit of repeating data elements caused by the flattening of the parent-child relationship. Further, I really wanted my in-memory database - or DataSet in .Net lingo - to look like the data structures in the database so I wouldn’t have to get heavily into the task of doing mental translations between what I had in the database and what I had in memory during development. SQLXML looked promising because of XML’s ability to “shape” the result set. (I'll define "shaped" below.)  I could get a single XML schema to accurately and efficiently describe my configuration hierarchy.

 

In this article I’d like to explore the VB.Net building blocks I used to get to this solution from the DBA or T-SQL developer’s  perspective and skill set rather than the VB.Net developer perspective and skill set. The reason for this approach is to provide the T-SQL savvy SQL Server user some fundamental understandings of DataAdapters and VB.Net as we prepare for Yukon and beyond. I’ve created an abbreviated example database and will try to walk you all the way through to defining the SQLXML query. In this discussion I will use SQL Server 2000 SP 3 and and Visual Studio .Net 2003. In Part 2 I'll also include references to SQLXML3.0 SP2.

 

First, let's look at the difference between the result set from a conventional relational query and the result set conformed to the XML schema. My aim is to keep this example very simple yet explore several aspects of solving a problem like getting data in and out of my configuration parent-child database tables with VB.Net. I would encourage you to try everything here if you want to get some time behind the wheel of the VB.Net DataAdapter.

 

First, I’ll define a parent-child table structure for the database. A pretty bogus database to be sure for this example, but it lets me get a little reminder in for all of you to become conscious eaters. This will be a two table database to describe some qualities of the entrees at particular restaurants. TB_Restaurants will be the parent table and TB_MenuItem will be the child. This model can easily be expanded to support any sort of group and item classification structure commonly found in data driven configuration implementations.

 

create table dbo.TB_Restaurant

      (Id int identity(1,1)

      , Name varchar(20)

      , constraint pkc_TB_Restaurant__Id primary key (Id))

 

create table dbo.TB_MenuItem

      (Id int identity(1,1)

      , RestaurantId int

      , Name varchar(20)

      , IsOrganic varchar(5)

      , IsVegan varchar(5)

      , constraint pkc_TB_MenuItem__Id primary key (Id)

      , constraint fk_TB_MenuItem__RestaurantId__To__TB_Restaurant__Id

            foreign key (RestaurantId) references TB_Restaurant(Id))

 

Now, a little data for the tables:

 

declare @id int

insert dbo.TB_Restaurant (Name)

values ('Alice''s Resturaunt')

 

select @id = @@identity

 

insert dbo.TB_MenuItem (Name, RestaurantId, IsOrganic, IsVegan)

values ('Lentil Soup', @id, 'True','True')

 

insert dbo.TB_MenuItem (Name, RestaurantId, IsOrganic, IsVegan)

values ('Turkey Dinner', @id, 'False','False')

 

insert dbo.TB_MenuItem (Name, RestaurantId, IsOrganic, IsVegan)

values ('Apple Pie', @id, 'True','False')

 

That gives us just enough data to demonstrate the difference between the flattened result produced by a normal T-SQL Query and a “shaped” XML result.

 

The T-SQL query :

 

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

 

produces the result set:

 

Name                 Name                 IsOrganic IsVegan

-------------------- -------------------- --------- -------

Alice's Resturaunt   Lentil Soup          True      True

Alice's Resturaunt   Turkey Dinner        False     False

Alice's Resturaunt   Apple Pie            True      False

 

Now if we extend the query to produce XML output as:

 

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

 

we get something like this (Though probably not formatted for the page as nicely as this as the XML output stream from SQL Server really wants to become ‘well formed” before we do anything with it.):

 

Alice's Resturaunt">

 

  Turkey Dinner" IsOrganic="False" IsVegan="False"/>

 

 

 

Hopefully that’s clear enough to demonstrate that the data from the parent data will not be repeated for each child row when it is sent across the wire when For XML is used. This is what is meant by XML “shaping” the result set. Not a big benefit for this example, but you can see how expensive a query could get if there were thousands of child rows and lots of wide columns being returned from the parent.

 

Now the really cool thing about SQLXML is you don’t have to do anything to get such XML query output into a dataset in the application when you use an SQLXMLAdapter rather than the out of the box .Net OLEDBDataAdapter, SQLDataAdapter or OdbcDataAdapter. Stay with me to the end of this discussion and I’ll do my best to make that distinction clear.

 

Taking the example query one step farther, I will define a DataSet in my application with two DataTables that match the structure of TB_Restaurants and TB_MenuItems – complete with DRI. If you’ve never created a DataSet, create and populate the two tables above on a SQL Server instance and follow along with these steps.

 

After opening Microsoft Visual Studio .Net 2003, from the menu Select File, then New, then Project.

 

In the left pane of the ensuing dialog select Visual Basic Projects.

 

In the right pane select Windows Application and hit the OK button. This will open a project with a blank form.

 

Now from the Visual Studio Toolbox select the Data heading bar to expand the Data controls.

Hold and drag or double click the SQLDataAdapter control icon onto the blank form. This will open the “DataAdapter Configuration Wizard”.

 

Select Next on the wizards first page to get to the “Choose Your Connection”.

 

Select the “Connections…” browse button to open the OLEDB DataLink Properties configuration dialog (udl). If this dialog is not familiar to you, be sure to use the help button and get familiar with it.

 

Specify the server, database, and authentication method you will use to access the two tables you created before opening Visual Studio.

 

Select the “Test the Connection” button

 

When the connection test succeeds hit "OK" in the DataLink Properties dialog.

 

Select "Next" on the “Choose Your Connection” page. Now you have to “Choose A Query Type”. Let’s stick with the default “Use SQL Statement” but also keep in mind that best practice remains to use stored procedures over the embedded SQL statements we’re about create. In this case It’s simply easier to see the power of the Data Adapter by using a SQL Statement because specifying a SQL Statement will allow the wizard to auto generate Insert, Update, and Delete statements based upon our select statement.

 

Select "Next" on the “hoose A Query Type” window to open the “Generate SQL Statements” page.

 

Paste the select query above into the textbox.

 

Select "Next".

 

First you should see a message that the wizard must add the primary key columns in order to continue. Answer with "OK' and the “View Wizard Results” window will open but you’ll notice that the insert, update and delete statements were not generated because of the join in our query. Oops! That won’t do if we want to be able to easily maintain the data in the database in our application. So let’s hit the "Back' button and enter:

 

    Select Id, Name from dbo.TB_Restaurant

 

(If you like you can go make another fruitless pass and continue to include the “For XML AUTO”, but trust me it’s not what we want here so I’ll leave it as an invitation for you to try it and see what happens. At least you’ll get a good indication of the difference between what XML data looks like to a standard DataAdapter and what it looks like through a SQLXMLAdapter once we get to the latter.)

 

Select "Next" and Viola! The wizard succeeds and you see we now have a DataSet, A SQLDataAdapter and a SQLConnection in our application. We still don’t have a schema for TB_MenuItem though.

 

Repeat the wizard by dragging another SQLDataAdapter from the toolbox onto the form. Select the same Connection and Query Option but this time include the query:

 

  Select Id, RestaurantId, Name, IsOrganic, IsVegan from TB_MenuItem

 

When the wizard completes we see another SQLDataAdapter in our application but there is still only one DataSet and only one SQLConnection because the second dataAdapter was able to re-use the existing components. Examine the properties of the DataSet and the Connection. Keep in mind that you can easily set most properties to the value you want programmatically. Examine the properties of the DataAdapters. Make sure you find the insert, update, and delete statements generated by the wizard. Also note in the properties just how easy it would be to turn these statements into stored procedures just by pasting the SQL statement into a stored procedure, changing the CommandText property to the name of that stored procedure and setting the CommandType property to stored procedure.

 

Now right click on the form and in the context menu select “Generate Dataset…” Don’t change anything, but notice that both of the tables are selected.

 

Hit "OK". Now notice that we have another DataSet in our application. The DataSet the wizard created is actually a class and the DataSet we just generated is our instance.

 

Look in the Visual Studio Solution Explorer to find a DataSet .xsd file in the project.

 

Double click the .xsd file and you should see a representation of our in-memory database. Notice at the bottom of the window you can toggle between the DataSet and an XML view of the .xsd file. One thing is still missing. No foreign key. Let’s fix that.

 

Clicking in the column bar (below the diamond) in the TB_Restaurant table on the Id column to highlight the entire Id column.

 

Hold and drag that column over to the TB_MenuItem table. This launches the Edit Relationship dialog. The dialog tries to select the same column name in the child table so we need to adjust this.

 

Selecting RestaurantId from the drop-down list of the Foreign Key Field input box.

 

Hit "OK "and we now have a DataSet complete with DRI.

 

Looking at the XML view is interesting. I encourage you to take the time to fully explore all the entries here using the MSXML4.0 SDK documentation to help you understand the details. Be warned, there’s a lot there that the wizard did for us very easily. We actually want to make one more adjustment to our schema. In the database we are storing the IsOrganic and IsVegan columns as varchar(5) so we can have the literals for “True” and “False” rather than a binary 1|0 and the associated problem of remembering which is which. VB.Net knows that the literals “True” and “False” are Boolean expressions so we can leverage such a data conversion by redefining IsOrganic and IsVegan in the .xsd. First, let’s change IsOrganic using the DataSet view.

 

Select "boolean" from the datatype dropdown box for the IsOrganic column.

 

Toggle over to the XML view and notice that all that happened was the type in the IsOrganic element changed from string to boolean.

 

Replace “string” in the IsVegan element’s type with "boolean" in the XML view.  

 

Toggle back over to the DataSet Vew to observe that the column’s type is updated here as well. Note that you could actually type out the entire .xsd in this XML view interface with IntelliSense support. The trick would be knowing enough about .xsd to get it right.

 

Look at the data in the DataSet by again right clicking the form and select “Preview Data…” from the context menu. You populate the in-memory database by clicking “Fill Dataset” at the top of the screen and you can see the contents of each table by selecting that table in the list in the lower part of the screen.

 

Congradulations, you’ve successfully created a fully functional SQLDataAdapter. I’ll give you a while to digest what you’ve just done and also to fully explore the properties of the basic SQL Connection, DataSet, and SQLDataAdapter. In the next article we’ll proceed with putting SQLXML to work in this example. Hope you’ll check it out.

 

Bill