Querying and Updating a Database Using Web Services in InfoPath and ASP.NET 

 

Mike Talley
Microsoft Corporation

April 2005

Applies to:
    Microsoft Office InfoPath 2003

Summary: Learn how to create a Web service and access that Web service in Microsoft Office InfoPath 2003 Service Pack (SP) 1 and a Web application using Microsoft ASP.NET. (11 printed pages)

Contents

Introduction
Overview
Requirements
Section 1: Create the Web Service That Returns and Accepts a DataSet
Section 2: Create the InfoPath Form Template
Section 3: Create the ASP.NET Web Application
Design Considerations for Web Services
Limitations of InfoPath
Conclusion
Additional Resources

Introduction

This proof-of-concept article demonstrates how some functionality of a Microsoft Office InfoPath solution, based on a Web service, can also be offered as a Microsoft ASP.NET Web application. This is useful when InfoPath is not available on the client computer to fill out a form. Commonly referred to as a "reach" experience, this kind of browser-based Web application allows a user to edit the same information in the InfoPath form, but without the features of InfoPath, such as rich-text formatting, the ability to check spelling, and the ability to edit the data if a network connection is not available.

The Web service uses the GetCustomers method to return a Microsoft .NET DataSet, which contains records from the Customers table of the Northwind SQL database. The Web service uses the UpdateCustomers method to accept changes to the records. Both methods are used in the InfoPath form template and the ASP.NET Web application, giving each solution the ability to edit the database records.

This article also includes architectural considerations for Web services and information about design limitations of InfoPath. These sections outline problems that can arise when designing and deploying Web services in the enterprise environment, and examine the limitations of InfoPath forms based on Web services.

Overview

This article describes three tasks:

  1. Creating the Web service that returns and accepts a DataSet
  2. Creating the InfoPath form template
  3. Creating the ASP.NET Web application

You build each of these three items separately in either Microsoft Visual Studio .NET 2003 or InfoPath 2003, beginning with the Web service.

Requirements

The scenario in this article requires the following:

  • Visual Studio .NET 2003
  • InfoPath 2003 SP 1
  • Microsoft SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
  • Microsoft Internet Information Services (IIS)

Section 1: Create the Web Service That Returns and Accepts a DataSet

This Web service retrieves information from the Customers table of the Northwind SQL database. If you do not have Microsoft SQL Server 2000, you can install the Microsoft SQL Server 2000 Desktop Engine, and install the Northwind database.

In Visual Studio .NET 2003, create a Microsoft Visual C# project with the ASP.NET Web Service template. Name it nwindCustomers.

  1. In the design surface of Service1.asmx.cs, insert a SQLConnection.
  2. Select SQLConnection1. In the Properties Window, click the ConnectionString menu and select New Connection.
  3. Configure the connection to connect to the Northwind database on the SQL Server. Whenever possible, use Microsoft Windows NT Integrated Security to connect to the database.
  4. Insert a SQLDataAdapter on the design surface.
  5. In the Data Adapter Configuration Wizard, click Next and select the connection you just created. Click Next.
  6. Select Use SQL Statements and click Next.
  7. In the box labeled "What data should the data adapter load into the DataSet?", type SELECT * FROM Customers. As an alternative, you can use the Query Builder to create your query.
  8. Click the Advanced Options button, ensure that all three check boxes are selected, and click OK.
  9. In the Properties Window of SQLDataAdapter1, click Generate dataset.
  10. Create a DataSet named "dsCustomers1" and select the Add this dataset to the designer check box. Click OK.

Add Code to the Web Service

  1. Insert the following code below the automatically inserted Web method section:

        [WebMethod]
    public DataSet GetCustomers()
    {
    sqlConnection1.Open();
    sqlDataAdapter1.Fill(dsCustomers1);
    sqlConnection1.Close();
    return dsCustomers1;

    }

    [WebMethod] public DataSet UpdateCustomers(DataSet dsUpdated) {

    if (dsUpdated!=null)
    {
        sqlConnection1.Open();
        int NumRows = sqlDataAdapter1.Update(dsUpdated);
        if (NumRows </tt>&gt;<tt xmlns:asp="https://msdn2.microsoft.com/asp"> 0) sqlDataAdapter1.Fill(dsUpdated);
        sqlConnection1.Close();
    }
    
    return dsUpdated;
    

    }

  2. Click File, and then click Save All.

  3. Click Debug, and then click Start Debugging.

  4. When the Web page loads, click GetCustomers.

  5. Click the Invoke button.

  6. Ensure that you see XML tags and all customer information from the Customers database, and then close both Web pages. Note that steps 3 through 6 will work only if the Web service is running on the local computer or if the Visual Studio Remote Debugging tools are installed.

  7. Click the Build menu, and then click Build Solution.

  8. Close the project.

Section 2: Create the InfoPath Form Template

  1. Open InfoPath and select Design a Form from the Fill Out a Form dialog box.
  2. In the Design a Form task pane, select New from Data Connection.
  3. In the first step of the Data Connection Wizard, select Web service, and then click Next.
  4. Select Receive and submit data, and then click Next.
  5. Type the URL of your Web service. In this example it would be https://localhost/NWindCustomers/service1.asmx. Click Next.
  6. In the Select an operation box, select GetCustomers, and then click Next.
  7. Click Next unless you want to change the name of the main query.
  8. The next URL is for the submit process, and the URL should appear automatically. Click Next.
  9. In the Select an operation box, select UpdateCustomers, and then click Next.
  10. Click the Modify button (Image of Modify button) to the right of the Field or group box, and then select the node under the dataFields group that represents the full DataSet. In this case, it should be ns1:dsCustomers.
  11. Click Next and then click Finish.
  12. Drag the Customers node from the dataFields group to the view, and choose to bind it to a Repeating section with controls.
  13. Preview the form to ensure that the Web service is returning the Customers information. You can also modify a field (with the exception of the CustomerID field because it is the primary key) and click Submit to test that the form can submit changes back to the database through the Web service.

Section 3: Create the ASP.NET Web Application

  1. In Visual Studio .NET 2003, create a project with the ASP.NET Web Application template. Name it Customers.
  2. On the main Web form (WebForm1.aspx), insert a DataGrid control.

Add the Ability to Edit Data

  1. Select DataGrid1 and press F4 to display the Properties Window, and then click Property Builder at the bottom of the window.
  2. Click the Columns tab.
  3. Under Column list, browse the Available columns list and open the Button Column node.
  4. Select Edit, Update, Cancel and click the Add button (Image of Add button) to add the buttons to the Selected columns box.

Add Columns from the Database

  1. In the Property Builder dialog, click the Columns tab.
  2. Clear the Create columns automatically at run time box.
  3. Under Column list, select Bound Column and click the Add button to add the column to the Selected columns box.
  4. Type the name of the database field in the Data Field text box. To display text other than the Data Field in the header of the Data Grid column, type that into the Header text field, and then click Apply.
  5. Repeat steps 3 and 4 for each database field you want to show in the Data Grid, such as CompanyName, ContactName, Address, City, Region, PostalCode, Country, Phone, and Fax.
  6. Click OK.

Add a Web Reference to the Project

  1. Add a Web reference to the Web service you created in Section 1: Create the Web Service That Returns and Accepts a DataSet.
  2. Add a using statement for the Web service reference. It begins with the namespace you used when creating the Web service, such as:
        using MyServer.Service1;

Insert Code to Initialize the DataGrid

  1. In the Page_Load event, insert the following code:

        if (!IsPostBack)
    {
    
    Web_service_namespace
    .Service1 wsCustomers =
            new Web_service_namespace.Service1();
            DataSet myCustomers = wsCustomers.GetCustomers();
            DataGrid1.DataSource = myCustomers;
            DataGrid1.DataBind();
        }
    

  2. In this code and the following code, replace Web_service_namespace with the namespace of your Web service. This is the same as your using statement syntax in the previous section.

Insert Code to Handle Events

  1. Select DataGrid1 and press F4 to display the Properties Window, and then click the Events button (Image of Events button) to display the available event handlers.

  2. In the Properties Window, double-click EditCommand.

  3. Insert the following code for the Edit_Command event handler:

        
    Web_service_namespace
    .Service1 wsCustomers =
        new Web_service_namespace.Service1();
        DataSet myCustomers = wsCustomers.GetCustomers();
        DataGrid1.DataSource = myCustomers;
        DataGrid1.EditItemIndex = e.Item.ItemIndex;
        DataGrid1.DataBind();
    

  4. Select the WebService1.aspx [Design] tab, and double-click CancelCommand in the Properties Window.

  5. Add the following code to the Cancel_Command event handler:

        
    Web_service_namespace
    .Service1 wsCustomers =
        new Web_service_namespace.Service1();
        DataSet myCustomers = wsCustomers.GetCustomers();
        DataGrid1.DataSource = myCustomers;
        DataGrid1.EditItemIndex = -1;
        DataGrid1.DataBind();
    

  6. Select the WebForm1.aspx [Design] tab, and double-click UpdateCommand in the Properties Window.

  7. Add the following code to the Update_Command event handler:

        //Connect to Web service, get DataSet to be updated
    
    Web_service_namespace
    .Service1 wsCustomers =
        new Web_service_namespace.Service1();
        DataSet myCustomers2 = wsCustomers.GetCustomers();
        
        DataRow drEditedRow =
         myCustomers2.Tables[0].Rows.Find(e.Item.Cells[1].Text);
        
        //Loop through each column in DataGrid and update DataRow
        int intCount;
        for (intCount=0;intCount<e.Item.Cells.Count;intCount++)
    {
    if (e.Item.Cells[intCount].Controls.Count > 0)
    {
    if (e.Item.Cells[intCount].Controls[0] is TextBox)
    {
    TextBox txtTemp = (TextBox)
    e.Item.Cells[intCount].Controls[0];
    string strValue = txtTemp.Text;
    if (strValue.Length == 0)
    {

                    drEditedRow[DataGrid1.Columns
                    [intCount].HeaderText] = System.DBNull.Value;
                }
                else
                {
                drEditedRow[DataGrid1.Columns
                [intCount].HeaderText] = strValue;
                }
            }
        }
    }
    

    //Call web service method UpdateCustomers //with updated DataSet DataSet myCustomersUpdated = wsCustomers.UpdateCustomers(myCustomers2); //Bind the updated DataSet to the DataGrid //and take edited row out of edit mode DataGrid1.DataSource = myCustomersUpdated; DataGrid1.SelectedIndex = -1; DataGrid1.EditItemIndex = -1; DataGrid1.DataBind();

  8. Save the project and build the solution by clicking the Build menu and then clicking Build Solution.

  9. Browse to the page by opening your browser and typing the following in the address bar: https://localhost/Customers/WebForm1.aspx. This confirms that the results of the Web service call are shown in the Web form.

Design Considerations for Web Services

Table 1. Design considerations for Web services

Consideration Explanation
Authentication From the InfoPath client, the username is used to access the Web service, while from the browser, the ComputerName\ASPNET account is used. As mentioned earlier, Windows NT Integrated Security, which specifies Security Support Provider Interface (SSPI) in the connection string, is more secure than SQL authentication but may not work with a tiered-application approach. Other options are available, depending on your server topology.

In a typical multi-tier authentication environment, Kerberos is the best available secure authentication option. As a workaround, you can use a single sign-on solution, such as Passport.

These authentication methods require additional configuration and code to pass credentials from a Web server, for example, to a business logic Web service, and finally to a database server. For more information about these options, see the Additional Resources section at the end of this article.

Security When designing the Web service described in this article, it is required to grant database access permissions to the ASPNET account.

In InfoPath, if the Web service resides in a domain other than where the form template is published, the user receives a cross-domain warning when the Web service is called.

Limitations of InfoPath

Table 2. Limitations of InfoPath

Limitation Explanation
InfoPath can access only Document/Literal SOAP messages InfoPath accesses only Web services that are described using the Document/Literal style in their WSDL. The WS-I Basic Profile requires Document/Literal type Web services and prohibits the use of RPC/Encoded Web services.
InfoPath accesses only typed and schema-conforming XML If the Web service returns untyped data, InfoPath makes an extra call to the Web service and infers the schema when the Web service connection is created.
InfoPath cannot access multiple .NET DataSets from a single call InfoPath can accept only one .NET DataSet per Web service call.
xsd:any nodes cannot be optional InfoPath cannot infer schema for xsd:any nodes that have a minOccurs=0 attribute.
WS-Security InfoPath does not support WS-Security, an extension to the Web services specification which, among other things, allows an application to embed credentials in the SOAP header.

Conclusion

When you want to deploy Web services in the enterprise environment for client computers with InfoPath, and also offer a solution for those computers that do not have InfoPath installed, you can develop an ASP.NET application that mimics some of the functionality of InfoPath. You will not, however, enjoy the full capabilities of the InfoPath editing environment, which offers declarative design features, standard Microsoft Office features such as rich-text formatting, the ability to check spelling, or the offline capability of editing a form without a network connection.

Additional Resources

© Microsoft Corporation. All rights reserved.