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().

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.

Related InfoPath Articles:

• InfoPath does not support the specified database. The database must be a Microsoft SQL Server
• Retrieve an InfoPath form from SQL Server and display it in an InfoPath FormControl
• Using a Receive data connection in InfoPath to execute an SQL Server stored procedure