Introduction to ADO Part 4 - Combining It All

,

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!

Rate

5 (1)

Share

Share

Rate

5 (1)