Introduction to ADO Part 4 - Combining It All

By Andy Warren, 2001/12/07

Total article views: 9823 | Views in the last 30 days: 12

Rate this |

Join the discussion |

Briefcase

In three previous articles (Introduction to the ADO Connection Object, Introduction to ADO Part 2 - Recordsets, Introduction to ADO - The Command Object) I've been trying to give you a simple easy to follow introduction to ADO. Why would a DBA need to know ADO? Well, it may come as a shock, but not all data access code is done in stored procedures! So reason number one is that if it's going to be running against one of your servers, it helps if you can follow the code when you see a performance issue or bottleneck. The second reason is that once you get comfortable with it, you may find it to be the tool of choice for building those small utilities we all have, generating reports, etc.

For this week I promised some code that would combine the connection, command, and recordset objects into one piece of code and you'll find that below. I tried to show you different ways of solving some fairly common problems. Keep in mind that ADO has a lot of options - there are lots of ways to solve problems and not all of them are shown here!

As I started writing the code sample, I kept thinking about things I didn't cover in the first three articles. Tons of things! But in keeping with my original goal, most of those things we can cover later. I did think of two that I thought were worth discussing at this point; client side filtering and disconnected recordsets, so I included some code to show them at work.

Client side filtering just means that once you do your select of x records into your recordset, you can apply additional an additional filter to the recordset without having to requery the database. The only catch is that is has to be a simple filter in the form fieldname=fieldvalue. You can apply multiple filters to come close to the power of using 'and' in your filter. If you need an 'or', a new recordset is probably your best bet. One point to remember about filtering is to always check to see that you have records in the recordset after filtering. If no records matched, the recordset would be empty. Why use this? No round trip! This lets the developer create a rich user experience without the overhead of another call to the server. That also means the server will scale higher.

Disconnected recordsets are used exactly like a regular recordset. The only difference is, once the recordset is populated, the activeconnection property is set to nothing. Ideally at that point the client application has NO connection to the database. The user can add, edit, delete records from the recordset...without affecting the server. Once all changes are complete, instead of calling the update method, the updatebatch method is called, sending all the changes over in one big...batch! This is definitely optimistic locking, so there is the possibility that conflicts may happen (the record was changed by another user sometime between the select and the updatebatch). ADO gives you the tools to handle those fairly easily, but we'll save that discussion for another day. For now, just take a look at how it works and think about doing more work on the client with fewer trips to the server combine to make both a better application and a more scaleable one.

Finally, here is the code.

Sub Demo(Optional ServerName As String = ".")

'set a reference to ADO 2.6 in VB

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim SQL As String
Dim params As ADODB.Parameters
Dim param As ADODB.Parameter

'we'll use this later
Const OrderID As Long = 10259

'first thing is to open a connection. For this demo, we're using a 'dsn-less' 'connection 'string. If you look at the servername parameter being passed in to 'this sub, you'll see it defaults to '.' - an easy way to say connect to the local 'server - and I'm using that variable in the connection string to make the code 'more dynamic. I'm also using the VB App.Title property to make the application 'name dynamic. For this demo we'll always be using Northwind so I've hard coded 'that and set it to use a trusted connection.
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionTimeout = 10
cn.CommandTimeout = 10
cn.Open "Provider = SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=" & ServerName & ";Application Name=" & App.Title

'lets start by creating a stored proc that we'll reuse in a moment. This one just 'returns counts of the titles in the customers table. First thing is to drop it if 'it already exists. Assigning your TSQL to a string variable makes it easier to 'debug - you can just debug.print it to the immediate window and copy/paste into 'query analyzer to test.
SQL = "if exists(select * from sysobjects where name='usp_gettitlecounts') drop proc usp_getTitlecounts"
cn.Execute SQL, adExecuteNoRecords

'now to create, Im building the TSQL string to create the proc - in this example 'Im adding cr/lf pairs to make the code more readable when you view it later using 'QA. Note that for both this execute and the previous one Im using the const 'adExecuteNoRecords' to tell SQL that I dont need anything returned - saves 'network traffic.
SQL = "create proc usp_GetTitleCounts as " & vbCrLf
SQL = SQL & "--test proc for use in ADO demo app" & vbCrLf
SQL = SQL & "set nocount on" & vbCrLf
SQL = SQL & "select contacttitle, count(*) as titlecount from customers group by contacttitle order by contacttitle"
cn.Execute SQL, , adExecuteNoRecords

'now lets execute the proc and return a recordset. Because the results will be 'read only anyway (its a group by), we want to use the fastest recordset - which 'is what we get by default. Since the proc has no parameters, it's hardly worth 'using a command object, we'll just continue to use our connection object.
SQL = "usp_getTitleCounts"
Set rs = cn.Execute(SQL)

'now lets loop through and see what it returned
Do Until rs.EOF
Debug.Print rs.Fields("ContactTitle"), rs.Fields("TitleCount")
rs.MoveNext
Loop

'we're done, so clean up the recordset
rs.Close
Set rs = Nothing

'now lets switch back to using stored procs, this time using one that already 'exists and uses a parameter AND a return value - now we need a command object. 'We'll reuse 'our existing connection object (set activeconnection).
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "CustOrdersDetail"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

'this proc has one parameter, plus the return value. Whether you need the return value
'depends on how the proc was coded.
params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
params.Append cmd.CreateParameter("@OrderID", adInteger, adParamInput, 0)

'we're going to get the details for order # 10259 - so we assign that to the input
'parameter, then execute the proc - which returns a recordset, in this case
'we wind up with a 'read only' recordset because of the way we are returning it,
'not because the recordset couldnt be updated (its only a select).
params("@OrderID") = OrderID
Set rs = cmd.Execute

'since you already know how to iterate, we'll just close this one and move on!
rs.Close
Set rs = Nothing
Set cmd = Nothing

'lets take a look at some richer functionality of the recordset. We're going to do 'basically what the stored proc did - return a bunch of detail records, but then 'we will disconnect the recordset, modify a couple, then reconnect and do the 'update. In this demo Im not killing the main connection (cn), but in practice you 'would. Set rs = New ADODB.Recordset
rs.Open "Select ProductID, UnitPrice from [Order Details] where orderid=" & OrderID, cn, adOpenStatic, adLockBatchOptimistic

'disconnect
Set rs.ActiveConnection = Nothing

'now I happen to know that this particular query will return two records, lets 'find the one we want to update by leveraging the client side filtering ability of 'the recordset. Then 'we'll just add one to the unit price. Note the use of the 'check for BOF - if the record is missing (or your filter is bad) and you try to 'update - you'll get an error!
rs.Filter = "ProductID=37"
If Not rs.BOF And Not rs.EOF Then
rs.Fields("UnitPrice") = rs.Fields("UnitPrice") + 1
rs.Update
End If

'now lets reconnect and make the update
Set rs.ActiveConnection = cn
rs.UpdateBatch

'clean up
rs.Close
Set rs = Nothing

'and we're done with our connection, so close it too
cn.Close
Set cn = Nothing

End Sub

As always, I enjoy hearing from readers - questions, comments, complaints - just post in the attached discussion forum!