Using Data Connections with SQL Server and Visual InterDev 6.0

How to use Visual InterDev's new features to create a Web application

Microsoft designed SQL Server 7.0 to scale up well in high-volume database applications. SQL Server 7.0's power and scalability combined with many new Internet and intranet features make it a great candidate for use in Web applications. In this article, I focus on how to use development tools with SQL Server to create Web applications.

The SQLOLEDB Driver

To create a new data connection for an application, you must first decide which OLE DB provider to use with the connection. In SQL Server 6.x, the primary provider for connecting Visual InterDev and most other applications to databases was the MSDASQL driver or Microsoft OLE DB Provider for ODBC.

SQL Server originally didn't have a native OLE DB provider you could use with ADO. However, SQL Server 7.0 includes the native Microsoft OLE DB Provider for SQL Server, the SQLOLEDB driver, for use with ADO.

To demonstrate how to use the SQLOLEDB provider with Visual InterDev 6.0, let's create a new Web site and add a data connection with Visual InterDev's new Data Environment. The Data Environment is simply an ADO wrapper that lets you develop faster and cleaner applications than if you developed them directly in ADO.

First, let's create a new Web application to test the data connection: In the Start Menu, click Programs, Visual Studio. Start Visual InterDev by clicking the New Web Project icon in the New Project dialog box, which Screen 1, page 38, shows. Assign a name to the project in the Name text box, and click Open to start the New Web Project Wizard. Next, enter the name of the server to host the new Web application, and click Next. For this example, accept the default settings for the remaining steps.

After you've created a new project, you can add a data connection to it by right-clicking Global.asa in Project Explorer (Visual InterDev's project interface) and selecting Add Data Connection. The Select Data Source dialog box will appear, as Screen 2, page 38, shows.

Testing the Data Connection

To create a new data source, click New on either the File Data Source tab or the Machine Data Source tab. Then, select SQL Server from the list of drivers. Answer the prompts on each successive page until you complete the data source definition. (In my test data source, I chose the Northwind sample database by selecting the Change default database option and selecting the Northwind database from the drop-down list.) The data source setup is complete when you see a prompt showing Test Data Source. If you click this button, Visual InterDev will test the data source for you. (For an explanation of data sources, see "Sorting Data Sources," page 42.)

After you add a data connection to your project, you can inspect the connection's properties. Right-click the data connection name, and select Properties to display the Connection Properties dialog box. Select the Use Connection String option, and click Build to display the Data Link Properties dialog box, which Screen 3 shows.

By default, a new connection will use the Microsoft OLE DB Provider for ODBC drivers. This option was the standard for ADO before native OLE DB providers became available. To use the OLE DB provider instead, select Microsoft OLE DB Provider for SQL Server. After you make this change and click OK, the connection will use SQLOLEDB. This setting will result in improved performance.

OLE DB providers let you use many different types of data. For example, by installing Visual InterDev 6.0 and SQL Server 7.0 on my system, I can access all the providers listed in Screen 3. OLE DB is making progress toward the goal of providing a universal data layer to the many different types of data that businesses need today.

When you insert the data connection into an application, Visual InterDev creates a Data Environment folder in Project Explorer and puts the data connection into this folder. Visual InterDev also adds code to the project's Global.asa to define the connection properties, which Listing 1 shows. The code in Listing 1 defines the data connection. The first part of the statement specifies the OLE DB provider, which is SQLOLEDB. If you don't specify a provider, the application will use the MSDASQL ODBC provider by default. The rest of the connection statement defines the connection's various options, which Table 1, page 39, outlines.

The code starting with Set DE in Listing 1, page 39, is the Project Data Environment section. These few lines of code initialize the Data En-vironment and set up an application variable for it. The DE variable lets each page in the application use the data connection for database access.

You can set up and modify a data connection directly either by scripting the entire connection, Data Environ-ment, or Recordset design-time control, or by using straight ADO and scripting it. All of these methods will work, but performance levels might vary.

Using Data Connections in Web Applications

The data connection is easy to use in a Web application with Visual InterDev 1.0 or Visual InterDev 6.0. In either version, Visual InterDev creates the appropriate connection information in Global.asa. Visual InterDev 1.0 also adds the connection information to the Session variables. The design-time controls you place in the application then reuse the Session variables on each page. In addition, with Active Server Pages (ASP) scripting and ADO, you can use the connection information directly in your application.

Visual InterDev 6.0 stores the connection information in application variables. The reason behind the change from version 1.0's session variables to version 6.0's application variables is the Data Environment. The Data Environment is a global system running on the Web server. With application variables, the application can use the Data Environment from any page.

You can use the design-time controls to create command and Recordset objects for use across the application. For instance, you can create a command object to pull data from a set of tables, and then use the object across many pages in an application. Command objects are truly objects. For example, if you create an object named GetCustomers, you can reuse that object on several pages. If you change the underlying SQL statement, all the pages using GetCustomers reflect the change automatically.

You can easily use ADO with ASP or Visual Basic (VB). Theoretically, you can build faster applications if you program directly to the ADO interface, and you can develop even faster applications if you write directly to the ODBC API. However, that type of programming requires a good understanding of ODBC and an exceptional application-writing ability.

Using the Data Environment

To use the data connection, put a Recordset design-time control on a page and add individual design-time controls to it. This method will let you extract data from a SQL Server database and display it on a Web page.

First, right-click the data connection, and select Add Data Command from the context menu to display the Data Command Properties dialog box, which Screen 4 shows. I changed the name of the data command to GetCustomers and added a SELECT statement. (I used Query Designer from SQL Builder to build a SELECT statement. Alternatively, you can type a SELECT statement directly into the SQL Statement window in the Data Command Properties dialog box.)

After you finish setting the data command properties, click OK to close the dialog box. This action will update the application and add the data command object GetCustomers to the Data Environment. In Project Explorer, you will see a plus sign to the left of GetCustomers. Click the plus sign to expand the data command and show the fields in the Recordset, as Screen 5, page 39, shows. This expansion lets you see the Recordset properties and change any property settings.

The data command is now available throughout the application on any ASP page. To use GetCustomers, create a new ASP page by clicking the Add Item icon on the toolbar or click Add Item in the Project menu. The easiest way to add GetCustomers to the page is to right-click the data command and drag it onto the page. When the command is complete, Visual InterDev will tell you it needs to add its Scripting Object Model (a set of ActiveX components or JScript files) to the page. Each Visual InterDev project you load on a Web server must include the Scripting Object Model to activate the design-time controls on the server.

When you drop the data command (e.g., GetCustomers) on the ASP page, Visual InterDev inserts a Recordset design-time control and links it to the data command. The Recordset design-time control implements the data command. (Or you can drop a Recordset control from the Toolbox onto an ASP page and set it to point to the data command.)

Next, place the fields from the data command on the ASP page. First, select the fields from the data command (e.g., GetCustomers) in Project Explorer. Then drag these fields into the Recordset object on the page to create a design-time control for each field and link the fields to the Recordset control. The type of design-time control for each field depends on the field's properties. (If you don't change the design-time control type, Visual InterDev will base it on the field's data type.)

To complete this page, you can drag the RecordsetNavbar to the ASP page just after the end table tag () following the Recordset design-time controls. Right-click the RecordsetNavbar design-time control, and select Properties. As a result, you will see the dialog box in Screen 6. Then, in the Recordset list, select the name for your data command object, and click OK.

You now have a live ASP page to retrieve data from the SQL Server database and display it on the Web. This application lets your user navigate through the database one record at a time. Also, you can change the page format and perform many other functions with design-time controls such as scripting recordsets and dynamically changing properties.

SQL Server Scales Well

SQL Server is a good choice for data-driven server applications because it scales well in a Web environment and is easy to use with other Microsoft tools. Configuring your systems correctly and setting up a proper connection strategy will save time.