Creating an ASP.NET page to submit the form to the database

You could use either a web service or a web page to submit the InfoPath form to the database. Here, you will use an ASP.NET web page to submit and save the XML data for the form to SQL Server.

Create a new web application in Microsoft Visual Studio 2005 and add the C# code shown in Listing 4 to the Page_Load event handler of the Default.aspx page of the web application. Do not forget to replace the values for the ServerName and DBName with the correct values for your own situation.

Listing 4: Code in the Page_Load event handler of the ASP.NET page

System.Xml.XPath.XPathDocument xDoc = 
        new System.Xml.XPath.XPathDocument(Request.InputStream);
System.Xml.XPath.XPathNavigator navigator = xDoc.CreateNavigator();
 
using (System.Data.SqlClient.SqlConnection conn = 
        new System.Data.SqlClient.SqlConnection(
        "Data Source=ServerName;Initial Catalog=DBName;Integrated Security=True"))
{
  conn.Open();
 
  if (!String.IsNullOrEmpty(navigator.InnerXml))
  {
    System.Data.SqlClient.SqlCommand cmd = 
        new System.Data.SqlClient.SqlCommand(
        @"INSERT INTO IPForms VALUES (@Form)", conn);
    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(
        "@Form", navigator.InnerXml));
    cmd.ExecuteNonQuery();
  }
 
  conn.Close();
 }

A brief explanation of the code in Listing 4 follows.

System.Xml.XPath.XPathDocument xDoc = 
      new System.Xml.XPath.XPathDocument(Request.InputStream);

This retrieves the binary stream that represents the XML contents of the InfoPath form from the Request object and uses it to instantiate an XPathDocument object.

System.Xml.XPath.XPathNavigator navigator = xDoc.CreateNavigator();

This creates an XPathNavigator object to be able to navigate through and retrieve nodes within the XML of the XPathDocument object. The rest of the code opens a database connection and uses

navigator.InnerXml

which returns the XML for the entire InfoPath document that was submitted to the web server to write the XML data of the InfoPath form to the database.

Note: You will have to give users permissions to access your database. In a standard configuration, web sites run under the Network Service account on Windows Server 2003 operating systems, so this account will require access to the database as well as INSERT permission on the table.

Configuring the InfoPath form to submit its data to a Web Server

Perform the following steps to configure the InfoPath form to submit its XML data to the ASP.NET page created in the previous section:

1.    On the Tools menu, click Submitting Forms.

2.    In the Submitting Forms dialog box, select Enable Submit command and buttons.

3.    In the Submit to drop-down list box, select Web server (HTTP).

4.    In the URL text box, type in the URL to the ASP.NET page you created in the previous section.

5.    Click OK.

You should now be able to fill out your InfoPath form and submit its XML data to the web page, which in turn will save the data to a strongly typed XML table column within SQL Server.