6. Retrieving Data from External Sources

Getting Started

In the last several chapters, we talked about working with data and controls. We learned all about the main data source, including fields and groups, their properties, and how they hook up with controls through binding. In this chapter, we’ll apply that knowledge to discuss secondary data sources. Like the main data source, secondary data sources also hold data but cater to different form design needs. We’ll walk through some scenarios that use secondary data sources and show you how easy it is to incorporate them in your form designs.

Talking about data sources won’t make much sense, however, without first going over data adapters. Specifically, we are referring to data that is external to a form template’s resource files (the Resource Files dialog, which manages internal form template files, is discussed in Chapter 2). Examples of external data sources include an XML file on a network share, a database in a corporate server farm, or even a Web service living on the Internet. For instance, say that you have a list of products changing on a daily basis. Instead of editing your form template every day, the data can be received dynamically from your company’s database each time your form is opened.

The job of a data adapter is to get data from, say, a database, and then put that data into the main or a secondary data source. A data adapter is the mechanism that enables external data to flow into your form. Collectively, the data adapter and associated data source are known as a data connection. As its name suggests, the data connection gets data from an external data source and brings it into the form.

Data Connections

Why Do We Need Them?

A data connection is what InfoPath uses to connect to external data sources (or endpoints), beyond the form template, when users fill out the form. For the purposes of this chapter, we discuss only those data connections that pull, or query, data from an external source into the form. Submit connections, which push data to an external data source, are discussed in Chapter 8. There are four types of query data connection endpoints that InfoPath knows how to communicate with: XML files, databases, Web servers via Web services, and SharePoint servers. A form template can be set up with any number of data connections of any type. And just because a form template defines a data connection does not mean it actually uses it when the form is being filled out. As we progress in our discussion, it will be evident how this works.

In this chapter, we’ll discuss each connection type so you can understand what it does and how to set it up. We’ll also show examples of their use in some sample forms.

Conceptually Understanding Data Connections and Data Sources

Before we get into details on how to set up a data connection when designing a form template, let’s get a bird’s-eye view of what happens conceptually when a query data connection is created. Creating a data connection establishes a potential channel of communication between the InfoPath form and an external data source. It is a “potential” channel because when a data connection is defined, it may not actually be used when filling out a form. If it is used, InfoPath requests data from the endpoint. Assuming the data successfully gets transferred via the channel, InfoPath will store the received data in a location for use by the form. The location is always a data source, but it isn’t necessarily the main data source! InfoPath creates a secondary data source to temporarily hold the received data—saving or submitting the form does not include this data, which disappears when the form is closed. Figure 6.1 shows the relationships between the form and data sources. The bubbles around the form represent sources of external data, while the lines represent channels of data for requests and responses.

Figure 6.1. Relationships between a form and its external data sources

The data received from the connection can also be persisted with saved or submitted data. The only way InfoPath supports the persisting of data is if the data resides in the main data source. Remember that the main data source is ultimately what is submitted or saved when your users fill out the form. To clearly save queried data from an endpoint, InfoPath would need to take the received data and plop it right into the main data source. This is indeed what happens at a high level, and Figure 6.1 shows this with the connecting line between the orders database and the main data source.

All Data Connections Are Not Created Equal

As we jump into talking about the various types of data connections, it’s important to understand that all data connections of the same type may not be the same. Different connections of the same type with similar endpoints can exhibit different behaviors when you’re designing and filling out a form with InfoPath. To better inform you on how to design data-rich forms, we highly recommend you keep the following in mind (and we will mention it again as well) as you continue: Many features around a form’s data connection will behave differently depending on whether the connection is hooked up to the main data source or a secondary data source.

Each data adapter may have its own nuances around exactly what is different when it’s connected to the main or a secondary data source, so, unfortunately, we cannot give you a blanket guideline for all connections. However, it’s good to remember that the main data source can have only one query connection associated with it. And you must decide whether to use a connection on the main data source as the first step when you’re designing a new form template. For example, only the main data source can be used to submit to a database. Keep this in mind as you gather the data requirements to design your form. (See Chapter 2 for details on gathering form requirements.) You will see that in the grand scheme of designing your form, allowing only one query connection on the main data source shouldn’t be much of a restriction at all.

Creating a Data Connection

Main Connection

To create a main query connection, you need to design a new form template (template parts do not support creating against a connection; they’re discussed in Chapter 10). To design a new form template based on an external data source—that is, to use a data connection with the main data source—select one of these three Based on options in the Design a Form Template dialog (Figure 6.2): Web Service, Database, or Connection Library. We’ll soon learn why the XML or Schema option does not create a connection.

Figure 6.2. Design a Form Template dialog, the starting point for creating a main query connection

Secondary Connection

You create a secondary query connection while designing a form template instead of at the beginning as with the main connection. At any time while designing a form template, go to the Data Connections menu item on the Tools menu. (Later in this chapter, Figure 6.8 shows the resulting dialog.) After clicking Add to create a new secondary connection, choose Create a new connection to receive data. (A Data Connection Library option is also available, but we will introduce it in Chapter 14 and discuss it at length in Chapter 17.) At this point, you decide which type of connection you need (as shown in Figure 6.3), depending on the external data source.

Figure 6.3. Creating a secondary query connection for a form template

Table 6.1 shows the choices for main and secondary query data connections.

Table 6.1. Types of Main and Secondary Data Query Connections

Now that you’re familiar with how query data connections generally work, let’s jump into details and examples for each of the connection types.

XML Files

With XML becoming a ubiquitous file format throughout the world, how can you quickly and easily get data from any XML file and incorporate it into your form? Use the XML file data adapter, of course! The purpose of this adapter is to read an XML file that can reside almost anywhere and to pull its data into a secondary data source for your form to use. The XML file used for the connection can come from your computer, a file share, or an HTTP or FTP site. You could even use an ASP or ASP.NET page to dynamically generate XML data on the fly! If XML exists somewhere, InfoPath can consume it, assuming the user filling out the form has been granted access.

The XML file adapter is available only for secondary data connections. The main data source has no concept of an XML adapter because it doesn’t really need it. Since InfoPath already has the ability to open an existing XML file when you want to fill out any previously saved form, an adapter doesn’t really make sense. But if you wanted to copy some data from an external XML file directly to the main data source, you could use rules for simple copying or write custom form code to orchestrate a complex copy scheme. Copying data from a secondary data source to the main data source is generic functionality any adapter can potentially leverage.

Let’s jump into an example that shows off the abilities of the XML file adapter. First, we’ll design a very simple form template that you’ll fill out to create the external data source. Starting with a new blank form, simply insert a Bulleted List control and save the template. Fill out the form based on the template you just saved by adding the list items shown in Figure 6.4. Save this form to your desktop as Sample.xml. (This file is available with the samples for this chapter on the book’s Web site. A sample called BulletedListItems also was used to create this XML file.)

Figure 6.4. Data to fill into a form and save to Sample.xml, to be read later by using an XML file adapter

Now let’s design another new blank form template, but this time the template will be used to read the data we saved in the previous example. Instead of inserting controls, go to the Data Connections item on the Tools menu. Click the Add button on the Data Connections dialog and then select the Receive data with an XML document option for the source of your data. When prompted for the file location, as shown in Figure 6.5, point to the Sample.xml file you previously saved on the desktop. The Resource Files button is used to select an XML file that is already included in the form template.

Figure 6.5. Entering the file location to use when creating a secondary connection to XML data

The next dialog (Figure 6.6), despite looking simple, has a pivotal effect on this connection’s settings. The first option, Include the data as a resource file in the form template or template part, will pull in the XML data when you click the Next button and save it in a file that’s kept with the form template. The file would then be accessible through the Resource Files dialog (available from the button in Figure 6.5 as well as the Tools menu in design mode). Since we want our XML data to be live, that is, if the data is updated we want our form to reflect the updates, we instead choose the option Access the data from the specified location. The checkbox to Store a copy of the data for offline use should not be checked. This is for the cached query feature, which we will discuss in Chapter 7 when we cover the topic of offline query support.

Figure 6.6. XML file connection settings to opt in or out of including the data as a file in your template

The final page of the wizard, shown in Figure 6.7, has two settings that we can change before we finish. The first is the data connection name. The connection name is what you’ll use to reference this XML file data connection versus other connections you might have. (Remember that a form template can have multiple connections.) Some places where you might see this name when designing a form are in the Data Source task pane, rules, and the Data Connections dialog if you’re modifying the connection settings. You’ll use the data connection name quite often when you’re writing form code with data connections and data sources; however, we’ll reserve conversation of this topic for Chapters 15 and 16 when we talk about writing custom form code. When users fill out the form, this name is revealed only if there is an error accessing the data. In other words, the name you choose for the data connection is useful only for form design and debugging activities.

Figure 6.7. Final page of the Data Connection Wizard for an XML file

The checkbox Automatically retrieve data when form is opened enables this XML data adapter to get the data when the user opens the form (either new or existing), but before the form is shown. This is convenient for external data that never changes, such as a list of states used in a Drop-Down List Box. (We’ll go into details when talking about List Box controls using data from secondary data sources in Chapter 7.) If you do not want the data to be queried automatically, which is the behavior we want in our sample form, you can query it on demand through a rule. We will set up a rule to query the XML file using this data connection in a moment. Click Finish to complete the creation of the data connection.

After clicking Finish, you can see the data connection we just created in the Data Connections dialog (Figure 6.8). The Details region shows the major settings for the connection. The Details region is helpful when you have many data connections listed and can’t quite recall their specifics. You’ll also notice that this dialog is the general entry point for configuring query data connections for both main and secondary data sources. You can add, modify, and remove connections from this dialog. However, if a main data source connection exists, it cannot be removed. We will find out why when we look at submit database connections in Chapter 8.

Figure 6.8. Data Connections dialog after finishing the XML file Data Connection Wizard

Now that we’ve created the XML file secondary data connection, let’s design the visual aspects of our form template and see how to use the XML file data. At first, you’ll notice nothing has visually changed in the form. Since a data connection is not visually represented, you might forget that it’s even there! Obviously, one way to tell is to go back to the Data Connections dialog. Instead, let’s go to the Data Source task pane (Figure 6.9). The Data source drop-down menu will have a new item, namely, Sample (Secondary). This is the secondary data source that was created when we finished creating our XML file data connection. Notice that this data source has the same name as our data connection and that it is a secondary data source instead of the main one. The data source itself may look strikingly similar to the main data source—that’s because InfoPath uses the same logic to create the data source for secondary sources as it does for primary sources. Unlike the main data source, secondary data sources are never permitted to be edited since the structure of the data source is directly correlated with the external data.

Figure 6.9. Data Source task pane showing secondary data source

Let’s insert the field1 repeating field into the view as a Bulleted List. This can be easily done by right-clicking field1 in the Data Source task pane. The Bulleted List is now bound to the secondary data source. (We’ll learn more about secondary data source binding in a moment.) Try previewing the form. Does nothing show up? That’s because we chose not to query the data connection automatically when we worked in the dialog shown in Figure 6.7. Then how do we get our data? We learned in Chapter 5 that we can use the Rules feature on a Button click, among other form and data source events. For now, we can use a Button control to query the data via the data connection. Close the preview window and insert a Button control from the Controls task pane. Let’s create a rule to query our Sample data connection.

1. Go to the Button Properties dialog.

2. Click on the Rules button.

3. Click the Add button to add a rule.

4. In this rule, click the Add Action button to add the query action.

5. Select the Query using a data connection action. The Sample data connection should be automatically chosen since it’s the only data connection in the form template.

6. Click OK to close all open dialogs.

You could rename the Button control’s label by using the Button Properties dialog if you want, but we’ll leave ours labeled with the default name “Button.” Let’s preview again. This time, we still don’t have any data. But clicking on the Button control will query the data connection and show the data from the Sample.xml file, as seen in Figure 6.10. If you see the Microsoft Office InfoPath Security Notice dialog, click the Yes button to continue. (A sample called QueryXmlFile is included with the samples for this chapter.)

Figure 6.10. Clicking on the Button to query the Sample data connection

Let’s do a quick demo to show you the power of query data connections. Keep the preview window open as shown in Figure 6.10. (Or you can minimize the preview window instead.) We want to go back and edit the Sample.xml file. Double-click on the Sample.xml file on the desktop to open it for editing, and then remove, say, the last line: “the lazy dog.” After removing that text, save the form. Switch back to the form with the data connection as shown in Figure 6.10, then click the button again. Poof, the last item in the bulleted list disappears!

You might have noticed that you cannot insert or remove rows in the secondary data source after clicking the button. We’ll investigate why this is the case, along with a series of other observations, in Chapter 7 when we discuss secondary data source binding.

Databases

One of the most sought-after connection scenarios is to get data from a database. The ubiquity of databases has exploded in the last decade, mostly due to the rapid growth of the Web. Why not leverage the existing data in these storage silos? Say, for example, that the MOI Consulting Corporation’s customer service department is still using a console application via a legacy system to connect to the company’s database. Why not use an InfoPath form instead? The benefits of using XML-based form technology and the well-known user interfaces of Microsoft Office are just a couple of reasons. Thus a database is a perfect and powerful complement to what InfoPath already offers.

InfoPath supports natively connecting to SQL Server 2000 (and later) as well as Access databases. To talk to a database, InfoPath uses Active Data Objects (ADO), which is a standard protocol for communicating with a database. InfoPath’s database adapter uses ADOXML, which is an extension of ADO used to track changes and send update diffgrams to a database. A diffgram is used by ADO to communicate changes (such as inserts, updates, and deletions from the client) and to replay the set of actions (on the server) given some data. We’ll look more closely at the database submit connection in Chapter 8.

Setting Up a Connection to a Database

When setting up the database connection, you can choose from a variety of ADO connection methods to talk with a SQL Server or Access database. These access mechanisms include DSN-less, ODBC DSN (for Access only), OLE DB, or even a Data Link file (with file extension .udl). InfoPath uses the My Data Sources folder in My Documents, so it’s easy to create a new connection with Microsoft Office’s built-in data source wizards. In particular, the +Connect to New Data Source and +New SQL Server Connection ODC files, when double-clicked, launch a wizard that helps you create an Office Data Connection file (also called an ODC file) to the database without a need to know details on creating an ADO connection string. The connection string itself can use standard security (where the credentials are stored in the form template) or a trusted security connection. If you set up a connection string with a user name and password, InfoPath will ask if you’re sure you want to save that sensitive information with the form template to which others will inevitably have access. Typically, you will not want to bundle the credentials in the form unless, of course, that database user has very limited access. Your best bet for high security is to use a trusted connection. Details on connecting to a SQL Server or Access database are beyond the scope of this book.

A database data connection is one of the few connections that can be used on the main data source. It can also be used as a secondary connection and, therefore, with a secondary data source. Designing a form (i.e., the main data source) against a database involves the same steps as creating an equivalent secondary data connection version, with the exception of submit. Secondary query data connections do not support submit. However, it’s important to realize that a database connection on the main data source always includes both query and submit; they are inseparable, although they seemingly appear as two separate adapters in the Data Connections dialog. In this chapter we will focus only on the database query connection. Submitting to a database will be briefly discussed in Chapter 8. As a result, we’ll begin with an example using a secondary data connection.

Since we’ll introduce many new InfoPath-specific concepts with this example, and to minimize distractions with a complicated scenario, let’s take the classic Customers and Orders tables in the Northwind database. (If you aren’t familiar with the Northwind database, imagine a database for tracking your customers and their orders: a table for customers and another for their orders.) The Northwind database is available on most SQL Server installations, so it’s easy for you to follow along if you’d like.

We start by creating a new blank form. Next, let’s add the secondary connection by using the Data Connections dialog, electing to Create a new connection to receive data, and selecting Database (refer back to Figure 6.3).

The Data Connection Wizard for a database connection (see Figure 6.11) is quite visually different than the one we used for the XML file connection. From this single dialog, we can perform the following actions to configure this data connection.

Figure 6.11. Data Connection Wizard when creating a new database connection

• Select a database to connect to (and create a connection if one doesn’t exist).

• Add, remove, or modify tables used in the query.

• Selectively add or remove columns from tables in the query.

• Define relationships between columns in two or more tables.

• Edit the SQL query used by InfoPath to get the data from the database.

Let’s show off these features by first selecting a database. Clicking Select Database shows the My Data Sources folder where we can create a new SQL Server connection. The result of creating a new SQL Server connection is an ODC file that InfoPath will use to create the InfoPath data connection to the database. Our computer’s name is localhost.com, so that’s what we’ll connect to, as shown in Figure 6.12. We’re using Windows Authentication, which means InfoPath will use the credentials of the user filling out the form to open the database connection when a query occurs. Don’t forget to ensure that your users will have access! If you prefer to use a specific SQL user name and password, consider that those credentials will be visible to anyone who has access to your form template.

Figure 6.12. Connecting to SQL Server after clicking Select Database

After you configure the connection in the dialog shown in Figure 6.12, move on to the next part of the wizard, which prompts you to pick a database and a specific table or view (Figure 6.13). In our case, we want to pick the Northwind database and use Customers as the primary table. If you don’t check the Connect to a specific table option, InfoPath will prompt you (unless there is a single table or view) to pick a table or view before using the ODC you create. For the purposes of a secondary query data connection where we do not submit, using a view is fine. Using a database view for the connection is not preferred if the changes to that data are to be submitted. Clicking Next shows the last page of the wizard, which lets you set the ODC file name, a description, a friendly name, and search keywords. Since these are sole properties of the ODC file itself that InfoPath won’t ever use, we won’t go into details on their functions. For all practical purposes, you can click Finish on the Select Database and Table dialog in Figure 6.13.

Figure 6.13. Select Database and Table dialog. Choose the Northwind database and the Customers table. The top two items in the list are database views instead of tables

When the Data Connection Wizard is completed, InfoPath shows an abstract representation of the data source in the Data source structure section (Figure 6.14).

Figure 6.14. Database connection’s data source structure showing the added Customers and Orders tables

Before we finish, we want to add the Northwind Orders table as a child table to the Customers table. This is very simple to do. The first step is to click Add Table to open the Add Table or Query dialog (Figure 6.15), and select the Orders table.

Figure 6.15. Adding the Orders child table to the Customers parent table

The next step is to define a relationship between the tables. Figure 6.16 shows the dialog for adding relationships. You must have at least one relationship to add a table and associate it to the existing tables in the data source. InfoPath tries to be helpful by automatically adding relationships between fields (i.e., table columns) with the same names. (In a moment, we’ll see how to manually define and edit relationships.) In this case, the CustomerID fields exist in both the Customers and Orders tables, so that relationship is already defined. You will want to add other relationships as you see necessary to ensure the integrity of data between tables. New relationships are added as the last step before a new table is added. The final result, after adding both the Customers and Orders tables, is shown with a collapsed structure in Figure 6.14.

Figure 6.16. Defining a relationship between the child and parent tables. Some relationships are automatically defined for you

Using the Show table columns checkbox is similar to expanding the groups in the data source. All fields (i.e., table columns) of the tables are shown under their respective tables. Similar to the Edit Default Values dialog discussed in Chapter 3, the checkboxes adjacent to each field let you decide whether or not you want to include the field in this database query connection. Some checkboxes may be checked and disabled. This means the associated field(s) is either a primary key in the database table or is participating in a relationship with another table and must be included in the query.

When we added the Orders table, it was simply a matter of clicking Add Table and selecting the Orders table. If you have two or more tables, adding a table will add it under the selected table in the Data source structure section. By default, the last table you added is the one that will have selection. Changing selection is easy: Just click the item in the Data source structure section.

We’ll leave adding yet another table—Order Details, as a child to the Orders table—as an exercise.

To further customize the data received by our database connection, the Modify Table button plays a pivotal role. This button is the entry point for two different actions: setting sort order and modifying table relationships. To better clarify the Modify Table button’s behavior, the button text should actually read “Sort Order” when the top-level table is selected and “Edit Relationship” when any other table is selected. Modifying the parent table allows for multiple levels of sorting on returned records when the data connection is queried.

In our example, Customers is the top-level table, so selecting it and clicking Modify Table shows the dialog in Figure 6.17. Here we can determine potentially three levels of sorting order. At each level you can set the order for a specific field and choose Ascending or Descending. If you’re familiar with SQL queries, note that this sorting is accomplished by using the ORDER BY clause followed by DESC or ASC. In our sample, we’ll first sort by ContactName (Ascending) and then by City (Descending).

Figure 6.17. Sort Order dialog for setting three levels of sorting order

Our next topic is to dive into the SQL statement itself to see how InfoPath generates the query text and uses it to create the secondary data source. Unfortunately, this sorting capability is not available through the InfoPath user interface on child tables since the Modify Table button shows the Edit Relationship dialog. If you wanted to implement sorting on child tables, you could edit the SQL statement for yourself.

Let’s now look at the Edit SQL button shown in Figure 6.14 and what we can do with it. Clicking it shows a plain dialog with a lot of text (Figure 6.18). Any changes you make in this dialog will attempt to be parsed by InfoPath to re-create the data source structure shown in Figure 6.14. If you make this shape query invalid through your own edits, you may receive one of these warnings or errors:

• Bad SQL

• Cannot create data source structure

• Column or table names do not adhere to XML naming standards

Figure 6.18. Edit SQL dialog, which allows hand-editing of the query statement used by InfoPath to create the data source

It’s highly recommended that you use the Test SQL Statement button in the Edit SQL dialog to ensure that your SQL query is valid and that data is properly returned from the database. If you don’t validate the SQL statement in this dialog, it will happen anyway when you click the Next button shown in Figure 6.14. So if you’re making any modifications to the SQL query, we highly recommend that you save time and test it first. If you receive a warning about the column or table names not adhering to XML node name rules, you can ignore it, however, because the invalid characters (such as spaces) will be automatically replaced with underscores by InfoPath to create the data source.

Since our current query is getting all customers and orders from the database, there will be way too much data and will take too long for the data connection to finish. As a result, let’s modify our query to give us just the first three customers and all of their orders. Toward the beginning of the SQL statement, replace {select "CustomerID" with {select TOP 3 "CustomerID". (The bold text is what you’re adding to the query.) When you click OK to the Edit SQL dialog, you will receive a warning that the SQL statement cannot be represented as a tree view. This is simply a warning and will not affect the outcome of our data connection. As you can see, sometimes warnings are okay to ignore, assuming you test your form and ensure that your modification works as expected. In a moment when we open the form in preview, only three customers (and many more orders) will appear instead of almost one hundred customers (and who knows how many orders)!

To find out more information about the ADO Shape command, its syntax, and usage, please see the “How to Use the ADO SHAPE Command” Microsoft Knowledge base article Q189657 (http://support.microsoft.com/kb/q189657).

After your database connection is set up as you’d like and you are ready to finish, click on the Next button shown in Figure 6.14. In the next step of the Data Connection Wizard, do not select the option Store a copy of the data in the form template. This option enables offline query support, which we’ll talk about in Chapter 7.

The last page of the wizard is similar to that of the XML file data connection shown in Figure 6.7, except that the default name of the connection is usually the name of the primary table. In our case, since the SQL Query could not be successfully parsed by InfoPath, the name defaults to Secondary Data Source. Let’s change it to Customers. The fact that the query could not be parsed is not a problem as it affects only InfoPath’s attempt to guess at a default connection name. This time, let’s leave the Automatically retrieve data when form is opened checkbox checked (which is the default setting).

Integrating the Database Connection into the Form Template

Now that we’re done with the wizard, let’s go to the Data Source task pane and look at the Customers secondary data source that was created. You can see in Figure 6.19 that tables are represented as repeating groups and the table columns shown as attributes. Let’s look at the data source for a moment and point out the peculiarities with database secondary data sources. First, you’ll notice the document element is myFields and has one child group called dataFields. The myFields group appears to be the same myFields group that appears when creating a new blank form, but this is not the case. If you drill down into its properties dialog Details tab, you’ll see that the namespace is http://schemas.microsoft.com/office/infopath/2003/dataFormSolution. This namespace is specific to all data sources created from database, Web service, or SharePoint data connections. (We’ll talk about Web service and SharePoint list connections later in this chapter.) As you might remember from Chapter 3, the namespace prefix (when an alias, or short name, followed by a colon exists in the field or group name) is not shown when the field or group in question is in the same namespace as its parent. That means myFields and dataFields are in the same namespace.

Figure 6.19. Customers secondary data source

The tables themselves, represented as repeating groups, are in a different namespace altogether. To find out what namespace the d alias represents, you can again peek at the Details tab in the d:Customers group properties dialog. The namespace is http://schemas.microsoft.com/office/infopath/2003/ado/dataFields. The “ado” string in the namespace references the database data connection, which is internally known to InfoPath as the ADOAdapter in the solution definition (.xsf) file. This name is also used programmatically when we write custom code using the InfoPath object model.

Last are the table columns themselves, which are shown as attributes in the data source. Database data sources, by design, always represent columns as attributes. The data types associated with attributes nicely map to database data types. For example, XHTML (or rich text) can never be a data type in a database. Likewise, attributes don’t support rich text. But you’re probably wondering why each of the attribute fields in the data source is prefaced with a colon, yet no alias exists before the colon. Since the colon is shown on the attribute fields, it means their namespaces are different than the repeating groups (tables) in which they’re contained. So what’s the new namespace? None. That’s right: Data source fields and groups may belong to the “no namespace” crowd. This is a feature of XML namespaces. The fact that there is no namespace does not affect any functionality whatsoever. Think of no namespace as just any other namespace! Moreover, since this secondary data connection will never submit, there is no concern about whether or not a server will accept this data in a particular namespace. (You will learn in Chapter 8 that submitted data must adhere to the restrictions of the endpoint, which includes the namespace of the data.)

As we did with the XML file data connection example, let’s insert controls to create the view. We’ll insert the top-level table, d:Customers, as a Repeating Section with Controls. We would have inserted it as a Repeating Table, but it’s difficult to get the nested Orders table to display properly since you’d need to pick a specific column in Customers for the Orders table. You can easily see this problem in Figure 6.20, where we inserted d:Customers as a Repeating Table. (This sample file is called CustomersOrders.)

Figure 6.20. A nested database table (Orders), which is hard to represent when the parent table (Customers) is in the view as a Repeating Table

We’ll change the Orders Repeating Section (which is inserted by default with the d:Customers Repeating Section) to a Repeating Table control, which is a good choice for database connections since the external data source is naturally tabular. Using a Repeating Table for the d:Customers table results in a nested Orders Repeating Table in the last column of the d:Customers table. This layout is not optimal for most uses, so we’ve modified the layout a bit for this example, as shown in Figure 6.21. (This sample file is called CustomersOrders2.)

Figure 6.21. Our rendition of displaying the Customers and Orders data

Next step is to preview the form. Since the data connection we created is set to retrieve data when the form is opened, we should immediately see the Customers and Orders data. If you receive a Microsoft Office InfoPath Security Notice dialog, answer Yes to the prompt. We’ll discuss this dialog in Chapter 8. Figure 6.22 shows the result.

Figure 6.22. Preview of the modified form layout with Northwind customers and orders data

If any data in the Customers or Orders tables were to change, we would need to close and reopen the form to retrieve them. The data connection is queried only when the form is loaded, so the data in the secondary data source is static for the duration of the form session. If we wanted the ability to requery the database for the latest data without reopening the form, we could add a Button control that uses a rule to perform the query.

Web Services

What Is a Web Service?

A Web service is one of the richest external data sources to which InfoPath can connect. If you have little experience with Web services, the next few sections should be a quick review. Otherwise, you can skip reading until after we create a simple Web service in Visual Studio 2005.

When we talk about a Web service, we are specifically referring to a software component that runs on a Web server. The component is defined by the Web Service Definition Language (WSDL), which is a W3C published protocol. (Details on the protocol are beyond the scope of this book. MSDN is recommended as an in-depth reference on Web services specifications; see the reference listed in the Appendix.)

To use a Web service data connection in InfoPath, the form uses the URL of the Web service in which you want to connect. For example, Microsoft TerraServer has a Web service at http://terraserver.microsoft.com/TerraService2.asmx. As you can see, the URL of the Web service endpoint appears somewhat similar to other Web pages you may visit in a Web browser. The distinguishing factor in the URL for this Web service is the “.asmx” file extension. The ASMX file is the actual Web service endpoint. This is the URL InfoPath uses for query and submit operations against the endpoint.

To create a Web service data connection while designing a form, InfoPath requires the WSDL URL. This is because a WSDL exposes the underlying Web service’s public interfaces, including methods, input parameters, and output types. Typically you can simply append “?wsdl” to the endpoint URL to get the WSDL. For the TerraServer Web service, visiting http://terraserver.microsoft.com/TerraService2.asmx?wsdl yields its WSDL. A WSDL, despite being XML, is really not meant for human consumption unless you are familiar with its intricacies. Due to its complexity, there are tools available on the Web to help you easily decipher WSDL content. As you will see in our upcoming example, InfoPath helps you to easily understand what methods (and even each of their parameters, if any) are available from a given Web service WSDL. However, InfoPath is not very good at revealing the output types until you finish creating the data connection.

Creating a Simple Web Service Using Visual Studio 2005

To follow along with the text and create a Web service in Visual Studio 2005, your computer must meet the following prerequisites:

• Visual C# 2005

• Visual Web Developer 2005

• Internet Information Services (IIS) 6.0 or later with ASP.NET enabled (that comes with .NET Framework 2.0)

• FrontPage 2002 Server Extensions (installable through the Add/Remove Windows Components button)

We’ll start with creating a new Web site in the Visual Studio Start page. This is the default page that appears when Visual Studio 2005 is opened. (Note that this is configurable, so your results may vary.) Click on the Web site link adjacent to the Create label, which is located in the Recent Projects (upper-left) region.

The New Web Site dialog appears with a list of templates (Figure 6.23). We want to choose the ASP.NET Web Service template. For this example, we’re assuming that the Web server is on the same machine as Visual Studio; thus, we’ll create our Web service via HTTP directly to http://localhost/InfoPathWebService. This address is not the address of the Web service, however. It is simply the Web site that will be created for the Web service. This will become clear when we eventually use the Web service. After you click the OK button, it will take a few moments to create the site. If a prompt appears about ASP.NET not being installed, Visual Studio will offer to install it for you.

Figure 6.23. New Web Site dialog with a template for an ASP.NET Web service

Once the site is created, Visual Studio opens the Service.cs file, which shows the code that defines the Web service. A single method called HelloWorld already exists, courtesy of the ASP.NET Web service template we selected. Feel free to experiment with HelloWorld before continuing with the next step.

Let’s add our own Web service method. We will eventually design a form against this Web service to see how it works in InfoPath. Below the HelloWorld method, we’ll want to add our own code. The C# code snippet in Listing 6.1 includes the HelloWorld method to show the relative placement of the new methods.

The code in Listing 6.1 adds a class-wide private variable and three public methods below HelloWorld: FilePath, GetSavedItems, SaveItem, and SaveItems, respectively. Collectively, this new code enables the Web service to get and set data for a specific file on the server. Since a Web service is intrinsically stateless, meaning that it does not remember the data you sent after it finishes executing, this code creates a very rudimentary storage system. Table 6.2 contains a summary of each method and what it does.

Table 6.2. Descriptions of the Web Service Methods Used in Listing 6.1.

Listing 6.1. Writing Code for the Web Service Sample

You might be wondering how this code creates, or shapes, the Web service. Each public method with the [WebMethod] attribute above the definition is directly exposed via the Web service. The GetSavedItems method, for example, is called with no parameters and returns an array of strings. On the other hand, the SaveItems method accepts an array of strings but returns nothing. We will not spend more time reviewing the sample Web service code since Table 6.2 dissects and explains its parts. If you are not familiar with C#, you can also create a Web service in other programming languages as supported by the .NET Framework and ASP.NET. The implementation details of a Web service do not matter to a client application (such as InfoPath).

To better understand how a Web service method is made available to a client such as InfoPath, let’s see what the WSDL looks like. Recall that a WSDL is generated to describe a specific Web service definition. Before getting the WSDL, we’ll first select the Start Debugging option on the Debug menu in Visual Studio. This option starts the default browser and navigates to the Web service default page (Figure 6.24). This page is auto-generated by ASP.NET for Web browsers. A true Web service client like InfoPath does not use this page. To get the WSDL, which is simply an XML file, click the Service Description link in the upper-right part of the page. Notice that “?WSDL” (which is not case-sensitive) is appended to the existing “Service.asmx” page URL.

Figure 6.24. Part of the browser-friendly test page when navigating to the Web service

The WSDL is quite a large and incomprehensible XML document. If you use your browser’s Find functionality to look for all instances of, say, HelloWorld, you will find almost a dozen occurrences. The most interesting parts are within the wsdl:types node, one part of which is shown below. These parts are schema fragments that define the input parameters and output types for the HelloWorld Web service method. A client such as InfoPath uses this information to construct the data source when a data connection is created for a Web service. In this case, the HelloWorld element is the input parameter; HelloWorldResponse is the output. An empty complexType element defines a parameterless method, while the response contains an optional string node called HelloWorldResult. So the HelloWorld method takes no parameters and returns a string, as shown in Listing 6.2.

Listing 6.2. The HelloWorld Definition in the Web Service WSDL

Using the Simple Web Service

Now’s the time to use the Web service we created! Let’s go back to the Web service default page shown in Figure 6.24 and click on the SaveItems method. A page appears that lets you test the method and also shows the choices you have for invoking it outside of the browser. Unfortunately, you’ll notice that the SaveItems method does not allow for testing because “The test form is only available for methods with primitive types as parameters.” Since this method has a string array (which is not a primitive type) for a parameter, we are restricted from using it in this browser test form. Now you understand why we have a SaveItem method that uses a primitive type! Try using that method instead by going back and clicking on the SaveItem link. The test form is now available with an input field into which you can enter a parameter value, as shown in Figure 6.25. Type in any data such as “banana” and click the Invoke button. The blank page that appears is the method response. A blank page is expected since SaveItem has no response; thus you see the empty page, which you can now close.

Figure 6.25. Browser test form for the SaveItem Web service method

It’s just natural that we now use the GetSavedItems method to see if our data was stored properly. Since the method takes no parameters, a click on the Invoke button returns the XML data shown in Listing 6.3. This response is exactly the data that a client application like InfoPath would receive when querying this Web service endpoint.

Listing 6.3. Web Service Response from Running GetSavedItems

InfoPath Web Services

The next step is to use InfoPath with our Web service sample. But unlike the browser test form shown in Figure 6.24, a true Web service client like InfoPath can reside on any computer (including the same computer) with access to the Web server. Let’s jump into InfoPath; however, this time we’ll create a main data connection to our Web service. Creating a main data connection uses the same Data Connection Wizard as secondary connections, so it’s a very similar process.

Instead of designing a new blank form, we’ll choose to design a form from a Web service. Next, we are asked whether we want to receive data, submit data, or both. Since our Web service has save methods as well as get methods, we’ll want to do both so we can submit to those methods that save data and receive from GetSavedItems. Since we’re choosing to receive and submit data, we’re really setting up two separate connections: Web service query and Web service submit data connections. This is one of the only cases when InfoPath sets up two separate connections simultaneously.

On the next page of the Data Connection Wizard we are prompted for the location of the Web service. Remember that this dialog is really asking for the WSDL location (as shown by the example text in Figure 6.26). The location to your WSDL is usually going to start with “http://” and typically includes “?WSDL” appended to the Web service address. But you don’t need to add “?WSDL” to the address since InfoPath will do it for you if the first attempt to get the WSDL fails. Please disregard the Search UDDI button for now. UDDI is a topic for discussion in Chapter 7.

Figure 6.26. Entering an address to the WSDL

Figure 6.27. List of methods from the Web service that can be used to receive data

On the next page (Figure 6.27), we’re presented with the list of all methods available from our Web service. You should recognize the method names from the code we wrote earlier using Visual Studio. Since this step of the wizard is asking us to choose an operation (i.e., a method) for receiving data, the obvious candidate is GetSavedItems. If you were to try choosing a method that does not return data (void is the method return type in code), such as SaveItem or SaveItems, InfoPath would show an error dialog saying the method cannot be used because it does not return any data.

The next wizard page is a summary for the query portion of our data connection. It’s visually identical to the final wizard pages of other data connections, such as Figure 6.7. Enter a name for the query data connection. As we’ve previously mentioned, the name is mainly used for own identification purposes. We’ll leave it as the default name of Main query.

Clicking Next shows what appears to be the same wizard page we saw in Figure 6.26. But notice upon closer examination that the text is slightly different. The text in this page is identifying this step as the submit portion of the data connection. You could set up your form to submit to a different server than the one from which you receive data. But as you may have guessed, the Web service we created has both receive and submit methods. The URL we used in the receive data part of the wizard is conveniently copied for us automatically, so we don’t need to do anything on this page but click Next. Most Web service data connections use the same server or Web service for receiving and submitting data, but it isn’t a requirement and won’t affect the behavior of the form or data connections.

Another strikingly familiar dialog appears on the next page. The only difference between this wizard page and that shown in Figure 6.27 is the text: This page is for submit and the previous one was for query. For submitting data, the SaveItems method is the clear choice. We do not want to use SaveItem because it was created for the sole purpose of using the browser test page. Moreover, we aren’t losing any functionality (as you may have inferred from the code implementation of SaveItem) since SaveItems can still accept a single item.

The next page, shown in Figure 6.28, is specific to submit and at first may appear quite complicated. There are indeed many options available, but for now we’ll use only its basics features to get your feet wet. This page is telling us that the SaveItems method has one parameter called items, which is of the type ArrayOfString.

Figure 6.28. Setting up the parameter to the SaveItems Web service method, which involves selecting which data source node provides its value

For submit to work properly, we need to select the data source node that InfoPath will use for the parameter when submit occurs when filling out the form. To select the data source node, click on the button adjacent to the Field or group text box. The Select a Field or Group dialog appears for selecting a node; see Figure 6.29 for proper node selection. Figure 6.28 shows the resulting dialog after choosing the GetSavedItemsResult group. For now we’ll just say that the GetSavedItemsResult node represents the ArrayOfString type that the SaveItems method expects. The discussion on mapping submit parameters with data source nodes is presented in Chapter 8. We’re done with this page and the submit connection, so click Next to continue to the final page of the wizard.

Figure 6.29. Choosing the data source node to submit to the SaveItems method. The data source is based on the query Web service data connection

It’s no surprise that the final page of the Data Connection Wizard for submit is identical in appearance to the final page for query. Let’s leave the data connection name as Main submit and click Finish to end what may have been the longest wizard session you’ve ever had! As shown in Figure 6.30, InfoPath design mode appears with some content already in the view to help guide you in creating your form based on your Web service connection. (The sample file is named BasicDataFormTemplate.)

Figure 6.30. InfoPath design mode after finishing the Web service Data Connection Wizard

The precanned items in the view shown in Figure 6.30 exist only for your convenience. You may remove or repurpose them without harming your form. The regions labeled Drag query fields here and Drag data fields here are simply suggestions that you need not follow. For now, we can insert the data source field named string into the view as a Bulleted List, although you can use any other compatible control. The control can be inserted anywhere in the view. For this example, we’ll insert it in the Drag query fields here region (even though the node we’re inserting is really under a data field element) so it will visually appear at the top of our form.

The data source shown in Figure 6.30 is fully expanded. Do you recognize the structure of the data source? It came from the Web service query data connection that hooks up to the GetSavedItems method. InfoPath created the myFields, queryFields, and dataFields nodes, but the rest came from the WSDL! (If you want to verify this for yourself, open the WSDL again in a Web browser and search for any of the nodes’ names in the data source.) Since the GetSavedItems method has no parameters, we have no field nodes in the queryFields group. It isn’t necessary to insert the GetSavedItems group since there are no fields that contain data.

We’ve finished designing a very basic Web service form, so let’s take it for a spin by previewing it. (This sample is called WebServiceQuerySubmit.) When the form opens, the first thing you want to do (after dismissing a security dialog, should it appear) is click the Run Query button. You should see “banana” in the list, or whatever data you entered into the test form back in Figure 6.25. Let’s add two new items to our list—“apple” and “orange”—to round out our grocery list of fruit. But to send these to the SaveItems Web service method, we need to invoke submit. To do so, click the Submit button in the Standard toolbar, as shown in Figure 6.31.

Figure 6.31. Querying and submitting our Web service form during preview

Web Service Compatibility with InfoPath

You will find that most Web services work fine during configuration in design mode as well as while users fill out the form. But some will pose problems—especially in design mode. InfoPath’s support for Web services is built on top of a slightly modified version (specifically for the Microsoft Office family of products) of the SOAP Toolkit version 3.0. For those curious readers, SOAP historically stood for Simple Object Access Protocol; that acronym has since been deprecated, but the name “SOAP” remains. The Toolkit is a software application programming interface (API) used by Microsoft Office products to implement Web service functionality. Despite the great variety of Web services supported by the Toolkit, not all are supported by InfoPath.

We won’t go into the numerous types of Web services, but the two most common ones are document/literal and RPC/encoded. The first part of each type, document or RPC, is called the binding style. The second part is called the use, which is either literal or encoded. Since this book is not about Web services, we will not discuss further details about Web services, SOAP, or WSDL. InfoPath supports only document/literal Web services. Supporting this type of Web service was a big gamble, not only for InfoPath but also for Microsoft as a whole. As of this writing, the number of document/literal services is growing while the number of RPC/ encoded services is on a decline. Most Web services providers have agreed to support only the document/literal format in the future since it’s a less complicated format and is easier to support from a client perspective.

What if you’re stuck with a Web service that InfoPath doesn’t support, such as one that is RPC/encoded? We’ve recommended to customers that they create what’s called a proxy Web service that would live in the middle tier between InfoPath (the client) and the unsupported Web service (the server). The basic idea is to create a Web service using a program, such as Visual Studio, that supports RPC/encoded Web services. A Web reference can be made to the unsupported Web service from your proxy Web service, thus establishing data flow from the proxy to the unsupported Web service. The final step is to create a data connection against the proxy Web service using InfoPath. You can find more information on creating a proxy Web service at www.developer.com (as referenced in the Appendix).

If you’re having problems getting a Web service working in InfoPath design mode, ask yourself the following questions to help you diagnose the issue.

• Can you see the Web service test page by using your browser?

• If you’re trying to create a query data connection, does the method you select return any data?

• If you’re trying to create a submit data connection, does the method you select accept input parameters?

• Does the Web service have an invalid inline Schema (in the wsdl:types section)?

• Do you have appropriate permissions to access the Web service endpoint?

Using the TerraServer Web Service in InfoPath

Have you used Microsoft’s TerraServer Web site? Born as a Microsoft Research project, TerraServer is one of the world’s largest map databases containing aerial photographs. It is a free service easily accessed from http://terraserver.microsoft.com. In our opinion, one of the coolest features of the TerraServer project is its Web service. The TerraServer team has a page dedicated to its Web services at http://terraserver.microsoft.com/webservices.aspx, including a white paper and full documentation on the (freely available!) public methods.

Let’s use InfoPath to connect to the Microsoft TerraService .NET Web service (as the TerraServer team likes to call it) to get map data. Start by designing a new form template based on a Web service. We want only to receive data for this example. The Web service WSDL address is http://terraserver.microsoft.com/TerraService2.asmx. We’ll select the GetTile method, which returns a 200-by-200-pixel image as base64Binary, which will bind to InfoPath’s image control. After inserting the dataFields and queryFields group nodes into the form and adding some spice, we get something similar to Figure 6.32. (The sample file is called TerraServer.)

Figure 6.32. Designing the TerraServer form using the GetTile Web service method

Let’s preview this form by clicking the Preview button on the toolbar. Without needing to understand the intricacies of the TerraServer Web service, you can simply use the following values in the queryField controls and click the Run Query button. Accept any security prompts that appear. Figure 6.33 shows the result. As a little bonus, we recommend switching the Theme value from 2 to 4 and running the query again.

Figure 6.33. Querying the TerraServer for a section of the San Francisco Bay near Highway 80

Theme = 2

Scale = Scale16m

Scene = 10

X = 173

Y = 1307

Web Service Repository

Are you looking for a repository of Web services? We recommend looking at the XMethods Web site. (See the reference in the Appendix.) This site has a few hundred Web services with topics ranging from anagrams to radio station finders and even has a Web service that will dial a phone number and verbally articulate the text you send!

Other Web service resources are also available at your disposal. Simply use your favorite engine to search the Web for “Web services” and you’re likely to find numerous references.

SharePoint Libraries and Lists

The SharePoint libraries and lists data connection leverages the rich array of SharePoint Web services. The connection queries data from any Windows SharePoint Services library or list and, similar to other secondary data connections, puts the results into a secondary data source. You may ask why we wouldn’t just use a Web service data connection to talk with SharePoint’s Web services. The problem lies in the Web service WSDL. Most of SharePoint’s methods return an undefined set of data. In the WSDL, this appears as a required xsd:any schema element. InfoPath is not very graceful in handling required xsd:any elements because they are wildcards and have no concrete definition. Thanks to the SharePoint data connection, InfoPath is smart enough to infer the data structure from the Web service and disregard the pesky xsd:any.

SharePoint connections are supported only as secondary data sources. This is because the list data is always read-only and never takes input parameter. If you wanted to upload an item to a document library (which could essentially add metadata to the list you’re querying), you could use the SharePoint document library submit connection. We discuss this connection in Chapter 8.

We have our personal set of photos on a SharePoint picture document library. The SharePoint Web site isn’t very helpful for easily tracking which pictures we have and how long each one has been there. In Chapter 3 we designed a form that tracks our music collection. Why not just use the same form to keep abreast of our photo gallery? (If we wanted to get creative, we could try to set up our form to associate pictures we have taken at some of our favorite bands’ concerts!) Figure 6.34 shows the Pictures library on our SharePoint site.

Figure 6.34. Pictures library on Windows SharePoint Services

Creating a SharePoint library or list connection is one of the easiest connections in InfoPath to set up. We’re starting by adding a data connection in our music collection form template (which you can find in this chapter’s samples as MyMusicAndPhotos); however, you can easily follow our steps while designing any form template. When creating a secondary SharePoint connection to receive data, we select the SharePoint library or list option button in the Data Connection Wizard. The next wizard page asks for your SharePoint server’s Web site address. After we enter the address and click the Next button, all of the lists and libraries on the selected SharePoint server are revealed (Figure 6.35).

Figure 6.35. The libraries and lists available for querying from the SharePoint server

Let’s select the Pictures document library, and go to the next wizard page (Figure 6.36). All properties from that library or list are available for your choosing. In the Pictures library, we choose the Created, Created_By, Title, Picture_Width, Picture_Height, and Description fields. As you may expect, the fields you selected will appear in the secondary data source structure as attributes—similar to a data source created when choosing a database.

Figure 6.36. Choosing which fields from the SharePoint list you want to use for the data connection

The Include data for the active form only option shown in Figure 6.36 is available only for document libraries. Its function is to show the data from the current form only if it was created from that document library. (The option is available in this dialog because we chose a document library; however, selecting it doesn’t make sense since we cannot create forms in this Pictures library.) It is possible to create a form from a document library when you publish a form template to a SharePoint form library. Publishing to SharePoint is discussed in Chapter 9.

After finishing the wizard, we inserted the repeating Pictures group from the secondary data source into the view. Since we’re retrofitting the music-tracking form template with photo tracking, we can insert the Photos Repeating Section under the Music Repeating Section that is already in the view. Figure 6.37 shows the result when previewing the form. (This sample form is also called MyMusicAndPhotos.)

Figure 6.37. Filling out the music and photos form. The list of photos comes from the Pictures library on SharePoint

What’s Next?

This chapter examined each type of InfoPath data connection and walked through examples of usage. We also learned how to set up, create, and test a Web service in Visual Studio 2005. Knowing how to use the main and secondary data sources via data connections, you have the foundation needed to proceed to the next chapter, which delves into extended features involving data connections. You’ll learn about extended data connection and secondary data source features that will help you design more useful and functional forms.