XML Programming (Core Reference)

Author R. Allen Wyke, Sultan Rehman, Brad Leupen Pages 736 Disk 1 Companion CD(s) Level Intermediate Published 01/09/2002 ISBN 9780735611856 ISBN-10 0-7356-1185-8 Price(USD) $59.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Index Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 9: Building a Server Application continued

The Golf Reservation System Server Application

The Database

The Course Table

The course table will contain all the course-related information in the server application. Records can be inserted into the course table by issuing the following SQL statement:

INSERT INTO course(city, country, description, name, postalCode, price, 
state, streetAddress, telephone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Table 9-2 contains the fields inside the course table.

Table 9-2   Course Fields

Column	Description
Id	Contains the course's ID. It is autoincremented by the database.
Name	Contains the course's name.
StreetAddress	Contains the course's street address.
City	Contains the course's city.
State	Contains the course's state.
PostalCode	Contains the course's postal code or zip code.
Country	Contains the course's country.
Telephone	Contains the course's telephone number.
Price	Contains the price for a round of golf.
Description	Contains a longer description of the golf course.

The Tee Table

The tee table will represent the different tees on the golf course (for example, red, white, and blue). Each tee will have a unique yardage and slope. Tee records can be created with the following SQL command:

INSERT INTO tee(courseId, description, distance, slope) VALUES (?, ?, ?, ?)

Table 9-3 contains the fields in the tee table.

Table 9-3   Tee Fields

Column	Description
Id	Contains the tee's ID. It is autoincremented by the database.
Slope	Contains a measurement of the difficulty of the tee.
Distance	Contains an aggregate measurement of all of the holes' distances.
Description	Contains a description of the tee (for example, red, white, and blue).
CourseId	Contains a primary key reference to the associated course.

The Hole Table

Records in the hole table represent a hole for a given tee. The hole's yardage, handicap, and par might vary with the tee. The following SQL command can be used to create new hole records:

INSERT INTO Hole(handicap, length, teeId, hole, par) VALUES (?, ?, ?, ?, ?)

Table 9-4 contains the fields in the hole table.

Table 9-4   Hole Fields

Column	Description
Id	Contains the hole's ID. It is autoincremented by the database.
TeeId	Contains a primary key reference to the associated tee.
Handicap	Contains the hole's handicap (1-18).
Length	Contains the hole's yardage.
Hole	Contains the hole number (1-18).
Par	Contains the hole's par.

The Golfer Table

The golfer table will contain records for each registered golfer in the system, regardless of whether or not they have booked a tee time. Golfer records can be created by issuing the following SQL command:

INSERT INTO golfers(city, country, email, firstName, lastName, phone, 
postalCode, state, streetAddress, [password], username) VALUES (?, ?, 
?, ?, ?, ?, ?, ?, ?, ?, ?)

Table 9-5 contains the fields in the golfer table.

Table 9-5 Golfer Fields

Column	Description
Id	Contains the golfer's ID. It is autoincremented by the database.
FirstName	Contains the golfer's first name.
LastName	Contains the golfer's last name.
StreetAddress	Contains the golfer's street address.
City	Contains the golfer's city.
State	Contains the golfer's state.
PostalCode	Contains the golfer's postal code or zip code.
Country	Contains the golfer's country.
Email	Contains the golfer's e-mail address.
Phone	Contains the golfer's telephone number.
Username	Contains the golfer's username, required to log in and request a tee time
Password	Contains the golfer's password, required to log in and request a tee time.

The Bookings Table

The bookings table will house all the requested course tee times. The table will contain only those tee times for a course that have been filled. Available tee times will be inferred by the "gaps" in this table. New bookings records can be created with the following SQL insert statement:

INSERT INTO bookings(courseId, golfer, teeDate, teeTime) VALUES (?, ?, ?, ?)

Table 9-6 contains the fields in the bookings table.

Table 9-6   Bookings Fields

Column	Description
Id	Contains the booking's ID. It is autoincremented by the database.
CourseId	Contains a primary key reference to the associated course.
Golfer	Contains a primary key reference to the associated golfer.
TeeDate	Contains the date of the tee time.
TeeTime	Contains the time of the tee time.

Data Sets and Data Adapters

Data adapters are the helper classes that populate the data sets and perform necessary database updates. They keep the data set classes simple by handling the dirty work of reading and writing data. Data adapter classes are tuned to a specific datasource such as a specific table in Access. They contain all the SQL Insert, Update, Select, and Delete statements needed to access that table. Multiple data adapters can be bound to a single data set. This is the case with the Golf Reservation System server application. Each data adapter is responsible for loading and storing a subset of the data set.

Data Adapters

The best way to create your data adapter classes is to use the .NET Studio Visual Designer. Use the Server Explorer pane to add a new data connection to your project. Upon adding this connection, the Server Explorer will update itself to show the connection and the data tables that can be managed by the connection.

Drag each of the tables you want to use onto the designer. The .NET Studio will generate a default data adapter for the table and display its properties in the Properties window. At this point you can configure the data adapter's Insert, Select, Update, and Delete SQL commands and other connection-related properties. We created data adapters this way for the course, hole, tee, golfer, and bookings tables in the application.

The .NET Studio will autogenerate the adapter code and place it into the .asmx.cs file of your project. You might need to tweak this code from time to time, so you should know what it does. (We needed to alter the SQL Insert command that some of the adapters were using to accommodate system-generated keys.) Let's take a look at how the GolferDataAdapter class works. The code fragments shown here come from the GolfCourseService.asmx.cs file.

First we need to create an instance of the data adapter class and its associated OLE-DB command classes.

this.oleDbUpdateCommand5 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand5 = new System.Data.OleDb.OleDbCommand();
this.oleDbInsertCommand5 = new System.Data.OleDb.OleDbCommand();
this.oleDbDeleteCommand5 = new System.Data.OleDb.OleDbCommand();
this.golferDataAdapter = new System.Data.OleDb.OleDbDataAdapter();

Next we need to configure each of the OLE-DB command classes. This is done by specifying the SQL statement and filling in its required parameters. Again, the .NET Studio Visual Designer will create this code for you, although you may need to customize it in order for it to work with your database. The following is the configuration code for the Insert command. This code will create a new golfer record in the golfers table.

this.oleDbInsertCommand5.CommandText = "INSERT INTO golfers(city, 
country, email, firstName, lastName, phone, postalCode," +
   " state, streetAddress, [password], username) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, " +
   "?, ?)";
this.oleDbInsertCommand5.Connection = this.oleDbConnection1;
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("city", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "city", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("country", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "country", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("email", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "email", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("firstName", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "firstName", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("lastName", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "lastName", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("phone", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "phone", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("postalCode", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "postalCode", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("state", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "state", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("streetAddress", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "streetAddress", System.Data.DataRowVersion.
Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb. 
OleDbParameter("password", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "password", System.Data.DataRowVersion.Current, null));
 
this.oleDbInsertCommand5.Parameters.Add(new System.Data.OleDb.
OleDbParameter("username", System.Data.OleDb.OleDbType.Char, 50, 
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), 
((System.Byte)(0)), "username", System.Data.DataRowVersion.Current, null));

The Select, Update, and Delete commands for the golfers table (and all other tables in the application) work in much the same way. In all cases, you must specify a parameterized SQL CommandText string to execute against the database. You must also add a new object to the Parameters array for each runtime value you intend to use.

Next we bind the newly created OLE-DB command objects to the data adapter. This will force the adapter to use the SQL we just wrote when inserting, updating, deleting, or selecting data to and from the table.

this.golferDataAdapter.DeleteCommand = this.oleDbDeleteCommand5;
this.golferDataAdapter.InsertCommand = this.oleDbInsertCommand5;
this.golferDataAdapter.SelectCommand = this.oleDbSelectCommand5;
this.golferDataAdapter.UpdateCommand = this.oleDbUpdateCommand5;

Finally we need to specify the table mappings that map columns in the golfers table to columns in the associated data set. We will take a closer look at the data set momentarily.

this.golferDataAdapter.TableMappings.AddRange(new System.Data.
Common.DataTableMapping[] {
  new System.Data.Common.DataTableMapping("Table", "golfers", new 
System.Data.Common.DataColumnMapping[] {
    new System.Data.Common.DataColumnMapping("city", "city"),
    new System.Data.Common.DataColumnMapping("country", "country"),
    new System.Data.Common.DataColumnMapping("email", "email"),
    new System.Data.Common.DataColumnMapping("firstName", "firstName"),
    new System.Data.Common.DataColumnMapping("id", "id"),
    new System.Data.Common.DataColumnMapping("lastName", "lastName"),
    new System.Data.Common.DataColumnMapping("phone", "phone"),
    new System.Data.Common.DataColumnMapping("postalCode", 
"postalCode"),
    new System.Data.Common.DataColumnMapping("state", "state"),
    new System.Data.Common.DataColumnMapping("streetAddress", "streetAddress"),
    new System.Data.Common.DataColumnMapping("password", "password"),
    new System.Data.Common.DataColumnMapping("username", "username")
  })
});

That's it for the golfer data adapter! As you can see, creating the adapter objects for your application is a tedious and error-prone process. The .NET Studio will save a lot of time and debugging effort if you let it create these classes for you.

Data Sets

You will use data sets for practically all the database manipulation you need to do in your application. Data sets are in-memory databases that provide their own insert, update, delete, select, transaction, and filter semantics. By coding to the data set interface you make it much easier to port your application to another database later.

Typed vs. Untyped Data Sets

.NET data sets come in two flavors: typed and untyped. Typed data sets have built-in schemas associated with particular databases and columns. .NET uses this schema to extend the base DataSet class and create a new class that contains column references as typed properties and accessors.

Untyped data sets, on the other hand, do not contain extra information provided by the schema. You must manually extract and cast data values from the data set's array. The difference between typed and untyped data sets can be illustrated best by an example.

To retrieve a golfer's last name from an untyped data set you would have to execute the following lines of code:

string lastName = (string)courseDataSet1.Tables
["golfers"].Rows[0]["lastName"];

Compare that to using a typed version of the same data set:

string lastName = courseDataSet1.golfers[0].lastName;

The typed data set is much easier to use (especially when you have command completion turned on) and the .NET Studio will generate all the code for you.

Generating the Data Set

Once you've prepared your data adapter classes, you're ready to generate a typed data set from them. Right-click on the visual designer and select Generate Dataset. Alternatively, you can select the Generate Dataset option in the Tools menu. The Generate Dataset Wizard will walk you through the steps necessary to create a new DataSet class. The only detail you definitely need take care of is to select which data tables and data adapters the data set should use. After you have checked these, the .NET Studio will create a new typed data set schema and will use this schema to create the typed data set class definition.

The code behind the new data set is incredibly long (over two thousand lines for the server application), so we won't get into the guts here. Basically, the typed data set class provides simple marshaling utilites that either put information into the data set array or pull information out of it. Figure 9-3 shows how the designer looks with all the data adapters and data sets already configured.

Click to view graphic

Figure 9-3 A snapshot of the .NET Studio Designer showing the completed data acess objects.

Data Views

DataView objects are bound to a specific table in a data set. The table can be passed into the DataView object through its constructor or set manually via its Table public property. Once the data view is bound to a table, you can change its RowFilter and Sort properties at runtime to manipulate the data shown in the view. The Count property tells you how many records the view currently holds.

The RowFilter data view property uses the .NET Expression syntax to query the data set. .NET Expressions is a powerful query language with rich operator support. For example, the following expressions can be used to filter the course table:

• "Name LIKE 'Pinehurst*'"
• "Price < 100.00"
• "State = 'North Carolina'"
• "Price < 100.00 AND State = 'North Carolina' AND (City = 'Durham' OR City = 'Chapel Hill' OR City ='Raleigh'"

Last Updated: December 12, 2001

Top of Page