Using SharePoint Lists as Data Sources in InfoPath Forms

Introduction

This article explains how to use a SharePoint list as a datasource to an InfoPath form. This is performed using code-behind in the InfoPath form, using Visual Studio Tools for Applications (VSTA).

Background

I have developed a form to track certain customer agreement tracking activities in InfoPath, but my users didn't want to use external workflows to send email notifications to the predetermined user lists. Rather, they wanted to use a set of SharePoint lists that they could maintain, and read the appropriate email addresses from the list. The simple click of a checkbox inside the InfoPath form would generate email notifications to the list appropriate for each activity.

Using the Code

If you've ever done any InfoPath development, you already know that navigating XML can be intimidating at times. Fortunately, there are plenty of good resources out on the Internet. Unfortunately, in spite of all my plagiaristic skills, I was unable to find a specific example of what I wanted to do. Namely, I wanted to iterate through a SharePoint list and extract a field from each entry, then build a concatenated string of email addresses and use that in the "To:" field of an email and send it.

The SharePoint list is simplicity itself. I merely created a Contact list and populated it with basic identifying information: name and email address. The next step is very important: you must add the list to the InfoPath form as a secondary datasource. This is accomplished through the InfoPath design client via the Tools...Data Connections...Add... dialogs. Just provide the SharePoint list URL, select the appropriate list, pick the fields you want (E-mail_Address is what I was after), and take the defaults after that. The SharePoint list is now a data connection, and part of your InfoPath project.

At this point, you'll need to invoke VSTA (Alt+Shift+F12). I'm going to assume you have passing familiarity with this, or else this article wouldn't really be of interest...

The trigger for executing the email notification is nothing more than an InfoPath checkbox, using the Changed event. For this example, I'll use the checkbox titled "Submitted for Credit Approval", named Submitted. The event is Submitted_Changed.

You'll need the following using statements in your project:

using Microsoft.Office.InfoPath;
using System;
using System.Net.Mail;
using System.Xml;
using System.Xml.XPath;

Here's the first code snippet:

public void Submitted_Changed(object sender, XmlEventArgs e)
{
    // Document submitted for Credit Approval
    XPathNavigator _subNode, _subNode2;
    _subNode = this.MainDataSource.CreateNavigator();
    _subNode2 = _subNode.SelectSingleNode("/my:myFields/my:Submitted", 
                                          this.NamespaceManager);
    SendMailNotification(_subNode2, "CATCorporateCreditContacts", 
                         "CAT - Submitted for Credit Approval");
    // Message the user that an email was sent
    _subNode2 = _subNode.SelectSingleNode("/my:myFields/my:EmailNotification1", 
                                          this.NamespaceManager);
    _subNode2.SetValue("Email sent");
}

Let's break this down. I refactored the SendMailNotification out from this, just to make life slightly easier. It's not a very good refactoring, but it serves for demonstration purposes. In this section, I create XPathNavigator objects to access the Submitted checkbox. I pass _subNode2, the name of my SharePoint list containing the email addresses (CATCorporateCreditContacts), and the text for my subject line to SendMailNotification. After I return from that, I set a text field in the InfoPath form (EmailNotification1) to inform the user that the email was submitted.

Here's the code for SendMailNotification:

private void SendMailNotification(XPathNavigator _subNode2, string _ListName, string _subject)
{
    // Only do this if the checkbox is being SET. If it's being cleared, forget it.
    XPathNavigator _rootNode;
    if (_subNode2.Value == "true")
    {
    // Get the Root node of the email addresses
    // from the SharePoint list where those are kept.
    _rootNode = this.DataSources[_ListName].CreateNavigator();
    // Get the list of nodes from the root
    XPathNodeIterator nodes = _rootNode.Select(
      "/dfs:myFields/dfs:dataFields/dfs:" + 
      _ListName, this.NamespaceManager);
    // Loop through the address nodes, build the email address list...
    string addies = "";
    while (nodes.MoveNext())
    {
        string cnode = nodes.Current.SelectSingleNode(
          "@E-mail_Address", NamespaceManager).Value;
        addies += cnode + ";";
    }
    // Establish the email connection
    // via Email Submit in the Data Connections
    EmailSubmitConnection emailConn = (EmailSubmitConnection)
                 this.DataConnections["Email Submit"];
    emailConn.To.SetStringValue(addies);
    emailConn.Subject.SetStringValue(_subject);
    // Send the email
    emailConn.Execute();
    }
    return;
}

The real tricks to making this work are with respect to the creation of the root node reference for the SharePoint list, which must first be added as a secondary datasource to the InfoPath project (see above). Note that in the creation of the reference to _rootNode, I'm using DataSources, with a reference to the _ListName, which was passed in. The navigation syntax of the SharePoint list had me tripped up for a while, but this should work for any SharePoint list. The .Select operation will fill the nodes list. The next trick is to create an XPathNodeIterator that creates a list of the nodes using the syntax above. The nodes object can now be looped through, extracting the email address field for formatting and creating the concatenated string of email addresses.

Creating an email is now trivial, and I just plopped that into this function to demonstrate. Of course, this references an E-mail Submit data connection that you'll need to set up in InfoPath prior to referencing it here.

Points of Interest

It took me quite a while to figure out how to reference the multiple nodes in the SharePoint list, so I thought documenting this process may be helpful for others. While this is a fairly trivial implementation, there is potential for other applications. I'd be interested in feedback.