I know it's easy to populate InfoPath form fields with SQL data using data connections but there are certain scenarios where you may want to populate fields with SQL data using custom code. For example, consider a scenario where you form has different sections, and each section is filled with data from a different database. One way is to create multiple data connections in your form. The other way is to write custom code. This is not the only example, there can be different situations where writing your own code could prove useful. Another situation is when you want to validate data entered by a user. Simply, open a connection to your database and check the field's value against data in your database.
1. Create an InfoPath form and add a field and a button.
2. Field name is "field1" which is the default name for a newly added field. You may want to change it to a name of your liking, for example, First Name, Last Name, Address, etc.
3. Double click the button (default name for the button is Ctrl_1) and select "Edit Form Code..." in the dialog box that opens.
4. Add following code in the click event of the button:
SqlConnection MyConnection = new SqlConnection("server=sqlserver;database=yourdatabase;UID=;PWD=;");
MyConnection.Open();
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = MyConnection;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "select * from tblUser";
SqlDataAdapter DA = new SqlDataAdapter(Cmd);
DataSet DS = new DataSet();
DA.Fill(DS);
thisXDocument.DOM.selectSingleNode("/my:myFields/my:field1").text = DS.Tables[0].Rows[1][1].ToString();
if(DS.Tables[0].Rows[1][1].ToString() == "John Doe")
{
thisXDocument.UI.Alert("User name is John Doe.");
} |
Code explanation:
Open a connection to the database using a connection string. Connection string contains your sql server, database name and userid and password to access the database. Open the connection before making any transaction. Add your sql query in the command object:
Cmd.CommandText = "select * from tblUser";
Following line will add data from SQL DB to your form field:
thisXDocument.DOM.selectSingleNode("/my:myFields/my:field1").text = DS.Tables[0].Rows[1][1].ToString();
"field1" is your field's name. In the line above, we are populating this field with Row 1, Column 1 of the table.
if(DS.Tables[0].Rows[1][1].ToString() == "John Doe")
{
thisXDocument.UI.Alert("User name is John Doe.");
}
If DB field is equal to "John Doe" then display a message to the user.
You can also do the opposite, instead of populating a field with DB data, get a value from the form field and find a record against this value in the DB. You just need to pass the form field value in the sql query:
Cmd.CommandText = "select * from tblUser where username='" + thisXDocument.DOM.selectSingleNode("/my:myFields/my:field1").text + "'";
Don't forget to add following namespaces in your code page:
using System;
using System.Data;
using System.Data.SqlClient;
Add following code to the project class:
public class InfoPathDBProject
{
private XDocument thisXDocument;
private Application thisApplication;
public void _Startup(Application app, XDocument doc)
{
thisXDocument = doc;
thisApplication = app;
}
//Application code
}
-SSA