Solved

Using stored procedure trough a generic database layer

Posted on 2006-06-21
6
343 Views
Last Modified: 2010-08-05
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
Comment
Question by:mrw76
  • 2
  • 2
  • 2
6 Comments
 
LVL 3

Expert Comment

by:Bowmanje
ID: 16951232
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
 

Author Comment

by:mrw76
ID: 16951419
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
 
LVL 3

Accepted Solution

by:
Bowmanje earned 500 total points
ID: 16951804
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 25

Expert Comment

by:dstanley9
ID: 16962170
Try using IDBCommand.CreateParameter():

IDbCommand myCommand // obtained from data layer
IDbDataParameter param = myCommand.CreateParameter();
param.Value = "myvalue";
myCommand.Parameters["@Res_Id"] = param;
0
 

Author Comment

by:mrw76
ID: 16999577
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
 
LVL 25

Expert Comment

by:dstanley9
ID: 17000980
I think you accepted the wrong answer.  My solution was to use CreateParameter().
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question