Using DataSets in InfoPath 2003

Article
07/11/2006

David Gerhardt
3Sharp

October 2004

Applies to:
Microsoft Office InfoPath 2003

Summary: With Microsoft Office InfoPath 2003 Service Pack (SP) 1, you can work with DataSet objects to retrieve and submit data. Find out how to design a form based on a DataSet, and learn how to deal with limitations. (13 printed pages)

Introduction
Designing a Form Based on a DataSet
Modifying the Form UI
Working with DataSet Limitations
Conclusion

Introduction

An ADO.NET DataSet object is a collection of data from one or more sources. If the source is a database, the DataSet contains DataTable objects—which represent the table data—and likely defines the relationships between those tables. The DataSet, however, does not remain connected to its data source. It works in memory, connecting to the source only to retrieve or submit data. When data is submitted, the DataSet uses the GetChanges method to return a new DataTable, which includes all rows from the original DataTable that have been added, changed, or deleted.

Microsoft Office InfoPath 2003 Service Pack (SP) 1 allows you to work with DataSet objects to retrieve and submit data. For example, you can design an InfoPath form solution based on a Web service that uses a query to receive a DataSet. InfoPath interprets the DataSet as an XML structure and uses its schema to create the form's main data connection and data source. When a submit operation is performed, the form data is passed back to the Web service by way of another data connection. That data connection exposes the Web service's submit method, which ultimately submits the added, changed, or deleted rows to the database.

You can learn how to design a form based on a DataSet by reviewing sample business scenarios that illustrate the design steps and your options for modifying the form's user interface. You can also learn how to deal with some limitations on working with DataSet objects in InfoPath.

For detailed instructions on how to set up a Web service that receives a DataSet for use in an InfoPath form, see Lab 9: ADO.NET DataSets in InfoPath 2003.

Designing a Form Based on a DataSet

In a customer-tracking business scenario, a manufacturing company needs to maintain customer and order information. The Northwind sample database available with Microsoft SQL Server 2000 uses tables named "Customers" and "Orders" to track this information. The Customers table uses the CustomerID column as a primary key. The Orders table uses the OrderID column as a primary key and the CustomerID column as a foreign key.

In this scenario, an InfoPath form is used to update the customer and order information. The form sends data to and from the database by using a Web service. The Web service exposes a query method, which retrieves a DataSet with the customer and order information from the database. The submit method of the Web service accepts the DataSet from the form data and then submits modified records to the Customers and Orders tables.

To create a form based on a Web service that returns and updates a DataSet

Start InfoPath, and on the File menu, click Design a Form.
In the Design a new form section of the task pane, click New from Data Connection.
In the Data Connection Wizard, select Web service as the type of data connection for the form to use, and click Next.
Select Receive and submit data to specify how the form works with the Web service, and click Next.
In the text field, type the location of the Web service from which to receive data, and click Next.
Select the operation to use to receive a DataSet, and click Next.
Keep the default name for the data connection that receives data (Main query), and click Next.
In the text field, type the location of the Web service from which to submit data, and click Next. The location should be the same as the one provided in Step 5 and should be filled in automatically.
Select the operation to use to submit a DataSet, and click Next.
In the Parameters dialog box, click the Modify icon next to the Field or group field to select the element that contains the tabular data structure. Select the element (Figure 1), click OK, and then click Next.

Figure 1. Selecting the data to submit to the Web service
Keep the default name for the data connection that submits data (Main submit), and click Finish.

If you want the form you just created to be part of a Microsoft Visual Studio .NET solution, you must save it to the hard disk as a form template (.xsn) file. Then, when you set up the Visual Studio .NET solution, the template is referenced when you create the InfoPath project.

To create an InfoPath project using the saved template

Start Visual Studio .NET 2003.
On the Start Page, click New Project.
In the New Project dialog box, expand the Microsoft Office InfoPath Projects project type.
Click either Visual Basic Projects or Visual C# Projects, type a name and location for the project in the appropriate fields, and click OK.
In the Microsoft Office Project Wizard, select Open existing form template.
In the Location of the InfoPath form template to import field, browse to the location of the template you created previously, and open the template (.xsn) file.
Click Finish to complete the wizard.

In the following subsections, the customer-tracking scenario is expanded to illustrate the types of structures the form can receive.

Nested Structures

The schema that the DataSet uses can use the IsNested attribute to enforce nesting between the Customers and Orders element structures. This attribute, when set to true, enables a logical parent-child relationship in the InfoPath data source. In the customer-tracking scenario, the Customers element is the parent, and the Orders element is the child, as shown in the following example from the schema. Note that the element definitions shown in this example are abbreviated for display purposes, and the IsNested attribute is shown in bold type.

<xs:element name="NorthwindNested" msdata:IsDataSet="true">
    <xs:complexType>
        <xs:choice maxOccurs="unbounded">
            <xs:element name="Customers">
                <xs:complexType>
                    <xs:sequence>
                        <xs:element name="CustomerID" type="xs:string" />
                        . . .                         <xs:element name="Orders">
                            <xs:complexType>
                                <xs:sequence>
                                    <xs:element name="OrderID" 
                                        msdata:ReadOnly="true" 
                                        msdata:AutoIncrement="true" 
                                        type="xs:int" />
                                    <xs:element name="CustomerID" 
                                        type="xs:string" minOccurs="0" />
                                    . . .
                                </xs:sequence>
                            </xs:complexType>
                        </xs:element>
                    </xs:sequence>
                </xs:complexType>
            </xs:element>
        </xs:choice>
    </xs:complexType>
    <xs:unique name="NorthwindNestedKey1" msdata:PrimaryKey="true">
        <xs:selector xpath=".//mstns:Customers" />
        <xs:field xpath="mstns:CustomerID" />
    </xs:unique>
    <xs:unique name="NorthwindNestedKey2" msdata:PrimaryKey="true">
        <xs:selector xpath=".//mstns:Orders" />
        <xs:field xpath="mstns:OrderID" />
    </xs:unique>
    <xs:keyref name="CustomersOrders" refer="NorthwindNestedKey1" 
        msdata:IsNested="true">
        <xs:selector xpath=".//mstns:Orders" />
        <xs:field xpath="mstns:CustomerID" />
    </xs:keyref>
</xs:element>

Nested structures are a logical choice if you want the InfoPath form to provide users with full access to the Customers and Orders tables of the database. When users delete a parent Customers element, all nested Orders elements are automatically deleted. Figure 2 shows the data source for the nested structures identified by this schema example.

Figure 2. Data source with nested structure

Flat Structures

If you set the IsNested attribute to false or do not use it in the DataSet schema, then the InfoPath form receives a flat structure. A flat structure is represented by having the parent and child database tables as sibling elements in the InfoPath form. Figure 3 shows the data source for a flat structure.

Figure 3. Data source with flat structure

Flat structures are useful in cases in which you want only the child table records of the database to be editable. In the customer-tracking scenario, a flat structure is preferable to a nested structure in the InfoPath form if you want users to be allowed to modify or delete only the Orders elements. You could set the Customers table to read-only without it having any effect on the Orders table.

Modifying the Form UI

You can make UI choices when working with DataSet objects in InfoPath that correspond to the structure types identified in the previous section.

Nested Repeating Controls

When working with nested structures, you can use nested repeating controls in the form's UI. These repeating controls, either sections or tables, offer a logical display of the parent-child relationship within InfoPath. In the customer-tracking scenario, you can use repeating tables to display the Customers and Orders elements. Rows within these tables can be added or removed as needed. If a Customers element is deleted, then all of the nested Orders elements are also deleted.

To create repeating tables for the nested structure in the customer-tracking scenario

Click inside the cell that contains the text, Drag data fields here.
In the Design Tasks task pane, click Data Source.
In the Data Source task pane, expand the dataFields element until the Customers element is displayed.
Right-click the Customers element, and click Repeating Table. This action inserts a repeating Customers table with a nested Orders repeating section into the form view.
To change the Orders repeating section to a repeating table, right-click the repeating section control, and on the Change To menu, click Repeating Table.

Master/Detail Functionality

If the form's data source contains a flat structure instead of a nested structure, you can use master/detail functionality to create a relationship between the sibling table elements. In the customer-tracking scenario, the parent Customers table is set up as the master, and a reference to the master is established using the foreign key field of the child Orders table (the detail table). As users make selections in the Customers table, the associated orders for that customer are displayed in the Orders table. You can also prevent users from adding or deleting customers by modifying the properties of the repeating table control for the Customers table.

To create a master/detail relationship for the flat structure in the customer-tracking scenario

Click inside the cell that contains the text, Drag data fields here.
In the Design Tasks task pane, click Data Source.
In the Data Source task pane, expand the dataFields element until the Customers and Orders elements are displayed.
Right-click the Customers element, and click Repeating Table. This action inserts a repeating Customers table into the form view.
Right-click the Orders element, and click Repeating Table. This action inserts a repeating Orders table into the form view.
Right-click the repeating table control for the parent table (Customers, in our example), and click Repeating Table Properties.
In the Repeating Table Properties dialog box, click the Master/Detail tab.
In the Master/detail settings area, click Set as master. In the Master ID field, type a unique name, and click OK.
Right-click the repeating table control for the child table (Orders, in our example), and click Repeating Table Properties.
In the Repeating Table Properties dialog box, click the Master/Detail tab.
In the Master/detail settings area, click Set as detail. In the Link to master ID field, select the unique name of the master table.
In the Master/detail relationship area, click By key field. Select the primary key field of the master table for the Key field (master), and select the foreign key field of the detail table for the Key field (detail). Figure 4 shows how to use the CustomerID field of the Customers table and the CustomerID of the Orders table to create the master/detail relationship.

Figure 4. Creating the master/detail relationship
Click OK to save the settings and close the dialog box.
To prevent users from adding or deleting customers, right-click the Customers repeating table control, click Repeating Table Properties, clear the option to Allow users to insert or delete rows, and click OK.

Submit Button

Whether you are working with nested or flat structures, you can use a button control to pass data to the Web service you are using to design the form. When you design the form based on a Web service that uses a DataSet, InfoPath sends only records that are added, modified, or deleted to the Web service. These changed records comprise a DiffGram, which the Web service then submits to the database. (For more information, see DiffGrams in the Microsoft .NET Framework Developer's Guide.) The button control uses the data connection that submits data, which you create when you design the form (see Steps 8 through 11 in Designing a Form Based on a DataSet), to pass the DiffGram to the Web service.

To add a button control for submitting data

Click the bottom of the form to place the insertion point.
In the Design Tasks task pane, click Controls.
In the Controls task pane, click Button.
Double-click the button control that you added previously, and in the Action list of the Button Properties dialog box, click Submit.
In the Submitting Forms dialog box, in the Choose a data connection for submit field, ensure that you select your submit data connection, as shown in Figure 5.

Figure 5. Tying the Submit button to a data connection
Click OK twice to save the button control properties.

Working with DataSet Limitations

You can learn how to handle some limitations of working with DataSet objects in InfoPath, as described in the following sections.

Multiple Datasets

The Web Services Description Language parser that InfoPath uses is not designed to support multiple DataSet instances. As a result, InfoPath does special checking for multiple DataSet instances. You can design forms against a Web service operation that has multiple DataSet instances, but only one of those DataSet instances can exist while a user is editing the form.

Cascading Rules

With DataSet objects, the SetNull and SetDefault cascading rules identify behavior that occurs when the user deletes rows that are referenced by foreign keys. If you specify the SetNull rule, the referencing rows are orphaned, meaning the foreign key values are set to null. If you use the SetDefault rule to identify a default primary key value, then the foreign key values are set to that default when the user deletes the referenced row.

InfoPath does not support these cascading rules for DataSet objects. Instead, when a user works with a flat data structure you create in InfoPath, the user must remove all child records before a parent entry can be deleted. In the customer-tracking scenario, if users are allowed to delete customers, all orders associated with the customer must be removed before that customer can be deleted.

Rich-Text Fields

The InfoPath implementation of DataSet objects ignores embedded columns with different namespaces. Furthermore, ADO.NET DataSet schemas do not support the xsd:any structure. As a result, data fields within the InfoPath form's DataSet structure cannot be bound to rich-text controls, because the XHTML namespace is ignored. Instead, you can map the text field intended to contain XHTML content to a rich-text field that exists outside of the DataSet structure in the data source.

In the customer-tracking scenario, the ShipVia field of the Orders element needs to allow for rich text so that links to shipping companies (for example, FedEx) can be added. Within the DataSet structure, the ShipVia element is defined with the string data type, a setting that cannot be changed. As an alternative, a structure for shipping company information is added to the data source outside of the DataSet structure, as shown in Figure 6.

Figure 6. Adding mapped shipping company information

In this example, the repeating my:ShipCompany element is added to the myFields root. The CustomerID and OrderID elements are used to uniquely identify the Orders element that contains the mapped ShipVia. The CompanyName element is defined with the XHTML data type, and that field is displayed in the form view instead of the ShipVia field. Then, for the CompanyName element, an OnAfterChange event is added that encodes the content as a string after a user edits that field.

public void CompanyName_OnAfterChange(DataDOMEvent e)
{
    if (e.IsUndoRedo)
    {
        return;
    }
    if(e.Operation != "Insert")
    {
        return;
    }
    string orderID = e.Site.selectSingleNode("../my:OrderID").text;
    string custID = e.Site.selectSingleNode("../my:CustomerID").text;
    string companyName = System.Web.HttpUtility.HtmlEncode(e.Site.xml);
    IXMLDOMNode shipVia = thisXDocument.DOM.selectSingleNode
        ("//*[local-name() = 'Orders']/*[local-name() = 'OrderID']
        [.='" + orderID + "']/../*[local-name() = 'CustomerID']
        [.='" + custID + "']/../*[local-name() = 'ShipVia']");
    shipVia.text = companyName;
}

After the form data is loaded, the content of the ShipVia element is decoded and added back to the CompanyName element as XHTML. The following example shows how the rich-text content is restored for the CompanyName.

IXMLDOMNode newDataSet = thisXDocument.DOM.selectSingleNode
    ("//*[local-name() = 'GetOrdersNestedResponse']/*[local-name() 
    = 'GetOrdersNestedResult']/*[local-name() = 'NorthwindNested']");
IXMLDOMNodeList orderList = newDataSet.selectNodes("*[local-name() 
    = 'Customers']/*[local-name() = 'Orders']");
IXMLDOMNode myFields = 
    thisXDocument.DOM.selectSingleNode("//dfs:myFields");
for(int i=0; i<orderList.length; i++)
{
    string orderID = orderList[i].selectSingleNode("*[local-name() = 
        'OrderID']").text;
    string custID = orderList[i].selectSingleNode("*[local-name() = 
        'CustomerID']").text;
    string shipVia = orderList[i].selectSingleNode("*[local-name() = 
        'ShipVia']").text;
    IXMLDOMNode richShipCompany = thisXDocument.DOM.createNode(1, 
        "my:ShipCompany", 
        "https://schemas.microsoft.com/office/infopath/2003/myXSD/
         2004-03-23T01:57:21");
    IXMLDOMNode richOrderID = thisXDocument.DOM.createNode(1, 
        "my:OrderID", 
        "https://schemas.microsoft.com/office/infopath/2003/myXSD/
         2004-03-23T01:57:21");
    IXMLDOMNode richCustID = thisXDocument.DOM.createNode(1, 
        "my:CustomerID", 
        "https://schemas.microsoft.com/office/infopath/2003/myXSD/
         2004-03-23T01:57:21");
    IXMLDOMNode richCompanyName = thisXDocument.DOM.createNode(1, 
        "my:CompanyName", 
        "https://schemas.microsoft.com/office/infopath/2003/myXSD/
         2004-03-23T01:57:21");
    richOrderID.text = orderID;
    richCustID.text = custID;
    if(shipVia != "")
    {
        System.IO.StringWriter richChild = new StringWriter();
        System.Web.HttpUtility.HtmlDecode(shipVia, richChild);
        MSXML2.DOMDocument50Class xmlDoc = new 
            MSXML2.DOMDocument50Class();
        xmlDoc.async = false;
        xmlDoc.loadXML(richChild.ToString());
        IXMLDOMNodeList docChildren = 
            (IXMLDOMNodeList)xmlDoc.documentElement.childNodes;
        for(int j=0; j<docChildren.length; j++)
        {
            richCompanyName.appendChild
                ((IXMLDOMNode)docChildren[j].cloneNode(true));
        }
    }
    else
    {
        richCompanyName.text = "";
    }
    richShipCompany.appendChild(richCustID);
    richShipCompany.appendChild(richOrderID);
    richShipCompany.appendChild(richCompanyName);
    myFields.appendChild(richShipCompany);
}

Calculated Fields

If your solution uses calculated fields, there may be situations in which the totals are double the expected values. The inflated totals may result from hidden fields that InfoPath uses internally within the DataSet structure. To work around this issue, use absolute paths in your calculated fields.

Conclusion

With InfoPath SP 1, you can use DataSet objects in your form solutions. The most common way to incorporate DataSet objects into a solution is to design the form based on a Web service. The Web service exposes a query method to return a DataSet to the form. InfoPath interprets the DataSet as an XML structure, which allows users to make changes to the data. When the form data is sent back to the originating database, the Web service's submit method is exposed. This method submits only modified rows back to the database.

By taking advantage of the ability to work with DataSet objects in InfoPath 2003 SP 1, you have more choice of data sources to use for your InfoPath forms and more flexibility in how you manage data.

Share via