In a push to standardize data access on ActiveX Data Object (ADO) and make ADO easier for developers to use, Microsoft introduced new database technologies in Visual Basic (VB) 6.0. One new VB 6.0 feature, the Data Environment, is an ActiveX designer that gives you a graphical front end for building database solutions. The Data Environment is included in Visual Studio Professional and Enterprise editions.
To use the Data Environment, you interact with the database as you would if you were using any graphical query-builder tool: You create a connection and a query, then use the query. VB includes Query Designer and other Visual Database Tools features, which make using the Data Environment a snap. Microsoft put the development tools you need into VB so you can use it without constantly switching between several tools. Because these tools are common throughout Visual Studio, you can easily use multiple products from Visual Studio to build applications.
To demonstrate how to use the Data Environment, let's create a new database application. This sample will be a two-tier application that uses many Data Environment features.
To build a new VB application, you first create a new project. You can start VB and build the project in one step or, if VB is already running, select New from the File menu. VB will display the New Project dialog box. Next, select Data Project from the list of project types, and click OK.
When you select Data Project, VB creates a new project and adds one form (frmDataEnv), one Data Environment Designer (DataEnvironment1), and one Data Report Designer (DataReport1); creates a reference to several component object model (COM) objects; and opens the project in the VB IDE.
Also, when you select Data Project, VB creates the architecture for your project. You need to add your code, but the basic plumbing is complete. You can remove items you don't need. For example, if your application won't generate a report, you can delete the Data Report Designer.
Next, you need to connect to a database. First, select Project Explorer, then double-click DataEnvironment1 to open the Data Environment Designer with the settings DataEnvironment1. Then, rename the Data Environment: Click DataEnvironment1, then press F4 to display DataEnvironment1's properties. Change the name to envNorthWind (NorthWind is the sample database that Microsoft ships with many development tools, including SQL Server 7.0).
Click the connection under envNorthWind. Then, click the Properties icon on the Data Environment Designer toolbar to open the Data Link Properties dialog box, which contains connection information. Select the database provider you're using. In this sample, I used the Microsoft OLE DB Provider for SQL Server. You can see this provider in the list shown in Screen 1. Now, click the Next button at the bottom of the dialog box. This dialog box displays the Connection property page for the provider you selected. Each provider will display a different property page according to its requirements. To complete the connection, enter the database server name and security information. Then select the database you want to use. For this sample, I used the NorthWind SQL Server 7.0 sample database.
After you complete these steps, click the Test Connection button. If your connection information is correct, you will see a confirmation message. If your connection information isn't correct, you'll see an error message such as the one shown in Screen 2. After you've successfully tested the connection, click OK to close the property page and save your connection information. Now click Connection1, press F4 to display the Properties window, then change the connection name to cnNorthWind.
Using Data Commands
Now that you've connected the application to the database, you can use VB's new features to add database functionality. First, let's create a data command, which is a named command object that executes a database action.
Data commands reside under the connection level and are linked to particular connections. You can use the same data command many times in one application. For example, if you want to retrieve a customer list from the NorthWind database, you can create a data command named Customers. You can use the data command as a central place to update SQL information for your application. By changing the data command, you affect everything in the application that uses the data command. This function improves application maintenance because each data command becomes a data object in your application. You can centrally change the data command, and the applications will automatically use the new changes.
Creating a data command is straightforward. First, click the connection name in the Data Environment Designer. Then, click the Add Command button on the Data Environment Designer toolbar to create Command1 under the data connection. Next, click Command1, then click the Properties icon on the Data Environment Designer toolbar. Change the command name to Customers.
You can associate a command with a stored procedure, view, SQL statement, or other database object. For this example, click the SQL Statement button, then click SQL Builder. This will open Query Designer, which you use to create the SQL statement for the data command.
The Data View window also opens when the Query Designer opens. You can move Data View on the screen, but keep it handy for bringing tables into Query Designer. Now, expand the cnNorthWind data connection in Data View, then expand the Tables folder. Drag the Customers table from the Tables folder, and drop it on the top pane of Query Designer. The default query is a Select query. To add columns to a query, check the box to the left of the column name. Query Designer will automatically add the column to the other panes. For this example, add the CustomerID, CompanyName, ContactName, and ContactTitle columns.
Your query will resemble the one shown in Screen 3. Next, right-click any of the Query Designer panes, except the SQL pane, and click Run. You'll get a handy test of the result set the query will return. To ensure you don't unintentionally execute a query that will return a large number of rows, right-click one of the Query Designer panes and select Properties. Next, enter in the Top box the number of records you want returned. (Some databases don't support the Top option.)
Now, close Query Designer, and look at the Data Environment Designer window. If you change any settings, you'll be prompted to save them. You can see the Customers data command attached to the cnNorthWind connection. You can change the SQL statement at any time, which will affect the data command wherever it's used. Also, you can open Query Designer and change the SQL commands by right-clicking the data command and selecting the Design command.
Let's use the data command on a standard form to display the customer list. This sample requires a little drag-and-drop magic, plus a few lines of code. Open frmDataEnv and position it so you can see the Data Environment Designer and the form. Click the data command, then drag it to the form. Be sure to position the fields in the proper location.You can reposition the fields as long as they remain selected. This drag-and-drop operation places controls on your form that are linked to the data connection and the data command you dragged on the form. You can find these settings in the DataSource and DataMember properties for each control.
You now have a live application. You can run the project to display data from the database. You cannot navigate from record to record yet, but the application is live. Note that the controls you dragged on the form are all text boxes. The control names use standard naming conventions including the .txt extension and the column's name. This naming scheme is a welcome departure from previous wizards that used the Textbox1, etc., naming convention.
If you go to the Data Environment Designer, you can expand the data command to see the fields in a data command's recordset. You can open a field's properties and change the data command's caption and ActiveX control. You use these properties when you drag a data command to a form, as in this example.
|Table 1: Command Buttons and Captions|
Now, let's add controls to provide the form's navigation features. Drag a command button from the Toolbox to the form. Change the command button's size to a small rectangle. Select the command button and press Ctrl+C to copy it to the clipboard. Then, press Ctrl+V to paste three copies of the control onto the form and place them as shown in Screen 4. Set the name of the four command buttons and captions to the values in Table 1.
Now, you can add the code to navigate through the recordset. When you create a data command, VB will automatically create a recordset. The recordset name will be rs plus the name of the data command. In this example, the recordset name is rsCustomers.
You can access the recordset through the Data Environment object. In this example, you use this syntax in the cmdFirst_Click event to move to the first record:
Private Sub cmdFirst_Click() envNorthWind.rsCustomers.MoveFirst End Sub
To complete the navigation buttons, add the following code in the event subroutine for the respective button:
Private Sub cmdLast_Click() envNorthWind.rsCustomers.MoveLast End Sub
Private Sub cmdNext_Click() envNorthWind.rsCustomers.MoveNext End Sub
Private Sub cmdPrevious_Click() envNorthWind.rsCustomers.Move Previous End Sub
Use error handling in your applications to prevent errors such as reaching the end or beginning of the recordset. You can use the EOF or BOF properties of the recordset to detect this error. For example, to check for the end of recordset when the user clicks the button, you can change the cmdNext_Click event to the following code:
If Not envNorthWind.rsCustomers.EOF Then envNorthWind.rsCustomers.MoveNext End If
For a better approach, you can detect the end or beginning of the recordset and disable any navigation controls so users can't click them. This method provides better feedback than letting the users click the button, then letting them find out that they are at the end of the recordset.
The data command's default setting is read-only, lock type, which prevents updates to the recordset. You can use the Advanced page in the data command's Properties dialog box to change the lock type, cursor type, and other parameters. If you change the lock type to Optimistic, you can update a record by navigating to another record after you've changed anything in the recordset. You must have update access to the database to make any updates.
Now that you have an updateable recordset, you can add an Update button to the form. Add the button and place this code in Click event:
Private Sub cmdUpdate_Click() envNorthWind.rsCustomers.Update End Sub
The Update method updates the current record. The Data Environment recordset exposes the properties of the underlying ADO recordset. You can use the recordset's methods and properties to control the recordset.
Fast and Easy
VB's Data Environment lets you quickly develop applications that you can easily maintain. Most wizards and other tools help you to develop applications quickly, but they don't help you maintain the application. You can use the data command to centralize application changes, which simplifies mantenance tasks. Also, creating the data command is a straightforward task.
Next month, I'll show you how to use the Data Environment and ActiveX to build parameterized queries and more robust database applications.