ADO 2.5 and XML | Developing XML Solutions (DV-MPS General)

[Previous] [Next]

Using ADO 2.5, you can read data from nearly any data source, place the data into an ADO recordset (which is similar to a virtual table containing the data in memory), and transform the data to XML. Once the data has been transformed to XML, it can be placed into a data stream and used as output to various sources, such as a file, the ADO ASP Response object (which is used to return information to the Web client), and the XML DOM. Outputting the data as XML can be accomplished by using the COM IStream interface. The IStream interface is an interface designed to support reading and writing data to ADO Stream objects. ADO 2.5 requires support for the IStream interface.

On the other hand, XML data can be used as a data source of a read-only or read/write recordset in ADO 2.5. For example, you can use ADO 2.5 in an ASP page on a Web server to retrieve data from a database by placing the data into an ADO recordset. The data will then be transferred as XML output to the ASP Response object using ADO 2.5. Once the XML data arrives on the client, it can be read into an ADO recordset on the client using DHTML. Using the client-side ADO recordset and DHTML, the user can read and update the data. Let's look at several examples of inputting and outputting data as XML in ADO 2.5 to see how this works.

Outputting Data as XML Using ADO 2.5

In this example, we will retrieve data from the SQL Server 7.0 Northwind Traders database and save the data as XML in a text file. We'll use ADO 2.5 in a Visual Basic application to perform this task. To create the example application, follow these steps:

Open Visual Basic, create a standard EXE application, and change the name of the default form to frmADOXML.

Choose Reference from the Project menu, and add a reference to Microsoft ActiveX Data Objects 2.5 Library.

Add a command button called cmdSave to the form with a caption Save.

Add the following code to the click event handler of the command button cmdSave:

 Private Sub cmdSave_Click() Dim objNWRecordset As ADODB.Recordset Dim objNWConnection As ADODB.Connection Set objNWRecordset = New ADODB.Recordset Set objNWConnection = New ADODB.Connection objNWConnection.CursorLocation = adUseClient 'You will need to replace IES-FUJI with the appropriate data 'source in the following statement. objNWConnection.Open "Provider=SQLOLEDB.1; " & _       "Integrated Security=SSPI;Persist Security Info=False;" & _       "User ID=sa;Initial Catalog=Northwind;"        "Data Source=IES-FUJI" objNWRecordset.CursorLocation = adUseClient objNWRecordset.CursorType = adOpenStatic Set objNWRecordset.ActiveConnection = objNWConnection objNWRecordset.Open "Products" 'Save the recordset to a file as XML. objNWRecordset.Save "C:\Products.xml", adPersistXML End Sub

This code initially creates an ADO Connection object called objNWConnection and a Recordset object called objNWRecordset, and then sets the properties for these objects and opens them. A Connection object provides a connection to any data source. A Recordset object is a virtual table in memory that contains the data that is retrieved from a data source. The CursorLocation property of the Recordset object determines whether the data will be located on the client or on the server. The CursorLocation property also determines whether the connection must be maintained with the database (server cursor) or the connection can be broken (client cursor) while creating a disconnected recordset. The Open method of the ADO Connection object contains the connection string as a parameter. The connection string includes the catalog, which is the database that is going to be used, the data source, which is the name of the SQL Server, and the user ID, which is a valid user name to use when opening the connection. This connection string is connecting to a SQL Server database. You will have to change the name of the data source to the name of your SQL Server database that contains the Northwind Traders database.

The ADO Connection object connects to the Northwind Traders database, and the Recordset object connects to the Products table of the Northwind Traders database. Once this is done, the Save method of the Recordset object is called to save the data as XML.

As you can see, the Save method uses the adPersistXML parameter to save the data as XML. The XML file that is created will have two main sections. The first section contains a BizTalk schema for the data, and the second section contains the actual data. There are four namespace prefixes that are used in the file. The first namespace prefix is s, which is used to prefix the schema definition for the data. The second namespace prefix is dt, which is used for the datatype definitions in the schema. The third namespace prefix is rs, which references the properties and methods of the ADO recordset. The fourth namespace prefix is z, which references the actual data. The Products.xml XML file that was generated from the above code looks as follows:

 <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='ProductID' rs:number='1'> <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'             rs:maybenull='false'/> </s:AttributeType> <s:AttributeType name='ProductName' rs:number='2' s:writeunknown='true'>        <s:datatype dt:type='string' dt:maxLength='40'       rs:maybenull='false'/> </s:AttributeType> <s:AttributeType name='SupplierID' 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='CategoryID' rs:number='4' 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='QuantityPerUnit' rs:number='5' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='string' dt:maxLength='20'/> </s:AttributeType> <s:AttributeType name='UnitPrice' rs:number='6' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='i8' rs:dbtype='currency' dt:maxLength='8' rs:precision='19' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='UnitsInStock' rs:number='7'    rs:nullable='true' rs:writeunknown='true'>    <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5'          rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='UnitsOnOrder' rs:number='8' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5'          rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='ReorderLevel' rs:number='9'       rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true'/> </s:AttributeType> <s:AttributeType name='Discontinued' rs:number='10'       rs:writeunknown='true'> <s:datatype dt:type='boolean' dt:maxLength='2'          rs:fixedlength='true' rs:maybenull='false'/> </s:AttributeType> <s:extends type='rs:rowbase'/> </s:ElementType> </s:Schema> <rs:data> <z:row ProductID='1' ProductName='Chai' SupplierID='1' CategoryID='1' QuantityPerUnit='10 boxes x 20 bags' UnitPrice='18' UnitsInStock='39' UnitsOnOrder='0' ReorderLevel='10' Discontinued='False'/> <z:row ProductID='2' ProductName='Chang' SupplierID='1' CategoryID='1' QuantityPerUnit='24 - 12 oz bottles' UnitPrice='19' UnitsInStock='17' UnitsOnOrder='40' ReorderLevel='25' Discontinued='False'/>  </rs:data> </xml>

Using ADO 2.5, we have created an XML document that contains the schema and the data for the Products table of the Northwind Traders database.

NOTE
For more information about ADO 2.5, look at the Microsoft Data Access Components (MDAC) 2.5 SDK on Microsoft's Web site.

In some cases, you will have to make changes to the generated data before it can be used as XML. For example, if there is an invalid character in the column name, such as a space, you will have to change the name to a valid XML name. You would do this by changing the name attribute and adding an rs:name attribute that contains the original name of the field. Thus, if you had a column in the database called Shipper Name you could end up with the following AttributeType:

 <s:AttributeType name='Shipper Name' rs:number='9'    rs:nullable='true' rs:writeunknown='true'>    <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='50'       rs:fixedlength='true'/> </s:AttributeType>

You would have to change the AttributeType as follows:

 <s:AttributeType name='ShipperName' rs:name='Shipper Name'    rs:number='9' rs:nullable='true' rs:writeunknown='true'> <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='50'       rs:fixedlength='true'/> </s:AttributeType>

This document can then be presented in Internet Explorer 5 as XML or transformed to XHTML or other formats using an XSL document.

The original file that is generated can be used only to create a read-only ADO recordset. If you want to create an updatable client-side disconnected recordset, you must add an rs:updatable attribute to the ElementType definition. A client-side disconnected recordset has no connection to the original data source. A user can review, edit, delete, update, and add records to the recordset, but a connection to the database must be reestablished in order for the changes to be saved to the database. To save the changes, the UpdateBatch method of an ADO recordset must be called after the disconnected recordset is reconnected to the database. The UpdateBatch method is used to send multiple recordset updates to the server in one call.

To make the data updatable, you would change the ElementType definition to the following:

 <s:ElementType name='row' content='eltOnly' rs:updatable='true'>

Inputting Data as XML Using ADO 2.5

Reading the XML data generated by ADO 2.5 is just as easy as outputting the data. To examine how to input data as XML using ADO 2.5 in the example application, add another command button to the frmADOXML form and call it cmdRetrieve with a caption Retrieve&Add. Add the following code to the click event handler of the command button cmdRetrieve:

 Private Sub cmdRetrieve_Click() Dim objNWRecordset As ADODB.Recordset Set objNWRecordset = New ADODB.Recordset 'Open the recordset to a file as XML. objNWRecordset.Open "C:\Products.XML", Options:=adCmdFile 'Add a new record. objNWRecordset.AddNew objNWRecordset.Fields("ProductName") = "Test" End Sub

NOTE
An error will be raised if you run this code without adding the rs:updatable='true' attribute to the schema section of the generated XML file.

Setting Options to adCmdFile tells ADO that this data will be coming from a regular file and not from a database.

Once you add a new record, edit a record, or delete a record, you must call the Update method of the ADO recordset. Each time you call the Update method, the updated record is marked within the recordset. If you make changes to the recordset and save the changes to a file, you can see the actual changes. These changes are being made only to the recordset, not to the actual data in the database, as there is no connection to the database.

Making Changes to the Data

Now that we have seen how to save data as XML and how to open XML data using ADO, we will look at how to make changes to the XML data. We will start by writing code to open the Products.xml file we created, and then we'll make changes to the data. Once the changes are made, we will call the Update method of the recordset. When the changes are complete, we will save the new data to a file called ProductsUpdate.xml. First add another command button called cmdMakeChanges to the form with a caption Make Changes. Add the following code to the click event handler of the command button cmdMakeChanges:

 Private Sub cmdMakeChanges_Click() Dim objNWRecordset As ADODB.Recordset Set objNWRecordset = New ADODB.Recordset 'Open the recordset to a file as XML objNWRecordset.Open "C:\Products.XML", Options:=adCmdFile objNWRecordset.Fields("ProductName") = "Test" objNWRecordset.Update objNWRecordset.MoveLast objNWRecordset.Delete objNWRecordset.Update objNWRecordset.Filter = adFilterPendingRecords objNWRecordset.Save "c:\ProductsUpdate.xml", adPersistXML End Sub

In this case, we have changed the product name of the first record to Test and deleted the last record. The changed recordset is then saved to the ProductUpdate.xml file. You can see the following new additions in the ProductUpdate.xml file after clicking the Make Changes command button:

  <rs:update> <rs:original>       <z:row ProductID='1' ProductName='Chai' SupplierID='1'        CategoryID='1' QuantityPerUnit='10 boxes x 20 bags'          UnitPrice='18' UnitsInStock='39' UnitsOnOrder='0'          ReorderLevel='10' Discontinued='False'/> </rs:original>    <z:row ProductName='Test'/> </rs:update>  <rs:delete>    <z:row ProductID='77' ProductName='Original Frankfurter grüne Soße'       SupplierID='12' CategoryID='2' QuantityPerUnit='12 boxes'     UnitPrice='13' UnitsInStock='32' UnitsOnOrder='0'    ReorderLevel='15' Discontinued='False'/> </rs:delete>

With the updated XML file, ADO can reconstruct a recordset that has the original and new values for fields that are edited and can determine which rows have been deleted. Now let's look at how we would actually update the data source.