Thank this author by sharing:
By Andy Warren,
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
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.
As always, I enjoy hearing from readers - questions, comments, complaints -
just post in the attached discussion forum!
Help filtering user connection with sp_who
Different recordsets counts returned
Last week's tip created quite a discussion on different techniques for returning a subset of a recor...
Stateless programming can be tricky... In case you ever need to return a specified subset of a query...
error adding record using Addnew/Update via recordset
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.