Whilst InfoPath has dataconnection capabilites to 'talk' direct to SQL & retrieve data, these can be a little problematic. An alternative approach is to implement a simple webservice in front of SQL.
The following example shows a simple service... (note - the code is not optimised & is passing the SQL query over http!)
[WebMethod]
public DataSet GetSQLData(string SqlStatement, string connection)
{
string conn = ConfigurationSettings.AppSettings[connection];
DataSet wsDataSet = new DataSet();
SqlConnection sqlConn = new SqlConnection(conn);
SqlDataAdapter adapter = new SqlDataAdapter(SqlStatement,conn);
adapter.Fill(wsDataSet);
}
return wsDataSet;
}
We can create an InfoPath data connection now to talk to this service.
A similar approach can be taken to 'write' to SQL. This can be useful where you need InfoPath to submit only partial data to SQL rather than the whole form
public string UpdateSQL(string SqlStatement, string connection)
{
string message = string.Empty;
try
{
string skey = ConfigurationSettings.AppSettings["SecurityToken"];
SqlConnection sqlConn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand(SqlStatement, sqlConn);
sqlConn.Open();
cmd.ExecuteNonQuery();
message = "succesful";
else
message = "Security Token Invalid";
}
catch (System.Exception e)
{
message = e.ToString();
}
return message;
To use, you can create 'Read' type web service
After configuring the Type (Web Service) and path, your service will be available as a secondary data source. Importantly, remove the check box on 'Automatically retrieve data'
Once completed, you can dynamically set the SqlStatement node (e.g. via a button rule)
