Execute a stored procedure from c#

[ace333]

I want to execute a stored procedure from where the name of the stored procedure and the parameters are passed to the method in an array, the name of the stored procedure will always be at array[0] and the remaining parameters will follow... However the number of parameters can vary from 1 to many, any ideas guys (and girls) !

 

[ca8msm]

What problems are you having with this (i.e. what have you done so far)? It seems fairly easy to me to loop through each enty of the array using a For Each loop.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

well i do have one question, how do u plan to make the parameters dynamic for a SP???

Known is handfull, Unknown is worldfull

 

[ace333]

Got it sorted guys thanks anyway

 

[ca8msm]

Would you like to share how you got it sorted for future readers of this post?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ace333]

This takes data from an array with the first argument the name of the stored procedure to be executed and the remaining values the arguments required for that stored procedure. Hope this is okay mate

public DataSet getData(string[] aParams)
{

string sConn = GetConnString();

OracleConnection oracleConn = new OracleConnection(sConn);
OracleCommand objCmd;

DataSet dsRet = new DataSet();

try
{
PopulateCommand(aParams,out objCmd); //Add the parameters to the stored procedure.

objCmd.Connection=oracleConn;

OracleDataAdapter objDA = new OracleDataAdapter(objCmd);

objDA.Fill(dsRet);

DataTable dataTable = dsRet.Tables[0];
DataGrid1.DataSource = dataTable;
DataGrid1.DataBind();

}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
oracleConn.Close();
}

return dsRet;

}



//***********************************************************************************************
private void PopulateCommand(string[] aParams,out OracleCommand objCmd)
{
objCmd = new OracleCommand();

objCmd.CommandType=CommandType.StoredProcedure;

OracleParameter param;

for (int i=0;i< aParams.Length;i++)
{
String par = aParams;
if (i==0)
{
objCmd.CommandText= aParams[0];
Response.Write("<br>"+"<br>"+"<br>"+"<br>"+"<br>"+aParams[0]);


}//End if.

else
{
String args = aParams;

param = new OracleParameter(args,OracleDbType.Varchar2);

param.Direction = ParameterDirection.Input;

param.Value = args;



Response.Write("<br>"+"<br>"+"<br>"+"<br>"+"<br>"+param);

objCmd.Parameters.Add(param);

}//End else.

}//End for loop.

}//End PopulateCommand method.


//*************************************************************************************************



//*************************************************************************************************
static public string GetConnString()
{
string sDBServer = SecurityConfig.GetVersionSetting("DBServer");
string sDBUser = SecurityConfig.GetVersionSetting("DBUser");
string sDBPassword = SecurityConfig.GetVersionSetting("DBPassword");
string sDBProvider = SecurityConfig.GetVersionSetting("DBProvider");
StringBuilder sb = new StringBuilder();

//sb.Append("Provider=");
//sb.Append(sDBProvider);
sb.Append("User Id=");
sb.Append(sDBUser);
sb.Append(";Password=");
sb.Append(sDBPassword);
sb.Append(";Data Source=");
sb.Append(sDBServer);


return sb.ToString();
}//End of method

//***********************************************************************************************************


}