Using a Receive data connection in InfoPath to execute an SQL Server stored procedure

Learn how to temporarily change the Command property of a Receive data connection in InfoPath to execute a SQL Server stored procedure and insert data into a database table.

Problem

You want to execute an SQL Server stored procedure from code within an InfoPath form to be able to insert data into a database table.

Solution

Programmatically change the Command property of a Receive data connection to execute a SQL Server stored procedure and insert data into a database table.

Discussion

You can achieve this functionality as follows:

1. In a SQL Server 2005 database, create a table using the following statement:
   
   CREATE TABLE OfficeApplications
(
[id] INT IDENTITY PRIMARY KEY NOT NULL,
[name] VARCHAR(50) NOT NULL
)
GO
   and a stored procedure using the following statement:
   
   CREATE PROCEDURE AddOfficeApplication
(
@name VARCHAR(50)
)
AS
BEGIN
INSERT INTO OfficeApplications VALUES (@name)
COMMIT
END
GO
   
2. In InfoPath, create a new Blank form template.
3. On the Tools menu, click Data Connections, and add a Receive data connection to the OfficeApplications table in the SQL Server 2005 database. Note: The data connection will be named OfficeApplications by default; you will use this name later on in code.
4. Add a Text Box control to the form template and name it OfficeApplicationName.
5. Add a Button control to the form template and add the following C# code to the Clicked event handler of the Button control:
   
   // Retrieve the name of the Office application to add to the database table
XPathNavigator dom = MainDataSource.CreateNavigator();
string officeApplicationName = dom.SelectSingleNode(
"//my:OfficeApplicationName", NamespaceManager).Value;

// Retrieve the connection to the database table
AdoQueryConnection connection =
(AdoQueryConnection)DataConnections["OfficeApplications"];

// Save the original statement that is stored in the Command property
string originalCommand = connection.Command;

// Execute the stored procedure
connection.Command = "EXEC AddOfficeApplication '" + officeApplicationName + "'";
connection.Execute();

// Restore the statement of the Command property to its original value
connection.Command = originalCommand;

   or add the following Visual Basic code to the Clicked event handler of the Button control:
   
   ' Retrieve the name of the Office application to add to the database table
Dim dom As XPathNavigator = MainDataSource.CreateNavigator()
Dim officeApplicationName As String = dom.SelectSingleNode( _
"//my:OfficeApplicationName", NamespaceManager).Value

' Retrieve the connection to the database table
Dim connection As AdoQueryConnection = DataConnections("OfficeApplications")

' Save the original statement that is stored in the Command property
Dim originalCommand As String = connection.Command

' Execute the stored procedure
connection.Command = "EXEC AddOfficeApplication '" & officeApplicationName & "'"
connection.Execute()

' Restore the statement of the Command property to its original value
connection.Command = originalCommand

   
6. Build the code and test the form.

Now whenever you enter an application name in the text box and then click the button, the stored procedure is executed, and a record is added through the Receive data connection to the table in the SQL Server database.

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 a new instance of InfoPath
• Retrieve an InfoPath form from SQL Server and display it in an InfoPath FormControl