本文介绍了ADO的基本使用方法，包括连接对象、记录集和命令对象的创建与应用。详细讲解了如何通过ADO进行数据库操作，如执行SQL语句、读取记录等。
If you have VB or Access, you'll find those environments are great productivity boosters - the strongly typed variables, syntax checking, online help, all help you work faster and learn faster. If you're using VBScript, you can still make it work, it may just take you a bit longer to get there.
If you have VB/Access, set a reference to 'Microsoft Active Data Objects 2.6'. If you don't see it, you may see older versions like 2.5 or even 2.1. I'd encourage you to apply the latest MDAC to get the bug fixes, but it's not critical to the examples that follow.
As the name implies, you'll be working with objects, which means using the set statement. Here is how to begin using the most basic object in ADO, the connection object, using VB:
| dim oConnection as adodb.connection set oConnection = new adodb.connection |
Or in VBScript:
| dim oConnection set oConnection=createobject("adodb.connection") |
You've just "instantiated" the object. Before you can actually do anything with it, you need to "open" it, like this:
| oconnection.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=eg;Application Name=TestApp" |
New developers often find getting the connection string correct the hardest part of using ADO. Just looking at it, you'll see the 'provider' is the SQLOLEDB provider - you'll definitely want to use this one when connecting to SQL. Initial Catalog is the same concept as default database. Setting the application name is a great best practice - ever run sp_who2 and see a blank program name? That's because the programmer didn't take the time to add this to the connection string. The order of the items in the connection string doesn't matter, the only time case matters is for the password if you're providing one or the server/database is case sensitive.
Still, aside from doing the copy and paste thing, it's a lot to remember when you're trying to right a quick 10 minute utility app. Try this trick to always get it right. Create a empty Notepad file on your desktop called connection.txt. Rename it to connection.udl. Double click it. You'll be in the UDL designer, possibly even better than sliced bread. Click on the provider tab to get started, then select 'Microsoft OLE DB Provider for SQL Server'.
Now click on the connection tab. Either type in your server name or pick it from the drop down list. Move down and select how you want to connect - NT if you're using a trusted connection (lan login) or the specific name/password if you're using a SQL login. If you're using the SQL login option, go ahead and check the box called 'Allow Saving Password'. Move on to step #3 and pick the database, and then click Test Connection - you should get a 'OK' if it worked.
Now skip the advanced tab (you can explore this later!) and move to the All tab. Highlight application name and click the Edit Value button. For this article I'm using the value of TestApp.
Click the OK button. Rename the connection.udl file to connection.txt, then double click to open. You should see something like this:
| [oledb] |
Look familiar? All you have to do is cut and paste that line into your code. No syntax errors, no trying to figure out if the password is wrong. Now let's actually DO something with this connection!
Connection objects can only execute "action" queries (insert, update, and delete). Here are some examples:
| oConnection.execute "Delete from Customers_History" oConnection.execute "Truncate table Customers_History" oConnection.execute "Insert Into SomeTable (CategoryID, CategoryName) values (1,'ADO Articles') oConnection.execute "Update SomeTable set CategoryName='ADO Articles & Tips' where CategoryName='ADO Articles' |
You can also do cross database actions just like you would in Query Analyzer:
| oConnection.execute "Truncate table Pubs.dbo.Publishers" |
You can also execute stored procedures, in this example passing the parameter '5' to the custOrderHist stored procedure:
| oConnection.execute "custOrderHist 5" |
When you're done, you always want to do a good clean up to avoid memory leaks and to release the connection.
| oConnection.close set oConnection = nothing |
Not too bad so far is it? Next week we'll talk about how to begin using the recordset object which will leverage what we covered in this article. As you test these, please do so in a test environment! As always, I'd love to hear any comments or questions you have about the article - just click the 'Your Opinion' tab below.
Last week I posted an article that covered the basics of the main object in ADO, the connection object. This week I'm going to continue by discussing how to use the second most used object in ADO, the recordset.
The first thing we need to talk about is when do you use a recordset versus a connection object? You'll use a recordset when you need to return records to the client - any time you need the results of a select statement. You use a connection object (or the command object as we'll cover next week) to execute 'action' queries. Where it may get a little confusing is that for a recordset to work, it has to have a connection! There are two different ways to do this, the first builds on what we did last week, instantiating a connection object first:
| dim cn as adodb.connection |
As you can see, our first step is to get the connection object. Next we instantiate the recordset object, then 'open' it by passing a sql select statement as the first parameter and an open connection object as the second parameter. This is the most common way, since normally you will reuse a connection object several times before closing it.
If you just need a recordset, you can use this abbreviated method:
| dim rs as adodb.recordset |
Did I mention there are different types of recordsets? Each recordset has a cursor type (static, dynamic, forward only, or keyset). Sounds like our friend the T-SQL cursor! If you understand the good and bad of T-SQL cursors, you're well on your way to deciding which cursor type to use in a recordset. We'll talk about this some more in a minute. While you're deciding on cursor type, you also have to think about the lock type - again, you get four options: optimistic, batch optimistic, pessimistic, and read only. And finally, we have to backtrack just a bit to consider one final, critical option - the cursor location. For each connection or recordset object, you have the option of setting a 'server' cursor or a 'client side' cursor. Here are some captures right from VB showing the options and how they work in the development environment:
I know that's a lot to put in one paragraph. We're not going to cover all the possible variations, I just want you to know they are there. I'm going to offer two configurations to get you started, then you can experiment and do some additional reading as your skill grows.
The first one is that you JUST need to read the data, not make any changes to it. Maybe to add a list of users to a listbox, or display a list of order details in a web page. You should use a cursor type of forward only, a lock type of read only, and a location of server. This will give you the fastest results with the least amount of locking. This is also known as the 'fire hose' cursor since the server just streams the data to you (the recordset) as quickly as it can, then it's done. This is such a common thing that it is actually the default. If you create a recordset and omit the cursor type and cursor location parameters, you get a forward only read only recordset.
| rs.CursorLocation = adUseServer |
In the second scenario, you need to update the data. For this, I recommend you use a cursor type of static, a lock type of optimistic, and a cursor location of client. Using these options, all the data matching your query will be pulled over to the client. No locks will be held on the rows you selected and there is no guarantee that someone else will not change them while you're working. This gives you the freedom to browse the recordset, sort it on the client, etc, and place NO load on the server.
| rs.CursorLocation = adUseClient |
Now that you know enough to decide how to configure your recordset, let's talk about how to use it. Here is a sample that shows iterating through a recordset and adding all the items to a listbox:
| Do Until rs.EOF |
Recordsets have two very important properties, BOF (beginning of file) and EOF (end of file). When you first open a recordset that has one or more records, the BOF property will be true, the EOF property will be false. Once you 'movenext' after looking at the last record, the EOF property will be true. It is absolutely critical that you always check for BOF and EOF. Failure to do so can result in the following error which your users will NOT appreciate:
Here is another example, this time I'm concatenating two values and adding both to the listbox:
| Do Until rs.EOF |
There is also an alternate syntax you can use for addressing field names which uses the 'bang' operator:
| Do Until rs.EOF |
And finally, you can address field names by using the ordinal position. This is probably the fastest way to address a field, but also the most dangerous and least understandable. Any change in your select statement will cause you a LOT of problems if you don't adjust your ordinals. I'm including this because you may see it in code samples, but please do not use this method!
| Do Until rs.EOF |
So far we've just been displaying values, now let's look at how to edit and add records. Here is how we would add a record:
| Do Until rs.EOF |
Use the AddNew method sets up a blank record. You then assign values to as many fields as you need. To save it, you can either do so explicitly by using the .Update statement, or by executing any .Movexxx operation. If you just want to add records, here is one trick you'll use a lot - in your select statement, add this as a where clause "where 1=0". No records will match, so very little traffic will be generated, but you still get the data structure that allows you to add records.
Finally when it's time to edit records, it works almost the same way:
| Do Until rs.EOF |
Notice that there no .Edit statement. It's not required in ADO, a change from how recordsets worked in DAO. Once you're done with your recordset, always do a good clean up:
| rs.Close |
Once you use this a couple times, it's comfortable and easy to use. Recordsets have a ton of features to make your life easier, we'll talk about some of those in the upcoming months. For now, give ADO a try! As always, I'd appreciate any questions or comments you have on this article. Just click the 'Your Opinion' tab below and start typing!
In two previous articles I've done a very basic introductions to the ADO Connection and ADO Recordset objects. This week I'd like to talk about the third main object, the Command object. Reading the previous articles isn't a requirement for making sense of this article, but probably worthwhile!
Let's jump right in. Here is a short code sample that shows how to execute an update (or insert or delete) query using a command object. For the duration of the article we'll assume you've already got a connection object open.
| Dim cmd As ADODB.Command |
As you can see, it works exactly the same way that using the connection execute method does. You can also use it to execute a stored procedure, again using the same syntax I illustrated earlier with the connection object:
| cmd.execute "usp_whatever" |
If the connection object can do it all, why use the command object. Probably the first reason is that you can give ADO "hints" to help it execute the statements more efficiently by setting the command type property. Taking a look at the options you see in the VB environment, you would select acCmdText for the first code example that uses dynamic SQL, or use acCmdStoredProc for the second example that executes a stored procedure.
Nice, but not really a compelling argument? Maybe..but so far we're just sending parameters, not leveraging the ability to declare parameters as output so that we can get a value returned to us. Let's take a look at something where the command object really excels, executing stored procedures with parameters. Using either the connection object or the command object, you can do it like this:
| Dim dteStart As Date |
We're just passing the parameters in the order that they are expected. In this case both parameters are dates, so I'm surrounding each with single quotes (Access developers remember to use the single quote and not the pound sign for date delimiters!).
Here is a full example showing how to use the parameters collection of a command object:
| Dim cn as ADODB.Connection |
There are a couple key points in this example. The first is the "params.refresh" call. Using refresh does the work for you of populating the parameters collection - for each parameter, you have to provide the name, data type, whether it's input or output. Refresh just queries the server for the parameter info and sets it up for you. This is good for you because it's easy, but generally considered a bad practice because it generates an extra "trip" to the server. We'll talk about alternatives in a minute. Then the next couple lines are where we start to see how having a parameters collection makes the code more readable:
| ' Specify input parameter values |
ParentID and Description could be parameters of a sub routine, or even properties in a class. The other thing you may have noticed is this line:
| ' Execute the command |
Passing the adExecuteNoRecords tells ADO that you're only expecting parameters back so there is no need to generate a recordset - saves some processing time and some bandwidth. Definitely a good idea to use it.
Now let's take another look at the "right" way to use the parameters collection. This involves building all the parameters up in code. Instead of params.refresh, we'll insert this code:
| ' Define stored procedure params and append to command. |
It's more work up front, but it's the best way to get the maximum performance. After all, isn't that why you wrote the stored procedure in the first place? Finally, as with all objects, be sure to close them when you're done by setting your command object = Nothing.
Coming up in the next week or two I'll have an article that combines all three of the main ADO objects into one simple application so you'll have a chance to really see them in action. Thanks for reading the article. Got a comment or question about it? Click the tab below!
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1697933/viewspace-899290/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/1697933/viewspace-899290/
扫一扫
361
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
