disconnecting ado l.
Download
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


  • 128 Views
  • 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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

rob@salterton.com

objectives
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
Agenda
  • 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 ?

Connection

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

Command

RecordSet

what is a recordset anyway6
What is a RecordSet Anyway ?

… the question is, which three?

RecordSet

Fields

Field

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

question
Question

What do the following have in common?

rs("last_name") = "Macdonald"

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

rs!last_name = "Macdonald"

rs.MoveNext

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

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

answer
Answer

They all make changes to Field objects

And why is this important...?

one reason is performance
rs.MoveFirst

while not rs.EOF

string1 = rs("State")

string2 = rs("Zip")

rs.movenext

wend

Set fState = rs("state")

Set fZip = rs("zip")

rs.MoveFirst

while not rs.EOF

string1 = fState

string2 = fZip

rs.movenext

wend

One Reason is Performance

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

takes 5.6 ms

takes 3.3 ms

slide10

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")

rs.MoveNext

Print dict("name")

Prints

White

Green

'add a Field's value to a dictionary

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

Print dict("name")

rs.MoveNext

Print dict("name")

Prints

White

White

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

MTS

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)
disconnecting
Disconnecting
  • 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

rs.UpdateBatch

'handle update conflicts

End Function

  • ADO automatically generates an SQL statement for each row modified . . .
    • UPDATE, INSERT, DELETE

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

rs.Open

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))

rs.MoveFirst

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

id

Name

Size

ROR

Quartile

  • 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

rs.MoveNext

Wend

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

Prints...

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,

getColumnCount,

getRowCount,

insertRows,

deleteRows,

isAsync, find

etc

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

Else

If iColumn < lColCount Then

rs.Move iRow - 1, adBookmarkFirst

vValue = rs(iColumn - 1).Value

Else

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?
marshalling
Marshalling
  • 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

Client

Server

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

rs

rs

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

Client

Server

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

rs

rs

  • 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

Client

Server

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

rs

rs

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?

Standardised

Programming Model

XML

Data Binding

MegaFast

Sorting

Microsoft Commitment

Providers

SQL

Easy

Stateless

Programming

Efficient Marshalling

Customisation

summary
Summary
  • 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'?