Retrieve an InfoPath form from SQL Server and display it in a new instance of InfoPath

Retrieve the XML value of the database field containing the InfoPath form, save it locally as a temporary file, and open it using this.Application.XmlForms.Open().

ADVERTISEMENTS

Problem

You saved the XML for InfoPath forms in an XML field in SQL Server 2005 and would now like to retrieve one of those forms and display it within InfoPath.

Solution

Retrieve the XML value of the database field containing the InfoPath form, save it locally as a temporary file, and open it using this.Application.XmlForms.Open().

Discussion

You can accomplish this functionality with the following C# code:

  1. Open Microsoft Visual Studio 2005 and create a new InfoPath project.
  2. Add a Drop-Down List Box control and a Button control to the form.
  3. Bind the Drop-Down List Box control to the database table where the forms are stored, and set the Value of the drop-down list box equal to the ID of the forms in the database table.
  4. Add using statements for System.Data.SqlClient, System.Text, System.IO, and put the following code in the Clicked event of the Button:
    // Retrieve the ID of the form to retrieve; this is selected from the drop-down list box
    string id = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:field1", this.NamespaceManager).Value;

    string data = string.Empty;

    using (SqlConnection conn = new SqlConnection("Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=True"))
    {
    // Open a connection to the database
    conn.Open();

    // Retrieve the XML data for an InfoPath form that has an ID equal to id
    SqlCommand cmd = new SqlCommand(@"SELECT Form FROM IPForms WHERE ID = " + id, conn);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
    data = reader.GetString(0);
    }

    // Close the connection to the database
    conn.Close();
    }

    // Convert the XML data for the InfoPath form into a byte array
    byte[] bytes = Encoding.UTF8.GetBytes(data);

    // Create a temp file
    string fileName = Path.GetTempFileName();

    // Read the byte array of the retrieved form into the temp file
    using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
    {
    fs.Write(bytes, 0, bytes.Length);
    fs.Flush();
    fs.Close();
    }

    // Display the form in a new instance of InfoPath
    this.Application.XmlForms.Open(fileName, XmlFormOpenMode.Default);
  5. Give your form Full Trust and sign it with a digital certificate.
 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services