Passing SqlParameters to a remote object by value...kinda
I have been struggling with passing SqlParameters to remoted data access object. Why? MarshalByRefObject and server callbacks...what a mess. So, I finally decided to build a value type that mirrors a SqlParameter and pass this to the remoted object. The problem was, how do I get Output params back to the client? With a SqlParameter type it is easy, since the AppDomain that creates the SqlParameter passes the SqlParameter as a ref type.
The solution? Well, in my case I have a local object whose sole purpose in life is to determine if there is a LAN or WAN connection available. If LAN, the data access code runnings locally. If WAN, we need to connect via HTTP to a remoted object on IIS.
The client exe builds the following structure:
<Serializable()> _
Public Class DataPortalGeneral
<Serializable()> _
Public Enum Direction
Input
InputOutput
Output
ReturnValue
End Enum
<Serializable()> _
Structure ParmData
Dim ParmName As String
Dim ParmValue As String
Dim ParmType As SqlDbType
Dim ParmSize As Integer
Dim ParmDirection As String
Sub New(ByVal Name As String, _
ByVal Value As String, _
ByVal Type As SqlDbType, _
ByVal Size As Integer, _
ByVal Direction As Direction)
Me.ParmName = Name
Me.ParmValue = Value
Me.ParmType = Type
Me.ParmSize = Size
Me.ParmDirection = Direction
End Sub
End Structure
End Class
Like this:
Dim ParmGroup(3) As ParmData
ParmGroup(0) = New ParmData("@ProductID", "1", SqlDbType.Int, 4, Direction.Input)
ParmGroup(1) = New ParmData("@ProductName", "", SqlDbType.VarChar, 40, Direction.Output)
ParmGroup(2) = New ParmData("@UnitPrice", "", SqlDbType.Money, 8, Direction.Output)
ParmGroup(3) = New ParmData("@QtyPerUnit", "", SqlDbType.VarChar, 20, Direction.Output)
The client exe passes this structure to a local client object. This local client object check for LAN or WAN, then passes this struct to the remoted server object (if WAN). The remoted server object uses this struct to build a SqlParameter array like this:
Private Shared Function BuildSqlParamArray(ByVal ParmData() As ParmData) As SqlParameter()
Dim arParms() As SqlParameter = New SqlParameter(ParmData.Length - 1) {}
Dim parm As ParmData
Dim i As Integer = 0
Dim myParam As SqlParameter
For Each parm In ParmData
myParam = New SqlParameter(parm.ParmName, parm.ParmType, parm.ParmSize)
Select Case parm.ParmDirection
Case Direction.Input
myParam.Direction = ParameterDirection.Input
Case Direction.InputOutput
myParam.Direction = ParameterDirection.InputOutput
Case Direction.Output
myParam.Direction = ParameterDirection.Output
Case Direction.ReturnValue
myParam.Direction = ParameterDirection.ReturnValue
End Select
myParam.Value = parm.ParmValue
arParms(i) = myParam
i += 1
Next
Return arParms
End Function
Now, I have a sqlParameter array I can pass to my generic Data Access Class.
The Generic Data Access component can build a Command object and add the SqlParameters and Execute.
The next issue is how to get the Output params stored in the SqlParameter back to the client after the Command Execute. Well, back in the calling remoted server object we still have our Reference Type SqlParameter array. So, I now take the SqlParameter array and build a ParmData structure array like this:
Private Shared Function BuildParmData(ByVal sqlParameter() As SqlParameter) As ParmData()
Dim ParmsDataArray(sqlParameter.Length - 1) As ParmData
Dim parm As ParmData
Dim i As Integer = 0
Dim myParam As sqlParameter
For Each myParam In sqlParameter
parm = New ParmData(myParam.ParameterName, myParam.Value, myParam.DbType, myParam.Size, myParam.Direction)
ParmsDataArray(i) = parm
i += 1
Next
Return ParmsDataArray
End Function
The remoted server object can now pass a Value Type, ParmData, back to the calling client. In the client, instead of doing something like this:
Textbox1.Text = mySqlParms(1).Value.Tostring
I do this:
Textbox1.Text = ParmGroup(1).ParmValue.ToString
So far, so good. I would love for someone to break this or offer critiques as we are still in testing.