You cannot create a form from a Web service that returns an ADO.NET dataset in InfoPath 2003

When an InfoPath form is created from a Web service that returns an ADO.NET DataSet (System.Data.DataSet), you may receive the following error message from InfoPath:

InfoPath cannot directly use ADO.NET DataSets. InfoPath works with generic XML payloads. ADO.NET DataSets that are returned by Web services are serialized as a special XML format that prevents the ADO.NET DataSets from working directly with InfoPath.

• In the Web Service Description Language file that defines the methods and the properties of a Web service, the DataSet type is represented by an open schema tag, <xsd:any />. InfoPath can infer the schema of an XML document that is returned by a Web service. However, InfoPath must make a sample call to the Web service do this.
• An ADO.NET DataSet contains an inline schema that describes the data that is stored in it. This version of InfoPath does not support inline schemas.
• The data in an ADO.NET DataSet is wrapped in a <diffgr:diffgram> XML element that is not described in the inline schema. The XML elements that contain the data of the ADO.NET DataSet also have additional attributes that are not described in the schema. Because the data does not match the schema, the data cannot be validated in the InfoPath form.
• InfoPath does not have built-in support for change tracking that is similar to an ADO.NET DataSet. For example, an ADO.NET DataSet tracks the fields and the rows that have been added, changed, or deleted since changes were last accepted. (This is the purpose of the diffgram element and the "diffgr" namespace in the XML Stream.) To support change tracking, InfoPath would have to either ship the .NET Framework and host a DataSet object to manage the XML data or re-implement the functionality. Neither of these options is available in this version of InfoPath.

This problem is resolved in Microsoft Office InfoPath 2003 Service Pack 1 (SP1). To resolve this problem, obtain the latest service pack for Microsoft Office 2003. InfoPath 2003 SP1 fully supports typed datasets and lets you create a form from a Web service that returns an ADO.NET DataSet in InfoPath 2003.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: To work around this problem, create a new Web service method that takes the ADO.NET DataSet that is returned from the original Web service method. Remove the inline schema, the diffgram elements, and the attributes. Return the cleaned XML to InfoPath. See the "More Information" section for two possible implementations of this workaround.

This behavior is by design.

Steps to reproduce the behavior

1. In Microsoft Visual Studio .NET, create a new Microsoft C# ASP.NET Web service project. Change the name of this project to NorthwindDataSet.
   
   You can do this by changing the location to http://localhost/NorthwindDataSet.
2. On the Project menu, click Add Web Service. Name the new Web service CustomerInfo.asmx.
3. Add the following code to the new CustomerInfo.asmx Web service:

   /************************************************************************
      * GetCustomerInfo -- This method retrieves information from the 
      * Customer table of the Northwind database and then returns it in an
      * ADO.NET DataSet.
      * Parameters: CustomerID is the string that contains the CustomerID of the 
      *                           customer to retrieve the information for.
      * Returns: An ADO.NET DataSet that contains information about the customer.
      * *********************************************************************/
   [WebMethod]
   public System.Data.DataSet GetCustomerInfo( string CustomerID )
   {
      try
      {
         //Create a Microsoft SQL Server connection to the local SQL Server.
         System.Data.SqlClient.SqlConnection theConnection = new SqlConnection();
         theConnection.ConnectionString = "Data Source=(local);" + 
            "Integrated Security=SSPI;Initial Catalog=northwind";
         
         //Create an SQL Command to query the data.
         System.Data.SqlClient.SqlCommand theCommand = new SqlCommand();
         theCommand.Connection = theConnection;
         theCommand.CommandText = "SELECT \"CustomerID\",\"CompanyName\"," + 
            "\"ContactName\",\"ContactTitle\",\"Address\",\"City\",\"Region\"," + 
            "\"PostalCode\",\"Country\",\"Phone\",\"Fax\" FROM \"Customers\" " + 
            "WHERE CustomerID='" + CustomerID + "'";
      
         //Create an SQL DataAdapter to read the data.
         System.Data.SqlClient.SqlDataAdapter theDataAdapter = new SqlDataAdapter();
         theDataAdapter.SelectCommand = theCommand;
   
         //Open the command, and then read the data.
         theConnection.Open();
         System.Data.DataSet theCustomerInfo = new DataSet();
         theCustomerInfo.DataSetName = "CustomerInfo";
         theCustomerInfo.Namespace = "http://localhost/NorthwindDataSet/CustomerInfo";
         theDataAdapter.Fill( theCustomerInfo, "Customers" );
   
         //Clean up.
         theConnection.Close();
      
         //Return the result.
         return theCustomerInfo;
      }
      catch(Exception ex)
      {
         return null;
      }
   }
   

   The sample code tries to connect to a computer that is running Microsoft SQL Server on the same computer as the Web service. Alternatively, you can change the connection string that is used by the System.Data.SqlConnection object to connect to a different computer that is running SQL Server. You may have to configure SQL Server permissions to permit the Web service to access the database. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
   815154� HOW TO: Configure SQL Server Security for .NET Applications
4. At the top of the CustomerInfo.asmx.cs page, add the following code to the USING statements:

   using System.Data.SqlClient;

5. Compile the NorthwindDataSet project.
6. Start InfoPath. On the File menu, click Design a Form.
7. In the Design a Form task pane, click New from Data Source.
8. In the Data Source Setup Wizard dialog box, select Web Service as the type of data source, and then click Next.
9. Select Receive Data, and then click Next.
10. Type the URL to the CustomerInfo Web service (for example, http://localhost/NorthwindDataSet/CustomerInfo.asmx), and then click Next.
11. Select the GetCustomerInfo method for the operation, and then click Next.
12. Select the s0:CustomerID parameter, and then click Set Sample Value.... In the Set Value dialog box, type ALFKI, and then click OK.
13. Click Next.
    
    Note InfoPath displays the error message that is in the "Symptoms" section.

Workarounds

To work around the problem, you can use steps that are similar to the following steps to add a new Web service method that removes the incompatible elements from the XML of the ADO.NET DataSet. You do this so that the XML data can be used by InfoPath. The Web service method can remove the inline schema information from the XML data, or the Web service method can serialize the XML data in a strongly typed wrapper class and return that.

Each method has advantages and disadvantages.

Workaround 1

Remove the Inline Schema and the Incompatible XML Attributes

This work around is good because it is simple to implement. Additionally, if the XML data that the original Web service returns changes for any reason, this workaround requires no changes to work with the new data.

However, this workaround also has a disadvantage. This workaround does not describe the schema of the XML data. Therefore, InfoPath must infer the schema from sample data. If the sample data does not contain all the possible elements and attributes that the Web service method can return, the schema that InfoPath infers will not contain those elements. If you run a query that returns elements or attributes that are not in the sample call, InfoPath displays an error.

To implement Workaround 1, follow these steps:

1. In Visual Studio .NET, open the NorthwindDataSet project.
2. On the Project menu, click Add Web Service. Name the new Web service IPCustomerInfo.asmx.
3. Add the following code to the new IPCustomerInfo.asmx Web service:

   /************************************************************************
      * GetCustomerInfoNoSchema: This method calls the GetCustomerInfo
      * method of the CustomerInfo.asmx Web service, and then strips the 
      * inline schema from the resulting DataSet.
      * Parameters: CustomerID is the string that contains the CustomerID of the 
      *                           customer to retrieve information for.
      * Returns: An XML Document with no inline schema.
      * *********************************************************************/
   [WebMethod]
   public System.Xml.XmlDocument GetCustomerInfoNoSchema( string CustomerID )
   {
      //Get the core data.
      CustomerInfo theCustomerInfoService = new CustomerInfo();
      System.Data.DataSet theDataSet = 
         theCustomerInfoService.GetCustomerInfo( CustomerID );
   
      //Create a new XmlDocument from the data of the dataset.
      System.Xml.XmlDocument theDocument = new System.Xml.XmlDocument();
      theDocument.LoadXml( theDataSet.GetXml() );
   
      //Return the result.
      return theDocument;
   }
   

   The sample code uses the original Web service to provide an ADO.NET DataSet and then creates a new XML document with no inline schema information.
4. Compile the NorthwindDataSet project.
5. Start InfoPath. On the File menu, click Design a Form.
6. In the Design a Form task pane, click New from Data Source.
7. In the Data Source Setup Wizard dialog box, select Web Service as the type of data source, and then click Next.
8. Select Receive Data, and then click Next.
9. Type the URL to the IPCustomerInfo Web service (for example, http://localhost/NorthwindDataSet/IPCustomerInfo.asmx), and then click Next.
10. Select the GetCustomerInfoNoSchema method for the operation, and then click Next.
11. Select the s0:CustomerID parameter, and then click Set Sample Value. In the Set Value dialog box, type ALFKI, and then click OK.
12. Click Next, and then click Finish.
13. Move the CustomerID field from the queryFields group in the Data Source task pane, and then add the CustomerID field to the Query view.
14. Move the Customers group from the dataFields group in the Data Source task pane, and then add the Customers group to the Data Entry view. Click Section with controls.
15. Preview, and then test the form. Notice that for some customer IDs such as QUEEN, the query encounters errors because the sample data does not contain all the possible elements that the Web service can return.

Workaround 2

Create a Strongly-Typed Wrapper Class to Serialize the XML Data of the ADO.NET DataSet

This workaround is more difficult to implement than the previous workaround. Additionally, this workaround must be tailored to each Web service method that it is used with, and that Web service method must not change the schema of the data that it returns. However, this workaround describes the schema of the XML data that it returns. Therefore, InfoPath does not have to infer the structure of the data. As a result, InfoPath forms that are created from this workaround are not susceptible to errors that are caused by optional elements and attributes.

1. In Microsoft Internet Explorer, move to the URL of the CustomerInfo Web service test page (for example, http://localhost/NorthwindDataSet/CustomerInfo.asmx).
2. Click GetCustomerInfo to move to the test page for that method.
3. In the CustomerID text box, enter ALFKI, and then click Invoke.
4. From the XML that results, copy the <xs:schema> element and all its children, and then paste them in a new text document in Notepad.
5. Remove the - characters from the pasted text, and then save the document as CustomerInfo.xsd.
6. Open a Visual Studio .NET command prompt, and move to the directory where you saved CustomerInfo.xsd.
7. Use the following line to create a wrapper class from the schema file:

   xsd.exe CustomerInfo.xsd /c /l:cs

8. In Visual Studio .NET, open the NorthwindDataSet project.
9. On the Project menu, click Add Existing Item.
10. Move to the CustomerInfo.cs file that you created with the Xsd.exe tool, and then click Open.
11. Add the namespace CustomerInfoWrapper around the CustomerInfo class and the CustomerInfoCustomers class.
12. Add the following code to the IPCustomerInfo.asmx Web service:

    /************************************************************************
       * GetCustomerInfoWrapper: This method calls the GetCustomerInfo
       * method of the CustomerInfo.asmx Web service, and then strips the 
       * inline schema from the resulting DataSet.
       * Parameters: CustomerID is the string that contains the CustomerID of the 
       *                           customer to retrieve information for.
       *                        exampleData is the wrapper class to fill with the data from the
       *                           ADO.NET DataSet.
       * Returns: none
       * *********************************************************************/
    [WebMethod]
    public void GetCustomerInfoWrapper( string CustomerID, 
       out CustomerInfoWrapper.CustomerInfo exampleData )
    {
       //Get the core data.
       CustomerInfo theCustomerInfoService = new CustomerInfo();
       System.Data.DataSet theDataSet = 
          theCustomerInfoService.GetCustomerInfo( CustomerID );
       theDataSet.Namespace = "http://localhost/NorthwindDataSet/CustomerInfo";
    
       //Create an in-memory stream to write the DataSet to.
       System.IO.MemoryStream theStream = new System.IO.MemoryStream();
    
       //Write the DataSet to the stream.
       theDataSet.WriteXml( theStream, XmlWriteMode.IgnoreSchema );
    
       //Move the streams seek pointer back to the beginning, or 
       //deserialization will fail.
       theStream.Seek(0, System.IO.SeekOrigin.Begin );
    
       //Create an XML Serializer to read the DataSet.
       System.Xml.Serialization.XmlSerializer ser = new
          System.Xml.Serialization.XmlSerializer(
             typeof(CustomerInfoWrapper.CustomerInfo));
    
       //Deserialize a CustomerInfo wrapper from the stream.
       exampleData = ((CustomerInfoWrapper.CustomerInfo)
          (ser.Deserialize( theStream )));
       return;
    }
    

    The sample code uses the original Web service to provide an ADO.NET DataSet, and then creates a new XML document with no inline schema information.
13. Compile the NorthwindDataSet project.
14. Start InfoPath. On the File menu, click Design a Form.
15. On the Design a Form task pane, click New from Data Source...
16. In the Data Source Setup Wizard dialog box, select Web Service as the type of data source, and then click Next.
17. Select Receive Data, and then click Next.
18. Type the URL to the IPCustomerInfo Web service (for example, http://localhost/NorthwindDataSet/IPCustomerInfo.asmx), and then click Next.
19. For the operation, select the GetCustomerInfoWrapper method, and then click Next.
    
    Notice that InfoPath does not prompt you to specify sample values for the Web service method.
20. Click Finish.
21. Move the CustomerID field from the queryFields group in the Data Source task pane, and then add the CustomerID field to the Query view.
22. Move the Customers group from the dataFields group in the Data Source task pane, and then add the Customers group to the Data Entry view.
23. Preview and then test the form.
    
    Notice that any valid customer ID works in this form.