ado and ado.net programming - perfectxml.com · 134 o ne of the big advances of ado over previous...

SYBEX Sample Chapter

ADO and ADO.NET ProgrammingMike Gunderloy

Chapter 6: Using Records and Streams

Copyright © 2002 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World rights reserved. No part of thispublication may be stored in a retrieval system, transmitted, or reproduced in any way, including but not limited tophotocopy, photograph, magnetic or other record, without the prior agreement and written permission of the publisher.

ISBN: 0-7821-2994-3

SYBEX and the SYBEX logo are either registered trademarks or trademarks of SYBEX Inc. in the USA and othercountries.

TRADEMARKS: Sybex has attempted throughout this book to distinguish proprietary trademarks from descriptive termsby following the capitalization style used by the manufacturer. Copyrights and trademarks of all products and serviceslisted or described herein are property of their respective owners and companies. All rules and laws pertaining to saidcopyrights and trademarks are inferred.

This document may contain images, text, trademarks, logos, and/or other material owned by third parties. All rightsreserved. Such material may not be copied, distributed, transmitted, or stored without the express, prior, written consentof the owner.

The author and publisher have made their best efforts to prepare this book, and the content is based upon final releasesoftware whenever possible. Portions of the manuscript may be based upon pre-release versions supplied by softwaremanufacturers. The author and the publisher make no representation or warranties of any kind with regard to thecompleteness or accuracy of the contents herein and accept no liability of any kind including but not limited toperformance, merchantability, fitness for any particular purpose, or any losses or damages of any kind caused or allegedto be caused directly or indirectly from this book.

Sybex Inc.1151 Marina Village ParkwayAlameda, CA 94501U.S.A.Phone: 510-523-8233www.sybex.com

Using Records and Streams

• Using ADO with nonrelational data

• Working with BLOBs in Recordsets

• Using Command and result Streams

Chapter 6

2994c06.qxd 1/15/02 12:02 PM Page 133

134

O ne of the big advances of ADO over previous data access models such as DAO or RDO isthat ADO has been explicitly extended to deal with nonrelational data. By implementing

a pair of specialized objects (Record and Stream), Microsoft has made it possible to treat datasources as diverse as web sites and Exchange servers in ADO code. The Record and Streamobjects are also used to integrate ADO with XML. In this chapter, you’ll learn how to usethese objects for a variety of data access tasks.

Using ADO with Nonrelational DataADO was designed from the start to handle nonrelational data in a database-like manner. For example, many types of data are better represented as a hierarchy than as a relationaldatabase:

� Folders and messages in an e-mail system

� Folders and files on a hard drive

� Directories and files on an FTP server

The Record, Stream, and Recordset objects work together to help you navigate throughhierarchical storage:

� You can open a Record object at the root level of a storage.

� Using the Record’s GetChildren method, you can open a Recordset object containing allthe children of a node. You can, in turn, set a Record object equal to one of these children.

� Using the Record’s associated Stream object, you can retrieve the actual binary data fromwithin the Record. You can think of Records as directory entries and Streams as the datacontained within the files that those entries refer to.

WARNING There’s no easy way to tell whether an arbitrary OLE DB provider supports the Recordobject. Perhaps the best way is to attempt to open a Record object using the provider. Ifthe provider can’t supply one, it will generally return error 3251, Operation Not Supported.

Using the Record ObjectA Record object represents one row of data, or, more abstractly, one item returned by anOLE DB provider. Although a Record object resembles a single-row Recordset, the Recordobject has fewer methods and properties than the Recordset and requires less applicationoverhead to open and manipulate. Table 6.1 shows some of the methods and properties ofthe Record object.

Chapter 6 • Using Records and Streams

2994c06.qxd 1/15/02 12:02 PM Page 134

135

TABLE 6.1: Selected Details about the Record Object

Name Type Explanation

ActiveConnection Property The OLE DB connection from which this Record object wasretrieved.

Close Method Removes the connection between this object and the originaldata source.

DeleteRecord Method Deletes the contents of the Record object.

Fields Collection Field objects contained within this Record.

GetChildren Method Opens a Recordset containing the Records (for example, subdi-rectories and files) in a hierarchical data structure directly belowthis Record.

Open Method Associates the Record with a data source.

RecordType Property Indicates the type of data represented by this object. This canbe adCollectionRecord (a Record that contains child nodes),adSimpleRecord (a Record without child nodes), adStructDoc (aCOM structured document), or adRecordUnknown (an unknownor uninitialized Record).

Source Property Contains the URL or Recordset from which this Record wasderived.

TIP For complete details on the Record and Stream objects, refer to Appendix A, “The ADOObject Model.”

Figure 6.1 shows the frmWeb form in the ADOChapter6 sample project. This formillustrates the use of the Record object in conjunction with the Recordset object to retrieve the structure of a website, by using the Microsoft OLE DB Provider for InternetPublishing.

The code in this example is mildly complex. Most of the complexity comes from managingtwo sets of objects: the Record and Recordset objects that represent the structure of the cho-sen web server, and the Node objects that map that structure back to the TreeView controlon the form. Listing 6.1 shows the code that runs after the user enters a URL and clicks theLoad button.

WARNING You must have administrative privileges over the web server in order to retrieve data viathe OLE DB Provider for Internet Publishing. Otherwise, you’ll get Error -2147467259,which shows up as “unspecified error.”

Using ADO with Nonrelational Data

2994c06.qxd 1/15/02 12:02 PM Page 135

136

➲ Listing 6.1: cmdLoad_Click Procedure

Private Sub cmdLoad_Click()

Dim rec As ADODB.RecordDim rstChildren As ADODB.RecordsetDim nodRoot As NodeDim nod As NodeDim strURL As StringDim strPath As String

On Error GoTo HandleErr

‘ Clear the treeviewtvwWeb.Nodes.Clear

‘ Open the indicated URLSet rec = New ADODB.Recordrec.Open “”, “URL=” & txtURL.Text

F I G U R E 6 . 1 :A website as dis-played via ADO


2994c06.qxd 1/15/02 12:02 PM Page 136

137

‘ Add this as the root of the treeviewSelect Case rec.RecordType

Case adSimpleRecordSet nodRoot = tvwWeb.Nodes.Add(, , _URLtoKey(txtURL.Text), txtURL.Text, conImageDocument)‘ No child nodes, we’re done

Case adCollectionRecordSet nodRoot = tvwWeb.Nodes.Add(, , _URLtoKey(txtURL.Text), txtURL.Text, conImageFolderClosed)‘ Open a Recordset containing this record’s children.Set rstChildren = rec.GetChildrenDo Until rstChildren.EOF

strURL = rstChildren.Fields(“RESOURCE_PARSENAME”)strPath = rstChildren.Fields(“RESOURCE_ABSOLUTEPARSENAME”)rec.Closerec.Open rstChildrenSelect Case rec.RecordType

Case adSimpleRecordSet nod = tvwWeb.Nodes.Add(nodRoot, tvwChild, _URLtoKey(strPath), strURL, conImageDocument)

Case adCollectionRecordSet nod = tvwWeb.Nodes.Add(nodRoot, tvwChild, _URLtoKey(strPath), strURL, conImageFolderClosed)‘ Keep track of where we arenod.Tag = strPath‘ Add a dummy child to get expandabilitySet nod = tvwWeb.Nodes.Add(nod, tvwChild, _“D” & nod.Key, “DUMMY”)

End SelectrstChildren.MoveNext

LoopEnd Select

‘ CleanupIf Not rstChildren Is Nothing Then

rstChildren.CloseSet rstChildren = Nothing

End Ifrec.CloseSet rec = Nothing

ExitHere:Exit Sub

HandleErr:MsgBox “Error “ & Err.Number & “: “ & Err.Description, _vbOKOnly, “cmdLoad”Resume ExitHereResume

End Sub


2994c06.qxd 1/15/02 12:02 PM Page 137

138

The cmdLoad procedure opens with a call to the Open method of the Record object. Thisparticular procedure uses a specialized syntax:

rec.Open “”, “URL=” & txtURL.Text

When you omit source information and supply a URL as the connection string for theRecord object, it automatically uses the Microsoft OLE DB Provider for Internet Publishingto open that URL. However, you’re free to use other OLE DB providers with the Recordobject.

Here’s the full syntax of the Record.Open method:Record.Open Source, ActiveConnection, Mode,➥ CreateOptions, Options, UserName, Password

This method has several arguments:

� The Source may be a URL, a Command object (in which case the Record object will holdthe first row of the Recordset returned by the Command object), a SQL statement, or atable name.

� The ActiveConnection can be a connection string or an open Connection object.

� The Mode argument can be any of the constants that are used with the Connection objectto specify a locking mode.

� The CreateOptions argument specifies whether a new Record object should be created oran existing Record opened.

� The Options argument allows fine-tuning of some of the Record object’s opening behav-ior. For example, you can use adOpenOutput to retrieve the results of an ASP pageinstead of the source of the page.

� The UserName and Password arguments supply security information.

TIP For additional information on the constants for Mode, CreateOptions, and Options, referto the ADO API Reference in the MDAC SDK.

The code then checks the RecordType of the retrieved Record to determine whether thesupplied URL points to a folder or a document. If the RecordType is adSimpleRecord, theURL will indicate a document, and there won’t be any child documents. In this case, thecode just needs to add a single node to the TreeView, and the job is done:

Set nodRoot = tvwWeb.Nodes.Add(, , _URLtoKey(txtURL.Text), txtURL.Text, conImageDocument)

TIP The URLtoKey procedure removes illegal characters to turn an arbitrary URL into a stringthat can be used as a Node.Key property.


2994c06.qxd 1/15/02 12:02 PM Page 138

139

More commonly, though, the opening URL will be a folder (in Figure 6.1, it’s the root of aweb server named STALLION on my local network). The code can detect this by notingthat the RecordType of the Record object will be adCollectionRecord. In that case, afteradding the root node (which, in this case, uses the closed folder image rather than the docu-ment image), the code calls the GetChildren method of the Record. This method returns aRecordset that contains one member for each of the children of the Record. The code iter-ates through this Recordset and adds child nodes for each member of the Recordset.

The code at this point uses a dodge to avoid having to retrieve the structure of the entireweb server at once. The TreeView control will show the + sign to indicate node expandabilityonly if there is a child node. In this case, if a node has the RecordType of adCollection-Record, the code adds a dummy node directly beneath it so that the + sign will be displayed:

Set nod = tvwWeb.Nodes.Add(nod, tvwChild, _“D” & nod.Key, “DUMMY”)

When the user expands a node, the code in Listing 6.2 runs to handle the necessary displaychanges.

➲ Listing 6.2: tvwWebExpand

Private Sub tvwWeb_Expand(ByVal Node As MSComctlLib.Node)‘ Called when the user expands a node. This will always‘ be a folder in this caseDim rec As ADODB.RecordDim rstChildren As ADODB.RecordsetDim nod As NodeDim strURL As StringDim strPath As String


‘ First, switch to the Open Folder imageIf Node.Image = conImageFolderClosed Then

Node.Image = conImageFolderOpenEnd If

‘ Check to see if the first child is a dummy nodeIf Node.Child.Text = “DUMMY” Then

‘ If so, we need to delete the dummy node and‘ get the real childrentvwWeb.Nodes.Remove (Node.Child.Index)Set rec = New ADODB.Recordrec.Open “”, “URL=” & Node.TagSet rstChildren = rec.GetChildrenDo Until rstChildren.EOF

strURL = rstChildren.Fields(“RESOURCE_PARSENAME”)strPath = rstChildren.Fields(“RESOURCE_ABSOLUTEPARSENAME”)rec.Close


2994c06.qxd 1/15/02 12:02 PM Page 139

140

rec.Open rstChildrenSelect Case rec.RecordType

Case adSimpleRecordSet nod = tvwWeb.Nodes.Add(Node, tvwChild, _URLtoKey(strPath), strURL, conImageDocument)

Case adCollectionRecordSet nod = tvwWeb.Nodes.Add(Node, tvwChild, _URLtoKey(strPath), strURL, conImageFolderClosed)‘ Keep track of where we arenod.Tag = strPath‘ Add a dummy child to get expandabilitySet nod = tvwWeb.Nodes.Add(nod, tvwChild, _“D” & nod.Key, “DUMMY”)

End SelectrstChildren.MoveNext

LoopEnd If

‘ CleanupIf Not rstChildren Is Nothing Then

rstChildren.CloseSet rstChildren = Nothing

End IfIf Not rec Is Nothing Then

rec.CloseSet rec = Nothing

End If

ExitHere:Exit Sub

HandleErr:MsgBox “Error “ & Err.Number & “: “ & Err.Description, _vbOKOnly, “tvwWeb_Expand”Resume ExitHereResume

End Sub

You can see in Listing 6.2 how the rest of the delayed information retrieval works. If thefirst child of the node being expanded is a dummy node, the code deletes that node and thenopens a Recordset populated with the actual children. By using this technique, the time toload the information is spread out instead of being concentrated at the initial load. Even bet-ter, if a node is never displayed, no time is wasted on creating that node.

Resource RecordsetsThe Recordset returned by the Record.GetChildren method isn’t simply a collection ofRecord objects representing child documents. Rather, it’s an example of what’s called a


2994c06.qxd 1/15/02 12:02 PM Page 140

141

resource Recordset. A resource Recordset is a collection of fields that describe the child docu-ments, without containing the child documents themselves.

Resource Recordsets are created by a class of OLE DB providers known as document sourceproviders. The Microsoft OLE DB Provider for Internet Publishing is one such provider.The fields of the resource Recordset are dictated by the provider. Table 6.2 lists the fields inthe resource Recordset from the OLE DB Provider for Internet Publishing. Two of thesefields (RESOURCE_PARSENAME and RESOURCE_ABSOLUTEPARSENAME) areused in the code in Listings 6.1 and 6.2.

TABLE 6.2: Resource Recordset Fields from the Internet Publishing Provider

Field Meaning

RESOURCE_PARSENAME URL of the resource

RESOURCE_PARENTNAME URL of the parent record

RESOURCE_ABSOLUTEPARSENAME URL, including the path of the resource

RESOURCE_ISHIDDEN True if the resource is hidden

RESOURCE_ISREADONLY True if the resource is read-only

RESOURCE_CONTENTTYPE MIME type of the document

RESOURCE_CONTENTCLASS Usually empty, but with an Office document, contains thetemplate used to create the document

RESOURCE_CONTENTLANGUAGE Language of the content

RESOURCE_CREATIONTIME Date and time the resource was created

RESOURCE_LASTACCESSTIME Date and time the resource was last read

RESOURCE_LASTWRITETIME Date and time the resource was last modified

RESOURCE_STREAMSIZE Size in bytes of the resource’s default stream

RESOURCE_ISCOLLECTION True if the resource is a directory or other collection

RESOURCE_ISSTRUCTUREDDOCUMENT True if the resource is a structured document

DEFAULT_DOCUMENT True if the resource is the default document of the parentfolder

RESOURCE_DISPLAYNAME Friendly name, if any, of the resource

RESOURCE_ISROOT True if the resource is the root of a tree

RESOURCE_ISMARKEDFOROFFLINE True if the resource is set to allow offline use

Using the Stream ObjectThe Stream object represents binary data. Most often, you’ll want to open a Stream object toretrieve the data associated with a Record object. You can also create your own stand-aloneStream objects and use them as handy spots to store binary data. Later in the chapter, you’llsee that Stream objects also integrate well with XML data.


2994c06.qxd 1/15/02 12:02 PM Page 141

142

Table 6.3 lists some of the details of the Stream object.

TABLE 6.3: Selected Details about the Stream Object

Name Type Explanation

Charset Property Character set to be used when storing this Stream object.

Close Method Disassociates this object from its data source.

LoadFromFile Method Loads the contents of a local disk file into an open Stream.

Open Method Retrieves data into the Stream.

Read Method Reads data from a binary Stream.

ReadText Method Reads data from a text Stream.

SaveToFile Method Writes the contents of the Stream to a local disk file.

Size Property Number of bytes in the Stream.

Type Property Specifies whether the Stream is binary (adTypeBinary) or tex-tual (adTypeText) information.

Write Method Writes data to a binary Stream.

WriteText Method Writes data to a text Stream.

One good use of Stream objects is retrieving the binary data (if any) associated with aRecord object. The web example that I’ve been using in this chapter makes use of this func-tionality to display the contents of web pages.

To run the following code, you can right-click any document in the TreeView and selectView Contents from the pop-up menu:

Private Sub mnuShortcutViewContents_Click()

Dim rec As ADODB.RecordDim stm As ADODB.Stream


‘ If this node represents a document, it will have‘ no children. In that case, retrieve the stream.If tvwWeb.SelectedItem.Children = 0 Then

Set rec = New ADODB.Recordrec.Open “”, “URL=” & tvwWeb.SelectedItem.TagSet stm = New ADODB.Streamstm.Open rec, adModeRead, adOpenStreamFromRecordLoad frmStreamIf stm.Type = adTypeBinary Then

frmStream.txtStream.Text = stm.Read(adReadAll)Else ‘ stm.type=adTypeText


2994c06.qxd 1/15/02 12:02 PM Page 142

143

stm.Charset = “ascii”frmStream.txtStream.Text = stm.ReadText(adReadAll)

End IffrmStream.Show vbModal

End If

‘Clean uprec.CloseSet rec = Nothingstm.CloseSet stm = Nothing

ExitHere:Exit Sub

HandleErr:MsgBox “Error “ & Err.Number & “: “ & Err.Description, _vbOKOnly, “tvwWeb_DblClick”Resume ExitHereResume

End Sub

The code first uses the information stored in the TreeView to open a Record variablereferring to the selected document. It then calls the Open method of the Stream object tograb the binary or text data of the Record. Given the Stream object, you call either the Reador the ReadText method to obtain the actual data, depending on whether the Stream isbinary or text, which you can determine from the Stream object’s Type property.

Note the use of the Charset property of the Stream object. The data in the Stream isalways stored in Unicode. However, it’s a fact of life that you probably want a non-Unicodecharacter set for display. The ASCII character set, as shown in this example, is usually a safebet. If you’d like to know what other options are available on your computer, check the Reg-istry under HKEY_CLASSES_ROOT\Mime\Database\Charset.

Here’s the full syntax of the Stream object’s Open method:Stream.Open Source, Mode, OpenOptions, UserName, Password

As you see, the method has five arguments:

� The Source argument specifies the source of the data to be stored in the Stream object.This can be a Record object (as in the example above) or a URL. The Source argument isoptional; if you don’t specify a source, an empty Stream object is opened.

� The Mode argument specifies the connection mode for the Stream. This argument usesthe same constants that are used with the Mode property of the Connection object.


2994c06.qxd 1/15/02 12:02 PM Page 143

144

� The OpenOptions argument allows you to specify that the Stream will be opened from aRecord, or that it should be opened asynchronously.

� The UserName and Password arguments are used to set the security context of the Openoperation.

Using Streams for Arbitrary DataYou can also create your own Stream objects and use them as general-purpose receptacles tohold any type of data that you care to store. One use for this technique is integrated directlyinto ADO. You can persist the contents of a Recordset to a Stream, or take a Stream that hassuch contents and reload it into a Recordset. You can also store the contents of a Stream to adisk file, or load a Stream from a disk file.

Figure 6.2 shows frmStreamPersist from this chapter’s sample project. This form lets youmove data from a database to a Recordset to a Stream to a disk file and back again.

Let’s trace the code for these data transformations step by step. First, the code for loadingdata from a database to a Recordset uses the familiar Recordset.Open method:

Private Sub cmdOpenRecordsetFromDatabase_Click()mrst.Open “Customers”, _“Provider=Microsoft.Jet.OLEDB.4.0;” & _“Data Source=” & App.Path & “\ADODHChapter6.mdb”, _adOpenKeyset, adLockOptimisticlblRecordset.BackColor = &H8000000C

End Sub

NOTE The BackColor property changes in this example provide a visual cue as to which objectscontain data at any given time.

To move the data from a Recordset to a Stream, you call the Recordset’s Save method:Private Sub cmdOpenStream_Click()

mrst.Save mstm, adPersistXMLlblStream.BackColor = &H8000000C

End Sub

F I G U R E 6 . 2 :Experimenting withStreams


2994c06.qxd 1/15/02 12:02 PM Page 144

145

The second argument to the Save method specifies the storage format for the data. Thiscan be either adPersistXML (to use industry-standard XML) or the default adPersistADTG(to use the proprietary Microsoft Advanced Data Tablegram format). If your OLE DBprovider supplies its own format for saving Recordsets, you can also specify adPersist-ProviderSpecific to use this format.

TIP To save a Recordset directly to a disk file, specify the name of the disk file as the firstargument to the Save method.

WARNING Although most Recordsets can be saved to XML, hierarchical Recordsets with pendingupdates or parameterized hierarchical Recordsets can be saved in ADTG format only.You’ll learn more about hierarchical Recordsets in Chapter 7, “Data Shaping.”

Given a Stream that contains data, you can save the data to a disk file by calling theStream’s SaveToFile method:

Private Sub cmdSave_Click()mstm.SaveToFile App.Path & “\Customers.xml”, adSaveCreateOverWritelblDiskFile.BackColor = &H8000000C

End Sub

The second argument in this case tells the Stream that it’s okay to overwrite the file if italready exists. If the file does not exist, the Save method will create it.

Moving data in the other direction, you can open a Stream from a disk file by calling theStream’s LoadFromFile method:

Private Sub cmdLoad_Click()mstm.LoadFromFile App.Path & “\Customers.xml”lblStream.BackColor = &H8000000C

End Sub

The LoadFromFile method overwrites any data that the Stream object might already con-tain. This method can be used with any file, not just a file that was created from a Streamobject.

Finally, to move data from a Stream back to a Recordset, you can use the Recordset’s Openmethod:

Private Sub cmdOpenRecordsetFromStream_Click()mrst.Open mstmlblRecordset.BackColor = &H8000000C

End Sub

TIP The Open method will fail if the Stream doesn’t contain data in a format that ADO recog-nizes as being a Recordset.


2994c06.qxd 1/15/02 12:02 PM 5

146

Using Stream objects and disk files in this manner can provide your applications with agreat deal of flexibility. For example, you can open a Recordset when you’re connected to anetwork that contains a database server, and then save that Recordset into a disk file (eitherdirectly or via an intermediate Stream object). If you later disconnect from the network, youcan still reconstitute the Recordset from the disk file, even if the server is no longer available.You can even save a Recordset with pending updates, open it later when you’re connected tothe server, and commit the updates at that time.

Working with BLOBs in RecordsetsStreams also provide a handy way to work with binary large objects (BLOBs) in Recordsets.Before the Stream object was added to ADO, the only way to manipulate data in a BLOBfield was by using the GetChunk and AppendChunk methods of the Field object. Now, how-ever, you can use a Stream object to easily retrieve data from or save data to a BLOB field.

Here’s an example using the SQL Server OLE DB provider:Private Sub cmdBlob_Click()

Dim cnn As ADODB.ConnectionDim rst As ADODB.RecordsetDim stm As ADODB.Stream


Set cnn = New ADODB.Connectioncnn.Open “Provider=SQLOLEDB.1;Server=(local);” & _“Initial Catalog=pubs;Integrated Security=SSPI”

Set rst = New ADODB.Recordsetrst.Open “pub_info”, cnn, adOpenKeyset, adLockOptimistic

Set stm = New ADODB.Streamstm.Type = adTypeBinarystm.Openstm.Write rst.Fields(“logo”).Valuestm.SaveToFile “c:\temp\test.gif”, adSaveCreateOverWrite

Image1.Picture = LoadPicture(“c:\temp\test.gif”)

‘Clean upstm.CloseSet stm = Nothingrst.CloseSet rst = Nothing


2994c06.qxd 1/15/02 12:02 PM Page 146

147

cnn.CloseSet cnn = Nothing

ExitHere:Exit Sub

HandleErr:MsgBox “Error “ & Err.Number & “: “ & Err.Description, _vbOKOnly, “cmdBLOB”Resume ExitHereResume

End Sub

WARNING This code depends on the existence of a C:\temp folder on the system where it’s run. Ifthere is no such folder, you’ll get an error message when the Stream attempts to write toa file in that folder.

After using the Connection and Recordset objects to open a table of data that includes aSQL Server image column, this procedure instantiates a Stream object and sets its data typeto binary. It then calls the Stream’s Open method with no arguments. This makes the Streamobject available to hold data, without actually placing any data in the Stream. Calling theStream’s Write method then moves data from the method’s argument (in this case, the rawdata contained in the logo field in the table) to the Stream. Once the data is in the Stream, itcan be treated just like any other Stream data. For example, it can be saved to a disk file, as inthis particular example.

To move data in the other direction—from a Stream to a BLOB field—you call theStream’s Read method. In the preceding example, to move data back from the Stream to thefield in the Recordset, you could use this line of code:

rst.Fields(“logo”).Value = stm.Read

Figure 6.3 shows the result. A graphic that was stored in the database is now visible on thefrmMenu form.

F I G U R E 6 . 3 :Loading a graphicfrom a Stream

Working with BLOBs in Recordsets

2994c06.qxd 1/15/02 12:02 PM Page 147

148

TIP The Read and Write methods of the Stream object refer to what is being done to the Stream,not to actions the Stream takes. That is, the Write method puts data into the Stream, and theRead method retrieves data from the Stream. This is reversed from the usual sense of theseverbs, and can thus be a source of confusion.

Using Command and Result StreamsThe ADO Command object also has a connection to the Stream object. Streams can be usedfor both the definition of the Command and the results returned by executing the Com-mand. I’ll demonstrate the syntax of these Stream objects within a Visual Basic application;then I’ll show how these objects make it easier to use ADO to manage XML data within aweb page when using an appropriate provider, such as that for Microsoft SQL Server 2000.

Figure 6.4 shows a sample form (frmCommandStreams) in this chapter’s sample projectthat demonstrates the use of Command and result Streams. In this case, I’ve retrieved theCustomers table from a SQL Server database and displayed the results as XML.

TIP In order to use Streams with the Command object, you must be using an OLE DB providerthat supports the necessary interfaces. Currently, this limits the use of this feature toMicrosoft SQL Server 2000.

The code for this example starts by defining a Command Stream as XML:strCommand = “<root xmlns:sql=’urn:schemas-microsoft-com:xml-sql’>” & _

vbCrLf & _“<sql:query> SELECT * FROM Customers ORDER BY Country “ & _“FOR XML AUTO </sql:query>” & vbCrLf & _“</root>”

F I G U R E 6 . 4 :Retrieving informationas an XML Stream


2994c06.qxd 1/15/02 12:02 PM Page 148

149

If you take away the Visual Basic syntax that quotes this string and spread it over severalprogram lines, the generated XML is as follows:

<root xmlns:sql=’urn:schemas-microsoft-com:xml-sql’><sql:query> SELECT * FROM Customers ORDER BY Country

FOR XML AUTO </sql:query></root>

This particular piece of XML is called an XML template query. There are two essentialpieces to this query:

� The root tag includes a reference to the XML namespace defined by Microsoft for XMLqueries to SQL Server.

� The sql:query tag contains the text of a SELECT query to be executed by SQL Server. Inthis particular case, the query uses the FOR XML AUTO tag to return the results as XML.

Once the XML template query has been defined, the code places it into a Stream object:Set stmCommand = New ADODB.StreamstmCommand.OpenstmCommand.WriteText strCommand, adWriteCharstmCommand.Position = 0

The Open method makes the Stream object ready to receive text, and the WriteTextmethod actually places the text into the Stream. The Position property is used to make surethat the next reading of the text starts at the beginning of the text rather than the end.

The next step in this example is to create a Command object that’s associated with a con-nection to a SQL Server:

Const DBGUID_MSSQLXML = “{5D531CB2-E6Ed-11D2-B252-00C04F681B71}”

Set cnn = New ADODB.Connectioncnn.Open “Provider=SQLOLEDB.1;Server=(local);” & _“Initial Catalog=Northwind;Integrated Security=SSPI”

Set cmd = New ADODB.CommandSet cmd.ActiveConnection = cnnSet cmd.CommandStream = stmCommandcmd.Dialect = DBGUID_MSSQLXML

There are a couple of features of this Command object that you haven’t seen in previousexamples. First, rather than setting the CommandText property, this code sets the Command-Stream property. Note that because CommandStream actually holds a Stream object, youmust use the Set keyword when setting it. The CommandText and CommandStream proper-ties are mutually exclusive. If you set the CommandText property, ADO will automaticallyset the CommandStream property to Nothing. If you set the CommandStream property,ADO will automatically set the CommandText property to an empty string, losing any textthat was previously in that property.

Using Command and Result Streams

2994c06.qxd 1/15/02 12:02 PM Page 149

150

Second, this code uses the Dialect property of the Command object. The Dialect propertyis a globally unique identifier (GUID) that tells the OLE DB provider how to interpret theCommandStream. The possible values for this property are supplied by the OLE DB provider.In this case, I’ve used a GUID that SQL Server specifies to indicate that the Command-Stream contains an XML template query.

NOTE If you don’t set the Dialect property, ADO will set it to the default value of {C8B521FB-5CF3-11CE-ADE5-00AA0044773D}, which tells the OLE DB provider to make its bestguess as to how to interpret the CommandStream.

In addition to the value used in this example, the SQL Server OLE DB provider supportstwo other special values for the Dialect property. You can use {C8B522D7-5CF3-11CE-ADE5-00AA0044773D} to indicate that the CommandStream contains a Transact-SQLquery, or {EC2A4293-E898-11D2-B1B7-00C04F680C56}, to indicate that the Command-Stream contains an XPath query.

The remaining code in this example uses the Command object to retrieve the results to asecond Stream object:

Set stmResponse = New ADODB.StreamstmResponse.Opencmd.Properties(“Output Stream”) = stmResponse

cmd.Execute , , adExecuteStream

txtXML.Text = stmResponse.ReadText()

This code first creates and opens the stmResponse Stream, so that it can be used to containdata. It then sets the provider-supplied Output Stream property of the Command object tothe name of this Stream. Calling the Execute method of the Command object with the adEx-ecuteStream constant tells ADO to pass the CommandStream and Dialect to the underlyingOLE DB provider, and to return the results to the text of the second Stream object. Finally,the code uses the ReadText method of the second Stream object to place the text on theform’s user interface.

Although this code demonstrates the basic technique of using Command and responseStreams, the real value of this method of retrieving data lies not in Visual Basic but in code tobe run on the Internet. Using Streams with a Command provides an ideal way to create aweb page with an XML representation of the results of a query.

Figure 6.5 shows an XML document constructed with this technique and displayed inInternet Explorer 5.0. Beginning with this version, Internet Explorer has the ability to dis-play XML documents directly, using a built-in XSLT stylesheet. This simplifies the task ofpreparing an XML document for display.


2994c06.qxd 1/15/02 12:02 PM Page 150

151

NOTE For more information on XSLT stylesheets, see Chapter 18, “Synchronizing DataSetswith XML.”

Listing 6.3 shows the code for the ASP page that produced Figure 6.5.

➲ Listing 6.3: CommandStream.asp

<% Response.ContentType = “text/xml” %><% Dim cnn

Dim cmdDim stmCommandDim strCommand

strCommand = “<root xmlns:sql=’urn:schemas-microsoft-com:xml-sql’>” & _“<sql:query> SELECT * FROM Customers ORDER BY Country “ & _“FOR XML AUTO </sql:query></root>”

Set stmCommand = Server.CreateObject(“ADODB.Stream”)stmCommand.Open

F I G U R E 6 . 5 :XML query results inInternet Explorer 5.0

Using Command and Result Streams

2994c06.qxd 1/15/02 12:02 PM Page 151

152

stmCommand.WriteText strCommand, adWriteCharstmCommand.Position = 0

Set cnn = Server.CreateObject(“ADODB.Connection”)cnn.CursorLocation = adUseClientcnn.Open “Provider=SQLOLEDB;Server=(local);” & _“Initial Catalog=Northwind;Integrated Security=SSPI”

Set cmd = Server.CreateObject(“ADODB.Command”)Set cmd.ActiveConnection = cnnSet cmd.CommandStream = stmCommandcmd.Dialect = “{5D531CB2-E6Ed-11D2-B252-00C04F681B71}”

Response.Write(“<?xml version=””1.0”” ?>”)cmd.Properties(“Output Stream”) = Response

cmd.Execute , , adExecuteStream%>

If you compare the ASP code in Listing 6.3 with the Visual Basic code I’ve alreadyreviewed in this section, you’ll see that the skeleton of the code is the same. But there aresome changes that I had to make to get the same code to function in a browser context:

� The ContentType property of the ASP Response object has to be set to tell InternetExplorer that the returned bytes should be treated as an XML file, even though theextension of the page is .asp. To be effective, this property must be set as the very firstoperation in the ASP file.

� The adovbs.inc file needs to be included to make the ADO constants (such as adExe-cuteStream) available to the ASP interpreter. This file is shipped as a part of ADO, butyou’ll need to copy it to your web server before you can use it.

� The ASP code uses Server.CreateObject to create objects, rather than the New keywordas used in Visual Basic.

� Instead of using a separate Stream object to hold the query results, the ASP code uses thebuilt-in ASP Response object. This is possible because the Response object implementsthe IStream interface.

� Before sending the query results to the Response object, the code uses Response.Write tosend a standard XML header.

Although I’ve chosen in this case to display the results in a web browser, in an actual busi-ness setting, you’d more likely use this XML file as the input to some other business process.With XML becoming the standard language of many business interchange applications, it’suseful to know how to generate XML results from SQL Server data, as shown here.


2994c06.qxd 1/15/02 12:02 PM Page 152

153

SummaryIn this chapter, you learned about the ADO Record and Stream objects. You saw how theseobjects extend the reach of ADO from traditional relational databases to other sorts of data,including hierarchical data, BLOB data, and XML Streams.

In the next chapter, I’ll explore another extension that ADO offers to the relational model:data shaping. With data shaping, you can create a hierarchical Recordset that combines datafrom several tables into one multilevel, browsable structure.

Summary

2994c06.qxd 1/15/02 12:02 PM Page 153

2994c06.qxd 1/15/02 12:02 PM Page 154

ado and ado.net programming - perfectxml.com · 134 o ne of the big advances of ado over previous...

Documents