Lab 9: Working with ADO.NET DataSets in InfoPath 2003
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Applies to: Microsoft Office InfoPath 2003, Microsoft Visual Studio .NET
Summary: Create a Web service that receives data from, and submits data to, a database. Learn how to design an InfoPath form that is based on the Web service. ( printed pages)
Download the odc_INF03_Labs.exe sample file.
The information technology (IT) department at Contoso Corporation plans to use a Web service to expose data from an internal database that is used by the sales force to store customer information. This customer information is available in the Northwind Traders database. As part of this project, the IT department must design an InfoPath form that allows sales representatives to interact with and update customer data from that database. To expose data from the database in the form, the IT department must design the form so that it retrieves data from and submits data to the Web service.
To complete this lab, you need the following software installed on your computer:
Microsoft Windows 2000, Microsoft Windows XP Professional, or Microsoft Windows Server 2003
Microsoft SQL Server 2000
Northwind Traders sample database (included in SQL Server 2000)
Microsoft Internet Information Services
Microsoft .NET Framework
Microsoft Visual Studio .NET and one of the following:
Microsoft Visual C# Standard
Microsoft Visual C# Professional
Microsoft Visual C# Enterprise
Microsoft Visual C# Architect Edition
Microsoft Visual Basic
ASP.NET platform for server applications
For more information about how to install ASP.NET to work with Internet Information Services and the .NET Framework, see .NET Framework Developer's Guide ASP.NET Platform Requirements.
Exercise 1: Create a Web Service and Establish a Connection to the Database
In this exercise, you set up a Web service for Contoso that returns customer data from the Northwind Traders sample database. The sales representatives at Contoso need to query customer data, as well as submit data to the database by means of the Web service.
The Web service allows the data in the database to be exposed in the form. ADO.NET helps expose the data by doing the following:
Establishing and managing the connection to the database.
Issuing a query to select or update the data in the database.
Encapsulating the database data into an ADO.NET DataSet object that InfoPath understands.
For more information about the ADO.NET DataSet, see the DataSet Class topic in the Microsoft .NET Framework Class Library.
InfoPath can create a form by analyzing the DataSet returned by the Web service. The DataSet contains an XML Schema when it is sent as XML from the Web service to InfoPath. This XML Schema defines the structure of the data the DataSet contains. InfoPath uses the XML Schema to create the main data connection and data source.
In the following procedures, you create the DataSet and expose it in a form using a Web service. You finish by writing another Web service method to accept the DataSet and update the database.
As the first step in this process, you must create an ASP.NET Web service project in Visual Studio .NET.
To create an ASP.NET Web service project
Start Microsoft Visual Studio .NET.
On the File menu, point to New, and then click Project.
In the New Project dialog box, in the Project Types pane, select Visual C# Projects.
This lab uses Visual C#. You can also use other languages, such as Visual Basic or the Microsoft Visual J# development tool, to create an ASP.NET Web Service.
In the Templates pane, click ASP.NET Web Service.
To create a project with the default name WebService1, click OK. For more information, see Microsoft Knowledge Base Article 306005, HOWTO: Repair IIS Mapping After You Remove and Reinstall IIS.
Now that you created a project, you are ready to specify the connection and database commands that the data adapter uses to select records and handle changes to the Northwind Traders database.
To establish database connectivity
On the View menu, select Toolbox.
Click the Data tab.
Drag the SqlDataAdapter data object to the design mode surface.
The Data Adapter Configuration Wizard opens.
In the Data Adapter Configuration Wizard, click Next.
On the Choose Your Data Connection page, click New Connection to create a data connection to the Northwind Traders database server.
The Data Link Properties dialog appears.
When configuring the Data Link Properties dialog box, choose Use a specific user name and password. SQL Server has a system administrator (sa) account. Use sa for the user name and enter the system administrator password. Also check the Allow saving password check box. After configuration is complete, click Next on the Choose Your Data Connection page.
For more information, see How to: Create Connections to SQL Server Databases.
On the Choose a Query Type page, select Use SQL Statements, and then click Next.
On the Generate the SQL statements page, click Query Builder to design the query.
In the Add Table dialog box, click the Tables tab, and then click Customers.
To add the table, click Add, and then click Close.
In the Query Builder dialog box, check * (All Columns) in the Customers window, and then click OK.
On the Generate the SQL statements page, click Next.
On the View Wizard Results page, click Finish.
If prompted to include the password in the connection string, click Include password.
At this point, the designer surface should look similar to the following figure.
Now that you specified the connection and database commands that the data adapter uses to select records and handle changes to the Northwind Traders database, you need to create the DataSet.
To create the DataSet
On the designer surface, right-click the sqlDataAdapter1 data object, and then, on the Shortcut menu, click Generate Dataset .
In the Generate Dataset dialog box, select the Add this dataset to the designer check box. Click OK.
At this point, the designer surface should look similar to Figure 2.
Now that you have created the DataSet, you need to expose it from the Web service.
To expose the DataSet
On the View menu, click Code.
To expose the DataSet from the Web service, copy the following code.
Paste the code copied from the previous step over the following code.
On the Build menu, click Build solution. If you encounter build errors, review the previous steps and correct any errors you find before continuing.
Figure 3 shows a successful build.
Now that you have finished setting up the DataSet, you should test the ReceiveDataSet method you created.
To test the Web service ReceiveDataSet method
On the Debug menu, click Start.
A Web page called Service1 Web Service opens in your Web browser.
On the Web page, click the ReceiveDataSet operation.
To run the code that you added earlier, click Invoke.
Verify that the results of invoking the Web service resembles Figure 4.
Once you tested the ReceiveDataSet method that you create, you are ready for the Web service to consume the DataSet.
To consume the DataSet (when InfoPath submits changes)
In Visual Studio .NET, on the View menu, click Code.
Copy the following code.
Paste the code immediately below the code you pasted in the "Expose the DataSet" task.
On the Build menu, click Build Solution.
If you encounter build errors, review the previous steps and correct any errors before continuing.
You cannot test the SubmitDataSet as you did with ReceiveDataSet method. That is because the Web interface does not support invocation of methods containing non-primitive data types as parameters. (Integer, string, and Boolean are examples of primitive data types.) In this case, DataSet is a parameter for the SubmitDataSet method.
Exercise 2: Design a Form Connected to the Web Service
In this exercise, you design a form that allows Contoso sales representatives to view and interact with customer data from the Northwind Traders database. Sales representatives can then use the form to make changes to customer data and submit those changes to the database.
You use the InfoPath Data Connection Wizard to connect your form to the Web service you created in Exercise 1. As you follow the steps in the Data Connection Wizard, InfoPath queries the Web service to determine the structure of the DataSet. This query invokes the ReceiveDataSet method.
In the Data Connection Wizard, you also configure the submit adapter to allow InfoPath to submit the DataSet back to the Web service. The DataSet is sent to and processed by the SubmitDataSet method. The following procedure provides more detail:
To establish a data connection to the Web service
In the Fill Out a Form dialog box, click Design a Form.
In the Design a Form task pane, click New from Data Connection.
In the Data Connection Wizard, click Web service, and then click Next.
On the next page of the wizard, click Receive and submit data, and then click Next.
On the next page of the wizard, type the location of the Web service, and then click Next.
If you do not know the location of the Web service, you can find it by repeating the first step of testing the ReceiveDataSet method explained at the end of Exercise 1. The location is revealed in the browser Address Bar. In this lab, the URL http://MyComputer/WebService1/Service1.asmx was used. Your address is similar, except that MyComputer is replaced by your computer's name or localhost.
Under Select an operation on the next page of the wizard, click ReceiveDataSet, and then click Next.
Keep the default name for the data connection used to receive data, and then click Next.
The submit data location should be automatically filled in for you. It is a similar address as used in step 6. Verify that this is the case, and then click Next.
Under Select an operation, click SubmitDataSet, and then click Next.
In the Parameters list on the next page of the wizard, double-click s0:ds. This represents the DataSet parameter that the Web service expects to receive.
In the Select a Field or Group dialog box, click the plus sign next to the following groups to expand them: dataFields, s0:ReceiveDataSetResponse, and ReceiveDataSetResult. The DataSet is now shown as ns1:DataSet1. Select this group, and then click OK.
If the submit adapter is not properly configured, the DataSet is not sent properly to the Web service. That is, the DataSet is sent as plain XML if the DataSet group itself is not chosen. The Web service does not understand the DataSet as plain XML, but rather a special XML format. In this step, the DataSet group is selected which allows the Web service to correctly recognize the DataSet.
Click Next, and then click Finish.
A connection to the Web service is established. The receive and submit Web methods are selected and the DataSet group is chosen as the data to submit. Finishing the wizard leaves you with a mostly empty view. In the next exercise, you design the view.
To design the view
In the Design Tasks task pane, click Data Source.
In the Data Source task pane, expand the dataFields group and every group contained within it.
In the form, click inside the Drag data fields here cell.
On the Data Source task pane, right-click the group named Customers, and then on the Shortcut menu, click Repeating Table. On the form, widen the layout table so that you can see all of the columns.
Because the ReceiveDataSet method does not have any parameters, there are no query fields to insert into the view. At this point, you can choose to make the form more attractive by adding a title or color scheme, or by removing columns you don't need. For example, you might create a form that is similar to the following:
Exercise 3: Fill Out the Form
Your form design is now complete. When the sales representatives run the query, the form shows all customers from the Northwind Traders database. A user may add, remove, or change an existing customer record, and then submit those updates to the Web service. This, in turn, updates the database.
The database may not accept certain changes to customer data. For example, in the Northwind Traders sample database, a relationship exists between the Customer ID field in the Customer table and the Customer ID field in an Orders table. Because the Northwind Traders database is not configured to cascade updates when a record in the primary table is changed, any change to a Customer ID may fail. In this case, the Web service (through ADO.NET) throws an error. This exception appears to the user as a submit-related error message.
To view customer records in the form
With the form open in design mode, click Preview Form on the Standard toolbar.
In the form, click Run Query.
After a sales representative runs the query, they can add additional customer records.
To add a new customer record
On the Insert menu, point to Section, and then click Customers.
In the form, specify a unique Customer ID and any other data for the customer.
If you do not specify a unique Customer ID based on the data already existing in the database, you cause a unique constraint violation when the database is updated. The update fails, and Submit fails with the database error message. Unless you are sure the data is unique, it is a good practice to first query the data, and then add a new table row before submitting the changes.
On the Standard toolbar, click Submit.
As mentioned, in addition to adding new customer records, sales representatives can also edit existing records.
To edit a customer record
In the form, click Run Query.
Choose any customer, and then edit the data for that customer.
Use caution if you choose to edit a field associated with the database's primary or foreign table key. In the Northwind Traders database example, the Customer ID field serves as the primary key and participates in a relationship with the Orders table. If you change Customer ID "ALFKI" to "GOOBA," for example, the change fails when you submit it because customer ALFKI has orders associated with it. The database is not set up to automatically cascade updates.
On the Standard toolbar, click Submit.
Sales representatives can also delete a customer record they no longer need.
To delete a customer record
In the form, click Run Query.
Select the row that you want to delete, and then press DELETE.
On the Standard toolbar, click Submit.
For more information about InfoPath, see the following resources: