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.