Extending the Meeting Room Booker Example

 

Chapter 15 - Web Services
bySimon Robinsonet al.
Wrox Press 2002

  

Now we know the basics of creating and consuming Web Services, let's apply our knowledge to extending the meeting room booker application from the last chapter. Specifically, we will extract the database access aspects from the application and place them into a Web Service. This Web Service will have two methods :

• GetData() , which will return a DataSet containing all three tables in the PCSWebApp3.mdb database

• AddEvent() , which will add an event and return an updated version of the DataSet that includes the change

In addition, we'll design the Web Service with some of the load-reducing techniques from the last chapter in mind. Specifically, we will store the DataSet at the application level in the Web Service application. This means that multiple requests for the data won't require additional database requests to work. The data in this application-level DataSet 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 this DataSet . Still, as long as we know that our Web Service is the only thing with direct access to the data we have nothing to worry about.

The Meeting Room Booking Web Service

Create a new Web Service project in VS .NET called PCSWebSrv2 . The first thing we'll add to this project is some code in the Application_Start() handler in Global.asax.cs . We want to load all the data in PCSWebApp3.mdb into a dataset and store it. This will mostly involve code that we've already seen, as getting the database into a DataSet is something we've already done. In fact, we can copy all the code we need from WebForm1.aspx.cs in PCSWebApp3 from the last chapter - including the database connection string in InitializeComponent() (which we won't show here because yours is likely to be different) - with only a few modifications:

   protected void Application_Start(Object sender, EventArgs e)     {     System.Data.DataSet ds;     System.Data.OleDb.OleDbConnection oleDbConnection1;     System.Data.OleDb.OleDbDataAdapter daAttendees;     System.Data.OleDb.OleDbDataAdapter daRooms;     System.Data.OleDb.OleDbDataAdapter daEvents;     oleDbConnection1 = new System.Data.OleDb.OleDbConnection();     oleDbConnection1.ConnectionString = @" ... ";     oleDbConnection1.Open();     ds = new DataSet();     daAttendees = new System.Data.OleDb.OleDbDataAdapter(     "SELECT * FROM Attendees", oleDbConnection1);     daRooms = new System.Data.OleDb.OleDbDataAdapter(     "SELECT * FROM Rooms", oleDbConnection1);     daEvents = new System.Data.OleDb.OleDbDataAdapter(     "SELECT * FROM Events", oleDbConnection1);     daAttendees.Fill(ds, "Attendees");     daRooms.Fill(ds, "Rooms");     daEvents.Fill(ds, "Events");     oleDbConnection1.Close();     Application["ds"] = ds;     }   

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

In order for the above code to work we also need to add a reference to the System.Data namespace to Global.asax.cs :

 ...   using System.Data;   

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

Next we need to add the GetData() method to our service in Service1.asmx.cs :

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

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

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

• Accept event data from the client

• Create a SQL INSERT statement using this data

• Connect to the database and execute the SQL statement

• If the addition is successful then refresh the data in Application["ds"]

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

Starting from the top, we'll accept all fields as strings:

   [WebMethod]     public int AddEvent(String eventName, String eventRoom,     String eventAttendees, String eventDate)     {     }   

Next we declare the objects we'll need for database access, connect to the database, and execute our query, all using similar code to that in PCSWebApp3 (again, we need the connection string here, but we won't show it):

 [WebMethod]       public int AddEvent(String eventName, String eventRoom,                           String eventAttendees, String eventDate)       {   System.Data.OleDb.OleDbConnection oleDbConnection1;     System.Data.OleDb.OleDbDataAdapter daEvents;     DataSet ds;     oleDbConnection1 = new System.Data.OleDb.OleDbConnection();     oleDbConnection1.ConnectionString = @" ... ";     String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," +     " EventDate) VALUES ('" + eventName + "', '" +     eventRoom + "', '" + eventAttendees + "', '" +     eventDate + "')";     System.Data.OleDb.OleDbCommand insertCommand =     new System.Data.OleDb.OleDbCommand(oleDbCommand,     oleDbConnection1);     oleDbConnection1.Open();     int queryResult = insertCommand.ExecuteNonQuery();   } 

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

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

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

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

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

The Meeting Room Booker Client

The client we'll use will be a development of the PCSWebApp3 web application from the last chapter. We'll call this application, unsurprisingly enough, PCSWebApp4 , and use the code from PCSWebApp3 as a starting point.

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

The first thing to do to our new web application is to add a web reference to the PCSWebSrv2/Service1.asmx service. We can do this in the same way we saw earlier in the chapter through right-clicking on the project in Server Explorer, locating the .asmx file, selecting Add Reference , and calling the folder eventDataService .

The first thing we'll do, then, is to add code to Global.asax.cs in much the same way as we did for our Web Service. This code, though, is a lot simpler. First we reference the Web Service and the System.Data namespace:

 ...   using System.Data;     using PCSWebApp4.eventDataService;   

Next we fill a DataSet and place it into an application-level data store called ds :

 protected void Application_Start(Object sender, EventArgs e)       {   Service1 dataService = new Service1();     DataSet ds = dataService.GetData();     Application["ds"] = ds;   } 

This DataSet is now available to all instances of PCSWebApp4 , meaning that multiple users can read data without any calls to the Web Service, or indeed to the database.

Now we have this DataSet we need to modify WebForm1.aspx.cs to use it. The first thing that we can do is remove the declarations of oleDbConnection1 , daAttendees , daRooms , and daEvents , as we won't be performing any database access. We can also remove the initialization code for oleDbConnection1 , found in InitializeComponent() . Next we need to add a using statement for PCSWebApp4.eventDataService , as we did for Global.asax.cs , and change Page_Load() as follows :

 private void Page_Load(object sender, System.EventArgs e)       {   ds = (DataSet)Application["ds"];   attendeeList.DataSource = ds.Tables["Attendees"];          roomList.DataSource = ds.Tables["Rooms"];          eventTable = ds.Tables["Events"];          eventDetails1.DataSource = eventTable;          eventDetails2.DataSource = eventTable;          if (!this.IsPostBack)          {             System.DateTime trialDate = System.DateTime.Now;             calendar.SelectedDate = getFreeDate(trialDate);             this.DataBind();          }          else          {             eventDetails1.DataBind();             eventDetails2.DataBind();          }       } 

Most of the code remains the same, all we need to do is to use Application["ds"] instead of getting the DataSet ourselves .

We also need to change submitButton_Click() to use the Web Service AddData() method. Again, much of the code remains unchanged:

 private void submitButton_Click(object sender, System.EventArgs e)       {          if (this.IsValid)          {             String attendees = "";             foreach (ListItem attendee in attendeeList.Items)             {                if (attendee.Selected)                {                   attendees += attendee.Text + " (" + attendee.Value + "), ";                }             }             attendees += " and " + nameBox.Text;             String dateString =                        calendar.SelectedDate.Date.Date.ToShortDateString();   Service1 dataService = new Service1();     int queryResult = dataService.AddEvent(eventBox.Text,     roomList.SelectedItem.Value,     attendees,     dateString);   if (queryResult == 1)             {                resultLabel.Text = "Event Added.";   ds = dataService.GetData();     Application.Lock();     Application["ds"] = ds;     Application.UnLock();   eventTable = ds.Tables["Events"];                calendar.SelectedDate =                               getFreeDate(calendar.SelectedDate.AddDays(1));   eventDetails1.DataSource = eventTable;   eventDetails1.DataBind();   eventDetails2.DataSource = eventTable;   eventDetails2.DataBind();             }             else             {                resultLabel.Text = "Event not added due to DB access problem.";             }          }       } 

In fact, all we've really done is simplify things a great deal. This is often the case when using well-designed Web Services - we 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.

The PCSWebApp4 web application should look and function exactly like PCSWebApp3 , but perform substantially better. We can also use the same Web Service for other applications very easily - simply displaying events on a page, for example, or even editing events, attendee names , and rooms if we add some more methods. Doing this won't break PCSWebApp4 as it will simply ignore any new methods created.