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

Applies to: InfoPath 2007

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.

ADVERTISEMENTS

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:

 

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

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