Friday, May 14, 2010

Populate InfoPath 2010 drop down list through code

In developing forms in InfoPath 2010, I found it quite difficult to manipulate form objects using custom code.  It seems that the form objects are proprietary InfoPath objects and not Windows Forms objects, so you won't be able to use standard techniques to populate the objects, even though they look identical.

In this example, we want to populate a drop down list with data in a database.

Before you pound your head against your desk trying to get this to work (as I did...), there is no way to add the data directly to the drop down list.  Therefore, we must trick the list into using values we pick, and it must do it at runtime.  So, we set up a repeating table that we CAN write to.

First, create a new section.  We will make the whole section invisible so it is not displayed on the published report.

Now give the section a name.  I'm calling it "invisible."

Inside the new section, insert a repeating table.  When it asks, you only need one field.

Give the field in the table a name.  I'm calling it "dropDownOptions."

As long as we are still in setup mode, let's force this invisible section to always be hidden.  This is done by adding a new Formatting rule to the section, select "Hide this control," and set the condition to "The expression", "1=1."

Now that we have a field that will act as our data source, we map the value/display values for the drop down list to this text box.  Right-click on the drop down list and choose Properties to set the data mapping.

Now that the form is set up, let's write the code to populate the table from the database.

Select "Loading Event" from the Developer tab.  (If you don't see the Developer tab, it can be added by going to File, Options, Customize Ribbon, and check the Developer box on the right hand side.)

This will bring up the Code Editor.  I prefer C#, so my code is written in C#.  If you are currently in VB and want to follow this tutorial, close the Code Editor, select "Language" (as shown on the above screen shot), delete the code and then select C# in the drop down list.  Open the Code Editor to continue.

Add the Reference to "System.Data" under References in the Project Explorer (right-hand side of code editor).  Then add the following code to the top of the code screen.

          using System.Data;
             using System.Data.SqlClient;

Under the "// Write code here" section, enter the following code.  Replace database connection as appropriate for your database environment.

            XPathNavigator DOM = this.MainDataSource.CreateNavigator();

            SqlConnection connection = new SqlConnection("Server=[server];Database=[database];UID=[username];PWD=[password];");
            SqlCommand command;
            SqlDataAdapter adapter;
            DataSet dataset;

                // Set up SQL connection and query

                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "SELECT * FROM [table];";

                dataset = new DataSet();                 
                adapter = new SqlDataAdapter(command);
                // Check dataset
                if (dataset.Tables[0].Rows[0][0].ToString() == null || dataset.Tables[0].Rows[0][0].ToString() == "")
                    throw new Exception("No data found.");
                XPathNavigator node = DOM.SelectSingleNode("/my:myFields/my:invisible/my:group2", this.NamespaceManager);
                string ns = node.NamespaceURI;
                // Fill options
                foreach (DataRow row in dataset.Tables[0].Rows)
                    using (XmlWriter writer = node.AppendChild())
                        writer.WriteStartElement("group3", ns);
                        writer.WriteElementString("dropDownOptions", ns, row["[column]"].ToString().Trim());
            catch (Exception ex)
             if (connection != null)
Anywhere you see the [...] tags, enter data appropriate to your environment. 
The last step is to give your form full trust.  This allows the custom code to connect to data outside the form itself.  Go to File,  Form Options, then select "Security and Trust" from the category list, uncheck the option for "Automatically determine security level (recommended)," and choose "Full Trust" from the list.  Click OK.
That's it!  Try running the form and see your drop down options populated!