Submitting to a Database (via Web Services) in InfoPath Forms Services

If you've ever designed and deployed a form that will be frequently used or require significant data analysis, you have probably looked into maintaining data in a SQL database.  With InfoPath 2003 and the InfoPath 2007 rich client, you get what you expect.  You create a main database data connection to the SQL server and pick tables and columns that meet the requirements for submit functionality.  When you open your form and click the "Run Query" button, you see the data pulled from the database as you'd expect.  You then happily insert, update, or delete records and, when the time is right, hit "Submit".  Luckily for you, the InfoPath client took care of maintaining the list of changes that you made while editing your form.  With this list of changes intact, your updated data streaks back to the database to await the next query.

Enter InfoPath Forms Server... Here we don't get the change tracking for free, so we'll need to do some work to simulate change tracking outside of the form's execution.  Basically, what we're going to try to accomplish is to use an intermediate web service that will handle querying and submitting the data from and to the target database.  The web service will timestamp the data at query time and send the data to the form for editing.  Then the form filling user will edit the data and click "Submit".  When the data arrives back at the web service, we need to figure out what changed in the meantime.  This means that we'll have to check to see if anything has changed in the database since the time when we queried the data.  If it has, then the submitted data should be rejected and the user should re-query before re-applying her edits.  If it hasn't, we'll diff the submitted data with the database data and submit the difference back to the database!  Let's get started!

Create the Web Service and Setup the Database

Since the InfoPath data connection wizard is easiest to use when your web service is already established and available, let's start with creating the web service and setting up the database.

1) Download the attached archive and extract it somewhere on your hard drive

2) Create a new web site in Internet Information Services (IIS)
NOTE:  IIS must be enabled as a Windows Component through "Add or remove Windows components" in the "Control Panel")

• Launch IIS ("Start" >> "Run", type 'inetmgr')
• Right-click the "Web Sites" node and select "New" >> "Web Site…"
• Click "Next >" on the first page of the wizard.
• Type a name for your web site (e.g., "IPFSDiffGram") and click "Next >"
• Type a unique port number (referred to, hereafter, as '<portNum>') for the web site and click "Next >"
• Enter the path to a folder where the site contents will be stored and click "Next >"
• Check "Read" and "Run scripts (such as ASP)" permissions and click "Next >"
• Click "Finish".
• You may want to create a new Application Pool for this web site "to make your server more efficient and reliable".
  

3) Create the web service

• Launch Visual Studio 2005.
• Click "File" >> "New" >> "Web Site…"
• Select "ASP.Net Web Service" in the "Visual Studio Installed Templates" section.
• In the "Location" drop-down, select HTTP and then click "Browse…"
• Select the Web Site created in step 1 and then click "Open"
• Click "OK" on the "New Web Site" dialog
  

4) Add the code and service asmx files to the project

1. Open the Solution Explorer task pane (click "View" >> "Solution Explorer")
2. Right-click the top-level project icon (labeled http://localhost:<portNum>) and select "Add Existing Item…"
3. Navigate to the "DiffGramService.asmx" file and then click "Add". Refer to the files you downloaded for the contents of this file.
4. Right-click the "App_Code" icon and select "Add Existing Item…"
5. Navigate to the "DiffGramService.cs" file and then click "Add". Refer to the files you downloaded for the contents of this file.

5) Customize the web service code for your database

1. Instance variables
   1. DBConnectionString -- The connection string to connect to your database.
   2. DBTable -- The name of the table to which the web service should connect.  The table must feature a surrogate single-column primary key with an integer data type.
   3. ColumnNames -- An array that contains the names of the data columns in your database.
      
2. WebMethod query parameters

   1. DBData(parameter_list)

      • parameter_list should be the columns, with appropriate System.Types to match your database
      • You'll need to specify the appropriate SqlDbTypes that correspond to the columns in your database
        

   2. UpdateDBData(DataSet, parameter_list)

      • Do not change the DataSet parameter
      • parameter_list should be the columns, with appropriate System.Types to match your database
      • You'll need to specify the appropriate SqlDbTypes that correspond to the columns in your database

6) Create the database table and DML trigger
The web service includes logic to update the database table and create a DML trigger to maintain a timestamp of Last Update for each record.  However, you may want to create the timestamp column and trigger yourself.

• Example SQL script to create a database named "DBTable" that is compatible with this web service is in the attached files, named "CreateDBTable.sql". The table will have a structure similar to the following:

• Example SQL script to create a DML trigger that will maintain the timestamp of last update for each record is also attached, and called "CreateDMLTrigger.sql".

7) Build the Visual Studio solution and publish the web site

Design the InfoPath Form Template

Now that we've setup our database and constructed our web service to do the querying and submitting for us, it'll be a breeze to design an InfoPath form template based on the web service.

1) Design a new, browser-enabled form template, based on the web service that will query/submit the DataSet.

1. Launch InfoPath and select "Design a Form Template…" on the "Getting Started" dashboard dialog.
2. Check "Enable browser-compatible features only" (required only for browser-enabled form templates)
   
3. Select the "Web Service" icon and click "OK".
4. Select "Receive and submit data" and click "Next >"
5. Type the WSDL URL for your web service (e.g., http://<server>:<port>/DiffgramService.asmx?WSDL) and click "Next >"
6. Select the web method that will return the DataSet and click "Next >".
7. Click "Next >" on the DataSet change-tracking page.
8. Click "Next >" to name the query data connection and move on to define the submit data connection.
9. If the submit WSDL URL is different than the query WSDL URL, enter it here and click "Next >".  Else, just click "Next >".
10. Select the web method to which the DataSet will be submitted and click "Next >".
11. Select the DataSet parameter in the "Parameters" listView.
12. In the "Parameter options" section, click the button next to the "Field or group" field.
13. In the "Select a Field or Group" dialog that pops up, expand the "dataFields" node until you see the node with the name of the DataSet (it's the parent of the repeating "(Choice)" node).

    

     

14. Select the DataSet node and click "OK"
15. If your web service takes parameters to constrain the select command, map these parameters to the queryFields generated during the creation of the query connection.
16. Click "Next >" on the submit parameter mapping page.
17. Click "Finish" to name the submit data connection and finish the Data Connection Wizard.

2) Set the default values for the "ID" and "QueryTime" fields

1. In the InfoPath Designer window, click the "View" menu and select the "Data Source..." option.
2. In the "Data Source" taskpane, expand the "dataFields" node completely.
3. Double-click the "QueryTime" node to open the properties dialog.
4. Type "1/1/1900 12:00:00 AM" (without the quotes) in the "Value" field in the "Default Value" section and click "OK"
5. Repeat steps 2.a-2.c for the "ID" field.
6. Type "-1" (without the quotes) in the "Value" field in the "Default Value" section and click "OK"

3) Insert the controls into the View.

1. Click inside the dataFields controls area (it says "Drag data fields here" if you haven't clicked there)
2. Click the "Insert" menu and select the "Repeating Table..." option.
3. In the "Repeating Table Binding" dialog, completely expand the "dataFields" node.
4. Select the group node that has the same name as your database table and click "Next >"
5. For each of your data columns (e.g., 'Column1', ..., 'Column5'), select the column name on the left, and then click the "Add >>" button to make the column show up in the Repeating Table.
   
   NOTE:  If you include the 'ID' column, the user will be able to edit the values, and the DataSet may not be validated or merged correctly.
6. Click "Finish" to accept the repeating table binding and insert the repeating table into the View.

4) Publish the form template to your InfoPath Forms Server

At this point, you have a form template that will work correctly when you open it in the InfoPath rich client.  But this post is all about getting things to work correctly in the InfoPath Forms Server.  So you'll need to configure your data connections to work in the browser by converting the main query and submit data connections to use Universal Data Connection (UDC) files in a Data Connection Library (DCL).  Now you should be all set.  The web service will query and submit the data to the database, and we'll make our best attempt at a diff of the database data against the submitted data.

From here on out, it's up to you.  If you want to, for example, modify the database structure or change the way the trigger works, then you're going to need to modify the web service code.  You'll also need to use "Convert Main Data Source" to update your form template whenever you modify your web service.  You might also want to add support for multiple tables.  All this will take some exploration of ADO.Net DataSets, but it is a reasonable exercise as long as you're comfortable writing managed code.

Forrest Dillaway
Software Design Engineer in Test