Recipe 5.9 Loading an ADO Recordset into a DataSet

Recipe 5.9 Loading an ADO Recordset into a DataSet

Problem

You want to convert an ADO Recordset generated within a legacy application to a DataSet so that you can use it in a .NET application.

Solution

Use COM interop or the Fill( ) method of the OLE DB data provider DataAdapter .

You'll need a reference to the Primary Interop Assembly (PIA) for ADO provided in the file ADODB.DLL . Select adodb from the .NET tab in Visual Studio .NET's Add Reference Dialog.

The sample code creates an ADO Recordset for the Orders table in Northwind. The Fill( ) method of the OleDbDataAdapter is used to load the Recordset into a DataTable .

The C# code is shown in Example 5-9.

Example 5-9. File: AdoRecordsetForm.cs
 // Namespaces, variables, and constants
using System;
using System.Data;
using System.Data.OleDb;

//  . . . 

// Open an ADO connection.
ADODB.Connection conn = new ADODB.Connection( );
conn.Open("Provider = SQLOLEDB;Data Source = (local);" +
    "Initial Catalog = northwind","sa","",0);

// Create an ADO recordset.
ADODB.Recordset rs = new ADODB.Recordset( );
rs.Open("SELECT * FROM Orders", conn,
    ADODB.CursorTypeEnum.adOpenForwardOnly,
    ADODB.LockTypeEnum.adLockReadOnly, 0);

// Create and fill a dt from the ADO recordset.
DataTable dt = new DataTable("Orders");
(new OleDbDataAdapter( )).Fill(dt, rs);
conn.Close( );

// Bind the default view of the dt to the grid.
dataGrid.DataSource = dt.DefaultView; 

Discussion

One of the overloads of the OLE DB .NET DataAdapter.Fill( ) method accepts an ADO Recordset or Record object. The COM component that returns an ADO Recordset or Record object is consumed using COM interop.

While the data can be loaded into a DataSet in this way, there is no way to reconcile the changes made to the data in the DataSet with the data source underlying the ADO object. This must be explicitly handled.

There is no FillSchema( ) method which allows the schema of an ADO Recordset to be retrieved into a DataSet .

Recipe 5.10 Converting a DataSet to an ADO Recordset

Problem

You need to convert a DataSet to an ADO Recordset so that you can use it in a legacy application.

Solution

You must persist the DataSet to XML, transform it to ADO Recordset schema, and load it into an ADO Recordset using COM interop.

You'll need a reference to the Primary Interop Assembly (PIA) for ADO provided in the file ADODB.DLL . Select adodb from the .NET tab in Visual Studio .NET's Add Reference Dialog.

The sample uses one XML file:

Orders.xslt

The XSLT stylesheet used to transform the XML document output by the DataSet into an ADO Recordset XML document.

The sample code contains one event handler and one method:

Go Button.Click

Converts the DataSet to an ADO Recordset using the following steps:

  1. A shell XML document for the ADO Recordset is created.

  2. A DataReader accesses the schema information for the data to convert using the GetSchemaTable( ) method. This information is mapped to and added to the ADO Recordset XML document.

  3. The DataSet is loaded with data for a single DataTable . The XML document for the DataSet is transformed and written into the ADO Recordset XML document.

  4. An ADO Recordset object is created and loaded with the ADO Recordset XML document. This completes the conversion.

  5. The ADO Recordset is loaded into a DataTable using the OleDbDataAdapter . The default view for the table is bound to the data grid on the form to display the results of the conversion.

GetDataTypeInfo( )

This method maps SQL Server specific types to data type attributes for the ds and rs namespaces used to serialize an ADO Rowset .

The XSLT file is shown in Example 5-10.

Example 5-10. File: Orders.xslt
 <?xml version="1.0" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:rs="urn:schemas-microsoft-com:rowset"
    xmlns:z="#RowsetSchema"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:wxh="http://element14.com/wxhnamespace"
    version="1.0">
<msxsl:script language="CSharp" implements-prefix="wxh">
    <![CDATA[
    private String GetShortTime(String longDateTime)
    {
        return longDateTime.Substring(0,19);
    }
    ]]>
</msxsl:script>

    <xsl:output method="xml" indent="yes" />
    <xsl:template match="NewDataSet">
        <rs:data>
            <xsl:apply-templates select="Orders" />
        </rs:data>
    </xsl:template>
    <xsl:template match="Orders">
        <z:row>
            <xsl:apply-templates select="@OrderID" />
            <xsl:apply-templates select="@CustomerID" />
            <xsl:apply-templates select="@EmployeeID" />
            <xsl:apply-templates select="@OrderDate" />
            <xsl:apply-templates select="@RequiredDate" />
            <xsl:apply-templates select="@ShippedDate" />
            <xsl:apply-templates select="@ShipVia" />
            <xsl:apply-templates select="@Freight" />
            <xsl:apply-templates select="@ShipName" />
            <xsl:apply-templates select="@ShipAddress" />
            <xsl:apply-templates select="@ShipCity" />
            <xsl:apply-templates select="@ShipRegion" />
            <xsl:apply-templates select="@ShipPostalCode" />
            <xsl:apply-templates select="@ShipCountry" />
        </z:row>
    </xsl:template>
    <xsl:template match="@OrderDate">
        <xsl:attribute name="OrderDate">
            <xsl:value-of select="wxh:GetShortTime(.)" />
        </xsl:attribute>
    </xsl:template>
    <xsl:template match="@RequiredDate">
        <xsl:attribute name="RequiredDate">
            <xsl:value-of select="wxh:GetShortTime(.)" />
        </xsl:attribute>
    </xsl:template>
    <xsl:template match="@ShippedDate">
        <xsl:attribute name="ShippedDate">
            <xsl:value-of select="wxh:GetShortTime(.)" />
        </xsl:attribute>
    </xsl:template>
    <xsl:template match="@*">
        <xsl:copy-of select="." />
    </xsl:template>
</xsl:stylesheet> 

The C# code is shown in Example 5-11.

Example 5-11. File: ConvertDataSetToAdoRecordsetForm.cs
 // Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Xsl;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
        
private const String ADOXMLFILE =
    ConfigurationSettings.AppSettings["Temp_Directory"] + "ADO_Orders.xml";

//  . . . 

private void goButton_Click(object sender, System.EventArgs e)
{
    Cursor.Current = Cursors.WaitCursor;

    String sqlText = "SELECT * FROM Orders";

    // Create the connection.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    // Create the command to load all orders records.
    SqlCommand cmd = new SqlCommand(sqlText, conn);
    conn.Open( );
    // Create a DataReader from the command.
    SqlDataReader dr = cmd.ExecuteReader(
        CommandBehavior.SchemaOnly  CommandBehavior.KeyInfo);
    // Create a table of the schema for the DataReader.
    DataTable schemaTable = dr.GetSchemaTable( );

    // Create an XML document.
    XmlDocument xmlDoc = new XmlDocument( );
    // Add ADO namespace and schema definition tags to the XML document.
    String adoXml =
        "<xml xmlns:s = 'uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' " +
        "xmlns:dt = 'uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' " +
        "xmlns:rs = 'urn:schemas-microsoft-com:rowset' " +
        "xmlns:z = '#RowsetSchema'>" +
        "<s:Schema id = 'RowsetSchema'>" +
        "<s:ElementType name = 'row' content = 'eltOnly'>" +
        "</s:ElementType>" +    
        "</s:Schema>" +
        "</xml>";
    xmlDoc.LoadXml(adoXml);

    // Create a namespace manager for the XML document.
    XmlNamespaceManager nm = new XmlNamespaceManager(xmlDoc.NameTable);
    // Add ADO prefixes.
    nm.AddNamespace("s", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882");
    nm.AddNamespace("dt", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882");
    nm.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
    nm.AddNamespace("z", "#RowsetSchema");

    // Select the s:ElementType node.
    XmlNode curNode = xmlDoc.SelectSingleNode("//s:ElementType", nm);

    XmlElement xe = null;
    XmlAttribute xa = null;
    // Iterate through the schema records for the DataReader.
    foreach(DataRow sr in schemaTable.Rows)
    {
        // Create an 'AttributeType' element for the schema record.
        xe = xmlDoc.CreateElement("s", "AttributeType",
            "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882");

        // Get the data type.
        SqlDbType sqlDbType = (SqlDbType)sr["ProviderType"];

        // Create the 'name' attribute.
        xa = xmlDoc.CreateAttribute("", "name", "");
        xa.Value = sr["ColumnName"].ToString( );
        xe.SetAttributeNode(xa);

        // Create the 'number' attribute.
        xa = xmlDoc.CreateAttribute("rs", "number",
             "urn:schemas-microsoft-com:rowset");
        xa.Value = ((int)sr["ColumnOrdinal"] + 1).ToString( );
        xe.SetAttributeNode(xa);

        // Add attribute if null values are allowed in the column.
        if((bool)sr["AllowDBNull"])
        {
            xa = xmlDoc.CreateAttribute("rs", "nullable",
                "urn:schemas-microsoft-com:rowset");
            xa.Value = sr["AllowDBNull"].ToString().ToLower( );
            xe.SetAttributeNode(xa);
        }

        // Add 'writeunknown' attribute.
        xa = xmlDoc.CreateAttribute("rs", "writeunknown",
            "urn:schemas-microsoft-com:rowset");
        xa.Value = "true";
        xe.SetAttributeNode(xa);

        // Create a 'datatype' element for the column within the
        // 'AttributeType'.
        XmlElement dataele = xmlDoc.CreateElement("s", "datatype",
            "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882");
        String typeName, dbTypeName;
        GetDataTypeInfo(sqlDbType, out typeName, out dbTypeName);

        // Add a 'type' attribute specifying the data type.
        xa = xmlDoc.CreateAttribute("dt", "type",
            "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882");
        xa.Value = typeName;
        dataele.SetAttributeNode(xa);

        // Add a 'dbtype' attribute, if necessary.
        if (dbTypeName != "")
        {
            xa = xmlDoc.CreateAttribute("rs", "dbtype",
                "urn:schemas-microsoft-com:rowset");
            xa.Value = dbTypeName;
            dataele.SetAttributeNode(xa);
        }

        // Add the 'maxlength' attribute.
        xa = xmlDoc.CreateAttribute("dt", "maxLength",
            "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882");
        xa.Value = sr["ColumnSize"].ToString( );
        dataele.SetAttributeNode(xa);

        // Add 'scale' and 'precision' attributes, if appropriate.
        if(sr["DataType"].ToString( ) != "System.String")
        {
            if(Convert.ToByte(sr["NumericScale"]) != 255)
            {
                xa = xmlDoc.CreateAttribute("rs", "scale",
                    "urn:schemas-microsoft-com:rowset");
                xa.Value = sr["NumericScale"].ToString( );
                dataele.SetAttributeNode(xa);
            }

            xa = xmlDoc.CreateAttribute("rs", "precision",
                "urn:schemas-microsoft-com:rowset");
            xa.Value = sr["NumericPrecision"].ToString( );
            dataele.SetAttributeNode(xa);
        }

        // Add a 'fixedlength' attribute, if appropriate.
        if (sqlDbType != SqlDbType.VarChar &&
            sqlDbType != SqlDbType.NVarChar)
        {
            xa = xmlDoc.CreateAttribute("rs", "fixedlength",
                "urn:schemas-microsoft-com:rowset");
            xa.Value = "true";
            dataele.SetAttributeNode(xa);
        }

        // Add a 'maybe' null attribute, if appropriate.
        if(!(bool)sr["AllowDBNull"])
        {
            xa = xmlDoc.CreateAttribute("rs", "maybenull",
                "urn:schemas-microsoft-com:rowset");
            xa.Value = sr["AllowDBNull"].ToString().ToLower( );
            dataele.SetAttributeNode(xa);
        }

        // Add the 'datatype' element to the 'AttributeType'.
        xe.AppendChild(dataele);

        // Add the 'AttributeType' element to the 'ElementType'
        // attribute.
        curNode.AppendChild(xe);
    }
    // Add the 'extends' element with attribute 'type" of 'rs:rowbase'.
    xe = xmlDoc.CreateElement("s", "extends",
        "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882");
    xa = xmlDoc.CreateAttribute("", "type", "");
    xa.Value = "rs:rowbase";
    xe.SetAttributeNode(xa);
    curNode.AppendChild(xe);

    // Close the reader and connection.
    dr.Close( );
    conn.Close( );

    // Load the Orders data into a table in a DataSet.
    DataSet ds = new DataSet( );
    SqlDataAdapter da = new SqlDataAdapter(sqlText,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    da.Fill(ds, "Orders");

    // Write the column data as attributes.
    foreach(DataColumn dc in ds.Tables["Orders"].Columns)
        dc.ColumnMapping = MappingType.Attribute;
    // Write the DataSet to an XML document.
    XmlDataDocument ordersXml = new XmlDataDocument(ds);

    // Load the XML transformation.
    XslTransform xslt = new XslTransform( );
    xslt.Load(ConfigurationSettings.AppSettings["Project_Directory"] +
        @"Chapter 05\Orders.xslt");

    // Transform the XML document.
    XmlReader xr = xslt.Transform(ordersXml, null, (XmlResolver)null);

    // Load the transformed document into an XML document.
    XmlDocument resultXmlDoc = new XmlDocument( );
    resultXmlDoc.Load(xr);
    xr.Close( );

    StringBuilder sb = new StringBuilder(xmlDoc.OuterXml);
    // Insert the data before the closing </xml> tag.
    sb.Insert(sb.Length - 6, resultXmlDoc.InnerXml.Remove(8,
        resultXmlDoc.InnerXml.IndexOf(">") - 8));
    // Make the <z:row> elements self closing
    // (ADO import doesn't work otherwise).
    sb.Replace("></z:row>","/>");

    // Write the order data to a file as ADO XML format.
    StreamWriter sw = new StreamWriter(ADOXMLFILE);
    sw.Write(sb.ToString( ));
    sw.Close( );

    // Create and open an ADO connection.
    ADODB.Connection adoConn = new ADODB.Connection( );
    adoConn.Open("Provider = SQLOLEDB;Data Source=(local);" + 
        "Initial Catalog=northwind", "sa", "", 0);

    // Create the ADO recordset.
    ADODB.Recordset rs = new ADODB.Recordset( );
    try
    {
        // Load the XML into the ADO recordset.
        rs.Open(ADOXMLFILE,
            adoConn,
            ADODB.CursorTypeEnum.adOpenStatic,
            ADODB.LockTypeEnum.adLockBatchOptimistic,
            (int)ADODB.CommandTypeEnum.adCmdFile);
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
        adoConn.Close( );

        Cursor.Current = Cursors.Default;

        return;
    }

    try
    {
        // Load the ADO recordset into a DataTable.
        OleDbDataAdapter oleDa = new OleDbDataAdapter( );
        DataTable dt = new DataTable("Orders");
        oleDa.Fill(dt, rs);

        // Bind the default view of the table to the grid.
        dataGrid.DataSource = dt.DefaultView;
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        adoConn.Close( );
        Cursor.Current = Cursors.Default;
    }

    dataGrid.CaptionText = "ADO Recordset Serialized as an XML document";
}

private void GetDataTypeInfo(SqlDbType sqlDbType, out String type,
    out String dbtype)
{
    type = "";
    dbtype = "";

    // Convert the SqlDbType to type attributes in the dt and rs namespaces.
    switch(sqlDbType)
    {
        case SqlDbType.BigInt:
            type = "i8";
            break;
        case SqlDbType.Binary:
            type = "bin.hex";
            break;
        case SqlDbType.Bit:
            type = "Boolean";
            break;
        case SqlDbType.Char:
            type = "string";
            dbtype = "str";
            break;
        case SqlDbType.DateTime:
            type = "dateTime";
            dbtype = "variantdate";
            break;
        case SqlDbType.Decimal:
            type = "number";
            dbtype = "decimal";
            break;
        case SqlDbType.Float:
            type = "float";
            break;
        case SqlDbType.Image:
            type = "bin.hex";
            break;
        case SqlDbType.Int:
            type = "int";
            break;
        case SqlDbType.Money:
            type = "i8";
            dbtype = "currency";
            break;
        case SqlDbType.NChar:
            type = "string";
            break;
        case SqlDbType.NText:
            type = "string";
            break;
        case SqlDbType.NVarChar:
            type = "string";
            break;
        case SqlDbType.Real:
            type = "r4";
            break;
        case SqlDbType.SmallDateTime:
            type = "dateTime";
            break;
        case SqlDbType.SmallInt:
            type = "i2";
            break;
        case SqlDbType.SmallMoney:
            type = "i4";
            dbtype = "currency";
            break;
        case SqlDbType.Text:
            type = "string";
            dbtype = "str";
            break;
        case SqlDbType.Timestamp:
            type = "dateTime";
            dbtype = "timestamp";
            break;
        case SqlDbType.TinyInt:
            type = "i1";
            break;
        case SqlDbType.UniqueIdentifier:
            type = "uuid";
            break;
        case SqlDbType.VarBinary:
            type = "bin.hex";
            break;
        case SqlDbType.VarChar:
            type = "string";
            dbtype = "str";
            break;
        case SqlDbType.Variant:
            type = "string";
            break;
    }
} 

Discussion

ADO uses UTF-8 encoding when it persists data as an XML stream. The XML persistence format used by ADO has four namespaces as described in Table 5-6.

Table 5-6. Namespaces for a serialized Rowset

Namespace URI

Prefix

Description

urn:schemas-microsoft-com:rowset

rs

OLE DB Persistence Provider Rowset, which are the elements and attributes specific to ADO Recordset properties and attributes

uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882

s

XML Data Reduced, which is the XML-Data namespace that contains elements and attributes defining the schema of the current ADO Recordset

uuid:C2F41010-65B3-11d1-A29F-00AA00C14882

dt

XML Data Reduced (XDR) Datatypes, which are the data type definition specification

#RowsetSchema

z

Contains the actual data for the RecordSet using the schema defined by the s namespace

The ADO XML format has three parts : the namespace declarations, followed by the schema section and the data section. The schema section is required and contains detailed metadata about each column in the table. The data section contains an element for each row. Column data is stored as attribute-value pairs according to the schema section definitions. For an empty row set, the data section can be empty, but the <rs:data> tags must be present.

Use the dt:type attribute to specify a data type for a column. The data type can be specified directly on the column definition or on the s:datatype nested element of the column definition. ADO adopts the latter approach. If the dt:type attribute is omitted from the column definition, the column type will default to a variable length string.

The sample converts the Orders table from the Northwind sample database to an ADO Recordset . The solution begins by getting a DataTable containing the schema for the Orders table using the GetSchemaTable( ) method of the DataReader . As mentioned earlier, the ADO XML format has three sections, and this schema information will be used to define the schema section.

The sample defines the shell of the ADO XML document for the Orders table containing the namespace declarations and the nested row elements that will contain the column definition elements.

The code then iterates over the rows in the schema table and adds a child s:AttributeType column element to the s:ElementType row element. The name of the column, as well as properties shown in Table 5-7, are defined as attributes of this column, while an s:datatype nested element is created with attributes specifying the data type properties described in Table 5-8.

Table 5-7. Attributes for s:AttributeType element

Attribute

Description

Name

Column name

rs:name

Column name in the Recordset . This value defaults to the value for the name attribute. This only need to be explicitly specified if a name other than the Recordset column name is used for the value of the name attribute.

rs:number

Column ordinal.

rs:nullable

Indicates whether the column can contain a null value.

rs:writeunknown

Indicates whether a value can be written to the column.

Table 5-8. Attributes for s:datatype Element

Attribute

Description

dt:type

XML column data type

rs:dbtype

Database column data type

dt:maxLength

The maximum length of the column

rs:scale

The numeric scale of the column

rs:precision

The precision of the column

rs:fixedlength

Indicates whether the column has a fixed length

rs:maybenull

Indicates whether the column can contain a null value

Having defined the schema inline, the solution loads the Orders table into a DataSet . The MappingType is set so that the column values are written as attributes rather than nested elements. The DataSet is then serialized to an XmlDataDocument object. The XML transformation Orders.xslt (see Example 5-11) is then applied to the XML document and the results are output to an XmlReader . The style sheet transforms the XML format for the data in the DataSet to the format required for the ADO XML data section. The namespace declarations are removed from the <rs:data> element and the document is inserted into the ADO XML document for the Orders table, immediately before the closing </xml> tag as the data section. Finally, the </z:row> closing tags for the <z:row> elements are removed and the <z:row> elements are made self closing, since the ADO import only imports the first row, otherwise. The ADO XML document for the Orders table is saved to the file ADO_Orders.xml shown in Example 5-12.

Example 5-12. File: ADO_Orders.xml
 <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
    xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
     <s:Schema id="RowsetSchema">
         <s:ElementType name="row" content="eltOnly">
             <s:AttributeType name="OrderID" rs:number="1"
                rs:writeunknown="true">
                  <s:datatype dt:type="int" dt:maxLength="4"
                    rs:precision="10" rs:fixedlength="true"
                    rs:maybenull="false" /> 
              </s:AttributeType>
            <s:AttributeType name="CustomerID" rs:number="2"
                rs:nullable="true" rs:writeunknown="true">
                <s:datatype dt:type="string" dt:maxLength="5"
                    rs:fixedlength="true" />
            </s:AttributeType>
            <s:AttributeType name="EmployeeID" rs:number="3"
                rs:nullable="true" rs:writeunknown="true">
                <s:datatype dt:type="int" dt:maxLength="4"
                    rs:precision="10" rs:fixedlength="true" /> 
            </s:AttributeType>

<!--  . . .  -->

            <s:AttributeType name="ShipCountry" rs:number="14"
                rs:nullable="true" rs:writeunknown="true">
                <s:datatype dt:type="string" dt:maxLength="15" /> 
            </s:AttributeType>
            <s:extends type="rs:rowbase" /> 
        </s:ElementType>
    </s:Schema>
    <rs:data>
        <z:row OrderID="10248" CustomerID="VINET" EmployeeID="5"
            OrderDate="1996-07-04T00:00:00"
            RequiredDate="1996-08-01T00:00:00"
            ShippedDate="1996-07-16T00:00:00" ShipVia="3"
            Freight="32.38" ShipName="Vins et alcools Chevalier"
            ShipAddress="59 rue de l'Abbaye" ShipCity="Reims"
            ShipPostalCode="51100" ShipCountry="France" /> 

<!--  . . .  -->

        <z:row OrderID="11077" CustomerID="RATTC" EmployeeID="1"
            OrderDate="1998-05-06T00:00:00"
            RequiredDate="1998-06-03T00:00:00" ShipVia="2"
            Freight="8.53" ShipName="Rattlesnake Canyon Grocery"
            ShipAddress="2817 Milton Dr." ShipCity="Albuquerque"
            ShipRegion="NM" ShipPostalCode="87110"
            ShipCountry="USA" /> 
    </rs:data>
</xml> 

Finally, the XML file is loaded into an ADO Recordset object.