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

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 = "" + "" + "" + "" + "" + ""; 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 05Orders.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 tag. sb.Insert(sb.Length - 6, resultXmlDoc.InnerXml.Remove(8, resultXmlDoc.InnerXml.IndexOf(">") - 8)); // Make the elements self closing // (ADO import doesn't work otherwise). sb.Replace(">","/>"); // 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

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 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

Table 5-8. Attributes for s:datatype Element

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 element and the document is inserted into the ADO XML document for the Orders table, immediately before the closing tag as the data section. Finally, the closing tags for the elements are removed and the 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

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