Lab 9: ADO.NET DataSets in InfoPath 2003

 

Microsoft Corporation

April 2004

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. (11 printed pages)

Contents

Prerequisites
Scenario
Lab Objective
Setup
Exercises
Conclusion

Download the odc_INF03_Labs.exe sample file.

Prerequisites

  • A familiarity with a .NET programming language such as Microsoft® Visual C#®
  • An understanding of Microsoft Office InfoPath™ 2003 editing environment
  • An understanding of Microsoft Visual Studio® .NET
  • An understanding of how to connect to Microsoft SQL Server™ 2000

Scenario

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.

Lab Objective

In this lab, you learn how to do the following:

  • Create a simple Web service in Visual Studio .NET
  • Design an InfoPath form that is based on that Web service
  • Use the Web service to receive customer data
  • Use the Web service to submit customer data

Setup

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

    Note   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.

Exercises

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.

    Note   For more information about the ADO.NET DataSet, see the DataSet Class topic in the .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

  1. Start Microsoft Visual Studio .NET.

  2. On the File menu, point to New, and then click Project.

  3. In the New Project dialog box, in the Project Types pane, select Visual C# Projects.

    Note   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.

  4. In the Templates pane, click ASP.NET Web Service.

  5. 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

  1. On the View menu, select Toolbox.

  2. Click the Data tab.

  3. Drag the SqlDataAdapter data object to the design mode surface.

    The Data Adapter Configuration Wizard opens.

  4. In the Data Adapter Configuration Wizard, click Next.

  5. 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.

    Note   For more information, see Creating Connections to SQL Server.

  6. On the Choose a Query Type page, select Use SQL Statements. Click Next.

  7. On the Generate the SQL statements page, click Query Builder to design the query. The Add Table dialog box appears.

  8. In the Add Table dialog box, click the Tables tab, and then click Customers.

  9. To add the table, click Add, and then click Close.

  10. In the Query Builder dialog box, check * (All Columns) in the Customers window, and then click OK.

  11. On the Generate the SQL statements page, click Next.

  12. On the View Wizard Results page, click Finish.

  13. 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:

Figure 1. Designer surface

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

  1. On the designer surface, right-click the sqlDataAdapter1 data object, and then, on the Shortcut menu, click Generate Dataset .
  2. 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 the following:

Click here for larger image.

Figure 2. Designer surface (Click picture to view larger image)

Now that you created the DataSet, you need to expose it from the Web service.

To expose the DataSet

  1. On the View menu, click Code.

  2. To expose the DataSet from the Web service, copy the following code:

    [WebMethod] // A method to expose on the Web service
    public DataSet ReceiveDataSet()
    {
       // get the data from the database and put it in the DataSet
    sqlDataAdapter1.Fill(dataSet11);
    // return the DataSet object with the filled data
    return dataSet11;
    }
    
  3. Paste the code copied from the previous step over the following code:

    //[WebMethod]
    //public string HelloWorld()
    //{
    //return "Hello World";
    //}
    
  4. On the Build menu, click Build solution. If you encounter build errors, review the previous steps and correct any errors you find before continuing.

A successful build looks like this:

Click here for larger image.

Figure 3. Successful build (Click picture to view larger image)

Now that you finished setting up the DataSet, you should test the ReceiveDataSet method you created.

To test the Web service ReceiveDataSet method

  1. On the Debug menu, click Start.

    A Web page called Service1 Web Service opens in your Web browser.

  2. On the Web page, click on the ReceiveDataSet operation.

  3. To run the code that you added earlier, click Invoke.

  4. Verify that the results of invoking the Web service resembles the following figure:

    Click here for larger image.

    Figure 4. Results of invoking the Web service (Click picture to view larger image)

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)

  1. In Visual Studio .NET, on the View menu, click Code.

  2. Copy the following code example:

    [WebMethod] // A method to expose on the Web service
    public void SubmitDataSet(DataSet ds)
    {
       // Pass the DataSet to ADO.NET; database updates automatically
    sqlDataAdapter1.Update(ds);
    }
    
  3. Paste the code immediately below the code you pasted in the previous task, "Expose the DataSet."

  4. On the Build menu, click Build Solution. If you encounter build errors, review the previous steps and correct any errors you find before continuing.

    Note   It is not possible for you to 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

  1. Start InfoPath.

  2. In the Fill Out a Form dialog box, click Design a Form.

  3. In the Design a Form task pane, click New from Data Connection.

  4. In the Data Connection Wizard, click Web service, and then click Next.

  5. On the next page of the wizard, click Receive and submit data, and then click Next.

  6. On the next page of the wizard, type the location of the Web service, and then click Next.

    Note   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.

  7. Under Select an operation on the next page of the wizard, click ReceiveDataSet, and then click Next.

  8. Keep the default name for the data connection used to receive data, and then click Next.

  9. 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.

  10. Under Select an operation, click SubmitDataSet, and then click Next.

  11. 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.

  12. 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.

    Figure 5. Select a Field or Group dialog box

    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.

  13. 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

  1. In the Design Tasks task pane, click Data Source.

  2. In the Data Source task pane, expand the dataFields group and every group contained within it.

    Figure 6. Data Source task pane

  3. In the form, click inside the Drag data fields here cell.

  4. 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.

    Click here for larger image.

    Figure 7. Layout table (Click picture to view larger image)

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:

Click here for larger image.

Figure 8. Northwind Traders Customer form (Click picture to view larger image)

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.

Note   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

  1. With the form open in design mode, click Preview Form on the Standard toolbar.
  2. 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

  1. On the Insert menu, point to Section, and then click Customers.

  2. In the form, specify a unique Customer ID and any other data for the customer.

    Note   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.

  3. 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

  1. In the form, click Run Query.

  2. Choose any customer, and then edit the data for that customer.

    Note   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.

  3. On the Standard toolbar, click Submit.

Sales representatives can also delete a customer record they no longer need.

To delete a customer record

  1. In the form, click Run Query.
  2. Select the row that you want to delete, and then press DELETE.
  3. On the Standard toolbar, click Submit.

Conclusion

After completing this lab, you should know how to create a Web service that receives submits data to a database. You also learned how to design an InfoPath form that is based on the Web service.