.NET DataSet Class

The last approach is a little bit different: We will not directly return XML from SQL Server, instead we store the query results in a DataSet. I added this technique to the lineup of this section a) because it’s the most commonly used way to access SQL Server in .NET applications, b) it enables data binding to UI controls in Windows Forms and ASP.NET applications and c) we can produce XML from the DataSet either by calling GetXml(), WriteXml() or by linking an XmlDataDocument object to the DataSet. However, in the context of an XML-driven application this approach faces quite a few challenges to make it work.

The first challenge is tell the DataSet object about the XML format in which you would like to see the data when you call GetXml(). Elsewhere we have explained how we can either load an XML schema into the DataSet or how we can set the ColumnMapping property on each DataColumn object to define the format. We can also do all this work up front at development time and create a typed DataSet, which already contains the correct property settings for all XML types, from an XML schema. OK, that was not a real challenge, but it is something to keep in mind.

The real challenge is to fill the DataSet correctly. There are no problems to correctly fill the typed DataSet with traditional SQL queries as long as each type in the XML schema has its counterpart in the database. With this approach we can benefit from the highly performance optimized classes built on top of native SQL Server access libraries instead of OLE DB and fill the DataSet with a method like the one shown below.

public static void FillDataSetFromSql( string[] sqlQueries,

  string[] tableNames, SqlConnection connection, ref DataSet ds )

{

  System.Diagnostics.Debug.Assert(sqlQueries.Length == tableNames.Length );

  int count = queries.Length;

  for( int i = 0; i < count; i++ )

  {

    using( SqlCommand cmd = new SqlCommand(sqlQueries[i], connection) )

    {

      using( SqlDataAdapter adapter = new SqlDataAdapter() )

      {

        adapter.SelectCommand = cmd;

        adapter.Fill( ds, tableNames [i] );

      }

    }

  }

}

Now there are cases where we do not have a 1:1 relationship between XML types and database tables. Sometimes XML formats introduce a common parent element around a group of elements of the same type, like the <OrderLines> element around the <OrderLine> elements.

Figure 15.4: Example of an XML document with nested elements that do not map to database tables.

To create the OrderLines element the DataSet needs an OrderLines table, but there is no such thing in the database. Without the correct values in the OrderLines table the DataSet will not be able to create the correct nesting and render a sequence of OrderHeader and OrderLine elements.

In this case we have to forgo the performance advantage we gained from not converting the results to XML and fill the DataSet from a properly constructed XML string using the SqlXmlCommand instead. We could just as easy load data into the DataSet using the ExecuteXPathQuery from the previous example.

public void FillDataSetWithXPath( string query, string schemaPath,

  string connectionString, DataSet ds )

{

  SqlXmlCommand cmd = new SqlXmlCommand( connectionString );     

  cmd.CommandText = query;

  cmd.CommandType = SqlXmlCommandType.XPath;

  cmd.SchemaPath = schemaPath;

  SqlXmlAdapter adapter = new SqlXmlAdapter( cmd );

  adapter.Fill( ds );

}

Once we filled the DataSet object we can get an XML representation of the data inside, or we can take advantage of the great data-binding capabilites of the UI controls in the .NET Framework.

So much for an overview or the possible options of querying XML from SQL Server, now let’s move on to inserting data into SQL Server.