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!)
[code:c#]
[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;
}
[/code]
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
[code:c#]
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;
[/code]
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)
