Extending The Event-Booking Example

Now that you know the basics of creating and consuming Web services, you can apply your knowledge to extending the meeting room booker application from the previous two chapters. Specifically, you extract the database access aspects from the application and place them into a Web service. This Web service has two methods:

• GetData(), which returns a DataSet object containing all three tables in the PCSWebApp3 database.

• AddEvent(), which adds an event and returns the number of rows affected so the client application can check that a change has been made.

In addition, you'll design the Web service with load-reducing in mind. Specifically, you'll store a DataSet containing the meeting room booker data at the application level in the Web service application. This means that multiple requests for the data won't require additional database requests. The data in this application-level DataSet object will only be refreshed when new data is added to the database. This means that changes made to the database by other means, such as manual editing, will not be reflected in the DataSet. Still, as long as you know that your Web service is the only application with direct access to the data you have nothing to worry about.

The Event-Booking Web Service

Create a new Web service project in Visual Studio in the C:\ProCSharp\Chapter28 directory and call it PCSWebService2. The first thing to do is to copy the database files (MeetingRoomBooker.mdf and MeetingRoomBooker_log.ldf) from PCSWebApp3 (or PCSDemoSite). Next you need to add a Global.asax file to the project, then modify the code in its Application_Start() event handler. You want to load all the data in the MeetingRoomBooker database into a data set and store it. This mostly involves code that you've already seen, because getting the database into a DataSet is something you've already done. You'll also use a connection string stored in Web.config as you've seen in earlier chapters. The code for the Web.config is as follows (the connection string should be placed on a single line):

<?xml version="1.0" ?> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">   <appSettings /> <connectionStrings> <add name="MRBConnectionString" connectionString="Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=|DataDirectory|MeetingRoomBooker.mdf" providerName="System.Data.SqlClient"/> </connectionStrings> </configuration> 

And the code for the Application_Start() event handler in Global.asax is as follows:

void Application_Start(Object sender, EventArgs e) { System.Data.DataSet ds; System.Data.SqlClient.SqlConnection sqlConnection1; System.Data.SqlClient.SqlDataAdapter daAttendees; System.Data.SqlClient.SqlDataAdapter daRooms; System.Data.SqlClient.SqlDataAdapter daEvents; sqlConnection1 = new System.Data.SqlClient.SqlConnection(); sqlConnection1.ConnectionString = ConfigurationManager.ConnectionStrings["MRBConnectionString"] .ConnectionString; sqlConnection1.Open(); ds = new System.Data.DataSet(); daAttendees = new System.Data.SqlClient.SqlDataAdapter( "SELECT * FROM Attendees", sqlConnection1); daRooms = new System.Data.SqlClient.SqlDataAdapter( "SELECT * FROM Rooms", sqlConnection1); daEvents = new System.Data.SqlClient.SqlDataAdapter( "SELECT * FROM Events", sqlConnection1); daAttendees.Fill(ds, "Attendees"); daRooms.Fill(ds, "Rooms"); daEvents.Fill(ds, "Events"); sqlConnection1.Close(); Application["ds"] = ds; }

The important code to note here is in the last line. Application objects (like Session objects) have a collection of name-value pairs that you can use to store data. Here you are creating a name in the Application store called ds, which takes the serialized value of ds containing the Attendees, Rooms, and Events tables from your database. This value will be accessible to all instances of the Web service at any time.

This technique is very useful for read-only data because multiple threads will be able to access it, reducing the load on your database. Note, however, that the Events table is likely to change, and you'll have to update the application-level DataSet class when this happens. You look at this shortly.

Next you can replace the default Service service with a new service, called MRBService. To do this, delete the existing Service.asmx and Service.cs files and add a new Web service to the project called MRBService. You can then add the GetData() method to your service in MRBService.cs:

 [WebMethod] public DataSet GetData() { return (DataSet) Application["ds"]; } 

This uses the same syntax as Application_Load() to access the stored DataSet, which you simply cast to the correct type and return.

Note that for this to work, and to make life easier in the other Web method you'll be adding, you can add the following using statements:

using System; using System.Configuration; using System.Data; using System.Web; using System.Collections; using System.Web.Services; using System.Web.Services.Protocols;

The AddEvent() method is slightly more complicated. Conceptually, you need to do the following:

• Accept event data from the client.

• Create a SQL INSERT command using this data.

• Connect to the database and execute the SQL statement.

• Refresh the data in Application["ds"] if the addition is successful.

• Return a success or failure notification to the client (you'll leave it up to the client to refresh its DataSet if required).

Starting from the top, you'll accept all fields in their correct data types:

 [WebMethod] public int AddEvent(string eventName, int eventRoom, string eventAttendees, DateTime eventDate) {    ... } 

Next, you declare the objects you'll need for database access, connect to the database, and execute your query, all using similar code to that in PCSWebApp3 (remember, you need the connection string here, taken from Web.config):

[WebMethod] public int AddEvent(string eventName, int eventRoom,                     string eventAttendees, DateTime eventDate) { System.Data.SqlClient.SqlConnection sqlConnection1; System.Data.SqlClient.SqlDataAdapter daEvents; DataSet ds; sqlConnection1 = new System.Data.SqlClient.SqlConnection(); sqlConnection1.ConnectionString = ConfigurationManager.ConnectionStrings["MRBConnectionString"] .ConnectionString; System.Data.SqlClient.SqlCommand insertCommand = new System.Data.SqlClient.SqlCommand("INSERT INTO [Events] (Name, Room, " + "AttendeeList, EventDate) VALUES  (@Name, @Room, @AttendeeList, " + "@EventDate)", sqlConnection1); insertCommand.Parameters.Add("Name", SqlDbType.VarChar, 255).Value = eventName; insertCommand.Parameters.Add("Room", SqlDbType.Int, 4).Value = eventRoom; insertCommand.Parameters.Add("AttendeeList", SqlDbType.Text, 16).Value = eventAttendees; insertCommand.Parameters.Add("EventDate", SqlDbType.DateTime, 8).Value = eventDate; sqlConnection1.Open(); int queryResult = insertCommand.ExecuteNonQuery(); }

You use queryResult to store the number of rows affected by the query as before. You can check this to see whether it is 1 to gauge your success. If you are successful, you execute a new query on the database to refresh the Events table in your DataSet. It is vital to lock the application data while you perform updates to ensure that no other threads can access Application["ds"] while you update it. You can do this using the Lock() and UnLock() methods of the Application object:

[WebMethod] public int AddEvent(string eventName, int eventRoom,                     string eventAttendees, DateTime eventDate) {    ...    int queryResult = insertCommand.ExecuteNonQuery(); if (queryResult == 1) { daEvents = new System.Data.SqlClient.SqlDataAdapter( "SELECT * FROM Events", sqlConnection1); ds = (DataSet)Application["ds"]; ds.Tables["Events"].Clear(); daEvents.Fill(ds, "Events"); Application.Lock(); Application["ds"] = ds; Application.UnLock(); } sqlConnection1.Close(); }

Finally, you return queryResult, allowing the client to know if the query was successful:

[WebMethod] public int AddEvent(string eventName, int eventRoom,                     string eventAttendees, DateTime eventDate) {    ... return queryResult; } 

And with that, you have completed your Web service. As before, you can test this service out simply by viewing the .asmx file in a Web browser, so you can add records and look at the XML representation of the DataSet returned by GetData() without writing any client code.

Before moving on, it's worth discussing the use of DataSet objects with Web services. At first glance this seems like a fantastic way of exchanging data, and indeed it is an extremely powerful technique. However, the fact that the DataSet class is so versatile does have implications. If you examine the WSDL generated for the GetData() method, you'll see the following:

 <s:element name="GetDataResponse"> <s:complexType> <s:sequence> <s:element minOccurs="0" maxOccurs="1" name="GetDataResult"> <s:complexType> <s:sequence> <s:element ref="s:schema" /> <s:any /> </s:sequence> </s:complexType> </s:element> </s:sequence> </s:complexType> </s:element> 

As you can see, this is very generic code, which allows the DataSet object passed to contain any data specified with an inline schema. Unfortunately, this does mean that the WSDL is not completely describing the Web service. For .NET clients this isn't a problem, and things progress as naturally as they did when passing a simple string in the earlier example, the only difference being that you exchange a DataSet object. However, non-.NET clients must have prior knowledge of the data that will be passed or some equivalent of a DataSet class in order to access the data.

A workaround to this requirement is to repackage the data into a different format — an array of structs, for example. If you were to do this, you could customize the XML produced in any way you want, and the XML could be completely described by the schema for the Web service. This can also have an impact in terms of performance, because passing a DataSet object can result in an awful lot of XML — far more than is necessary in most cases. The overhead resulting from repackaging data is likely to be much less than that associated with sending the data over the Web, and because there'll probably be less data the serialization and deserialization is also likely to be quicker, so if performance is an issue you probably should avoid using DataSet objects in this way — unless of course you will be making use of the additional functionality that DataSet objects make available to you.

For the purposes of this example, though, using a DataSet object is not a problem and greatly simplifies other code.

The Event-Booking Client

The client you use in this section is a development of the PCSDemoSite Web site from the previous chapter. Call this application PCSDemoSite2, in the directory C:\ProCSharp\Chapter28, and use the code from PCSDemoSite as a starting point.

You'll make two major modifications to the project. First, you'll remove all direct database access from this application and use the Web service instead. Second, you'll introduce an application-level store of the DataSet object returned from the Web service that is updated only when necessary, meaning that even less of a load is placed on the database.

The first thing to do to your new Web application is to add a Web reference to the PCSWebService2/ MRBService.asmx service. You can do this in the same way you saw earlier in this chapter through right-clicking on the project in Server Explorer, locating the .asmx file, calling the Web reference MRBService, and clicking Add Reference. Because you aren't using the local database anymore you can also delete that from the App_Data directory, and remove the MRBConnectionString entry from Web.config. All the rest of the modifications are to MRB.ascx and MRB.ascx.cs.

To start with, you can delete all the data sources on MRB.ascx, and remove the DataSourceID entries on all the currently data-bound controls. This is because you'll be handling the data binding yourself from the code-behind file.

Note

Note that when you change or remove the DataSourceID property of a Web server control you may be asked if you want to remove the templates you have defined, because there is no guarantee that the data the control will work with will be valid for those templates. In this case you'll be using the same data, just from a different source, so make sure you keep the templates. If you do delete them the HTML layout of the result will revert to the default, which won't look very nice, so you'd have to add them again from scratch or rewrite them.

Next, you'll need to add a property to MRB.ascx.cs to store the DataSet returned by the Web service. This property will actually use Application state storage, in much the same way as Global.asax in the Web service. The code is as follows:

 public DataSet MRBData { get { if (Application["mrbData"] == null) { Application.Lock(); MRBService.MRBService service = new MRBService.MRBService(); service.Credentials = System.Net.CredentialCache.DefaultCredentials; Application["mrbData"] = service.GetData(); Application.UnLock(); } return Application["mrbData"] as DataSet; } set { Application.Lock(); if (value == null && Application["mrbData"] != null) { Application.Remove("mrbData"); } else { Application["mrbData"] = value; } Application.UnLock(); } } 

Note that you need to lock and unlock the Application state, also just like in the Web service. Also, note that the Application["mrbData"] storage is filled only when necessary, that is, when it is empty. This DataSet object is now available to all instances of PCSDemoSite2, meaning that multiple users can read data without any calls to the Web service or indeed to the database. The credentials are also set here, which as noted earlier is necessary for using Web services hosted using the ASP.NET Development Server. You can comment out this line if you don't need it.

To bind to the controls on the Web page, you can supply DataView properties that map to data stored in this property, as follows:

 private DataView EventData { get { return MRBData.Tables["Events"].DefaultView; } } private DataView RoomData { get { return MRBData.Tables["Rooms"].DefaultView; } } private DataView AttendeeData { get { return MRBData.Tables["Attendees"].DefaultView; } } private DataView EventDetailData { get { if (EventList != null && EventList.SelectedValue != null) { return new DataView(MRBData.Tables["Events"], "background-color:#c0c0c0">EventList.SelectedValue.ToString(), "", DataViewRowState.CurrentRows); } else { return null; } } } 

You can also remove the existing eventData field and EventData property.

Most of these properties are simple; it's only the last that does anything new. In this case, you are filtering the data in the Events table to obtain just one event — ready to display in the detail view FormView control.

Now that you aren't using data source controls, you have to bind data yourself. A call to the DataBind() method of the page will achieve this, but you also need to set the data source DataView properties for the various data-bound controls on the page. One good way to do this is to do it during an override of the OnDataBinding() event handler, as follows:

 protected override void OnDataBinding(EventArgs e) { roomList.DataSource = RoomData; attendeeList.DataSource = AttendeeData; EventList.DataSource = EventData; FormView1.DataSource = EventDetailData; base.OnDataBinding(e); } 

Here you are just setting the DataSource properties of roomList, attendeeList, EventList, and FormView1 to the properties defined earlier. Next, you can add the DataBind() call to Page_Load():

void Page_Load(object sender, EventArgs e) {    if (!this.IsPostBack)    {       nameBox.Text = Context.User.Identity.Name;       System.DateTime trialDate = System.DateTime.Now;       calendar.SelectedDate = GetFreeDate(trialDate); DataBind();    } }

Also, you must change submitButton_Click() to use the Web service AddData() method. Again, much of the code can remain unchanged; only the data addition code needs changing:

void submitButton_Click(object sender, EventArgs e) {    if (Page.IsValid)    {       string attendees = "";       foreach (ListItem attendee in attendeeList.Items)       {          if (attendee.Selected)          {             attendees += attendee.Text + " (" + attendee.Value                          + "), ";          }       }       attendees += " and " + nameBox.Text;       try       { MRBService.MRBService service = new MRBService.MRBService(); if (service.AddEvent(eventBox.Text, int.Parse(roomList.SelectedValue), attendees, calendar.SelectedDate) == 1) { MRBData = null; DataBind();             calendar.SelectedDate =               GetFreeDate(calendar.SelectedDate.AddDays(1)); }       }       catch       {       }    } }

In fact, all you've really done here is simplify things a great deal. This is often the case when using well- designed Web services — you can forget about much of the workings and instead concentrate on the user experience.

There isn't a huge amount to comment on in this code. Continuing to make use of queryResult is a bonus, and locking the application is essential as already noted.

One final modification is required: EventList_SelectedIndexChanged():

void EventList_SelectedIndexChanged(object sender, EventArgs e) { FormView1.DataSource = EventDetailData; EventList.DataSource = EventData; EventList.DataBind(); FormView1.DataBind(); }

This is simply to make sure that the data sources for the event list and detail views are refreshed properly.

The meeting room booker in the PCSDemoSite2 Web site should look and function exactly like the one in PCSDemoSite, but perform substantially better. You can also use the same Web service very easily for other applications — simply displaying events on a page, for example, or even editing events, attendee names, and rooms if you add some more methods. Doing this won't break PCSDemoSite2 because it will simply ignore any new methods created. You will, however, have to introduce some kind of trigger mechanism to update the data cached in the event list, because modifying this data elsewhere will cause data to go out of date.