Microsoft TechNet Solutions for Education

July 2000

This Solutions paper shows how to build and deploy a Web-based data retrieval solution using Microsoft® SQL Server, Active Server Pages, and Internet Explorer.

Combining the power of client/server applications with platform independence, Web applications represent a significant advance in information technology. This month's solutions paper explores how educational instructions can build simple but powerful Web search tools, using Active Server Pages to access and retrieve SQL Server™-based data through a Web interface.

On This Page

Web-based data applications
Components of a Web-based data application
Selecting the right hardware, software, and development tools
Choosing a design methodology
A Web-based data access and retrieval application
Resources

Web-based data applications

The Web has become a key environment for developing client/server applications. The marriage of HTML with server-side components, services and scripting permits developers to build fully featured Web-based applications for both intranets and the Internet.

There are a number of technical and tactical advantages to deploying applications based on the Web, including:

• Easier deployment and maintenance, translating directly into time and cost savings.

• Rapid development and virtually automatic cross-platform support through the client-side framework provided by the Web browser.

• The ability to serve users independent of their physical location.

Incorporating direct database access into a Web application brings additional benefits:

• A relational database server can optimize network traffic by returning only the data an application needs.

• Hardware costs can be minimized. Because the data is not stored on each client, clients do not have to dedicate disk space to storing data. The clients also do not need the processing capacity to manage data locally, and the server does not need to dedicate processing power to displaying data.

• Maintenance tasks such as backing up and restoring data are simplified.

• Business and security rules for all users can be defined once, for everyone, on the server.

Components of a Web-based data application

A Web-based data application is composed of these four elements:

• A set of HTML pages comprising the user interface.

• Server-side script code to construct dynamic pages. Active Server Pages (ASP) provides server-side scripting for Microsoft Internet Information Server (IIS) Web servers. Active Server Pages technology dynamically locates and delivers information requested by users.

• Server components providing necessary application functionality such as sessioning and database access.

• Services such as SQL Server and Internet Information Server providing communication pathways between the operating system, Web server, database and Web application.

Active Server Pages (ASP)

An Active Server Page is simply a standard HTML page with script code embedded directly in the document. A Web developer can use the embedded script code to implement business logic in line with the HTML. Additionally, a Web developer can use script code to access logic embedded in external components or to connect directly to a database. When a browser requests an Active Server Page, the embedded script is executed by the scripting engine on the server and the results are dynamically combined with the HTML before the document is returned to the browser.

An ASP application is a collection of Active Server Pages, along with included HTML pages and components that the application requires

OLE DB providers are the data access engines or services, as well as the business logic components that these applications can use in a highly interoperable, component-based environment. OLE DB interfaces encapsulate various data management services, allowing an application to access data without regard to its source.

Active Data Objects (ADO) is a high-level programming interface that applications developers can use for accessing OLE DB data sources. ADO permits a collection of records to be passed between clients and servers in the form of a recordset. A recordset can be used to pass a query result from the server to the client, and to pass updated records from the client to the server.

Server Components

ActiveX® Server Components give Active Server Pages much of their power. ActiveX Server Components enhance the power of Active Server Pages by promoting code reuse and extensibility, thereby reducing both development time and cost. Every component has a number of publicly accessible functions that other components can discover and use. The base and intrinsic components alone offer a wide variety of services to the scriptwriter, from session management to database access. Many of these functions are notoriously difficult to implement, and the ability to easily reference them from within an HTML page represents an enormous saving in development time.

In addition, ActiveX Server components offer the Web application developer a new way to think about HTML page authoring. ActiveX Server Components provide objects that transcend the individual HTML page, and thus make possible coordination between them. And this is what really distinguishes a Web-based application from a static Web site: the ability to arrange a set of HTML pages, communicating with one another via scripts, so that they constitute a cohesive application.

Services

Internet Information Server. At its most elemental, a Web server such as IIS is a service that receives information requests, in the form of URLs, from browsers and returns HTML pages. As Web sites have grown larger and dynamic content has become more popular, Web servers have expanded their range of functionality beyond merely servicing HTTP requests. IIS, for example, offers support for site administration and publishing, security, and database communication in addition to HTTP, FTP and gopher services.

SQL Server. As the need for dynamic Web content grows, SQL Server has become an essential tool for Web-based application development. Some of its utility is rooted in the same characteristics that make it a useful service for client/server application development. In addition, SQL Server offers functionality of particular value to Web application developers. Its benefits include:

• The ability to work together with Internet Information Server to generate dynamic HTML pages.

• The ability to ensure database integrity under high transaction and update loads.

• Support for distributing data services across multiple computers.

• Support for transaction-oriented applications.

• Contributing to shortened Web site development time for sites with a large volume of related data by leveraging the back-end database to create a few HTML templates into which data may be poured rather than hundreds or even thousands of static HTML pages.

In addition to these benefits, IIS and SQL Server are closely integrated with each other and with Windows NT® and Windows® 2000 Server. Both IIS and SQL Server are designed to take advantage of the security, performance analysis, and event logging features of Windows NT and Windows 2000. And Internet Database Connector (IDC) and Active Server Pages provide ready access to SQL Server from IIS. This cohesiveness permits the developer to focus on application functionality rather than developing communication pathways between the operating system, Web server and database.

Selecting the right hardware, software, and development tools

Before embarking on Web application development, it is important to consider both the functional project requirements defining the behavior expected of the application, and the nonfunctional system requirements addressing the server hardware and software and development tools necessary to successfully develop and deploy the application.

This section presents an overview of the requirements that you must consider.

Hardware and Software

It is important to consider the anticipated hardware requirements for the site as a whole. To determine an adequate hardware configuration, keep the following questions in mind:

• How much traffic is the site expected to generate? This includes both the numbers of users likely to visit the site and the amount of time they are expected to spend per visit.

• How easy is it to scale the system to accommodate increases in traffic and/or content over time?

• How much content will the site contain, and how will it be distributed across the file system and the database?

• Must the site be running continuously, or is occasional downtime tolerable or acceptable?

The answers to these questions affect the choice of Internet connection type, CPU, memory, and disk subsystems. In addition, you must establish security mechanisms for the existing internal network to protect confidential information from the outside world. Proxy servers and firewalls are useful for managing user access.

Internet Development Tools

It is essential to choose tools that are tailored to developing Web applications with a database component.

• For HTML authors with minimal SQL familiarity, SQL Server Web Assistant offers a non-programmatic means of generating dynamic content.

• For designers who are acquainted with SQL, Internet Database Connector (IDC) provides a useful tool for executing SQL statements and populating an HTML page with the results.

• For a more sophisticated developer, Active Server Pages, Visual Basic® Script and Visual InterDev offer a powerful combination of server-side components, a scripting language to interface with those components, and an integrated development environment for working with HTML, script code, and database design simultaneously.

• For enterprise-class scalability and reliability, consider building a component-based Web application where Microsoft Transaction Server manages the application components, transactions, and security.

Choosing a design methodology

The general process of designing and developing a Web-based application is not significantly different from that for any software product. The design of a Web application centers around three fundamental areas:

• The user interface design seeks to strike a balance between the range of browsers supported, the aesthetics of the application, and how easy the application is to use. When designing an interface for use on the Internet, it is important to keep in mind the length of time a page will take to download over the slowest connection you expect to support.

• An efficient database design is crucial to ensure quick client response time. A good database design requires appropriate distribution of data between the database and the file system, reasonably normalized tables that do not require excessive joins to satisfy queries, enough indices to optimize frequently performed queries without consuming excessive drive space, and the use of stored procedures for frequently performed or complex database operations. For an overview of database design issues, see "Database Design Considerations" in the SQL Server Books Online.

• A database access plan specifying which HTML pages require database content can help the designer to determine which pages should be generated entirely dynamically using server-side scripting and which can be created pseudo-dynamically using a tool like SQL Server Web Assistant to generate static pages on a pre-determined schedule.

Web applications have a unique set of development tasks. These tasks include implementing the script code to interface with server components such as the database or ActiveX Server components, and in some cases implementing custom components as well.

Security is a paramount concern throughout the deployment process. In controlling access to a site, administrators must take into account both who may and may not access the site and who may both access the site and modify its content. Firewalls are commonly used to protect school or campus intranets from outsiders with access to a campus' public Internet sites; Windows NT Challenge/Response is useful to restrict access to sensitive areas on the corporate Intranet. With regard to content modification, Microsoft FrontPage®, Visual InterDev and SQL Server all offer administrator-configurable access rights.

A Web-based data access and retrieval application

To illustrate the strategies and methodologies that we've introduced here, let's look at an example of a Web-based solution: Aggie Search, an off-campus housing search system at Texas A&M University. Aggie Search allows students to look for both housing and roommate options using a Web-based user interface.

The university's Department of Student Life (DSL) had built a limited software solution to assist students living off-campus in finding housing and roommates. In looking to define a more comprehensive solution to off-campus housing needs, the university committed itself to developing a system that could accommodate an increasing number of users over time.

Functional project requirements

The university's project staff contacted Applied Computing Services Inc., a local Microsoft Solution Provider, to help develop the Aggie Search application. As its initial step, this team defined a set of functional project requirements:

• Students must be able to use the application to find off-campus housing and/or roommates, based on their stated preferences.

• Landlords must be able to list apartments and other properties that are available to student renters. However, apartment owners will not be able to directly post their information. Administrators – DSL staff members who are members of a Windows NT Applications Management Group – will perform all data entry and editing for apartment listings.

• Students with long-term rental arrangements must be able to advertise for renters willing to sub-lease.

• DSL staff members must be able to run management reports on system activity.

• The application must allow users to respond to advertisers via secure e-mail, based on security guidelines.

• Existing data must be converted to the Aggie Search system.

In addition, the team defined these technical requirements:

• With students in a mixed computing environment, who may be located anywhere throughout the world, it is imperative that the application be available on the Web. The application must be accessible to anyone with Internet Explorer 3.0 or later, or Netscape Navigator 3.0 or later, and at least a 28.8k Internet connection.

• Because some users have slow dial-up connections or may be using low-bandwidth networks, the application must also have a simplified user interface with minimal graphics that would load quickly. For this reason, frames are not permitted. Any image will have a descriptive alternate text tag indicating its content and if it is a link or merely an image.

• The application must be fully scalable, to allow for future growth.

• The application must be rigorously tested before it is made available within the campus community.

Reflecting the university's longstanding preference for Microsoft solutions, Aggie Search was based on Microsoft Windows NT Server and Internet Information Server, and was developed using VBScript, COM, and Active Server Pages. The database engine is Microsoft SQL Server 7.0.

User interface design

The team designed Aggie Search as a program with five modules that reflect the functional requirements that were articulated at the onset of the project:

• Apartment Complex Locator. Allows students to look for apartments matching their needs.

• Roommate Locator. Allows students to list themselves as available roommates or search for other students.

• Miscellaneous Property Locator. Allows owners of other rental housing to list their properties for the consideration of the students. Allows students to search these listings.

• Sublease Locator. Allows students to list housing units that are available fopr sublease. Allows students to search these listings.

• Management Module. Allows administrators to screen new listings before making them available to students, update apartment data, obtain reports on system utilization, and perform related tasks.

For example, the Management Module features a wizard-like user interface that administrators can use to add or update information for apartment complexes.

In subsequent screens, the wizard steps the administrator through the process of entering additional information: the address of the apartment complex, its owner or manager, various amenities, and a default floor plan for apartments in the complex.

Coding support for finding and editing data in this solution is via Active Server Pages and its' script-style format. For example, the following is an excerpt for one of the function calls from the Application:

'*********************************************************************************
function EditPerson(ID, FirstName, MiddleName, LastName, Phone, Mobile, _ 
                    Pager, Email, Connection, NeedsReview) 
'*********************************************************************************
'Purpose               :Add or edit a record in tblPerson. 
'Params                :ID                      - pers_PK 
'                       FirstName               - pers_Name_First 
'                       MiddleName              - pers_Name_Middle 
'                       LastName                - pers_Name_Last 
'                       Phone                   - pers_PhoneNum 
'                       Mobile                  - pers_MobileNum 
'                       Pager                   - pers_BeeperNum 
'                       Email                   - pers_EmailAddress 
'                       Connection -            an ADO Connection object 
'                                               used to access the database 
'                       NeedsReview -           a boolean value that is 
'                                               used to determine whether or not 
'                                               to flag the record for review. 
'                                               True = Review.  False = No Review. 
'Returns               :        Returns the PK of the record that 
'                               was added or edited. 
'Calls (non-system)    :        none 
'Called by (non-system):        The submit pages in the application. 
'Databases Accessed    :        The database accessed by the ADO 
'                               Connection object (Connection).
'History               :        Created 19990601 JMH 
'Comments              :        The parameter "ID" should be equal to 
'                               0 if this is an addition to the table.
'                               The parameter "Connection" must be a
'                               valid, *OPEN* connection.
'                               NeedsReview is passed byref.  This is
'                               required because this procedure will
'                               change its value based on the logic within.
'*********************************************************************************
'----- trim away any extra spaces 
FirstName = trim(firstname) 
MiddleName = trim(middlename) 
LastName = trim(lastname) 
Phone = trim(phone) 
Mobile = trim(mobile) 
Pager = trim(pager) 
Email = trim(email) 
'----- 
dim rsPerson  'an ADO Recordset object that will contain 
'  a record from the Person Table (tblPerson) 
'----- create and open the recordset 
set rsPerson = server.CreateObject("ADODB.Recordset") 
rsPerson.Open "Select * from tblPerson where pers_PK = 
 " & ID,connection,adOpenStatic,adLockOptimistic, adCmdText
'----- 
if ID = 0 then 
'we are adding a record 
rsPerson.AddNew 
end if 
'check to be sure we got the record 
if (rsPerson.BOF and rsPerson.EOF) and ID <> 0 then 
'this failed so return an id of 0 
        EditPerson = 0 
        else 
        'we are ok so do the editing 
'----- these fields must be checked for change 
'      if they have changed then the record must be 
'      flagged for review 
if FirstName = "" then 
        FirstName = Null 
end if 
        rsPerson("pers_Name_First") = FirstName 
if (rsPerson("pers_Name_First").OriginalValue <> FirstName) _
    or (ID = 0) then 
        NeedsReview = true 
end if 
if MiddleName = "" then 
        MiddleName = Null 
end if 
rsPerson("pers_Name_Middle") = MiddleName 
        if (rsPerson("pers_Name_Middle").OriginalValue <> MiddleName) _
            or (ID = 0) then 
                NeedsReview = true 
end if 
if LastName = "" then 
   LastName = Null 
        end if 
        rsPerson("pers_Name_Last") = LastName 
        if (rsPerson("pers_Name_Last").OriginalValue <> LastName) _
            or (ID = 0) then 
                NeedsReview = true 
end if 
if Phone = "" then 
        Phone = Null 
        end if 
        rsPerson("pers_PhoneNum") = Phone 
        if (rsPerson("pers_PhoneNum").OriginalValue <> Phone) _
            or (ID = 0) then 
                NeedsReview = true 
end if 
if Mobile = "" then 
        Mobile = Null 
        end if 
        rsPerson("pers_MobileNum") = Mobile 
        if (rsPerson("pers_MobileNum").OriginalValue <> Mobile) _
            or (ID = 0) then 
                NeedsReview = true 
        end if 
if Pager = "" then 
        Pager = Null 
        end if 
        rsPerson("pers_BeeperNum") = Pager 
        if (rsPerson("pers_BeeperNum").OriginalValue <> Pager) _
            or (ID = 0) then 
                NeedsReview = true 
end if 
        if Email = "" then 
                Email = Null 
        end if 
        rsPerson("pers_EmailAddress") = Email 
        if (rsPerson("pers_EmailAddress").OriginalValue <> Email) _
            or (ID = 0) then 
                NeedsReview = true 
end if 
'-----            
'update and close the recordset 
rsPerson.Update 
rsPerson.Close 
'----- return the PK of the record 
if ID = 0 then 
'retrieve the PK by using the @@IDENTITY variable 
'  The @@IDENTITY variable contains the Identity field 
'  for the last inserted record on this connection. 
        rsPerson.Open "Select @@IDENTITY As ID from tblPerson",
                       connection,adOpenForwardOnly,adLockReadOnly,adCmdText
        EditPerson = rsPerson("ID") 
        rsPerson.Close 
        else  
           EditPerson = ID 
        end if 
'----- 
end if 
'clean up the recordset 
set rsPerson = nothing 
end function 
'*********************************************************************************

Similarly, individual students can use the Need Housing wizard to indicate their preferences for housing.

Aggie Search uses this information to suggest potential roommates with compatible preferences.

Database design

The Aggie Search database is organized into three long tables for roommates, apartments and other housing units (See Figure 1). A set of 21 lookup tables enable searches so specific they can pinpoint roommates based on sleep, dating and cleanliness habits, and housing units based on the availability of patios, tennis courts and compliance with the Americans with Disabilities Act (See Figure 2 for database table names and descriptions). (A lookup table is simply a "helper" table that uses keywords to manage data for its parent table.)

Users – both students seeking roommates and landlords seeking to list available units – do so over the Web, entering their data onto Active Server Pages. Reviewers in the Department of Student Life scan the proposed database entries each day to ensure they meet campus standards, and then the Active Server Pages are brought into the SQL Server database using Active Data Objects (ADOs) built into the system.

For ease of maintenance and data updates, most Aggie Search Web pages use data stored in lookup tables. In the Need Housing wizard, all fields (drop-down boxes) use lookup tables except for the standard two-value lists such as Yes/No or Male/Female.

Figure 1: Aggie Search Database Diagram

Figure 2: Aggie Search Database Tables

Resources

The article "Building a Web-Based Search Tool" (by Andrew Peterson in SQL Server Magazine) gives another example of how you can create useful Web pages for data access and retrieval. The design and code examples may be of interest.

The SQL Server section on Microsoft TechNet provides resources to help you deploy, maintain, and support Microsoft SQL Server. TechNet includes a CD-based subscription service available from Microsoft that contains the latest technical information, supplemental drivers and patches, utilities, white papers, service packs, resource kits, and a host of other tools essential for installing and supporting Microsoft products. The TechNet web site contains extensive information about planning, evaluating, deploying, maintaining, and supporting a range of information technology systems.

The Microsoft SQL Server Administrator's Companion provides essential information to guide you in configuring SQL Server to obtain optimum performance for a given hardware configuration or software application.

The Internet Information Server Resource Kit available from Microsoft Press, covers all aspects of using IIS 4.0, including development of Web applications, and contains an appendix on ASP standards.

The article "The ABCs of ADO and ASP," by Michael Otey, provides details about using Active Server Pages with ActiveX Data Objects.

The Microsoft Developer Network (MSDN) is the comprehensive resource for all Microsoft development technologies, including ASP.