Converting a DataSet to an ADO Recordset

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:15

Thêm vào BST

Báo xấu

110
lượt xem 15
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

[ Team LiB ] 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

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Converting a DataSet to an ADO Recordset

[ Team LiB ] 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
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 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 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 Namespace URI Prefix Description OLE DB Persistence Provider Rowset, which are urn:schemas-microsoft- rs the elements and attributes specific to ADO com:rowset Recordset properties and attributes uuid:BDC6E3F0-6DA3- XML Data Reduced, which is the XML-Data 11d1-A2A3- s namespace that contains elements and attributes 00AA00C14882 defining the schema of the current ADO Recordset uuid:C2F41010-65B3- XML Data Reduced (XDR) Datatypes, which are 11d1-A29F- dt the data type definition specification 00AA00C14882 Contains the actual data for the RecordSet using #RowsetSchema z 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 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 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 rs: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 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. [ Team LiB ]