The Joy, Blood, Sweat And Tears Of InfoPath 2007

InfoPathI recently completed a project based on InfoPath 2007 (Office client version) and Microsoft Office SharePoint Services 2007 (MOSS 2007). Looking back I can say that InfoPath has its uses, but before you build a solution around it you have to be very sure about its limitations. InfoPath has a number of limitations, especially with regards to submitting data, that aren’t that apparent at first sight. If you don’t watch out, you can quickly get caught up in what feels like a never ending maize of dead ends.

InfoPath is often pitched as a solution that doesn’t require writing custom code. This project was no different, and its time lines were made accordingly. In the end we had to write a fair amount of custom code, which was fun, but took more time.

The Many Limitations Of InfoPath DataBase Data Source

InfoPath generally works well viewing standard enterprise data sources such as database tables or SharePoint lists. The limitations become apparent when you attempt to submit to a database using an InfoPath SQL connection, or perform advanced queries. There are a number of limitations when you work with an InfoPath SQL database data source/connection:

  1. Only submit to a single table. This excludes database data sources such as views, and queries with joins. You cannot submit to views or SQL DataSources with joins.
  2. To submit to a database you can only use the main data connection. In other words you can’t have a database-view as the main data source, and setup another simple single table select to submit to.
  3. Range queries are not possible. You can only use a field once in a query’s WHERE clause with an equality operator.
  4. SQL data source dependent on table schema. If a SQL data source’s underlying table is modified, even just adding a column (in other words InfoPath’s SELECT statement doesn’t actually change), the data source will break.
Data Connection Wizard

Data Connection Wizard

With SharePoint lists you cannot query the data source with queryfields like relational data sources.

The above limitations, especially regarding relational data sources, mean one thing: Web services are mandatory for working with your relational data. Using web services allows you to overcome all the limitations of the standard InfoPath SQL data source, and work with a consistent schema.

Another thing to watch out for is that InfoPath’s performance deteriorates quickly when you have more than 50 rows in your result set. Sometimes this figure is much lower. In the project I worked on the data was coming lightning fast from the data base through the web service. But when the data hits the form, and InfoPath starts parsing the XML document, it completely froze for quite a while. I have decided not to torture myself trying to page my form data, so I haven’t looked into this yet (and I believe InfoPath is not meant to be used in this manner). The quickest and most effective solution I could come up with is to allow users to load data into their form incrementally. How this works is that you’ll do a normal retrieve of your data from the data source, but instead of clearing the form, you’ll just add the new result set to the rest of the form’s data. The big drawback of this is that you need to write custom code to modify the XML document directly using XmlWriter: Not a too pleasant exercise.

public void Load_Clicked(object sender, ClickedEventArgs e)
{
  // Call the web service of the secondary DataSource, which will populate it         
  DataSources["ClientWS2"].QueryConnection.Execute();
  var clients = DataSources["ClientWS2"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult/tns:Client", NamespaceManager);

  // The 1st time rows are added GetClientsResult might not exist, only GetClientsResponse
  var main = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult", NamespaceManager);
  if (main == null) main = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse", NamespaceManager);

  using (XmlWriter writer = main.AppendChild())
  {
    // Make sure we are adding Client elements to /dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult and not, /dfs:myFields/dfs:dataFields/tns:GetClientsResponse
    if (main.LocalName == "GetClientsResponse")
    {
      // So if it doesn't exist, create it first
      writer.WriteStartElement("GetClientsResult", "http://sh.inobido.com/CRM/Service");
    }

    while (clients.MoveNext())
    {
      writer.WriteStartElement("Client", "http://sh.inobido.com/CRM/Service");

      // Select all the client element's child elements
      var fields = proposals.Current.Select("*", NamespaceManager);
      while (fields.MoveNext())
      {
        // Write each element and value to the Main DataSource
        writer.WriteStartElement(fields.Current.Name, "http://sh.inobido.com/CRM/Service");
        writer.WriteString(fields.Current.Value);
        writer.WriteEndElement();
      }

      writer.WriteEndElement();
    }

    if (main.LocalName == "GetClientsResponse)
    {
      writer.WriteEndElement();
    }
    writer.Close();
  }
}

The above event fires when a user clicks the Load button. The trick to load data incrementally is that you need a second DataSource exactly the same as the Main DataSource (they should point to the same data store). Whenever you call DataSource.QueryConnection.Execute() InfoPath will wipe any previous data from that DataSource, and reload it with the new data. That’s why you need a separate second DataSource that you call Execute on, and then copy that data to the Main DataSource. The end result is the Main DataSource doesn’t lose its data, but data gets added to it on each query.

Just another side note on InfoPath: Pivot tables are not possible, because you have to know exactly which columns your binding to at design time, and cannot create columns dynamically at runtime. This shouldn’t be a show stopper to most projects, but I’m just mentioning it. All the InfoPath forms we had to do came from Excel spreadsheets, and the one spreadsheet was a monster pivot table.

Hacking The DataConnection

It’s possible to query a data connection directly from InfoPath, change the SQL command dynamically, or extract the connection string. The biggest drawback of this hack (apart from being a hack, i.e. not recommended) is that it requires FullTrust and Sql Code Access Security (CAS) permissions. That means you have to certify your InfoPath form, or create an installer so users have to install it locally onto their machines. This doesn’t really work well when the form is made available to users through a SharePoint document library.

Anyways, here is a very unrefined sample to achieve this:

private const string CONNECTION_STRING = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;";

private string GetConnectionString(AdoQueryConnection queryConnection)
{
  var password = GetConnectionStringParameter(queryConnection, "Password");
  var user = GetConnectionStringParameter(queryConnection, "User ID");
  var server = GetConnectionStringParameter(queryConnection, "Data Source");
  var db = GetConnectionStringParameter(queryConnection, "Initial Catalog");
  return string.Format(CONNECTION_STRING, server, db, user, password);
}

// Hmmm, if your using my wonderful hack, then you might want to
// consider rewriting this method to use regular expressions instead 😉
private string GetConnectionStringParameter(AdoQueryConnection queryConnection, string name)
{
  var paramIndex = queryConnection.Connection.IndexOf(name + "=");
  var parameter = queryConnection.Connection.Substring(paramIndex, queryConnection.Connection.IndexOf(";", paramIndex) - paramIndex);
  return parameter.Substring(parameter.IndexOf("=") + 1);
}

private IDataReader SelectWorksheetItems(SqlConnection connection, int pocketID)
{
  using (var dbCommand = new SqlCommand("WorksheetItemGetByPocket", connection))
  {
    connection.Open();
    dbCommand.Parameters.Add("@pocketID", SqlDbType.Int).Value = pocketID;
    dbCommand.CommandType = CommandType.StoredProcedure;
    return dbCommand.ExecuteReader();
  }
}

worksheetItemCurrentDS = DataSources["WorksheetItemCurrent"];
worksheetItemCurrentCmd = ((AdoQueryConnection)worksheetItemCurrentDS.QueryConnection).Command + " where \"PocketID\"={0}";

using (var connection = new SqlConnection(GetConnectionString((AdoQueryConnection)worksheetItemCurrentDS.QueryConnection)))
{
  using (var reader = SelectWorksheetItems(connection, 24))
  {
    // Do some stuff with the DataReader here...
  }
}

InfoPath’s different data sources each use a specific data connection that inherits from the abstract class Microsoft.Office.InfoPath.DataConnection. The main point of the above example is that you can cast your InfoPath’s DataConnections to its specific implementation. For SQL database data sources InfoPath uses AdoQueryConnection. With AdoQueryConnection you have the ability to extract or manipulate the data source’s command and connection string.

Using SQL Server To Store InfoPath Documents

You cannot call store procedures directly from InfoPath, but if you develop on SQL Server 2005 or later, you can use SQL Web Services to call stored procedures as a web service. The big catch here is that SQL authentication and SQL Web Services don’t really go well together. Therefore when using SQL authentication for your InfoPath DataConnections you will either have to support integrated authentication for calls coming through the SQL Web Service (and SQL authetication for direct calls from the InfoPath form), or you will have to throw open access to your stored procedure to all users. If your using SQL authentication, there’s usually a good reason your doing so, so additionally supporting integrated authentication might not be an option. Giving access to anyone is an even worse idea.

CREATE ENDPOINT ClientInsertEndpoint
STATE = STARTED
AS HTTP
(
  SITE = 'ServerName',
  PATH = '/WebServiceName',
  AUTHENTICATION = (NTLM),
  PORTS = (CLEAR)
)
FOR SOAP
(
  WEBMETHOD 'ClientInsert'
  (
    NAME = 'DataBase.dbo.ClientInsert',
    SCHEMA = DEFAULT,
    FORMAT = ROWSETS_ONLY
  ),
  WSDL = DEFAULT,
  BATCHES = DISABLED,
  DATABASE = 'DataBase'
)
GO

If is possible to support SQL authentication for SQL Server Web Services, but this requires a SSL server certificate. Microsoft also plans to remove this feature from SQL Server in future releases.

“This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.”

Definitely read Microsoft’s best practices for SQL Server Native XML Web Services.

Finally, you can use SQL Server 2005’s XML data type to save an InfoPath Form or query it in a relational format. Here’s a sample stored procedure that takes in the root node of the InfoPath’s XML document, and inserts the items into a table:

ALTER PROCEDURE [dbo].[ClientUpdate]
(
  @clientsXml XML
)
AS
  INSERT INTO  Client (FirstName,
               LastName,
               CellNo,
               TelNo,
               WorkNo)
SELECT         Clients.Client.query('data(@FirstName)').value('.', 'VARCHAR(25)') as FirstName,
               Clients.Client.query('data(@LastName)').value('.', 'VARCHAR(25)') as LastName,
               Clients.Client.query('data(@CellNo)').value('.', 'VARCHAR(25)') as CellNo,
               Clients.Client.query('data(@TelNo)').value('.', 'VARCHAR(25)') as TelNo,
               Clients.Client.query('data(@WorkNo)').value('.', 'VARCHAR(25)') as WorkNo
FROM           @clientsXml.nodes('declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"; //d:Client') Clients(Client)

The new SQL XML syntax is a bit tricky, but once you get it right it works wonderfully well.

XPath Expressions Are Your Friend

Conditional Format

Conditional Format

Conditional formatting and XPath expressions are very handy to display unique values in a RepeatingTable. For instance say you’ve got a Client object, with multiple Addresses – street, postal, and work. Say you only wanted to show a client’s name once, and list each of his addresses without repeating his name. When you’re using a SQL DataSource, you will do a left join with the address table from the client table. This means you’ll repeat the same client name for each address.

To solve the aforementioned you need to make sure you order by client name, and then hide the textbox with a XPath expression:

tns:ClientName = preceding-sibling::tns:Client/tns:ClientName

What this expression is saying is that if the current Client’s ClientName is the same as the previous Client’s, then do something. That something is the action you’ll check on the Conditional Formatting window, that will be “hide” in our case.

This approach can be extended to multiple fields. All you have to do is make sure your order sequence is correct. So just by ordering your resultset corrrectly and using the right XPath expression, you’ll achieve quite a bit without having to write code.

InfoPath And Visual SourceSafe Does Not Play Well Together

If you’re creating InfoPath forms with Visual Studio Tools for Office and using Visual SourceSafe for source control, you will quickly get a whole range of different and meaningless error messages. Here are the general things to do to resolve them:

  1. Make sure all files in your Visual Studio InfoPath project’s “InfoPath Form Template” directory are checked out, before doing any work on manifest.xsf (the InfoPath form).
  2. If you’re having trouble checking files out of SourceSafe, from within Visual Studio:a. Close Visual Studio.b. Open the Visual SourceSafe application, and check out the files for the project from there. Once you’ve done this you can close Visual SourceSafe.c. Make the directory “InfoPath Form Template” and all its content writeable, by unchecking the Read-only option from the folder’s Options.d. Reopen Visual Studio, and continue working as usual.

Most Annoying InfoPath Deployment

Another aspect of InfoPath you need to consider is how you’re deploying your forms. To deploy a form you need to manually, that’s right manually, update each DataConnection to point to your production environment. Ouch! If you have say 5 forms, with 5 DataConnections each, then your looking at 25 DataConnections to manually update. Nasty! And if you mess one connection up, you’ve got a problem.

SQL DataConnections are the worst to update. When you want to change to a new DataBase, InfoPath completely clears your original select statement and forgets which table you were using, and you have to reselect the columns/table. Should your DataSource’s schema change (i.e. your select statement is not exactly the same as previously), InfoPath will do you the favor of removing your controls’ databindings. Most of the time you’ll probably use all the table’s columns, but you still have to go and re-select that table.

Web Service DataConnections are the easiest to reconfigure (but still pretty painful). You can just take the URL of the new web service, and copy it into the web service address box, and quickly click Next through the DataConnection wizard. InfoPath doesn’t forget which web method your DataConnection uses, like it does with SQL table DataConnections.

The aforementioned makes it extremely time consuming and error prone to deploy InfoPath forms between development, QA and production environments.

Conclusion

  1. Use web services to retrieve and save form data, and plan accordingly. I cannot state this enough. Yes, maybe for the simplest of simple forms you can get away with using InfoPath’s SQL DataConnection (and I mean really simple), but for everything else a web service is an absolute must.
  2. Try to avoid large editable, repeating data grids (or referred to as a RepeatingTable in InfoPath lingo). Be extra cautious when you’re planning on editing large result sets, with lots of drop down lists and lookup data. Forms that work best are ones that displays and edits a single entity, and apposed to forms that edit multiple instances of an entity on the same form.
  3. Don’t think you’re going to deploy those 5 forms in a few minutes. Give yourself enough time for the deployment, and to test each form afterwards to check that you didn’t mess a DataConnection up.
  4. Do a quick prototype of your forms to check whether InfoPath can really handle it. In my case the person who recommended InfoPath for the solution should have checked that it can accommodate pivot tables. This is general good software dev practice, but I think because of all InfoPath’s restrictions, I think one needs to be particularly careful.We’ve gotten so used to having control over every element of the user interface with ASP .NET and Windows Forms that we expect the same of other technologies we use. Remember InfoPath’s controls and their behaviour dictate how your information is displayed. You do not have access to the underlying API that these controls are based on. In other words know what InfoPath’s UI controls can do, because you won’t be able to write your own.
Advertisements

14 Comments on “The Joy, Blood, Sweat And Tears Of InfoPath 2007”

  1. Like usual, not the shortest post 🙂

    InfoPath has always been painful to work with at best, and in two out of my three projects where I’ve developed InfoPath forms, I ended up converting them to custom aspx pages.

    Generally, the rule of thumb is to use InfoPath forms for VERY simple forms – as soon as you need logic in the form, things get tricky. I’ve also found it easier submitting these things to a document library and writing a custom list item event handler to handle document parsing / database insertion, etc.

    >> InfoPath And Visual SourceSafe Does Not Play Well Together

    Does VSS play well with anything? 🙂

    All the best,
    Riaan

    • openlandscape says:

      Very good to hear from you. Glad to see your still doing the rounds.

      > Like usual, not the shortest post

      I guess my blog philosophy is do it well, or don’t do it at all.

      > Does VSS play well with anything?

      I guess it plays well with easy setup and nice integration with Visual Studio. But then you have to use it as well 🙂

      > I’ve also found it easier submitting these things to a document library and writing a custom list item event handler to
      > handle document parsing / database insertion, etc.

      Yes. I’ve actually gone down a similar path as well. At one stage we wrote very simple custom SharePoint workflows to do this. However, we ended up preferring web services for ease of admin, deployment, maintenance, and reuse.

  2. Craig Carns says:

    Good information in your post.
    I have a simple question: Is it possible to create a secondary datasource soley in code? {hacks are welcome}

    I would pull this off by building the path from incoming InputParameters in a browser form (MOSS 2007).

    • openlandscape says:

      @Craig. Maybe it’s possible. Haven’t tried to create InfoPath data sources programmatically. Please elaborate on what you mean by “building the path from incoming InputParameters in a browser form”? What InputParanmeters are you referring to? InfoPath’s QueryParameters, SqlDataConnection’s input parameters, or a method’s parameters?

  3. Craig Carns says:

    OK here is the code I think almost works – I get a 403 error so now I am down to just permissions. That being said if no one else is doing this I question myself – why not- and should I be doing it another way? I havent looked into the Sharepoint Data Connections Libraries. Anyways here is the code:
    ———————————————–

    public void FormEvents_Loading(object sender, LoadingEventArgs e)
    {

    string varURI = string.Empty;
    try
    {
    e.InputParameters.TryGetValue(“varURI”, out varURI);
    }
    catch (Exception ex)
    { }

    System.Net.WebRequest webreq = System.Net.WebRequest.Create(varURI);
    //webreq.Credentials = new System.Net.NetworkCredential(“{username here}”, “{password here}”);
    System.Net.WebResponse webres = webreq.GetResponse();
    System.IO.Stream xmlStream = webres.GetResponseStream();

    XmlDocument doc = new XmlDocument();
    doc.Load(xmlStream);

    }

    • Craig Carns says:

      I forgot to mention:
      1) As you can see in the code I was referring to Querystring input parameters
      2) You will also noticed I commented out webreq.Credentials – it didnt seem to work but then again I have yet to test on a signed fully trusted deployed web form.

  4. Craig Carns says:

    OK I managed to track down the answer to my question – forum post here:
    http://social.msdn.microsoft.com/Forums/en-US/sharepointinfopath/thread/160fa2f9-def4-4f2a-8948-169ff0f9757a

  5. openlandscape says:

    Hi Craig,

    Thank you very much for your comments and the links. I think a lot of devs will find the msdn forum link very valuable.

    And yes it will be completely possible to change the values of whatever properties the concrete DataConnection has. I have not coded with the FileSubmitConnection or FileConnection but I’d say that as long as the two data sources have exactly the same schema, then you’ll be okay to change the file path.

    Glad you came right. Good luck with your InfoPath adventures. It’s not always the most pleasant technology to work with.

  6. silversabre says:

    You are the infopath champion here, i dunno if you knew 🙂

    • openlandscape says:

      I wasn’t aware of that. But I guess some glory is appropriate after all the too many late nights and shocking cursing… 😉

  7. […] SQL issues and more For those of you working with InfoPath, have a look at this post. It will definately help you out if you need to access a db or customise forms with […]

  8. Nick says:

    I spent the last 3 days at work pouring through MSDN and messing around with things in Infopath trying to make a SQL connection that would only be formed for data submission on the click of a button in a view. Thanks very much for this, I have no clue how long I would have spent driving into a dead-end without this.

    Thank you for the depth you went into also. This is thorough enough I can convince my senior co-worker (with no Infopath experience, same as me) that what he thinks we should be able to whip up pretty quickly will be no such thing.

  9. Maureen says:

    Hi Craig –

    Holey moley, excuse me, trying to connect to a SQLsever view, and Infopath crashes repeatedly!

    So glad you have this information available, for the same reasons as Nick.

    Is this a BUG in Infopath? Pretty severe limitation.

    Thanks much.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s