Redesign your coded InfoPath 2010 Form to handle 1000’s of records

Scenario

Let’s say you have an InfoPath form with Code Behind where you want to have the user paste in thousands of Job Codes or Organization Codes or Cost Codes into a text box, and then they would press a button and it would validate those records – the Codes – against a System of Record (like a list in SharePoint) and then add the records into a Repeating Table in your InfoPath form.  You want to do this because your Codes have metadata associated with them that you either want to display and/or have the user update, and you want to have conditional formatting and buttons on each record to make it look and function the way you want it to.

You will quickly find that doing this in a Web Browser-enabled form is near to impossible, requires too many configuration settings to change in various places, and performs really sluggishly any time you take an action on the form like clicking a checkbox or pressing a button that only runs rules.  You’ll run into InfoPath settings in Central Admin that you have to configure, and you’ll run in to the dreaded 6-minute default timeout of IIS pages waiting for responses from the server.

The first step I took, on the advice of Doug my co-worker, Architect extraordinaire, was to offload all of the individual item processing – the validation against the SOR – off to a custom SOAP web service.  This did the heavy lifting – I passed in a comma-separated string of all of the Codes (which is why it had to be a SOAP service and not a REST service – too much data to pass in a URL), and then it validated the codes.  Finally, I had the web service create an XML Document with exactly the structure of my repeating table in InfoPath, fill it in with all of the Codes and their metadata, and pass that Outer XML as a string back to the form.  I used XPathNavigator to then insert the XML directly into the form.

What I found, through constructing a timing debug string, was that my Web Service call was taking 2 – 4 seconds.  Inserting the XML and waiting for the XML to render into HTML was taking 4 – 10 minutes, depending on how many codes I had.  It was completely unacceptable as I had clients who needed to put in 2000 codes.  My form got sluggish at 300 and outright unusable over 700.

Solution

So I went back to Doug, which is what I usually do when I’m stuck on a problem.  I explained where the bottleneck was and confirmed my suspicions by reading up on Performance and InfoPath on a series of 6 Microsoft blogs.  Doug said, since the bottleneck is the size of the XML in the form, you have to get the XML out of the form, into some other storage mechanism.  Allow the form to hold no more than 100 items at a time in the repeating table, and implement Paging of sorts to get at the other 1900 records.

Wow, sounded like a great idea so I tried it, and so far it seems to be working.  Because I’m getting paid to do this, I can’t give you all of the code.  However, here’s the general steps you will need to take in order to accomplish this idea:

1. Create a SOAP Web Service.  I had done this before and put it in ISAPI, but you have to do some extra manual work to get it to work there.  I found that if you add it directly to a subfolder under the Layouts directory, you can call it from there without any extra work.  The Web Service will need to have 4 methods or so:
   1. AddSQLDataGetPageXML(string identifier, int pageNum, string codes, string extraMetadataIfNecessary, string xmlChanges, string ns)
      This method is responsible for
      1. (a) getting existing data out of SQL for that identifier, or creating a new identifier if none exists;
      2. (b) copying the existing data, if any, into a  Generic List of custom objects representing your Code and its metadata;
      3. (c) processing  any changes that the customer has made to the repeating table of the 100 or so items that are showing (you need to capture your changes before blowing away and recreating the 100 items);
      4. (d) doing the actual Validation of the Code against your SOR;
      5. (e) adding the valid (and Invalid) codes to both the Generic List, as well as adding the record to the SQL table;
      6. (f) sorting your Generic List by RowNum or some kind of numbering identifier that you have added to the custom object;
      7. (g) iterating through the entire Generic List, gathering statistics of things like how many are invalid, how many total, etc., and taking only the ones in “that page” and turning them into an XML Element that you insert into a generic XmlDocument object that you created at the top of the method (using the ns parameter which is the namespace of the “my” prefix of your InfoPath form);
      8. (h) and finally returning a custom Return Object that holds both the statistics that you wanted to capture, as well as the XML which is the OuterXML of the xml document.
   2. GetPageXML(string identifier, int pageNum, string xmlChanges, int oldPageNum, string ns)
      This method is responsible for
      1. (a) getting existing data out of SQL for that identifier;
      2. (b) copying the existing data, if any, into a  Generic List of custom objects representing your Code and its metadata;
      3. (c) processing  any changes that the customer has made to the repeating table of the 100 or so items that are showing (you need to capture your changes before blowing away and recreating the 100 items) – NOTE HERE – this has to handle deletions too, so you need some special manipulation of comparing what is (or rather is NOT) in the XML string of changes, versus what IS in your Generic List that you read out of SQL;
      4. (d) sorting your Generic List by RowNum or some kind of numbering identifier that you have added to the custom object;
      5. (e) iterating through the entire Generic List, gathering statistics of things like how many are invalid, how many total, etc., and taking only the ones in “that page” and turning them into an XML Element that you insert into a generic XmlDocument object that you created at the top of the method (using the ns parameter which is the namespace of the “my” prefix of your InfoPath form);
      6. (f) and finally returning a custom Return Object that holds both the statistics that you wanted to capture, as well as the XML which is the OuterXML of the xml document.
   3. ProcessChanges(string identifier, string xmlChanges, string ns, int startItem, int stopItem, SqlConnection conn, List<YourCustomCodeObject> allCodesFromSQL)
      This is the method that you call from the first 2 methods to process your existing changes.  Remember, if the user changes something on the current page, and then you switch to another page, you need to make sure that you capture what has been changed on the current page.
   4. ReValidateCode(string code, string otherMetadataHere)
      This method is responsible for
      1. (a) reading in the code and finding it / validating in against your SOR;
      2. (b) creating a custom Return Class that can hold your Code’s data and metadata, and whether or not it’s valid;
      3. (c) returning the custom Return Class (your InfoPath form handles the changing of the repeating table item in the repeating table)
2. Hook this up to your InfoPath Form
   1. There’s too much involved to go into here, but you need to basically do the following:
      1. Have a SOAP web service Data Connection, and methods to set values into and Execute that connection.
      2. Have a text box to paste the Codes into, and a method to take those codes, wrap them up and send it to the SOAP web service
      3. Have a repeating grid with all of your fields, metadata fields, buttons (such as ReValidate), and conditional formatting
      4. Have buttons for Previous and Next that have conditional formatting on them to only show if it is valid to show them (i.e. don’t show Previous if you’re on page 1)
      5. Have a MoveToPage method that gets called from the buttons and optionally some other places (like a delete all on this page kind of method)
3. Create your Workflow (a custom Console Application probably) that handles the business processes of the form
   It will do the following:
   1. Loads in the InfoPath form, which you have created a class for using the XSD of the InfoPath Form (Export to Source Files to find the XSD)
   2. Checks to see if it’s already processed
   3. Looks for the Identifier in the Form that got stored when you were using the form
   4. Looks in SQL using the Identifier to get all the records
   5. Processes the records
   6. Deletes the records out of SQL