VB Toolkit: ADO or the Data Environment?

Visual Basic (VB) 6.0 includes the new Data Environment, based on ADO, which I have explained in previous issues. Data Environment components such as connections and commands hide ADO and its details from the developer. Also, the Data Environment's graphical nature and tools make it easy to create data applications without having to remember a lot of technical details, such as how to set parameters and cursor options in the underlying ADO code. So, with these new features, should you ever consider using ADO directly?

Absolutely! First, ADO can be faster than the Data Environment. The Data Environment's overhead contributes to this speed difference, which is similar to the difference between applications written in VB and those written in C++. Many developers say that C++ applications are faster than those written in VB. This claim is debatable.

It's possible to use ADO to build an application that performs better than the same application written with the Data Environment. The converse is also true. You can use the Data Environment to build an application that outperforms the same application with ADO. The performance difference depends on how developers code applications and the features they use. For instance, one developer may never use a wildcard SELECT statement (SELECT * FROM ...), but another developer might use wildcard SELECT statements routinely. The wildcard SELECT statement might be many times slower than explicitly listing the columns you want to retrieve because of the additional overhead incurred in returning the additional data. This result depends on the number of columns and the amount of data in the accessed tables.

Another reason to use ADO is the difference between its feature set and that of the Data Environment. The Data Environment uses ADO but doesn't take full advantage of all the ADO features. For instance, ADO gives you direct access to the Connection, Command, and Recordset objects. When you use the Data Environment, these objects are hidden and you may interact only with the Data Environment and Data Command objects. When you create an application with ADO, you can use only the particular ADO features that you need for the application. However, if you use the Data Environment, you must use the features it provides. For instance, to use a Data Command, you must create a Data Connection and bind the Data Command to it. In contrast, if you use ADO directly, you can use the ADO Command object to create the connection on the fly. This freedom to choose gives you more control of the process, although it forces you to learn ADO and its idiosyncrasies.

I can think of a few good reasons to use the Data Environment. First, the Data Environment's many easy-to-use graphical features can significantly reduce development time--a big benefit to most organizations. The Data Environment provides easy-to-use tools such as Query Designer and the Data Environment Designer to ease the development process. The Data Environment also supports graphical command objects that make it easy to build reusable queries.

Also, the Data Environment is handy for creating SQL queries. You can use Query Designer to create and test the SQL, then use ADO to copy and paste the query into the application. Also, you can use Data Environment Data Commands to create complex Shape statements, then use them with ADO and copy and paste the statements into the application. However, the Data Environment can't handle some complex queries that ADO can. I've seen examples where the same complex nested query that works in ADO doesn't work in the Data Environment.

Let's look at a simple ADO example, which updates the Pubs database. Before you start using ADO in your code, you must create a project reference to the ADO type library. I used the Microsoft ActiveX Data Objects 2.1 Library for the examples.

Listing 1 is the DataStuff class, which does all the database work. This class uses only the ADO Connection object to interact with the database. The Connect method of the class opens the connection, and the Disconnect method closes the connection. The code uses the OLE DB Provider for ODBC and a system data source named Pubs.

Listing 2 shows Form 1. The form's code opens the database by calling the Connect method of the class in the Form_Load event. The cmdUpdateTitle button builds and executes an SQL statement to update the Title column in the Titles table. Users enter the TitleId and Title in the form. When a user clicks this button, the code in Listing 3 runs in the Click event. This code creates an SQL statement and stores it in the sSQL variable. Then the ExecuteSQL method of the DataStuff class executes this statement. In this code, a standard SQL UPDATE statement is created. When ExecuteSQL executes, the Execute method of the Connection object runs:

conPubs.Execute sSQL

This example is as simple as ADO gets without a Command or Recordset object. Just use the Execute method of the Connection object to execute the SQL statement. Using ADO this way lets you create code as robust or slim as you desire.

Although this application uses ADO, you don't need to create this SQL by hand. I used Visual Studio to create a database project. Next, I added a connection to the Pubs database. After these steps were complete, I had access to the Query Designer, Stored Procedure Editor, and more. These features let me construct and test any SQL statement quickly, then copy it to the clipboard and paste it into my VB code to set the sSQL variable.

Also, you can use stored procedures to create the database project. I took the following code and used Visual Studio's Stored Procedure Editor to turn the statement into a stored procedure as in Listing 4. I used the Visual Studio Script Editor to test the stored procedure with this code.

UpdateTitle 'Cooking with Computers:Better Balance Sheets', 'Bu1111'

Next, I added the Sub cmdupdatetitleSp command button and the code in Listing 5 to the Click event. This code is similar to the cmdupdatetitle code. The main difference is that this code uses only the stored procedure and does not need to create the SQL statement, so you need less code. Also, this code shows how easy it is to leverage stored procedures by directly using the Connection object's Execute method. You can use SQL this way with the Data Environment, but you don't have as much control of the underlying ADO code.

I am not suggesting that you use either ADO or the Data Environment exclusively. Each has its place, depending on the needs of your application.