Persisting an ADO Recordset to XML

by Mark Wilson

When Microsoft introduced ADO 2.0, a new method was included on the Recordset object - the Save method.

The Save method accepts two arguments, FileName and PersistFormat.  At the time, the only PersistFormat you could use was "adPersistADTG” which was a binary format.

Sub Save([FileName as String],[PersistFormat As PersistFormatEnum = adPersistADTG])

In ADO 2.0, the only member of the Enum PersistFormatEnum was adPersistADTG and you could use it in the following example ways...

Save a recordset to disk

Private Sub CacheRecords(theRecordset As ADODB.Recordset)
	' save in binary format
	theRecordset.Save App.Path & "\testdata.dat", adPersistADTG
End Sub

Load a persisted recordset from disk

Private Function GetCachedRecords() As ADODB.Recordset
	Dim strConnect As String
	strConnect = "Provider=MSPersist"
	Dim rs As ADODB.Recordset
	Set rs = New ADODB.Recordset
	rs.Open App.Path & "\testdata.dat", strConnect
	Set GetCachedRecords = rs
End  Function

To save as XML

The ability for the ADO objects to save the recordsets as XML is provided by version 2.1 of the ADO objects. As long as the file is not changed, another program using ADO 2.1 can unpack the recordset file back in the original XML text file.

SubSaveAsXML(theRecordset As ADODB.Recordset)
    'save in binary format
    theRecordset.Save App.Path & "\testdata.xml", adPersistXML
End Sub

To load from XML (you can test using our site)

Private Function GetVBOSPData() As ADODB.Recordset
	Dim strConnect As String
	strConnect = "Provider=MSPersist"
	Dim rs As ADODB.Recordset
	Set rs = New ADODB.Recordset
	rs.Open "http://www.topxml.com/vbopensource/ado/testdata.xml", strConnect
	Set  GetVBOSPData= rs
End  Function

You can download a sample program that illustrates this concept in more detail. We look forward to your feedback!

So, what could you do with this feature?

Well, for one thing, your remote applications can retrieve XML from a webserver using the IE5 XMLHTTPRequest object.

One of the cool things about XMLHTTP is the ability to post and receive XML data to or from an HTTP server. This is an easy way to become server and platform independent because program don't mind who or what the HTTP server it's talking to is, or even what operating system it is running on. All the HTTP server needs to do is have the capability to receive HTTP calls such as POST or GET and then pass the call on to some form of program (ASP, CGI, Java Servlets, etc).

So you can look at the combination of XML via the HTTP calls as a kind of middleware or glue to tie systems together. In our case, we are going to open a connection to the host web server on a PC and then use an ASP file that will reply with an XML file (which could be a persisted ADO file). To make the connection, we will make use of an object that ships with IE5 and its called XMLHTTP.

Basically, you open a “POST” connection to your webserver (which is all a web browser does anyway!) and then using this connection, you “talk” to the PEOPLEVB.ASP file and it will return your pick list in XML format. Receiving an XML DOMDocument object in VB

In the VB part of this example, this is the code we could use to connect to the web server and retrieve our XML string back:

‘create the objects
Dim xmlHttp As new XMLHTTPRequest
Dim objXmlDoc As DOMDocument
Dim sRequest as string
‘to avoid a bug in the HTTPRequest object, you must pass in a string variable
sRequest = " http://localserver/xmlcode/demo.asp"
‘open the POST (or GET) connection to the web server with a string variable as the second parameter
xmlHttp.Open "POST", sRequest, False
‘establish the connection
xmlHttp.Send
‘receive the response – note there are different types of responses, binary and text is also supported
Set objXmlDoc = xmlHttp.responseXML

On the web server side, we will use some ASP in the demo.asp file that is being called.  This code extracts the data from the database and persists it:

Private Sub cmdSaveAdoFile_Click()
    Dim PEOPLE As ADODB.Recordset
    Dim ObjConn As New ADODB.Connection
    ‘open an existing ODBC connection to your database
    ObjConn.Open "VBXML"
    ‘execute a select SQL statement against the database
    Set PEOPLE = ObjConn.Execute("select * from People")
    ‘save the recordset as an XML file
    PEOPLE.Save "c:\adodemo.xml", adPersistXML
End Sub

This code extracts the data from the database and persists it:

Private Sub cmdLoadAdoFile_Click()
    Dim PEOPLE As ADODB.Recordset
    Set PEOPLE = New ADODB.Recordset
    ‘open your XML file and load it
    PEOPLE.Open "c:\adodemo.xml", "Provider=MSPersist"
    'lets have a message box popup the number of records
    MsgBox PEOPLE.RecordCount
End Sub

Make sure your server-side ASP file sets the ContentType to "text/xml" and to send the XML back to the client.  The ASP file on the web server, could contain:  

‘make a variant object
Dim objXmlDoc
‘place the DOMDocument into the objXmlDoc object
Set objXmlDoc = Server.CreateObject("MSXML.DOMDocument")
'load the XML document object here
objXmlDoc.load Server.MapPath("people.xml")
‘set the content type to XML – note other types are supported!
Response.contenttype = “text/xml”
‘now send back the DOMDocument you have loaded above
response.write objXmlDoc

Now you have a simple VB application that communicates with an ASP file on a web server and receives a DOMDocument as a response.

---

Rate this article on a scale of 1 to 10

Your vote : 1 2 3 4 5 6 7 8 9 10  

---