Microsoft’s .NET Framework offers a very rich set of functionality to the database application developer. Visual Studio .NET has many features to help take advantage of the expansive .NET Framework, which consists of a vast number of base classes. There are so many that one can easily be overwhelmed by such a large number of programming objects. Additionally, for those new to object-oriented development, a new programming paradigm can be intimidating as well. The good news is that .NET simplifies many common tasks.

There are several techniques for loading XML data into SQL Server. SQLXML offers both XML Bulk Load and Updategrams. Bulk Load requires a schema definition. Updategrams require a specific XML syntax conforming to Microsoft’s Updategram namespace. OPENXML requires XPATH statements, which can be tedious to write. XSLT could transform an XML file into SQL insert statements, but that would require a lot of effort. The challenge is to easily and quickly load an XML file into SQL Server without using a schema. Using Visual Studio .NET makes this task simple and quick.

In this example, an XML file is used to insert new rows into the Region table in the Northwind database. The XML file is named RegionElements.xml and contains the following XML element-centric fragment:

<root>   
<Region>   
          <RegionID>5</RegionID> 
          <RegionDescription>Central</RegionDescription> 
</Region>   
<Region>   
          <RegionID>6</RegionID> 
          <RegionDescription>International</RegionDescription> 
</Region>   
</root>

A VB.NET application will read the RegionElements.xml file and insert its data into Northwind. Start Visual Studio and select New Project. Create a Visual Basic Project using a Windows Application template and name it XML Loader.

If Auto Hide is enabled for your Toolbox, you might want to turn it off for this exercise by clicking on the pushpin icon as shown below.

Click on the Data tab on your Toolbox.

The Data tab of the Toolbox provides a glimpse of the classes used for creating objects that allow .NET code to interact with a database. The first decision to make is to choose a data provider, either OleDb or Sql. The new .NET Data Provider (earlier documentation referred to it as a Managed Provider) for SQL Server eliminates the OLE DB and ADODB layers, so it provides faster access to SQL Server. A similar provider for Oracle is under development. Since the target database is SQL Server 2000, the appropriate choice is the .NET Sql Data Provider, which is made by using the SqlConnection object found on the Toolbox.

Position the mouse over the SqlConnection object. Click the left mouse button once to select the object. Move the mouse so that the cursor is over the form. Notice that the cursor changes to a SqlConnection object. Click the left mouse button to place the object on the form.

Notice that the SqlConnection object does not appear on the form, but underneath it where nonvisible controls are segregated. Go to the Properties window for the SqlConnection1 object and select New Connection for the Connection String property.

The Data Link Properties window appears. Specify your server, authentication method, and database.

Click OK. Visual Studio .NET uses the property settings to automatically generate the code used to connect to the specified SQL Server. The generated code is similar to an ADO connection string.

The next step is to add a SqlDataAdapter object, which is used to accomplish the data flows between a DataSet and a database. ADO.NET uses a disconnected paradigm where the data is stored in a memory structure called a DataSet. A DataSet not only can contain data from multiple tables, it can also hold their relationships and constraints. The DataSet can be modified and it keeps track of the changes in case it is used to update the database. Go to the Data tab of the Toolbox and single click on a SqlDataAdapter object. Place the cursor over the form and single click. This causes the Data Adapter Configuration Wizard to appear. Chose the data connection previously created, then click Next.

The next step may at first seem counterintuitive. Our objective is to insert data into the database, not get data from the database. Because Visual Studio .NET is being used to generate the necessary code, the wizard employs a select statement to obtain metadata from the database. This metadata is used to generate the code that will push data from the DataSet into the Northwind database. Enter a select statement specifying the columns found in the input XML file.

Since RegionID and RegionDescription are the only column in the Region table, the query could have been written as:

select * from region

Notice that insert, update, and delete statements are automatically generated. Click Finish.

One more object must be added, the DataSet object. Go to the Data tab on the Toolbox, select a DataSet object, move the cursor over the form and single click. The Add Dataset dialog box appears. Because there isn’t a schema, select Untyped dataset.

Click OK. Here is the form with the three nonvisible controls.

Click the Windows Forms tab on the Toolbox. Click on the Button control. Click on the form to add a button control to the form as shown below.

Double-click Button1 on the form to bring up the code view. Notice that very little code is visible. Approximately 120 lines of Visual Studio .NET generated code are hidden behind the line that says Windows Form Designer generated code. Click on the + sign to see the hidden code.

Add the following two lines of code inside the Button1_Click event:

DataSet1.ReadXml(“C:\RegionElements.xml”)
SqlDataAdapter1.Update(DataSet1)

The completed code looks like this:

Run the application and click the button to read the XML file and insert the data into the database. Query the database to verify that the data was inserted. The same code will also load attribute-centric XML such as this example:

<root>
<Region RegionID=“5” RegionDescription=“Central” />
<Region RegionID=“6” RegionDescription=“International” />
</root>

As you have seen, Visual Studio .NET simplifies the task of loading XML without the benefit of a schema. This technique is well suited for small, one-off data loads. It is simple and setup is quick. Because most of the work is done for you and no schema is used, this isn’t the most efficient approach to loading XML data, nor is it appropriate for bulk loading. It is left as an exercise for the reader to optimize the code and add error handling. Whether you load a small or large XML file, use a schema or not, Visual Studio .NET can simplify the process.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with SQL Server, Oracle, and the .NET framework.