Working with Database Connections

There are a number of steps you must take to work with databases from within Visual InterDev 6.0. The first step is to create a data connection and link it to a data source. If you do not have a data source definition before you create the data connection, you can add one at the time. The steps in this process are very similar to those in Visual InterDev 1.0.

Define a data connection to an existing data source by following these steps:

1. Right-click the Project name or global.asa in Project Explorer, and select Add Data Connection from the context menu. You will see the Select Data Source dialog box shown in Figure 5-2.
2. Click the File Data Source tab or the Machine Data Source tab to select the type of data source you want to select. Figure 5-2 shows the Machine Data Source tab.
3. Select the data source name, and click OK. A dialog box showing parameters for the data connection is displayed based upon the driver for the data source you select. (Setting these parameters is discussed in the following section.)
4. Enter the data connection parameters.
5. Click OK to add the data connection.

Figure 5-2. The Select Data Source dialog box shows the existing data sources on a system.

The data connection is displayed under the Data Environment icon in Project Explorer. You can also browse and edit the data from the database in the Data View window.

Setting Data Connection Parameters

After you select a data source for your data connection, you will see the data connection Properties dialog box shown in Figure 5-3. The dialog box is displayed as "Connection1 Properties" because Connection1 is the default name for a new data connection when it is the first in a project. Figure 5-3 also shows the new name I have assigned the data connection—cnPubs. This will become the name of the dialog box once I apply the change.

Figure 5-3. The data connection Properties dialog box lets you name the data connection and set various design-time and run-time properties.

You can use the data connection Properties dialog box to change the configuration of a data connection. You can:

• Change the data source
• Change the authentication properties used at both run time and design time
• Change other properties, such as cursor type

If you change the data source to a connection string, you can use the Build button to select the driver for the connection and then configure its properties. This option is not available for an ODBC data source or a data link file.

Figure 5-4 shows the Authentication tab of the data connection Properties dialog box. Notice that you can specify both user name and password strings for the run-time and design-time connections. You can choose whether to allow the Visual InterDev environment to prompt you at design time for any required connection information. You can also choose to allow a user prompt for connection information during design time.

Figure 5-4. The Authentication tab provides a design-time interface for changing both design-time and run-time authentication for the data connection.

The run-time prompt must always be set to Never. The data connection cannot prompt the user for the connection information when the application executes because the data connection will be running on the server, not the client. Even if you set the run-time environment for DTCs to client, the prompting behavior will not work in Web applications. You can, of course, override these defaults by passing the security credentials in to the data connection when the application is executing.

Creating a New Data Source

Visual InterDev 6.0 will let you create a new data source when you add a data connection. The steps are similar to the ones used in Visual InterDev 1.0.

1. Right-click the Project name or global.asa in Project Explorer, and select Add Data Connection from the context menu. You will see the Select Data Source dialog box, shown in Figure 5-2.
2. Click the File Data Source tab or the Machine Data Source tab to select the type of data source. Figure 5-2 shows the Machine Data Source tab.
3. Click the New button.
4. Select the database driver for your database, and choose Next.
5. Type the name for the data source, and choose Next. The extension .dsn is automatically added to the name for a file data source.
6. Choose Finish. The database driver will be invoked and will display dialog boxes to complete the setup process. Enter driver-specific information, such as the name of the database.

Be careful when using data sources. Observe the guidelines listed below:

• When you specify the path for a file database such as Access, use the relative path to the database, not its location on your development computer. For example, if the database is located on a Web server, use the URL path to the database. This ensures that the database will be available from your Web server.
• Often it is best to use a system data source rather than a file data source. The reasons are twofold: First, using a system data source allows you to create the same data source name on all the developer systems and on the production server. Then you can move the application from one system to the other without any database issues. Second, the application will run faster, since system data sources are faster than file data sources.
• Be careful when using system and user data sources. System data sources are available to all users on the system. User data sources are only available to the user who creates them. For Web servers, always define the data sources as system, unless you want to define them explicitly for the run-time user only. Figure 5-5 shows this step in the Create New Data Source Wizard.

The step shown in Figure 5-5 will display only if you select the Machine Data Source tab in the Select Data Source dialog box before you click the New button in step 3 above.

Figure 5-5. The Create New Data Source Wizard steps you through the process of creating a new data source.

When you reach the last step of the Create New Data Source Wizard, you will see a summary of the data source information you specified. An example of a SQL Server data source is shown in Figure 5-6. At the bottom of this dialog box you will see the Test Data Source button. Click this button, and the data source connection will be tested for you. This ensures that the data source information is valid and that the data connection will work.

Figure 5-6. You can test your data source during the creation stage by clicking the Test Data Source button.

Once you have completed the steps to set up the data source, you are returned to the Select Data Source dialog box shown in Figure 5-2 and the data source name you created is displayed in the list. Select this name and click OK. This will add the data source to your project.