How to: Access External Data Sources
When working with an InfoPath form template, you can write code to access the form's secondary data sources and manipulate the data that they contain.
Each secondary data source is represented by an object instantiated using the DataSource class, and corresponds to stored data, obtained from some external source of data, such as a database or a Web Service query. These data sources are referred to as secondary because when the user saves an InfoPath form, the user is saving the data only in the main (or primary) data source, not the data in the secondary data sources. The connection to a data source is represented by an object instantiated using one of the "data connection" classes, such as the WebServiceConnection class, which represents a data connection to an XML Web Service.
The instantiated DataSource object represents the storage of XML data returned by a data connection (from a database or Web Service query), and the "data connection" class represents the data connection itself (as defined and named using the Data Connections command on the Data tab).
The InfoPath object model also provides a set of data connection classes, containing information about the data connections used by the form.
In Microsoft InfoPath 2003, a data connection is referred to as a data adapter.
Data connections are of two kinds: Query connections are used to obtain the data that is then stored in a secondary data source. Submit connections are used to submit data, to a database or Web service, for example. The submitted data is copied from the main or secondary data sources.
Returns a count of the number of DataSource object instances contained in the collection.
Returns an IEnumerator that can be used to iterate through the collection.
Returns a reference to the specified DataSource object by index value.
Returns a reference to the specified DataSource object by name.
The DataSourceCollection class provides the following method and properties, which form developers can use to interact with an InfoPath secondary data source.
Returns an XPathNavigator object for accessing and editing the data source
Gets a reference to the associated data connection object.
To execute the query on the data connection and insert the returned data as XML into the XML node associated with the DataSource object, use the Execute method of the associated data connection object.
Gets the name of the DataSource object.
Gets a value that indicates whether the data source is in a read-only state
Gets the value of a named property for the specified XML node, which must be a nonattribute node in the main data source.
Sets the value of a named property for the specified XML node, which must be a nonattribute node in the main data source.
The classes for accessing data connections provide different properties and methods that retrieve and submit data through connections to external data sources; the data connection that is associated with a DataSource object is dependent on the type of external data connection. InfoPath implements the following classes for accessing data connections.
Queries an ADO/OLEDB data source; limited to Microsoft Access and Microsoft SQL Server.
Submits to an ADO/OLEDB data source; limited to Microsoft Access and Microsoft SQL Server.
Queries a SharePoint list or document library.
Submits to a SharePoint list or document library.
Connects to an XML Web service.
Queries an XML file.
Submits to an XML file.
Submits a form as an attachment in e-mail.
Queries an external list on a server running SharePoint Foundation 2010 or SharePoint Server 2010.
Submits to an external list on a server running SharePoint Foundation 2010 or SharePoint Server 2010.
The DataSourceCollection object that represents the collection of data sources associated with a form template is accessed through the DataSources property of the XmlForm class. For example, if you create a secondary data source named Employees that retrieves data from the Employees table in the Northwind database, you can use the DataSourceCollection object to set a reference to a DataSource object that represents the retrieved data.
In the following code sample, the name of the secondary data source is passed to the accessor property of the DataSourceCollection class, which returns a reference to the DataSource object that represents the retrieved Employees table data. The XML node that stores the retrieved data from the secondary data source is displayed in a message box using the CreateNavigator method of the DataSource class to access the InnerXml property of the XPathNavigator class.
// Instantiate a variable to access the specified data source // from the DataSourceCollection of the form. DataSource myDataSource = this.DataSources["Employees"]; // Display the XML data from the secondary data source. MessageBox.Show("Data source data: " + myDataSource.CreateNavigator().InnerXml.ToString());
To manipulate the data that is contained in a secondary data source, use the CreateNavigator method of the DataSource class to return a reference to an XPathNavigator object positioned at the node where the secondary data is stored. You can use the properties or methods of the XPathNavigator class to manipulate the data. For more information, see How to: Work with the XPathNavigator and XPathNodeIterator Classes.
The DataConnectionCollection object that represents the collection of data connections associated with a form template is accessed through the DataConnections property of the XmlForm class. For example, if you create a secondary data source named Employees that retrieves data from the Employees table in the Northwind database, you can use the DataConnectionCollection object associated with the form template to set a reference to the DataConnection that represents the connection to the database.
In the following code sample, the name of the secondary data source is passed to the accessor property of the DataConnectionCollection class, which, in this case, returns a reference to the ADOQueryConnection object that represents the connection to the Northwind database. For this to work properly, you must explicitly cast the object being returned to the ADOQueryConnection type. The Connection property of the ADOAdapterObject interface is used to display the ADO connection string in a message box.
// Instantiate a variable to access the specified data connection // from the DataConnectionCollection of the form. // You must cast to the specific data connection type // (ADOQueryConnection) before you can access the data connection. ADOQueryConnection myADOConnection = (ADOQueryConnection)this.DataConnections["Employees"]; // Display the connection information for the data connection. MessageBox.Show("Connection string: " + myADOConnection.Connection);