Disconnecting ADO

1. Disconnecting ADO Or ADO Unplugged Rob Macdonald Salterton Hill Ltd rob@salterton.com

2. 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

3. 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’?

4. 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

5. What is a RecordSet Anyway ? Connection Tradition has it that there are three primary objects in ADO ... Command RecordSet

6. 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

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

8. Answer They all make changes to Field objects And why is this important...?

9. 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

10. 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

11. Unplugging ADO • Five good reasons … • reduce Server Load • modify RecordSet Behaviour • generate Custom Data • create a stateless Middle Tier • provide a consistent Data Presentation

12. 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

13. 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

14. 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

15. 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

16. 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

17. Three ways of Unplugging ADO • Disconnecting • Fabricating • Do It Yourself (Custom Provider)

18. 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

19. 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

20. 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

21. 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

22. 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

23. 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

24. 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

25. 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

26. 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++

27. 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 (!)

28. 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

29. 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...

30. 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

31. 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

32. 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

33. 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

34. RecordSet Manipulation • Marshalling • what happens when you start passing RecordSets around? • Sorting and Filtering • how good are RecordSets at these standard data operations?

35. 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?

36. 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!

37. 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

38. 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

39. 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

40. 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

41. 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

42. Horizons - How Universal is ADO? Standardised Programming Model XML Data Binding MegaFast Sorting Microsoft Commitment Providers SQL Easy Stateless Programming Efficient Marshalling Customisation

43. 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'?