Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

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
0
mrw76
Asked:
mrw76
  • 2
  • 2
  • 2
1 Solution
 
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
 
BowmanjeCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now