Accessing Data in InfoPath Forms Share Point 2010

One of the powerful features of InfoPath, especially when it comes to generating browserbased forms, is the ability to connect to additional data sources. We can reuse our earlier Demonstration Equipment Request form to see this in action.

  1. Open the EquipmentRequest.xsn file from the local file system. Our form has two sections: the top section captures customer details and the bottom section captures a list of products. Rather than manually keying in customer details, we’ll make use of a SQL Server database to look up the required information and automatically populate the fields.
  2. TIPFor demonstration purposes, SQL connections are easiest to use. However, their usefulness within InfoPath forms is pretty limited. Passing parameters generally requires custom code, as you’ll see later. As a general rule, the best way to communicate with external data sources is to create a custom web service interface. InfoPath can then parse the Web Service Definition Language (WSDL) and create fields for any parameters that may be required. To provide some sample data to work within our various examples, we need to download and install the SQL Server 2008 sample databases. Our examples make use of the AdventureWorksLT database installed on the local instance of SQL Server 2008.

  3. At the bottom of the Customer Details section, add a button. Buttons and other controls can be found in the Controls section of the Home ribbon. Type the button label as Find Customer.
  4. To add a data connection, switch to the Data tab on the ribbon, and then click From Other Sources | From Database, as shown:
  5. Accessing Data in InfoPath Forms
  6. Click Select Database to select from the data sources that are available on the current machine. If one is not available for the AdventureWorksLT database, click New Source to add one. Select the Customer table when prompted. Once the data connection has been made, the columns will be listed, as shown next:
  7. Accessing Data in InfoPath Forms

  8. Since we want to use a query to extract data from more than one table, click Edit SQL and enter the following SQL statement:
  9. Select C.CustomerID, C.CompanyName, A.AddressLine1, A.AddressLine2, A.City, A.StateProvince, A.PostalCode
    From SalesLT.Customer as C
    Inner Join SalesLT.CustomerAddress as CA
    On CA.CustomerID=C.CustomerID
    Inner Join SalesLT.Address as A
    On A.AddressID=CA.AddressID
  10. Click Next. Leave the Store A Copy Of The Data In The Form Template checkbox unchecked. Click Next and then uncheck the Automatically Retrieve Data When Form Is Opened checkbox. Set the connection name to Customer and then click Finish to create the connection.
  11. We’ll make use of the Company Name field to search for customers. All other fields will be populated automatically from the search results. To make all other fields read-only, select the field and then from the Properties tab of the ribbon, check the Read-Only option in the Modify section. Repeat this process for all fields in the Customer Details section other than the Company Name field.
  12. We’ll display our search results on a separate page. From the Page Design tab’s Views section, select New. Name the new view Customer Search Results.
  13. Type the title of the new view as Customer Search and then, from the Fields pane, in the Fields drop-down, select Customer (Secondary). (The Fields drop-down is something of a misnomer. In reality, the drop-down contains a list of the data connections that are available to the current form.)
  14. Drag the d:Customer repeating group onto the Customer Search form. Select Repeating Table as the control type. When producing a schema from a SQL statement, InfoPath also adds columns for both sides of a join relationship. As a result, three redundant columns are named CustomerID1, AddressID, and AddressID1. To remove these from our repeating table, simply select the offending columns and choose Delete | Columns from the Layout tab.
  15. Since we don’t want the user to be able to edit the contents of these fields, using the Change Control button on the Properties menu that we demonstrated earlier, change the controls to Calculated Values.
  16. The final item that we need to add to our search form is a button to select the correct customer. Place the cursor in the first column of the table and then, from the Table tab in the ribbon, click Insert Left.
  17. Add a button in the data area of the new column and type the label as Select. The completed form should look like this:

Customer search
Data Connection Libraries
When we added our data connection, the connection details were stored along with the InfoPath form. This technique is known as “embedding connection details.” In simple cases, this approach works well; however, in larger developments, such a technique may not be appropriate. Often multiple environments exist for testing, staging, and production. Embedding connection details within a form template would require the template to be changed for each environment. Also, if an embedded connection contains user credentials, these are stored as plain text within the file. To get around problems like this and to promote the reuse of administrator controlled data connections, InfoPath can save data connections to a data connection library. To create a new data connection library, take the following steps:

  1. Browse to the home page of the sample site that we created earlier.
  2. From the Site Actions menu, select More Options, and then, from the Create dialog, select Data Connection Library. Name the new libraryMyConnections.

We can now publish the data connection from our Demonstration Equipment Request form by taking the following steps:

  1. In InfoPath Designer Fields pane, click the Manage Data Connections link.
  2. Select the Customer data connection and then click Convert To Connection File.
  3. Using the Browse button on the Convert To Data Connection dialog, select the MyConnections Document Library and save the file as Customer.udcx.
  4. Click OK to save the Universal Data Connection (UDC) file.

Modifying UDC Files

By default, our data connection is configured to use Integrated Windows Authentication. This means that connections to the data store are made using the credentials of the user viewing the form. There are, however, a few problems with this approach. Probably the most obvious is that all users accessing the form must have permissions to the underlying data store. Another problem that isn’t so apparent is what’s known as the “double-hop issue.” NT LAN Manager (NTLM) doesn’t allow credentials to be delegated by an intermediary system. This is a problem when using InfoPath Forms Services, because the credentials are captured on the user interface tier, but it is the middle tier, the InfoPath Forms Services layer, that actually connects to the data source. Since the middle tier can’t impersonate the user connected to the user interface tier, it’s not possible to connect to a data source on a separate server using Integrated Windows Authentication.
You can, however, deal with this problem in a few ways: One way is to make use of the Secure Store Service The other way is to embed a username and password in the connection details. Although using the Secure Store Service is the most secure option, for the purposes of this demonstration, we’ll use embedded credentials.

  1. Create a login on the SQL server that contains the AdventureWorksLT database.
    Create a SQL login named InfoPathDemo with a password of password. Make sure that the server is configured to use Windows and SQL authentication.
  2. With the login created, grant it read permissions on the AdventureWorksLT database. Check that it can connect by using the Connect option in SQL Server Management Studio.
  3. UDC files are stored as plain text files containing XML in the SharePoint document library. However, by opening a file with Visual Studio 2010, the XML Designer makes it easier to see what’s going on. Navigate to the MyConnections document library, and then click the Check Out button on the Documents tab to check out the Customer data connection file.
  4. From the Library tab, click the Open With Explorer button as shown:
  5. Explorer button

  6. The document library will be opened in Windows Explorer. Open the Customer.
    udcx file with Visual Studio and find the udc:ConnectionString element.
  7. Change the connection string to include the following:
  8. Provider=SQLOLEDB.1;
    Persist Security Info=True; Initial Catalog=AdventureWorksLT; User Id=InfoPathDemo;Password=password; Data Source=<YourServerName>;
  9. Save the file and then switch back to the MyConnections document library and check in the updated document.
  10. Before the connection string can be used, a system administrator must explicitly approve embedded credentials in connection files. Navigate to Central Administration | General Application Settings | Configure InfoPath Forms services. Check the Embedded SQL Authentication checkbox.

Our connection file is now set up to use embedded connection credentials and will work properly for all users accessing it.

NOTE Within the UDC file is a udc: Authentication element that’s commented out by default. To configure the connection to use Secure Store Service for authentication, uncomment this section. The AppId is the Target Application Id and the CredentialType will either be NTLM for Windows authentication or SQL for SQL Authentication.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Share Point 2010 Topics