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.