Visual Basic is appearing everywhere--in the entire Microsoft Office Suite, in Active Server Web pages, and in the standard Visual Basic development environment. Also on the rise is the client/server relational database management system (RDBMS) of choice in a Microsoft Windows NT environment: SQL Server. SQL Server is a high-performance RDBMS system that is tightly integrated with the Windows NT operating system. SQL Server supports ANSI-92 SQL and offers high-performance features, such as stored procedures, triggers, rules, defaults, constraints, and SMP support. In an intranet/Internet environment based on Microsoft technologies, SQL Server can play a large role as a back-end database for your Web applications. SQL Server integrates well with Visual Basic. By using SQL Server stored procedures, you can invoke a Visual Basic ActiveX component from a SQL Server stored procedure. For easy-to-use Web access, SQL Server has a user-friendly hook called the Web Assistant that enables you to easily generate static HTML pages from SQL Server tables. SQL Server was the first RDBMS supported by an interesting database Web technology called the Advanced Data Connector (ADC). This chapter will show you how to use the SQL Server Web Wizard to generate HTML pages. For the more advanced SQL Server users reading this book, the stored procedures behind the Web Assistant, as well some trigger and scheduling ideas, are covered. This chapter also will introduce you to the Microsoft ADC.
The SQL Server Web Assistant automates the generation of HTML Web pages. You can use this wizard to publish data that resides in SQL Server on the Internet or intranet. As you can imagine, this link between SQL Server and the Internet/intranet creates several new and exciting possibilities for the distribution of information from SQL Server. Some of the uses for distributing SQL Server information in the HTML format follow:
Follow these steps to use the SQL Server Web Assistant:
FIGURE 23.1. The SQL Server Web Assistant-Login dialog box.
FIGURE 23.2. SQL Server Web Assistant-Query dialog box.
FIGURE 23.3. The SQL Server Web Assistant-Scheduling dialog box.
FIGURE 23.4. The SQL Server Web Assistant-File Options dialog box.
FIGURE 23.5. The SQL Server Web Assistant-Formatting dialog box.
That's it! You have just created a Web page that will automatically be generated
the next time data changes in the pubs database table Authors.
You can view the page generated by using Microsoft Internet Explorer, as shown in
Figure 23.6.
FIGURE
23.6. Viewing a Web page created by the
SQL Server Web Assistant.
The SQL Server Web Assistant provides a tremendous amount of automation through a simple-to-use graphical interface. If you are interested in how the wizard performs Web-page automation, take a closer look at the Web Assistant's components. Understanding the Assistant's functionality is useful when you need to modify an existing Web. The following sections explain the components used by the wizard. Web System Stored Procedures The following system procedures are used by the Web Assistant to generate Web pages:
You can use these system procedures to further extend the functionality of the
Web Assistant, although the Web Assistant does a good job of doing everything you
need. Triggers If you want your Web page to be a current reflection of your data,
you will have to regenerate the Web page whenever your data changes. (Remember that
the data in an HTML file is static; to reflect updates, the file must be regenerated.)
To automate the generation of the HTML file, the SQL Server Web Assistant can create
a set of INSERT, UPDATE, and DELETE triggers that schedule
program calls to regenerate the Web page (see Figure 23.7). A trigger is a user-defined
collection of SQL statements that are executed when an item is added, updated, or
deleted in a table. Triggers often are used for data and referential integrity. The
good news for you, as a Visual Basic programmer, is that the Web Assistant automatically
generates the required triggers.
FIGURE
23.7.A trigger used to regenerate a Web page.
SQL Server's Task Scheduler SQL Server's Task Scheduler is another component of the
SQL Server Web Assistant. If you want the wizard to regenerate the Web page on an
hourly, daily, or weekly basis, the wizard will schedule a task that calls sp_runwebtask
(see Figure 23.8). The Web page is rebuilt when this system procedure is executed
(see Figure 23.9). A perfect use for the Task Scheduler is to generate weekly, monthly,
or quarterly status reports that do not change after they are generated. You can
schedule a stored procedure to execute and roll up the data in a table and then execute
the stored procedure sp_runwebtask to update the HTML.
You can invoke the Task Scheduler from SQL Server's GUI front-end tool called
the Enterprise Manager (see Figure 23.8). When using the Enterprise Manager, you
can get to the Task Scheduler by choosing Server | Scheduled Tasks.
FIGURE
23.8.Task Scheduler and Web page integration.
FIGURE
23.9.The call to sp_runwebtask from the Task Scheduler.
Stored Procedures When the sp_runwebtask system procedure is executed from the Task Scheduler, it calls a stored procedure generated by the SQL Server Assistant. This stored procedure contains the information input by the user when the original Web page was created. To determine the name of the stored procedure being called, look at the code in Figure 23.9:
EXEC sp_runwebtask @procname
A stored procedure is a series of SQL statements that are precompiled and reside on SQL Server. A stored procedure can declare variables, pass parameters, call other stored procedures, and perform conditional logic as well as call functions that reside on SQL Server.
As stated earlier, Microsoft wants to make SQL Server the RDBMS of choice in Windows NT environments as well as NT Web environments. Therefore, the RDBMS that works immediately with new Web database technologies released from Microsoft is SQL Server. SQL Server 6.5 was the first database to support the ADC. What's the ADC? Well, if you have a client/server database background (like I do), you probably have been waiting for the day when you could create Web-based applications that use bound controls across HTTP. There are no scripts to write and no IDC or HTX files--just a bound control on a Web page, similar to your current client/server applications that use RDO or DAO to communicate with an RDBMS system. The ADC enables you to use a special ActiveX data control on a Web page to bind to other ActiveX controls, such as grids and textboxes, and populate them with data retrieved from an RDBMS (just like a client/server application). No scripts are required to retrieve and format each record in HTML. Furthermore, the ADC retrieves and caches the database records locally on the client PC so that a user can edit them in the browser and then submit the changes back to the database. The ADC uses OLE DB to communicate with SQL Server and Oracle (check Microsoft's Website for other databases supported).
Figure 23.10 shows a general, high-level look at the architecture used with the ADC.
The first thing you might notice about Figure 23.10 is that many pieces make up the request to retrieve data from SQL Server. Unlike a typical two-tier client/server application, the ADC uses a three-tier architecture. The browser and Advanced Data Control provide the user interface services. The ADC comes with a default business object called the Advanced Data Factory object. This object sends SQL statements from the client to SQL Server and passes the results back to the client. The Advanced Data Factory object provides the business services. The data services are provided by an RDBMS system--in this case, SQL Server.
Use Figure 23.10 to walk through an interaction between the browser and SQL Server. A user on the browser loads a Web page that contains an ActiveX grid control and the Advanced Data Control. The Advanced Data Control is an invisible ActiveX control that enables you to bind ActiveX controls to a database recordset.
FIGURE 23.10. An architectural overview of the ADC.
As the page loads, a SQL statement is executed. The ADC creates an Advanced Data Factory object proxy on the client. The proxy is required because the business object on the client cannot communicate directly with the business object on the server. The proxy passes the request via HTTP to the Microsoft Internet Information Server. IIS determines the type of business object it is and creates an instance of the business object on the server, in this case the Advanced Data Factory object. The SQL statement then goes to SQL Server via OLE DB layered on top of ODBC. The recordset is retrieved and loaded into a server-side data cache. The information then is passed to the server-side Advanced Data Factory object and then is packaged and sent back to the client-side proxy. The information is unpackaged, placed in the client-side data cache, and passed to the Advanced Data Control, which loads the ActiveX grid control with the data.
http://www.microsoft.com/adc/default.htm
From the description of the ADC's architecture, the ADC sounds very difficult
to use. Actually, I think you will find the ADC about as difficult to use as the
ActiveX data control that comes with Visual Basic. Now look at one of the sample
applications that comes with the ADC, as shown in Figure 23.11.
FIGURE
23.11. Microsoft's sample ADC query page.
The ADC Query Page application comes with the ADC. The application is a good way to test whether you have properly configured your Web server and SQL Server to run the ADC. The application enables you to enter any valid select statement against the SQL Server pubs database. After you click the Run button (located at the bottom of the Web page and not shown in Figure 23.11), the data grid is almost immediately filled with the recordset. Again, the ADC is an invisible bound control, so you do not have to write an IDC file or ADO script to execute your script. Think of the ADC as client/server for the Web.
Now take a quick look at some important properties and methods of the ADC's Advanced Data Control required to retrieve data. For starters, look at an example of the <OBJECT> tag used for the Advanced Data Control. Listing 23.1 shows the <OBJECT> tag used in the sample application for the Advanced Data Control.
Listing 23.1. The Advanced Data Control <OBJECT> tag.
<OBJECT CLASSID="clsid:9381D8F2-0288-11d0-9501-00AA00B911A5"
ID=tstADC HEIGHT=10 WIDTH = 10
CODEBASE="HTTP://www.kscsinc.com/MSADC/msadc10.cab">
<PARAM NAME="BINDINGS" VALUE="tstGrid;">
</OBJECT>
Notice in Listing 23.1 the BINDINGS parameter, which has a value equal to the name of the grid on the page. Look at some other properties you must set before you can query the SQL Server via the ADC. For starters, you must set the ADC Server property to the IIS Server that will communicate to the SQL Server. The following line of code sets the Server property of the Advanced Data Control to a fictitious Web server, for example:
MyADC.Server = http//www.mspenik.com
The Advanced Data Control has a Connect property that, for SQL Server, requires a system ODBC DSN and a user ID and password. The following line of code is an example of setting the Connect property:
MyADC.Connect = "DSN=SQLPubs;UID=guest;PWD=guest;"
To set the Advanced Data Control with the SQL statement you want to execute, use the SQL property, as in this example:
MyADC.SQL = Select * from titles
After you have the control properly bound via the <OBJECT> tag and the SQL, Server, and Connect properties set, you are ready to retrieve some data.
To populate your bound controls, use the Advanced Data Control's Refresh method, as shown in this example:
MyADC.Refresh
You can navigate through the cached recordset by using the following Advanced Data Control methods:
You can modify several records on the client side--in a grid, for example--and then send the batched changes back to SQL Server by using the SubmitChanges method of the Advanced Data Control.
The ADC is a very new and exciting technology. Because the ADC integrates so well with SQL Server, I decided to introduce you to the technology well after the book outline was completed. This section has only scratched the surface. The ADC in itself is a powerful and compelling technology and probably can fill an entire book. To learn more about the ADC and to get a copy of the ADC, visit the Microsoft site at
http://www.microsoft.com/adc/default.htm
SQL Server is the perfect RDBMS for a Windows NT and Microsoft IIS environment.
Using the Web Assistant, you easily can generate HTML from any existing SQL Server
table. You can take advantage of SQL Server features, such as triggers, to update
the Web pages as your data changes. Or, you can use the SQL Server Task Scheduler
to update information from a table on a regular basis. The Microsoft ADC is being
integrated into the SQL Server environment, and it enables you to create applications
that use database bound ActiveX controls over HTTP.
