disconnecting ado l.
Skip this Video
Loading SlideShow in 5 Seconds..
Disconnecting ADO PowerPoint Presentation
Download Presentation
Disconnecting ADO

Loading in 2 Seconds...

play fullscreen
1 / 43

Disconnecting ADO - PowerPoint PPT Presentation

  • Uploaded on

Disconnecting ADO. Or ADO Unplugged Rob Macdonald Salterton Hill Ltd rob@salterton.com. Objectives. To present ADO as ‘more than just a new way of executing SQL’ To get the most out of the ADO Cursor Library To place ADO under the microscope or at least, some interesting bits. Agenda.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Disconnecting ADO' - devi

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
disconnecting ado

Disconnecting ADO

Or ADO Unplugged

Rob Macdonald

Salterton Hill Ltd


  • To present ADO as ‘more than just a new way of executing SQL’
  • To get the most out of the ADO Cursor Library
  • To place ADO under the microscope
    • or at least, some interesting bits
  • What is a RecordSet Anyway?
  • Three ways of Unplugging ADO
    • Disconnecting, Fabricating, Do It Yourself
  • Recordset Manipulation
    • Marshalling, Filtering and Sorting
  • Horizons
    • Should we see RecordSets as a ‘universal data structure’?
who am i
Who Am I?
  • Nine years running a Windows/client-server software house
  • Two years as an independent software specialist
  • Author of RDO/ODBC/ADO/COM/COM+ books and articles
  • Trainer
what is a recordset anyway
What is a RecordSet Anyway ?


Tradition has it that there are three primary objects in ADO ...



what is a recordset anyway6
What is a RecordSet Anyway ?

… the question is, which three?




Note: ADO 2.5 introduces the Record object, which also has a Fields collection


What do the following have in common?

rs("last_name") = "Macdonald"

rs("last_name").Value = "Macdonald"

rs!last_name = "Macdonald"


rs.Fields("last_name").Value = "Macdonald"

rs.Fields("last_name").Properties("OPTIMIZE") = True


They all make changes to Field objects

And why is this important...?

one reason is performance

while not rs.EOF

string1 = rs("State")

string2 = rs("Zip")



Set fState = rs("state")

Set fZip = rs("zip")


while not rs.EOF

string1 = fState

string2 = fZip



One Reason is Performance

rs.open "select * from authors", "DSN=Pubs;"

takes 5.6 ms

takes 3.3 ms


Also, consider ...

Dim rs As New ADODB.Recordset

Dim dict As New Dictionary

rs.open "select * from authors", "DSN=Pubs;"

'add a Field object to a dictionary

dict.Add "name", rs("au_lname")

Print dict("name")


Print dict("name")




'add a Field's value to a dictionary

dict.Add "name", rs("au_lname").Value

Print dict("name")


Print dict("name")




unplugging ado
Unplugging ADO
  • Five good reasons …
    • reduce Server Load
    • modify RecordSet Behaviour
    • generate Custom Data
    • create a stateless Middle Tier
    • provide a consistent Data Presentation
unplugging ado server load
Unplugging ADO - Server Load
  • Holding Connections and RecordSets open is a drain on server resources
    • can result in extensive database locking that reduces concurrency and performance further
  • 'Fetch and Forget' is efficient but not always appropriate
  • Disconnected RecordSets allow the server to forget
    • but allow the application to remember
unplugging ado behaviour
Unplugging ADO - Behaviour
  • With disconnected RecordSets, you can allow client applications to operate directly on the RecordSet
    • the data source will not be affected
  • You can then validate the changes. . .
    • check that the updates meet your business rules
  • . . . before re-connecting to the data source
unplugging ado custom data
Unplugging ADO - Custom Data
  • ADO provides a great model for working with all kinds of data, not just SQL results
    • any type of data that can be thought of as 'records'
  • By creating custom RecordSets you can allow clients to use these same techniques with your own data
    • mix and match with SQL data
unplugging ado middle tier
Unplugging ADO - Middle Tier
  • To create highly scalable applications, server and middle tier resources should be freed as quickly as possible

1 - HTTP/DCOM Request

Web Server


App Code

Client / Browser

2 -SQL

3 - HTTP/DCOM - ADO Data

4 - HTTP/DCOM - ADO Update

5 -SQL

Client holds Recordset between 3 and 4

Server holds Recordset between 2 and 3,

and between 4 and 5

unplugging ado consistency
Unplugging ADO - Consistency
  • Making data look like ADO has many benefits:
    • users of data (ie client programmers) use known techniques
    • data binding can be used
    • ADO offers powerful data manipulation
three ways of unplugging ado
Three ways of Unplugging ADO
  • Disconnecting
  • Fabricating
  • Do It Yourself (Custom Provider)
  • Apply this technique when:
    • RecordSet manipulation is required
      • (i.e. anything other than MoveNext)
    • Returning whole RecordSets to clients
    • RecordSet state is required when performing updates
  • Primarily used with SQL RecordSets
    • batch update functionality generates SQL
read only disconnection
Read-Only Disconnection

Public Function getAuthors() As ADODB.Recordset

Dim rs As New ADODB.Recordset

rs.CursorLocation = adUseClient

rs.CursorType = adOpenStatic

rs.Open "select au_fname, au_lname, state, “ & _

zip from authors", "DSN=pubs;

Set rs.ActiveConnection = Nothing

Set getAuthors = rs

End Function

Must set the correct cursor properties


Disconnecting takes place here

updateable disconnection 1
Updateable Disconnection (1)
  • Updates are held in the RecordSet
    • Compare fd.Value and fd.OriginalValue
  • When all updates have been made, call

Public Function setRS(rs as ADODB.RecordSet) As Boolean

'validate data


'handle update conflicts

End Function

  • ADO automatically generates an SQL statement for each row modified . . .

We'll discuss this more later

updateable disconnection 2
Updateable Disconnection (2)
  • Updating requires setting the correct lock type before opening the RecordSet
    • rs.LockType = adBatchOptimistic
  • Query must include the primary key:
    • all the required data must be in the RecordSet
    • if the auto-update updates multiple rows:
      • an error will be raised
      • the update will still take place!
    • SQLServer 7 is a 'special case'
      • uses 'built in' stored procedures instead of SQL
updating joins 1
Updating Joins (1)
  • Prior to ADO 2.1, ADO attempted to update each table in the query
    • required each table's primary key
    • rarely desired behaviour

AddNew will attempt to update both tables!

Trading Data

Currency LookUp

updating joins 2
Updating Joins (2)
  • ADO 2.1 added some control over this process
    • identify a Unique Table

rs.Properties("UNIQUE CATALOG") = "pubs"

rs.Properties("UNIQUE TABLE") = "titles"

    • Update/UpdateBatch/AddNew/Delete/Resync affect only the unique table
fabricating recordsets
Fabricating RecordSets
  • Simple technique for generating RecordSet structures
  • You define a RecordSet structure
    • wide range of data types supported
    • including Hierarchical RecordSets
    • can also use DataFactory.CreateRecordSet
  • And add data using AddNew
    • full range of RecordSet functionality supported
for example
For Example

Public Function getMeetingTimes(dtGMT As Date) As ADODB.Recordset

Dim rs As New ADODB.Recordset

Dim vCols As Variant

rs.Fields.Append "Location", adVarChar, 15

rs.Fields.Append "Time", adDate


vCols = Array("Location", "Time")

rs.AddNew vCols, Array("London", dtGMT)

rs.AddNew vCols, Array("Washington", DateAdd("h", -5, dtGMT))

rs.AddNew vCols, Array("Tokyo", DateAdd("h", 9, dtGMT))

rs.AddNew vCols, Array("Samoa", DateAdd("h", -11, dtGMT))


Set getMeetingTimes = rs

End Function

Fields must be appended BEFORE the RecordSet is opened

It's good manners (and inexpensive) to reposition the cursor

creating an ole db provider
Creating an OLE DB Provider
  • Fabricated RecordSets are not a replacement for OLE DB Providers
    • don't support Connection.Open
    • can't be directly bound to controls
    • not known to the registry as providers
    • can't be fetched asynchronously
  • You can create 'Simple' Providers in VB
    • or full blown providers using C++
5 steps to being a provider writer
5 Steps to being a provider writer...
  • Create a VB6 ActiveX DLL
  • Create a 'Connection' Class
    • Set the DataSourceBehaviour property to 1
    • Implement the GetDataMember event
  • Create one or more 'RecordSet' classes
    • Implement the OLEDBSimpleProvider interface
  • Register the DLL with OLE DB
  • Use like any other provider (!)
for example28
For example ...

You have a Funds table in your database






  • You want to create a RecordSet that contains:
    • those funds meeting certain criteria
    • a quartile column ranking the selected columns
simple provider client
Simple Provider - Client

Dim rs As New Recordset

With rs

.Open "250", "Provider=VB Custom Data;"

While Not .EOF

Print !FundCol2, !FundCol3, !FundCol4, !FundCol5



End With

The command string

The Provider name

Fund Size ROR Quartile

IncomePlus 800 6.5 2

MegaBucks 550 7.1 4

PotLuck 300 9.1 2

OrbitRage 600 4.5 1

SuperGrowth 1200 7.8 3

CapitalGold 1500 6.4 4


simple provider connection
Simple Provider - 'Connection'

Must assign an object that 'Implements' OLEDBSimpleProvider

Private Sub Class_GetDataMember( _

DataMember As String, Data As Object)

Dim oCustom As CustomRecords

Set oCustom = New CustomRecords

oCustom.Prepare CLng(DataMember)

Set Data = oCustom

End Sub

The command string

Passes DataMember to the oCustom object

simple provider recordset 1
Simple Provider - RecordSet (1)

Implements OLEDBSimpleProvider

Private lColCount As Long

Private rs As Recordset

Public Sub Prepare(lSize As Long)

'create disconnected RecordSet

Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient

rs.CursorType = adOpenStatic

rs.Open "select * from Funds where Size > " & lSize, "DSN=Funds"

lColCount = rs.Fields.Count + 1

Set rs.ActiveConnection = Nothing

End Sub

Standard interface

getVariant, setVariant,





isAsync, find


Add 'extra' column

simple provider recordset 2
Simple Provider - RecordSet (2)

Private Function OLEDBSimpleProvider_getVariant( _

ByVal iRow As Long, _

ByVal iColumn As Long, _

ByVal format As MSDAOSP.OSPFORMAT) As Variant

Dim vValue As Variant

If iRow = 0 Then

vValue = "FundCol" & iColumn


If iColumn < lColCount Then

rs.Move iRow - 1, adBookmarkFirst

vValue = rs(iColumn - 1).Value


vValue = CInt(Rnd * 3) + 1

End If

End If

OLEDBSimpleProvider_getVariant = vValue

End Function

Row = 0 means - provide a column name

If the 'extra' column is selected, supply an 'internally generated' quartile number instead of a RecordSet value

simple provider summary
Simple Provider - Summary
  • The client uses ADO in a standard way
    • '= rs!col1' is translated into getVariant
    • 'rs!col1 =' is translated into setVariant
  • The Custom provider can do anything it likes …
    • so long as it implements OLEDBSimpleProvider
  • The DLL must be registered with OLE DB
    • download a complete example from www.salterton.com/hill
recordset manipulation
RecordSet Manipulation
  • Marshalling
    • what happens when you start passing RecordSets around?
  • Sorting and Filtering
    • how good are RecordSets at these standard data operations?
  • You have an object that provides disconnected RecordSets
    • Public Function getRS() As Recordset
    • Public Function setRS( rs As Recordset) As Boolean
  • And a client that uses this server

Dim obj As New ADOServer.Data

Set rs = obj.getRS()

'make some changes to records here

obj.setRS rs

focus on this line!

What happens when the RecordSet is passed between client and server?

well it depends
Well, It Depends ...
  • When both objects are in the same process, a pointer is passed
  • but you can't pass pointers across process or machine boundaries
    • http or DCOM
  • Instead, marshalling is used to move the RecordSet between the processes
    • your code looks the same
    • but performance is very different!
it s all smoke and mirrors
It's all Smoke and Mirrors
  • Assume rs has 5000 rows
    • Set rs = obj.getRS()takes 2.2 s
      • it involves passing rs once between the two processes
  • obj.setRS rs will take 4.4 s
      • rs gets passed in both directions !

obj.setRS rs



As the call is made, rs is copied to the server process



At the end of the call, rs is copied back to the client process

using byval
Using ByVal

Typically, the client doesn't want to see any changes that the server makes.

So, change the server function's definition to

Public Function setRS (ByVal rs As Recordset) As Boolean



As the call is made, rs is copied to the server process



  • obj.setRS rswill now take 2.2 s
      • because it isn't passed back to the client
using marshalloptions
Using MarshallOptions

Often, the server only needs to see the records the client has changed.

So, in the client code, set

rs.MarshalOptions = adMarshalModifiedOnly



As the call is made, only the changed records in rs are copied to the server process



obj.setRS rs will now take 0.2 s

sorting and filtering
Sorting and Filtering
  • ADO provides sort, filter and find operations through the client cursor library
    • very easy and convenient to use
  • Client-side index can be created to improve performance
    • these were NOT reliable in ADO 2.0

rs.Fields("last_name").Properties("OPTIMIZE") = True

data structures compared
Data Structures Compared
  • Figures below are based on a 5000 row, 4 column data structure.
    • Times are normalised on Variant Array performance
    • Array sorting is 400 times slower than iteration

1 - timings include iterating through the results

2 - the faster time is achieved if searching by collection key is possible

horizons how universal is ado
Horizons - How Universal is ADO?


Programming Model


Data Binding



Microsoft Commitment






Efficient Marshalling


  • What is a RecordSet Anyway?
  • Three ways of Unplugging ADO
    • Disconnecting, Fabricating, Do It Yourself
  • Recordset Manipulation
    • marshalling, filtering and sorting
  • Horizons
    • should we see RecordSets as a 'universal data structure'?