Read an Excerpt

SQLXML Web Services

Once you install the SQL 2000 Web Services Toolkit (most importantly for this discussion, SQLXML 3.0) you will be able to communicate to SQL Server via HTTP by creating a SQL Virtual Root Directory from one of your selected databases. SQLXML 3.0 is configured to work with .NET and will do the work of generating your Web Service Description Language (WSDL) file in order to process the stored procedures or user defined functions as Web Services. A host of managed classes are available via .NET, but because they are beyond the scope of this topic, we will not discuss them here. However, the SQLXML Web Services Toolkit includes a white paper.

One of the key benefits of using SQL Web Services is the cost savings on time for development. If you have developed any type of case management software you know that the work you do in the back-end pays huge dividends on the front-end application. Every database requires some set of stored procedures to interact with it, for example adding new records to an employee’s table or editing those same records. After creating your set of stored procedures you then have to access them via ADO.NET in the application logic in order to pass the data. In this sample application we will get around this last bit of coding by enabling our set of stored procedures to be Web Services and to handle all of our database interaction.

Developing the TimeTrack Application

We will now develop a time tracking application that will use an employee sign in to track time spent on a given project or task. This is always a useful tool to have developed for your organization for reporting and using the data for cost analysis. Everyone in IT has had the problem of over- or under-projected timelines for a given job or task. By tracking current project times an organization can better formulate future job timeline projections.

This is a simple example of how to cut out ADO and enable stored procedures to do the work instead. Our application has a simple database with a set of stored procedures to handle the data interactions. This application will track employees and the projects they are working on. We have laid out the shell of the application and will let you fill in or expand upon the application framework to suit your own needs.

Creating the Database

The first thing we need to design is the database. Let’s take a look at the schema layout, which can be seen in Figure 7.1.

Figure 7.1 Database Schema Layout

As you can see by the schema, we will have a simple three-table database. To reiterate, this is just a shell—if you want to add tables to this sample feel free. This will track time spent on a project and by what employee. First we need to create the database in SQL Server. Right-click on Databases in EM and select New Database (see Figure 7.2). You can call it what you want; we used the name TimeTracker. Leave all of the create database defaults set and select OK. You may attach our database from the code section on the Solutions Web site (www.syngress.com/solutions), filename TimeTrack_data.mdf. To attach a database to your sever, right-click on Databases in EM and select Attach Database as seen in Figure 7.3. To create the tables simply navigate in Enterprise Manager (EM) and right-click on Tables and select New.

Figure 7.2 New Database Menu

Figure 7.3 Attach Database Menu

If you are using the database that has been provided for you on the Solutions Web site for this book (www.syngress.com/solutions), please be sure that you have selected the TimeTrack_data.mdf file. Make sure that you set the correct Database Owner (DBO) for the database; most likely it will be SA. Now that you have the database created you can add the tables. If you have created a new database instead of attaching the one provided on the Solutions Web site for this book, you must also add each of the tables. Right-click on the Table menu under database and select New Table. Add additional tables to the schema if needed.