Call Store Procedure at Control event using c#

[mzam]

Hi,

In my infopath 2007 form whenever the user changes the value of a drop down
list I want to call a store procedure from a DB, I will pass the drop down
list selected ID as parameter to the SP and would like to get the results
from the sp and store them in a Dataset. I will use some of the values from
the dataset to fill other fields in the infopath form.

Could someone provide some source code in C# on how to do this?

Regards

 

[Clay Fox]

Have you considered using a web service where you can use an actual select
statement?

There is information on this and code for doing a select statement in code
at http://www.infopathdev.com

 

[mzam]

Hi Clay,

Thanks for your feedback. I could not find how to call a stored procedure
in the code for infopath 2007 at http://www.infopathdev.com
, do you have a specific link?

Another question, can I just use ADO.NET in the C# code?

Thanks for your help,

 

[S.Y.M. Wong-A-Ton]

I don't think you'll find an article specifc to InfoPath on how to do this,
but you can use normal classes from the System.Data and System.Data.SqlClient
to do what you want to do. The only InfoPath specific feature in your
scenario is retrieving and setting values of InfoPath fields. So if you
already know how to work with SqlConnection, SqlCommand, SqlParameter, and
DataSet, you've got your answer on how to call a stored procedure.

There is also this old article (http://support.microsoft.com/kb/827007), but
I don't think it'll help you much in InfoPath 2007.

 

[mzam]

It looks like from the code I am not allowed to use ADO.NET.
I have the following code on the changed event of a field:

SqlConnection conn = new SqlConnection("User
Id=myuser;Password=myPassword;Initial Catalog=mydabtase;Data
Source=myserver");

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM
iCDO_LTF_Issuer_FR_Rating_View; SELECT * FROM
iCDO_LTF_Issuer_MR_Rating_View;", conn);

da.TableMappings.Add("iCDO_LTF_Issuer_FR_Rating_View",
"iCDO_LTF_Issuer_MR_Rating_View");
DataSet ds = new DataSet();
da.Fill(ds);

The Error is:
System.Security.SecurityException
Request for the permission of type
'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at
System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection
outerConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at LTFTESTForms.FormCode.ddlbCreditName_Changed(Object sender,
XmlEventArgs e)
at Microsoft.Office.InfoPath.Internal.XmlEventHost.GenericProxy(Object
genericDelegate, DataDOMEvent dataDOMEvent, InfoPathEvents type)
at
Microsoft.Office.InfoPath.Internal.XmlEventHost.ChangedProxy(DataDOMEvent
dataDOMEvent)
at
Microsoft.Office.Interop.InfoPath.SemiTrust._DataDOMEventSink_SinkHelper.OnAfterChange(DataDOMEvent pDataDOMEvent)

 

[S.Y.M. Wong-A-Ton]

The System.Data assembly is demanding that its callers (your InfoPath form
template's code) have the SqlClientPermission permission, so you need to give
your form template full trust for the code to work. This is why it is usually
recommended to perform such actions through a web service.
---
S.Y.M. Wong-A-Ton

It looks like from the code I am not allowed to use ADO.NET.
I have the following code on the changed event of a field:

SqlConnection conn = new SqlConnection("User
Id=myuser;Password=myPassword;Initial Catalog=mydabtase;Data
Source=myserver");

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM
iCDO_LTF_Issuer_FR_Rating_View; SELECT * FROM
iCDO_LTF_Issuer_MR_Rating_View;", conn);

da.TableMappings.Add("iCDO_LTF_Issuer_FR_Rating_View",
"iCDO_LTF_Issuer_MR_Rating_View");
DataSet ds = new DataSet();
da.Fill(ds);

The Error is:
System.Security.SecurityException
Request for the permission of type
'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at
System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection
outerConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at LTFTESTForms.FormCode.ddlbCreditName_Changed(Object sender,
XmlEventArgs e)
at Microsoft.Office.InfoPath.Internal.XmlEventHost.GenericProxy(Object
genericDelegate, DataDOMEvent dataDOMEvent, InfoPathEvents type)
at
Microsoft.Office.InfoPath.Internal.XmlEventHost.ChangedProxy(DataDOMEvent
dataDOMEvent)
at
Microsoft.Office.Interop.InfoPath.SemiTrust._DataDOMEventSink_SinkHelper.OnAfterChange(DataDOMEvent pDataDOMEvent)

I don't think you'll find an article specifc to InfoPath on how to do this,
but you can use normal classes from the System.Data and System.Data.SqlClient
to do what you want to do. The only InfoPath specific feature in your
scenario is retrieving and setting values of InfoPath fields. So if you
already know how to work with SqlConnection, SqlCommand, SqlParameter, and
DataSet, you've got your answer on how to call a stored procedure.

There is also this old article (http://support.microsoft.com/kb/827007), but
I don't think it'll help you much in InfoPath 2007.