Dynamically Populating a SqlParameter object

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the SqlHelper object model from MDAAB, which requires that I pass
in a SqlParameter object if I have more then parameter when calling a Stored
Procedure. My current code seems verbose in the setup of this parameter and
I'm trying to figure out if there is a better approach to populate this
object.

Here is a sample in VB that uses SqlHelper to call a Stored Procedure:

Public Shared Sub GatewayRecordCreate(ByVal GatewayName As String, _
ByVal ResponseString As
String, _
ByVal UserEmail As String, _
ByVal TranAmount As String)
' Public Consts used to store element position in String Array,
used in other classes
Const PositionGatewayName As Short = 0
Const PositionResponseString As Short = 1
Const PositionUserEmail As Short = 2
Const PositionTranAmount As Short = 3
Const PositionMaxElement As Short = 3

Dim returnVal(PositionMaxElement) As String ' String Array
returned by function
Dim parms() As SqlParameter = New
SqlParameter(PositionMaxElement) {} 'Used to hold Details from DB

' @GeneratedBy Input Parameter
parms(PositionGatewayName) = New SqlParameter("@GatewayName",
SqlDbType.VarChar, 200)
parms(PositionGatewayName).Direction = ParameterDirection.Input
parms(PositionGatewayName).Value = GatewayName

' @ResponseString Output Parameter
parms(PositionResponseString) = New
SqlParameter("@ResponseString", SqlDbType.VarChar, 5000)
parms(PositionResponseString).Direction = ParameterDirection.Input
parms(PositionResponseString).Value = ResponseString

' @UserEmail Output Parameter
parms(PositionUserEmail) = New SqlParameter("@UserEmail",
SqlDbType.VarChar, 100)
parms(PositionUserEmail).Direction = ParameterDirection.Input
parms(PositionUserEmail).Value = UserEmail

' @TranAmount Output Parameter
parms(PositionTranAmount) = New SqlParameter("@TranAmount",
SqlDbType.VarChar, 20)
parms(PositionTranAmount).Direction = ParameterDirection.Input
parms(PositionTranAmount).Value = TranAmount

' Call ExecuteNonQuery static method of SqlHelper class
' Pass in database connection string, command type, stored
procedure name and an array of SqlParameter object
SqlHelper.ExecuteNonQuery(Global.ConnectionString,
CommandType.StoredProcedure, "spGatewayTranInsert", parms)
End Sub

My primary goal is to improve the code so that I don't have to setup
Constants and hard code the PositionMaxElement value which is used
SqlParameter object.

Any thoughts? Please post or point to VB source code if you are replying. I
learn best by example. Thanks.
 
jmhmaine said:
I'm using the SqlHelper object model from MDAAB, which requires that I
pass
in a SqlParameter object if I have more then parameter when calling a
Stored
Procedure. My current code seems verbose in the setup of this parameter
and
I'm trying to figure out if there is a better approach to populate this
object.

Here is a sample in VB that uses SqlHelper to call a Stored Procedure:

Public Shared Sub GatewayRecordCreate(ByVal GatewayName As String, _
ByVal ResponseString As
String, _
ByVal UserEmail As String, _
ByVal TranAmount As String)
' Public Consts used to store element position in String
Array,
used in other classes
Const PositionGatewayName As Short = 0
Const PositionResponseString As Short = 1
Const PositionUserEmail As Short = 2
Const PositionTranAmount As Short = 3
Const PositionMaxElement As Short = 3

Dim returnVal(PositionMaxElement) As String ' String Array
returned by function
Dim parms() As SqlParameter = New
SqlParameter(PositionMaxElement) {} 'Used to hold Details from DB

' @GeneratedBy Input Parameter
parms(PositionGatewayName) = New SqlParameter("@GatewayName",
SqlDbType.VarChar, 200)
parms(PositionGatewayName).Direction = ParameterDirection.Input
parms(PositionGatewayName).Value = GatewayName

' @ResponseString Output Parameter
parms(PositionResponseString) = New
SqlParameter("@ResponseString", SqlDbType.VarChar, 5000)
parms(PositionResponseString).Direction =
ParameterDirection.Input
parms(PositionResponseString).Value = ResponseString

' @UserEmail Output Parameter
parms(PositionUserEmail) = New SqlParameter("@UserEmail",
SqlDbType.VarChar, 100)
parms(PositionUserEmail).Direction = ParameterDirection.Input
parms(PositionUserEmail).Value = UserEmail

' @TranAmount Output Parameter
parms(PositionTranAmount) = New SqlParameter("@TranAmount",
SqlDbType.VarChar, 20)
parms(PositionTranAmount).Direction = ParameterDirection.Input
parms(PositionTranAmount).Value = TranAmount

' Call ExecuteNonQuery static method of SqlHelper class
' Pass in database connection string, command type, stored
procedure name and an array of SqlParameter object
SqlHelper.ExecuteNonQuery(Global.ConnectionString,
CommandType.StoredProcedure, "spGatewayTranInsert", parms)
End Sub

My primary goal is to improve the code so that I don't have to setup
Constants and hard code the PositionMaxElement value which is used
SqlParameter object.

Any thoughts? Please post or point to VB source code if you are replying.
I
learn best by example. Thanks.
Click to expand...

This code looks good to me. But you don't really need to get the parameter
position right with SQLClient as it will bind the parameters by name anyway.

But this is about the right amount of code for a method wrapping a stored
procedure.

Think about how to automatically generate this method at design-time, rather
than how to make it less verbose. SQL Server will tell you all this stuff
if you ask, or you can use DeriveParameters at design-time.

David
 
I want to save an extra trip to the database server by explicitly building
the parameters; using DeriveParameters would require an extra trip to
retrieve the list. Ideally I would use something similar to the SqlCommand
Parameters Add functionality such as:

Dim salesCMD As SqlCommand = New SqlCommand("SalesByCategory", nwindConn)
salesCMD.CommandType = CommandType.StoredProcedure
salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15)

Josh.
 
jmhmaine said:
I want to save an extra trip to the database server by explicitly building
the parameters; using DeriveParameters would require an extra trip to
retrieve the list.
Click to expand...

Only once, though -- not on each call. I've never understood the
need to type miles and miles of sourcecode for parameters. Our
typical application has somewhere around 100 tables and 1200
to 1500 columns. What a nightmare. Even using a tool to generate
them (which is better than hand-coding) is not great. When a
stored proc changes, you've got to regenerate to get the new
parameters.

Here's what we do: We use the DeriveParameters on the first
call, and then cache away the Parameters collection in a custom
DataEngine object we use for all data access. Any subsequent
calls find the cached parameters, clone them, and hand them
back to the caller to populate with actual data values.

This way, yes -- you do take the extra round-trip hit, but only
on the first call. Any subsequent calls get the parameters
from the cache.
 
Well, that round trip might make sense for a Windows Forms application with
a persistent connection, but for an ASP program it can cut the number of
users your server can support. It might also be more useful if the method
actually worked correctly--it doesn't. While it works for simple SPs, it
falls apart quickly with more complex SPs--like those that use OUTPUT
parameters.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top