Using stored procedure trough a generic database layer

Hi,

I have a generic database layer which is using IDbCommand, IDbConnection, IDataReader and IDataAdapter, but how do I use stored procedures trough system.data interface?

/Morten
mrw76Asked:
Who is Participating?
 
BowmanjeConnect With a Mentor Commented:
You can use the OleDb methods...

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDbCommand();
cmd.CommandText = "sp_name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Res_ID",res_id);

Or, if you are writing your own generic classes, extend the IDbParameter interface.

HTH,
Josh
0
 
BowmanjeCommented:
Hi Morten,

For classes that implement IDbCommand, set

cmd.CommandText = "sp_name";
cmd.CommandType = CommandType.StoredProcedure;

and then add any parameters to the cmd.Parameters collection.

Finally, use cmd.ExecuteNonQuery(); to make the call.

HTH,
Josh
0
 
mrw76Author Commented:
Yes but how do I create the parameter list for the command

parms(0) = New SqlClient.SqlParameter("@Res_Id", SqlDbType.NChar, 30)
parms(0).Value = res_id

The parameter above is restricted to the SqlClient, but I want to make it generic.

/Morten
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dstanley9Commented:
Try using IDBCommand.CreateParameter():

IDbCommand myCommand // obtained from data layer
IDbDataParameter param = myCommand.CreateParameter();
param.Value = "myvalue";
myCommand.Parameters["@Res_Id"] = param;
0
 
mrw76Author Commented:
This works, thanks for the help.

IDbDataParameter newParm;           //Stored Procedure parameter

adapter = null;
ds = new DataSet();
connection = this.GetConnection();
command = this.GetCommand(CmdText, connection);
command.CommandType = CommandType.StoredProcedure;

foreach (CommandParameters parm in parms)
{
  newParm = command.CreateParameter();
  newParm.ParameterName = parm.parmName;
  newParm.Direction = parm.parmDirection;
  newParm.Value = parm.parmValue;
  newParm.DbType = parm.parmDBType;
  command.Parameters.Add(newParm);
}
0
 
dstanley9Commented:
I think you accepted the wrong answer.  My solution was to use CreateParameter().
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.