Deploy Infopath with backend code in sharepoint 2010

I was so much struggled to deploy a infopath form into a sharepoint 2010 which is having backend code to connect database. Finally got the solution. I want to share this because it will definitely helpful to you.

Objective: 

Deploy a Infopath 2010 form which connecting to the Database into sharepoint server.

Requirements:

1. Infopath 2010.

2. Sql server 2008 with northwind database (what ever you required).

3. Sharepoint server 2010. (it should be server only)

Steps:

1. Create infopath form and write backend code in code editor.

2. Complie the solution. Get the dll.

3.  Publish the infopath form with administrator approved.

4. Create a feature with and deploy into shareoint server.

5. Activating  feature in  site collection.

Procedure

1. Create infopath form with backend code:

Open the infopath blank form and click on design. It will open a new form. Insert any design template what you needed. Drag and drop the textboxes and button into the form layout.

infopathdesign1

Change the control ids of text boxes and buttons if needed. Double Click on the button. In the ribbon you will see the Properties tab under Control Tools section is in visible state. If not visible select manually in ribbon. Click on custom code.

We want to connect to database. But here the opened visual studio kind tool doesn’t have a reference of  System.Data. To enable this add a reference to System.Data by right click on reference add reference. Now add the following code in your code editor.  Before pasting this code into your code editor please make sure the names/control ids of buttons and text boxes.


using Microsoft.Office.InfoPath;
using System;
using System.Xml;
using System.Xml.XPath;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace NWInfopath
{
 public partial class FormCode
 {
 public void InternalStartup()
 {
 ((ButtonEvent)EventManager.ControlEvents["CTRL6_5"]).Clicked += new ClickedEventHandler(CTRL6_5_Clicked);
 ((ButtonEvent)EventManager.ControlEvents["CTRL7_5"]).Clicked += new ClickedEventHandler(CTRL7_5_Clicked);
 }

public void CTRL6_5_Clicked(object sender, ClickedEventArgs e)
 {
 SqlConnection sqlconn = null;
 SqlCommand sqlcmd = null;
 SqlDataReader rdr;
 XPathNavigator empid = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtEmpid", this.NamespaceManager);
 XPathNavigator empLastName = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtLastName", this.NamespaceManager);
 XPathNavigator empFirstName = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtFirstName", this.NamespaceManager);
 XPathNavigator empTitle = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtTitle", this.NamespaceManager);
 XPathNavigator empCity = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtCity", this.NamespaceManager);
 int intEmpID = Convert.ToInt32(empid.Value);
 XPathNavigator resultBox = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:result", this.NamespaceManager);
 try
 {
 using (sqlconn = new SqlConnection())
 {
 sqlconn.ConnectionString = "Data Source=locat;Initial Catalog=Northwind;Integrated Security=true";
 string executeQuery = "Select [LastName],[FirstName],[Title],[City] from MYEmployees where [EmployeeID]=" + intEmpID;
 sqlcmd = new SqlCommand(executeQuery, sqlconn);
 sqlconn.Open();
 rdr = sqlcmd.ExecuteReader();
 while (rdr.Read())
 {
 empLastName.SetValue(rdr["LastName"].ToString());
 empFirstName.SetValue(rdr["FirstName"].ToString());
 empTitle.SetValue(rdr["Title"].ToString());
 empCity.SetValue(rdr["City"].ToString());
 }
 sqlconn.Close();
 }
 }
 catch (Exception ex)
 {
 resultBox.SetValue("Exception occurred" + ex.Message);
 }
 }

public void CTRL7_5_Clicked(object sender, ClickedEventArgs e)
 {
 SqlConnection sqlconn = null;
 SqlCommand sqlcmd = null;
 SqlDataReader rdr;
 XPathNavigator empid = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtEmpid", this.NamespaceManager);
 XPathNavigator empLastName = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtLastName", this.NamespaceManager);
 XPathNavigator empFirstName = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtFirstName", this.NamespaceManager);
 XPathNavigator empTitle = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtTitle", this.NamespaceManager);
 XPathNavigator empCity = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:txtCity", this.NamespaceManager);
 XPathNavigator resultBox = this.MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:result", this.NamespaceManager);

 try
 {
 using (sqlconn = new SqlConnection())
 {
 sqlconn.ConnectionString = "Data Source=local;Initial Catalog=Northwind;Integrated Security=true";
 string executeQuery = "insert into MYEmployees(EmployeeID,LastName,FirstName,Title,City) values("+empid.Value+",'" + empLastName.Value.ToString() + "','" + empFirstName.Value.ToString() + "','" + empTitle.Value.ToString() + "','" + empCity.Value.ToString() + "')";
 sqlcmd = new SqlCommand(executeQuery, sqlconn);
 sqlconn.Open();
 int result = sqlcmd.ExecuteNonQuery();
 if (result > 0)
 {
 resultBox.SetValue("Record inserted successfully.");
 empid.SetValue("");
 empLastName.SetValue("");
 empFirstName.SetValue("");
 empTitle.SetValue("");
 empCity.SetValue("");
 }
 sqlconn.Close();
 }
 }
 catch (Exception ex)
 {
 resultBox.SetValue("Exception occured" + ex.Message);
 }
 }
 }
}

Now your code is ready. Just test it whether it is working fine or not. To test this one save the project and close the editor. Now goto infopath form and click on preview option or click F5. Now test it. If it is not working fine then open the code editor and put a debug points and press F5 and find out where mistake was happened.  Lets hope every thing is working fine.

Build the project and get .dll file:

Now we need to collect the .dll file. To copy the dll open once the code editor and click on the root node in the solution explorer. in the properties pane the path where the project was saved will appear. Copy that path and paste in the windows explorer. From there got BIN –>Release copy the .dll file. and paste in any other folder from where you want to deploy your solution package.

Publish  .xsn form with as administrator approved:

Its time to approve our form with administrator privilege.  Why administrator privilege? Any reason? Yes! Because we are deploy this one as a farm solution. So administrator only can do. Thats why we are publishing with administrator approved.  To do this click on file menu in infopath designer and click on farm options and select the following values and click on ok.

Programming : C# (because i given code in c#).

Compatability : Webbrowser form

Security and trust : Full trust.

Now select the publish option in the file menu select sharepoint server. Now one window will come there you enter the site collection to where your form should deploy. Click next –> Next –> Give the location where you want to store your published form. Next –>Next. The screens will appear like this.

publishstep1 publishstep2 publishstep3

Now your form was published. You have published form and .dll file.

Create Feature to deploy into sharepoint server:

you have to take care at this step.  Now i am showing to create feature manually. We can also do with visual studio. But Manual deployment will give you the good practice in writing scripts for deployment.

Now create a folder any where and in that folder create a 14 hive structure for features as below.

Your_folder_Name\14\Template\Features\Your_Folder_Name (here feature name need not be the same. But if you give same name it is safe side).

Paste the published xsn file and .dll file in the last folder.

In the last folder create two xml files with names element.xml , feature.xml. Elements.xml should contain following code.


<?xml version="1.0" encoding="utf-8" ?>
 <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
<Module Name="XSN" Url="FormServerTemplates" RootWebOnly="TRUE">
 <File Url="<span style="color: #ff6600;">myxsnname.xsn"</span> Name="<span style="color: #ff6600;">myxsnname.xsn</span>" Type="GhostableInLibrary" />
 </Module>
 </Elements>

feature.xml should contain following code.


<?xml version="1.0" encoding="utf-8" ?>
<Feature xmlns="http://schemas.microsoft.com/sharepoint/"
 Id="<span style="color: #ff6600;">68B4013A-DD8D-48DB-ADA2-D6561A8E7E54</span>"
 Title="TitleOfYourFeature"
 Description="This feature deploys the browser enabled Expenses Workflow InfoPath Form."
 Version="14.0.0.1"
 Scope="Site"
 DefaultResourceFile="ipfscore"
 ReceiverClass="Microsoft.Office.InfoPath.Server.Administration.XsnFeatureReceiver"
 ReceiverAssembly="Microsoft.Office.InfoPath.Server, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" >
 <ActivationDependencies>
 <ActivationDependency FeatureId="C88C4FF1-DBF5-4649-AD9F-C6C426EBCBF5"/>
 </ActivationDependencies>
 <ElementManifests>
 <ElementManifest Location="element.xml"/>
 <ElementFile Location="myxsnname.xsn"/>
 <ElementFile Location="dllname.dll"/>
 </ElementManifests>
</Feature>

Here .dll name will generate by the code editor it self. so just copy that name and paste it here. Make sure all the valueswere give correctly or not. I marked in red color where you want to take care about it.

Now come back to root folder. Now you have to create two batch files. One is to create the .wsp file and another one is to deploy into sharepoint server. Lets name the two files as Createwsp.bat InfopathInstall.bat.

Createwsp.bat


@ECHO OFF
echo Running WSP Builder
"C:\WSPBuilder\Buildx86\Buildx86\WSPBuilder.exe" -wspname NWInfopath_Approved.wsp -Buildcas false -solutionId 336BF7D6-E848-4ABD-98B1-EA96B7779DEC
pause

Every time you are using this change the solution id to new GUID.

InfopathInstall.bat


@ECHO OFF
SET STSADM="C:\Program Files\Common Files\Microsoft Shared\web server extensions\14\BIN\STSADM"

@ECHO OFF
ECHO ======================================

ECHO Retract Solution
%STSADM% -o retractsolution -n NWInfopath_Approved.wsp -immediate
ECHO ======
%STSADM% -o execadmsvcjobs
ECHO Remove Solution
%STSADM% -o deletesolution -n NWInfopath_Approved.wsp -override
ECHO ======
%STSADM% -o execadmsvcjobs
ECHO Adding Solution
%STSADM% -o addsolution -f NWInfopath_Approved.wsp
ECHO ======
%STSADM% -o execadmsvcjobs
ECHO Deploying Solution
%STSADM% -o deploysolution -n NWInfopath_Approved.wsp -immediate -allowgacdeployment -force
ECHO ======
%STSADM% -o execadmsvcjobs

pause

ECHO Installation Completed

first run the createwsp.bat file. You can see the wsp created in your folder. Now run the infopathinstall.bat now the solution will deploy into sharpeoint sever.

Activating  feature in  site collection :

Now goto your site collection and see in the site collection features. under the site collection features you can see your feature name. Now activate it.

Now it was activated. We have to use this as a content type in the document library.

If you know how to add content types to a document library its fine. Otherwise I will show how to use this infopath form to use as a content type for a document library in my next post.

Happy coding.

Advertisements

1 Comment

  1. Hi ,
    I got an following error in a line sqlconn.Open();….
    “Request for the permission of type ‘System.Data.SqlClient.SqlClientPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.”

    Plz tell me the solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s