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!
