Add / Insert records to sql server database using InfoPath programmatically

Adding values to sql database using InfoPath form is possible using code, same as we do in asp.net. This technique will work for the both InfoPath filler and browser form.

1. Design a form and add required fiend to main data source. Drag them to design surface. Add a button control.

2. Right click on Submit button and click "Edit Form Code" button. It will open VSTA code editor window. Add your code as below it has been added for the above form.

using Microsoft.Office.InfoPath;

using System;

using System.Xml;

using System.Xml.XPath;

using System.Data.SqlClient;

using System.Data;

namespace AddItemToSql

{

public partial class FormCode

{

public void InternalStartup()

{

((ButtonEvent)EventManager.ControlEvents["btnSubmit"]).Clicked += new ClickedEventHandler(btnSubmit_Clicked);

}

public void btnSubmit_Clicked(object sender, ClickedEventArgs e)

{

//Navigator object to navigate through Main Datasource

XPathNavigator xNavigator = this.MainDataSource.CreateNavigator();

string strConString = "";

string strLocation = string.Empty;

string strGrade = string.Empty;

string strItem = string.Empty;

string strDepartment = string.Empty;

string strEmployee = string.Empty;

//Taking value from infopath's main datasource fields.

strLocation = xNavigator.SelectSingleNode("/my:myFields/my:Location", NamespaceManager).Value;

strGrade = xNavigator.SelectSingleNode("/my:myFields/my:Grade", NamespaceManager).Value;

strItem = xNavigator.SelectSingleNode("/my:myFields/my:Group/my:Item", NamespaceManager).Value;

strDepartment = xNavigator.SelectSingleNode("/my:myFields/my:Group/my:Dept", NamespaceManager).Value;

strEmployee = xNavigator.SelectSingleNode("/my:myFields/my:Group/my:Emp", NamespaceManager).Value;

SqlConnection sqlCon = new SqlConnection(strConString);

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.CommandText = "";

sqlCmd.Parameters.AddWithValue("@Location", strLocation);

sqlCmd.Parameters.AddWithValue("@Grade", strLocation);

sqlCmd.Parameters.AddWithValue("@Item", strLocation);

sqlCmd.Parameters.AddWithValue("@Dept", strLocation);

sqlCmd.Parameters.AddWithValue("@EmpId", strLocation);

sqlCmd.Connection = sqlCon;

try

{

sqlCon.Open();

int result = sqlCmd.ExecuteNonQuery();

if (result > 0)

{

xNavigator.SelectSingleNode("/my:myFields/my:Message", NamespaceManager).SetValue("Item Added !!"); ;

}

else

{

xNavigator.SelectSingleNode("/my:myFields/my:Message", NamespaceManager).SetValue("Fail !!"); ;

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

sqlCon.Close();

}

3. To test preview and submit by adding values in text boxes.

XPathNavigator class used to read and write values in InfoPath form field.

XPathNavigator xNavigator = this.MainDataSource.CreateNavigator();

To read value from InfoPath form field

xNavigator.SelectSingleNode("/my:myFields/my:Group/my:Emp", NamespaceManager).Value;

To write value in InfoPath form field

xNavigator.SelectSingleNode("/my:myFields/my:Message", NamespaceManager).SetValue("Item Added !!");