Connecting to Databases in the Data Interop Developer Sample Form [InfoPath 2003 SDK Documentation]
Applies to:
Microsoft Office InfoPath 2003
For information on working with master/detail controls, see "About master/detail controls" in the InfoPath 2003 Help.
Microsoft Office InfoPath 2003 can use different types of data as the data source for a form. In the Data Interop developer sample form, the Microsoft SQL Server Northwind sample database is used as the source of data. Database connections are established in InfoPath by using the Data Source Setup Wizard. This wizard is opened by clicking New from Data Source in the Design a Form task pane.
When you open the Data Source Setup Wizard, you are prompted to select the source of data that you want to use for the form. For the Data Interop developer sample form, the Database (Microsoft SQL Server or Microsoft Access only) option was selected. If you select this data source type, the wizard prompts you to select a database. By clicking Select Database, you can choose an existing data source or connect directly to a specific database file. Click New Source to launch the Data Connection Wizard, then select Microsoft SQL Server and enter the server name and authentication information.
After you select a database on the server, clear the Connect to a specific table check box and finish the Data Connection Wizard. Open the new data source and select the table or view on which to base your form. For the Data Interop developer sample form, the Orders table was selected first. Then, using the Add Table dialog box, the Order Details table (and its relationship to the Orders table) was added. As the tables are added and their relationships to each other are established, the wizard displays the tables and their hierarchical relationships in the Data source structure list. If you select the Show table columns check box, the wizard displays the field names of each table in the Data source structure list; you use the check boxes next to each field name to specify whether a field is included in the SQL query that the wizard constructs.
Note Primary and foreign key fields from each table are always selected and cannot be removed.
After the tables, relationships, and fields have been specified using the Data Source Setup Wizard, you can click Edit SQL to view the SQL query statement that will be used to establish the data source for the form. In the Edit SQL dialog box, you can click Test SQL Statement to verify that InfoPath will be able to create the data source from the information provided. You can also use the Edit SQL dialog box to modify the SQL query statement for more advanced queries.
Note The SQL query statements that are generated by the wizard use the SHAPE syntax of the Microsoft Data Shaping Service for OLE DB to create the table hierarchy within the query. It is possible to use SQL JOIN statements.
The last page of the Data Source Setup Wizard displays summary information about the data source, including the name and file location of the data source, the name of the primary parent table, the number of tables used, and the submit status. The submit status tells you whether the generated SQL query statement will allow for submission of data to the data source.
The last page of the wizard also allows you to specify which view to open in design mode. Two views are created by the Data Source Setup Wizard:
- Query view You can use this view to specify filters with which to query the data source for data.
- Data view You can use this view to display and modify the data returned from a query.
Note In Service Pack 1, InfoPath creates a single default view, instead of two separate views, for the query and submit capabilities. The new default view is designed to guide solution developers by providing specific instructions in particular parts of the form.
After the wizard has finished, InfoPath creates entries in the form definition file (.xsf) about the data source, and it also creates a number of XML schemas that are based on the fields and tables specified for the data source.
Data source information in the form definition file
When a data source for a form has been created using the Data Source Setup Wizard, InfoPath saves information about the data source in the form definition (.xsf) file. The .xsf file stores data source information in the adoAdapter child element of the query element. The connectionString attribute of the adoAdapter element contains the ADO connection string that is used to connect to the data source, and the commandText attribute contains the SQL statement that is used to access the data. The queryAllowed attribute determines whether the data source can be used for retrieving new data, and the submitAllowed attribute determines whether the data source can be used to update data. The following is a portion of the .xsf file used in the Data Interop developer sample form:
<xsf:query>
<xsf:adoAdapter
connectionString="Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=True;
Initial Catalog=Northwind;
Data Source=YourSQLServer"
commandText="shape

{select quot;OrderID","CustomerID",
"EmployeeID","OrderDate","RequiredDate",
"ShippedDate","ShipVia","Freight",
"ShipName","ShipAddress","ShipCity",
"ShipRegion","ShipPostalCode",
"ShipCountry" from "dbo"."Orders" as
"Orders" order by "OrderDate"} as
"Orders"
 append
 ({select "OrderID",
"ProductID","UnitPrice","Quantity",
"Discount" from "dbo"."Order Details" as
"Order Details"}
 relate "OrderID" TO
"OrderID") as "Order_Details""
queryAllowed="yes" submitAllowed="yes">
</xsf:adoAdapter>
</xsf:query>
Notes
User names and passwords are not saved in the .xsf file unless the user chooses to save that information when prompted to log on to the database. Users are prompted for this information when they fill out a form that is connected to a database that requires a logon. If integrated authentication is used as the connection authentication method, no logon information will be saved to the .xsf file. Integrated authentication is more secure than using an explicit user name and password because the logon information is not stored in the .xsf file.
Security Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.
Data source–based XML schemas
When a data source for a form is created using the Data Source Setup Wizard, InfoPath creates three XML schema files based on the tables and fields that were selected. These schema files are used as the data source for the form.
Note The order that you add tables determines the hierarchy of the XML schema files that InfoPath creates.
The first XML Schema file that InfoPath creates is named schema.xsd, and it imports the two other schema files. The main element that schema.xsd defines is the myFields element, which serves as the parent element of the queryFields and dataFields elements. The other two schema files that InfoPath creates are named schema1.xsd and schema2.xsd, and they define the structure of the data sources used for the Query view and the Data view, respectively.
Note You may rename the schema files that the Data Source Setup Wizard produces, but you must manually update the file names listed in the files and documentSchemas elements of the .xsf file and update the references to them contained in the primary schema.xsd file.
The XML Schema files are packaged with all of the other form files that are part of the form template file.