| [ Team LiB ] |
|
Recipe 5.10 Converting a DataSet to an ADO RecordsetProblemYou need to convert a DataSet to an ADO Recordset so that you can use it in a legacy application. SolutionYou 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:
The sample code contains one event handler and one method:
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;
}
}
DiscussionADO 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.
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.
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.  |
| [ Team LiB ] |
|