SqlParameter Design Problem

[Steven Blair]

Hi,

I am currently writing a method for calling stored procedures. I want the
method to be able to handle variable amount of parameters and need some
advice on the design of this.

The way I see it, there is two ways of doing this.

Number 1 being a method for each stored procedure.
Each of these procedures would call the appropiate SP and also build up the
SqlParameters in there.
This could be a bit time consuming and difficult to maintain. My class would
be messy with various different methods just for calling SP.

The second way is to create a generic method which calls a SP by a parameter
name passed and also an array of SqlParameters. This means the client is
responsible for setting up an parameters required for the SP (name, data
type).
The SP method simply loops round the array adding all the SqlParameters.

Thats my thoughts on the matter.

Does anyone have any other ways of doing this, or opinions on what I have
just described ?

--
Steven
www.stevenblair.com

[Gary van der Merwe]

Hi Steven

This is how I would do it

My SP would contain the logic to hand diferent prams, e.g.

ALTER PROCEDURE InsertCupDetail
@CupId INT,
@Msg VARCHAR(8000),
@Time DateTime =null
AS

if @Time is null
INSERT INTO Detail (Cup, Msg) VALUES (@CupId, @Msg)
else
INSERT INTO Detail (Cup, Msg, Time) VALUES (@CupId, @Msg,@Time)

Then if I am not passing a pram, I just pass null to the prameter.

You may want to create overloaded methods to call the sp, e.g.

private void InsertCupDetail (int CupId)
{
InsertCupDetail (int CupId,'');
}
private void InsertCupDetail (int CupId, string Msg)
{
InsertCupDetail (int CupId,'',null);
}
private void InsertCupDetail (int CupId, string Msg, DateTime Time)
{
call procedure....
}

Gary
"Steven Blair" <st**********@b tinternet.com> wrote in message
news:bo******** **@hercules.bti nternet.com...

Hi,

I am currently writing a method for calling stored procedures. I want the
method to be able to handle variable amount of parameters and need some
advice on the design of this.

The way I see it, there is two ways of doing this.

Number 1 being a method for each stored procedure.
Each of these procedures would call the appropiate SP and also build up the SqlParameters in there.
This could be a bit time consuming and difficult to maintain. My class would be messy with various different methods just for calling SP.

The second way is to create a generic method which calls a SP by a parameter name passed and also an array of SqlParameters. This means the client is
responsible for setting up an parameters required for the SP (name, data
type).
The SP method simply loops round the array adding all the SqlParameters.

Thats my thoughts on the matter.

Does anyone have any other ways of doing this, or opinions on what I have
just described ?

--
Steven
www.stevenblair.com

[Charlie Smith]

> "Steven Blair" <st**********@b tinternet.com> wrote in message

news:bo******** **@hercules.bti nternet.com...

Hi,

I am currently writing a method for calling stored procedures. I want the
method to be able to handle variable amount of parameters and need some
advice on the design of this.

Steven,

This works well for multiple stored procedures with a variable number of parameters:

objCommand = New SQLCommand(spNa me,objConnectio n)

With objCommand
.CommandType = CommandType.Sto redProcedure
.Parameters.Cle ar()
End With

With objCommand
.Parameters.Add ("@prmcName" , name)
.Parameters.Add ("@prmcAddress" , address)
.Parameters.Add ("@prmcCSZ", citystatezip)
.Parameters.Add ("@prmcDM", pdm)
End With

objConnection.O pen()

objDataAdapter. Fill(mobjDataSe t)

objConnection.C lose()

Or, if using an insert or update command:

objConnection.O pen()
objCommand.Exec uteNonQuery()
objConnection.C lose()