18.3. Looking at the Boiler Web Service

The Boiler Web service is responsible for handling all transactions between the Access database and the client application, in this case InfoPath. It was created using the XML Web services along with Visual Studio .NET 2003 and C#, as outlined in Chapter 15, "Creating and Working with Web Services."

• Start by creating a new C# ASP.NET Web service using Visual Studio

• Name the new Web service BoilerWebService

• Change the name of Service1.asmx, to DataService.asmx (make sure you open the .cs file and change the name of the class to DataService as well, shown here in Figure 18-3

  Figure 18.3. Figure 18-3

  

18.3.1. Creating the Data Adapters

The ADO.NET OleDbDataAdapters, OleDbDataConnection, and datasets of the System.Data namespace of the .NET Framework were used to create the middle tier of the solution. These components handle all of the interaction between InfoPath and the Access database created for the solution.

OleDbDataAdapters have been created to query the database for specified information. Three were created to handle the lookup tables we have created in the database: daBoilers, daShifts, and daOperators. Two other data adapters were created for daBoilerLog and daSystemLog. You can see all the data adapters in Figure 18-4, along with a oleDbConnection that was created.

Figure 18.4. Figure 18-4

18.3.2. Creating the Datasets

The next task was to create the datasets that hold the data for the client application. This was started by creating the dataset that holds the lookup tables. This is a fairly straightforward design-time task and a good place to start with datasets. Using the Generate dataset from the main Visual Studio tool menu, the dataset was created as shown in Figure 18-5.

This dataset was named dsLookups with only the Boilers, Operators, and Shifts tables are added. A repository for the three data adapters that we are using to retrieve the data from the database.

Two more datasets were created using a slightly different approach because they contain relations to other tables as well as hold data.

Figure 18.5. Figure 18-5

18.3.3. Creating Typed Datasets

These next two datasets are a more advanced type of dataset. These two will by typed datasets that have relationships in them. This will allow you to have one dataset that holds multiple tables that relate to each other.

Using the Solution Explorer, a new DataSet object is added to the project, and named BoilersOperatingLog. Once the dataset is opened, the tables BoilersOperationLog, Boilers, Shifts, and Operators are dragged onto the design surface of the dataset.

A relation object is then dragged onto the BoilerID field in the BoilersOperationLog table in the dataset. The relationship editor inside VS opens. A relationship between the BoilerID field in the BoilersOperationLog table and the Boilers table is then created, as shown in Figure 18-6.

Relationships between ShiftID, OperatorID, and their respective tables have also been created. The last dataset created for this solution was generated using the same methods used to create the BoilerOperatingLog dataset. Two datasets were created to work with the schema of the database.

Figure 18.6. Figure 18-6

18.3.4. Exposing the Datasets

After creating the datasets to work with, you need to add the code necessary to expose these objects to calling applications using a Web service.

• In the DataService.asmx.cs code file a Web method was created to return the dataset dsLookups from the Web service (and database) that contains the three lookup tables.

• At the top of the code file you will add the following using statement:

  using System.Data.OleDb

  
  

• The Web method that follows has three data adapters that go out to the database and fetch all the records to fill up this dataset. The dataset is a container that has the ability to hold many tables and relationships.

  [WebMethod]
  public System.Data.DataSet ReturnLookups()
   {
        OleDbDataAdapter daBoilers = new OleDbDataAdapter("Select * From
                Boilers",this.oleDbConnection1);
        OleDbDataAdapter daShifts = new OleDbDataAdapter("Select * From Shifts",

  
  

  this.oleDbConnection1);
        OleDbDataAdapter daOperators = new OleDbDataAdapter("Select * From
                Operators", this.oleDbConnection1);
        dsLookups ds = new dsLookups();
        daBoilers.Fill(ds,"Boilers");
        daShifts.Fill(ds,"Shifts");
        daOperators.Fill(ds,"Operators");
  
        return ds;
  
   }

  
  

  Now that the Web method to expose the lookup tables has been described, the InfoPath template will be opened and the datasource added to the form.

• Using the Data Connection Wizard a connection to the Web service is created. Because the only method written thus far returns a dataset, something InfoPath is familiar with, you are allowed to use this in the InfoPath document.

• Be sure that you select Receive Data, from a Web Service. http://localhost/BoilerWebService/DataService.asmx should be the name of your Web service, type that in the text box, you'll notice right away that the Web method we created earlier shows up in the box on the left, as shown in Figure 18-7:

  Figure 18.7. Figure 18-7

  

• This connection was named ReturnLookups.

• During design time in the InfoPath document, three drop-down list box controls were dragged onto the InfoPath form.

• The Web service is used to retrieve the data needed to fill these controls, following the necessary steps to bind the drop-down list box controls to the WebServices dataset.

18.3.5. Inserting Data

Going back to Visual Studio to add another Web method will allow us to submit data from InfoPath, displayed here:

[WebMethod]
public void InsertNewBoilerLog(int BoilerID, DateTime LogDate, DateTime LogTime, int ShiftID, int OperatorID, string SteamDrumStatus, int SteamDrumPSIG, int Excess, int SteamLoad, int SteamCycles, string Comments)
{
    string sql = "Insert into BoilersOperationLog(BoilerID, LogDate, LogTime,
          ShiftID,OperatorID,SteamDrumStatus, SteamDrumPSIG, Excess,
           SteamLoad,SteamCycles, Comments) values (" + BoilerID + ", " + LogDate +
           ", " + LogTime + ", " + ShiftID+ ", " + OperatorID + ", " +
           SteamDrumStatus + ", " + SteamDrumPSIG + ", " + Excess + ", " +
           SteamLoad + ", " + SteamCycles + ", " + Comments + ");";
     System.Data.OleDb.OleDbCommand cmd = new
           System.Data.OleDb.OleDbCommand(sql,this.oleDbConnection1);
     cmd.Connection.Open();
     cmd.ExecuteNonQuery();
     cmd.Connection.Close();
}

Examine the preceding code, notice that the parameters of the Web method directly reflect the fields in the table. The next step is to go back to InfoPath and create the DataConnection used to submit data from InfoPath to this Web service.

18.3.6. Submitting from InfoPath

Switching back to InfoPath to set up the document for submitting its data, we are adding a new data connection to submit data. On the first screen of the wizard, select Submit Data. On the Data Connect dialog box that asks for the Web address of the Web service to use, the following is entered: http://localhost/BoilerWebService/DataService.asmx.

Under Select an Operation, there are now two Web methods exposed to you. For submitting data, the InsertNewBoilerLog method has been used. At one point in the wizard, the ability to match up the parameters of the Web method with the data fields on the InfoPath form is given, as shown here in Figure 18-8.

All of the Web method parameters are matched up with the elements on the InfoPath form. The default name of "Submit"' is left as the name of this data connection.

Figure 18.8. Figure 18-8

18.3.7. Submitting Forms

The last task is to configure the way that InfoPath submits data from the form. Select Submitting Forms... from the Tools menu; the Enable Submit check box is checked. Select the Web service from the drop-down list, and select the Submit data connection for the submit property. Last, check Enable submit on the file menu. The form is now set up to utilize your Web service to submit data.

Then add a button to the form, with the Submit property set.

18.3.8. Situation

The reason for creating this application was that an automotive manufacturing plant needed a way to reduce the paper workflow currently used to collect boiler operation information. There are six boilers spread out around the facility, and this requires constant supervision and safety checks.

Previously the inspectors used a paper-based solution. The inspector would fill out the paper form and then pass it on to someone else to proof read before manually entering the data into the database.

This posed many problems, as you might have guessed. It was becoming more and more difficult to keep track of how the boilers were operating as a whole and which boilers were last inspected and if any work was done to them or not.