Using ADO in .NET Applications

From DataSet Objects to ADO Recordset Objects

Binding an ADO Recordset object to a .NET application is a one-way process that works seamlessly only for loading ADO data into .NET data structures. A number of design reasons make this binding quite acceptable from the .NET viewpoint, but when your main requirement is working with the existing middle and data tiers, to update data, you might need to convert .NET data objects back into ADO Recordset objects. The Recordset object is then passed as an input argument to another method on a COM or COM+ middle-tier component.

An ADO Recordset object is a publicly created object that can work disconnected from a data source. After you have created a new instance of the object, you add some fields and shape it up to reflect the desired structure. The code that does this is simple to write in Visual Basic 6 but turns out to be a bit more quirky to arrange in .NET because of method arguments that you cannot leave blank or unspecified.

Serializing DataSet Objects to XML Recordset Objects

Any interaction with the ADO library involves COM Interop Services. Any time you send a Recordset object over the network to or from a .NET environment, type conversion and marshaling are performed. In light of this, let s consider an alternative approach to converting to ADO Recordset objects. We ll serialize a DataSet object into a non-standard XML format, the XML schema used by ADO Recordset objects.

The DataSet object already provides for XML serialization, as we discussed in Chapter 7. The default schema used to store XML, however, is in no way similar to or compatible with ADO Recordset objects. The most significant difference between the XML serialization format of the DataSet object and the Recordset object is that whereas ADO.NET fully leverages XML Schema Definition (XSD), ADO relies on an early version of XSD named XML Data Reduced (XDR). The .NET Framework comes with a tool named xsd.exe that can convert an XDR schema into the newest XSD format. However, no tool is provided to perform the reverse operation of serializing a DataSet object into an ADO Recordset object. We need a tool capable of automatically converting an XSD schema (belonging to the DataSet object) into an XDR schema. You can handle this conversion programmatically.

XML Schemas for DataSet Objects

Before continuing this discussion any further and illustrating the code that persists the changes entered in a DataSet object to an XML ADO Recordset object, let me briefly explain again the main concepts behind XML schemas.

XML schemas are XML documents used to define the data model behind an XML document. The schema describes the structure of the XML data in much the same way column metadata defines the table schema in a database. An XML schema defines the structure of XML data items by using the XML Schema Definition language. XSD provides elements, attributes, types, and groups that you use to define structure, validate data, and navigate internal relationships between data. XML schemas are now a World Wide Web Consortium (W3C) recommendation.

When it comes to interoperability, XML schemas are compelling because they are a recognized way to describe data formats, and XML is broadly supported on all platforms. XML schemas can be used as a contract for data exchange between two interoperable applications. Even applications running on different platforms can send each other data formatted in XML. Each application understands the incoming message as long as it is written in XML and includes the proper schema.

	Prior to XML schemas, Document Type Definition (DTD) was the preferred way to describe XML data. XML schemas have several advantages over DTDs. XML schemas use XML syntax and do not require you to learn a new language and employ another parser. XML schemas have in their type system reusable types, which are extensible and allow you to create new types through inheritance. XML schemas also can group elements and apply recurrence rules to elements and attributes.

As discussed in Chapter 7, ADO.NET DataSet objects can load from and save to XML formats. DataSet objects can read and write XML schemas by using the ReadXmlSchema and WriteXmlSchema methods. These methods are the object counterparts of the previously mentioned xsd.exe tool.

The ADO XML Schema

The next listing you ll see shows a simple yet effective XML file that represents the records selected by the following query:

SELECT employeeid, firstname, lastname FROM Employees

The XML data is generated by the Save method of the ADO Recordset object:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'> <s:Schema id='RowsetSchema'> <s:ElementType name='row' content='eltOnly'> <s:AttributeType name='employeeid' rs:number='1' /> <s:AttributeType name='firstname' rs:number='2' /> <s:AttributeType name='lastname' rs:number='3' /> <s:extends type='rs:rowbase'/> </s:ElementType> </s:Schema> <rs:data> <z:row employeeid='1' firstname='Nancy' lastname='Davolio' /> <z:row employeeid='3' firstname='Janet' lastname='Leverling' /> <z:row employeeid='5' firstname='Steve' lastname='Buchanan' /> </rs:data> </xml>

The DataSet object renders the same records as follows:

<MyDataSet> <xsd:schema targetNamespace="" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xsd:element name="MyDataSet" msdata:IsDataSet="true"> <xsd:complexType> <xsd:choice maxOccurs="unbounded"> <xsd:element name="MyTable"> <xsd:complexType> <xsd:sequence> <xsd:element name="employeeid" type="xsd:int" minOccurs="0" /> <xsd:element name="firstname" type="xsd:string" minOccurs="0" /> <xsd:element name="lastname" type="xsd:string" minOccurs="0" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:choice> </xsd:complexType> </xsd:element> </xsd:schema> <MyTable> <employeeid>1</employeeid> <firstname>Nancy</firstname> <lastname>Davolio</lastname> </MyTable> <MyTable> <employeeid>3</employeeid> <firstname>Janet</firstname> <lastname>Leverling</lastname> </MyTable> <MyTable> <employeeid>5</employeeid> <firstname>Steve</firstname> <lastname>Buchanan</lastname> </MyTable> </MyDataSet>

The root node <MyDataSet> represents the DataSet object, and the name of the tag is indeed the name of the DataSet object. The contents of the <MyTable> node can be consistently compared to the XML code for the Recordset object. The schema information has a more detailed data structure and single pieces of information are rendered using a node.

Creating an ADO XML Schema

In spite of the significantly different syntax, at the highest level of abstraction, the two XML schemas have something in common: both describe the columns and provide data for each row. Let s see how to persist the contents of a DataSet table to an ADO XML file. The XML file can be loaded as a Recordset object by any existing COM component in the middle tier.

In Figure 8-7, you can see the user interface of the sample application. After the user clicks to load records, the selected database rows are displayed in a list of check box controls.

Figure 8-7

The sample application where you can check some records to be exported as members of an XML ADO Recordset object.

The records are retrieved from the database by using COM components in your middle or data tier. These records enter the context of the application via an ADO Recordset object and are then loaded into a DataSet object. As long as you work in a .NET environment, a DataSet object is undoubtedly more manageable than an ADO data container.

When the user checks a few records in the check box list and clicks to export those records to XML, the XML format must be understood by the COM middle-tier components that are actually updating data on the database server. For this understanding to happen, the .NET code must serialize, all or in part, the contents of the DataSet object to a custom XML schema.

	The DataSet class does not provide a direct way to change the template of the XML code being generated. However, you won t find changing the template particularly hard to accomplish as long as you know how to leverage XSL Transformations (XSLT). The output of the DataSet object serialization process is XML data, and the main purpose of XSLT is transforming XML data from one schema to another.

Writing the Root Information

The root tag of the ADO XML schema is <xml> and includes a few namespaces that qualify some tags used in body of the document. This part of the output is considered invariant:

StringBuilder rootInfo = new StringBuilder(""); rootInfo.Append("<xml xmlns:s='"); rootInfo.Append("uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' \r\n\t"); rootInfo.Append("xmlns:dt='"); rootInfo.Append("uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' \r\n\t"); rootInfo.Append("xmlns:dt='"); rootInfo.Append("uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' \r\n\t"); rootInfo.Append("xmlns:rs='urn:schemas-microsoft-com:rowset' \r\n\t"); rootInfo.Append("xmlns:z='#RowsetSchema'>\r\n"); sw.Write(rootInfo.ToString()); rootInfo = null;

The text accumulated in the StringBuilder object is flushed into an output stream that is opened on a server-side, temporary XML file. This is an aspect of the solution that you might want to rework according to the characteristics of the system.

Writing the Schema Information

As you can see, the XDR schema employed by ADO is much simpler than the XSD schema of ADO.NET. The XDR schema comprises only one element, named row. It is marked as eltOnly, which means that the element can contain only the specified elements and no free text:

StringBuilder schemaOpenInfo = new StringBuilder(""); schemaOpenInfo.Append("<s:Schema id='RowsetSchema'>\r\n\t"); schemaOpenInfo.Append("<s:ElementType name='row' content='eltOnly'>\r\n"); sw.Write(schemaOpenInfo.ToString()); schemaOpenInfo = null;

The critical information stored in the schema includes the column names and other information about the columns. The contents of each column are stored through attributes of the row element. The names of the allowable attributes are listed after each column name. Each column has a unique identifier:

int index=0; foreach(DataColumn dc in ds.Tables[0].Columns) { StringBuilder colInfo = new StringBuilder(""); index ++; colInfo.Append("\t\t<s:AttributeType name='"); colInfo.Append(dc.ColumnName); colInfo.Append("' rs:number='"); colInfo.Append(index.ToString()); colInfo.Append("' />\r\n"); sw.Write(colInfo.ToString()); colInfo = null; }

After the column-specific information is written, you close the element tag and overall schema tag:

StringBuilder schemaCloseInfo = new StringBuilder(""); schemaCloseInfo.Append("\t\t<s:extends type='rs:rowbase'/>\r\n"); schemaCloseInfo.Append("\t</s:ElementType>\r\n"); schemaCloseInfo.Append("</s:Schema>\r\n"); sw.Write(schemaCloseInfo.ToString()); schemaCloseInfo = null;

Writing the Data

Your only remaining task is writing the actual data. The data of the Recordset object is bracketed by the <rs:data> tag. The recordset is expected to include only the records selected in the check box list. As shown in Chapter 1, you enumerate the contents of the Items collection of the CheckBoxList control and verify the Boolean state of the Selected property.

sw.WriteLine("<rs:data>"); // Collect only the rows with a selected item foreach(ListItem item in chkOutput.Items) { if (item.Selected) { StringBuilder data = new StringBuilder("\t<z:row "); DataRow[] rgRows; rgRows = ds.Tables[0].Select("employeeemphasis">data.Append(dc.ColumnName); data.Append("='"); data.Append(row[dc.ColumnName].ToString()); data.Append("' "); } data.Append(" />"); sw.WriteLine(data.ToString()); data = null; } } sw.WriteLine("</rs:data>");

If the nth item is selected, you retrieve the underlying DataRow object by using the Select method of the DataTable object, and the primary key value stored in the CheckBoxList object s Value property. (Storing the primary key in the Value property of CheckBoxList is not mandatory but does make good sense in almost all situations.)

Figure 8-8 shows the sample application using a <iframe> tag to show the content of the freshly generated XML file as rendered by the following code:

frXmlRecordset.Attributes["src"] = Server.MapPath("adorecordset.xml");

Figure 8-8

The sample application successfully exported the checked records to an XML document.

What About Data Types?

You probably noticed that in our XML document representing a subset of the data originally stored in a DataSet object, no type information exists. Earlier in the chapter, I defined the XML schema I was going to build as a simple yet effective schema. Well, the lack of explicit type information is the main reason I made that statement.

The XML document we have built so far is technically legal and correct. All Visual Basic applications will easily accept it and use it to build in-memory ADO Recordset objects. However, that XML document is simple and appropriate only for tasks that are not much more complicated than displaying text-based data. All the various pieces of information in the document are rendered in the same way, that is, using Unicode strings. Making those fields type-aware means adding some type information to the <AttributeType> node in the XML schema. You do this using a pair of attributes in the dt namespace one of the name spaces defined in the root node:

<s:AttributeType name='lastname' rs:number='2'> <s:datatype dt:type='string' dt:maxLength='20' /></s:AttributeType>

The <s:datatype> element describes the type of corresponding character data used in the parent attribute value. The main attribute of <s:datatype> is the dt:type attribute. For variable-length data types, XDR also allows you to specify a maximum length via the dt:maxLength attribute.

The .NET type system and the ADO Recordset object recognize different types. And ADO types are, in turn, different from predefined XDR data types. You can t obtain the XDR data type that corresponds to a .NET Type object the only type information available for an ADO.NET column object in a straightforward way. Whenever type information is critical for the health of your application, you should figure out how to map a DataTable object s column .NET type to an XDR type. In fact, you should exhaustively consider each .NET type and map it to an element in another set of data types. The mapping process won t be particularly hard, just boring.

From .NET Data to XML

So far we have mostly focused on how to make the contents of a DataSet object available to clients regardless of platform. Because of its inherently high level of interoperability, XML is the perfect data description language to use. Saving a table of data to XML allows .NET (and ADO) applications to interoperate with applications written for non-Windows platforms and written in a variety of programming languages. The flexibility of XML schemas dramatically increases the effectiveness of an XML-based approach. XML allows you to model the data the way you want and need. XML schemas allow you to provide an XML-based description of the data model you used. By publishing the schema along with the data, you enable your clients and your partner applications to easily import and understand the data.

Historically, the demand for data interoperability was associated with the need to retrieve rows of data from a data source, thus producing data in relational, tabular format. Tailor-made frameworks (such as Microsoft Foundation Classes and the Java set of classes) provided their own way to dump living instances of objects to streams of bytes. Needless to say, this dumping approach offered no form of cross-framework interoperability. Today the recognized success of XML and SOAP (Simple Object Access Protocol) make object serialization a much more compelling choice. Any framework-specific object can be exposed to other applications on other platforms. Thanks to XML and XML schemas, any living instance of a framework-specific object can morph into a living instance of another object that could comply with a radically different component model and memory format. In the .NET Framework, the challenge of XML object serialization is taken very seriously. As a result, you can see any .NET object through the lens of interoperability and plan to publicly expose all of your own classes (and not just DataSet objects) via XML. The ubiquity of XML and SOAP allows you to use .NET classes on Microsoft Windows XP and then use them on a Solaris platform, all dressed up as Java classes.

In general, serialization is the process that converts an object or a graph of objects into a linear sequence of bytes. You can then use the resultant block of memory for either storage or transmission over the network on top of an ad-hoc protocol. In .NET, object serialization can have three different output forms: binary, SOAP, or XML.

XML serialization is the process that converts the public interface of an object to a particular XML schema. Such a mechanism is widely used throughout the whole .NET Framework as a way to save the state of an object into a stream or a memory buffer. For example, ASP.NET Web Services (which I ll cover in Chapter 9) use the XmlSerializer class to encode messages. Also, any object reference that ASP.NET applications store in the Session object is serialized to a binary format before being stored.