Developing a Data Access Layer for Sybase using ADO.NET: Working With Stored Procedures - Adding a parameter before executing a Sybase stored procedure
(Page 3 of 5 )
We need to use the class defined in the previous sections when we want to add a parameter before executing a Sybase stored procedure. To help us in adding a parameter, I developed a new method as follows:
Public Sub SPaddParameter(ByVal ParameterName As String, Optional
ByVal Value As Object = Nothing, Optional ByVal SQLType As
SybaseDataType = Nothing, Optional ByVal Size As Integer =
Nothing, Optional ByVal Direction As ParameterDirection =
ParameterDirection.Input)
Dim buildDataType As AseDbType
Dim buildParameter As ASEProcParameter = Nothing
Select Case SQLType
Case SybaseDataType.SybaseString
buildDataType = AseDbType.VarChar
Case SybaseDataType.SybaseChar
buildDataType = AseDbType.Char
Case SybaseDataType.SybaseInteger
buildDataType = AseDbType.Integer
Case SybaseDataType.SybaseBit
buildDataType = AseDbType.Bit
Case SybaseDataType.SybaseDateTime
buildDataType = AseDbType.DateTime
Case SybaseDataType.SybaseDecimal
buildDataType = AseDbType.Decimal
Case SybaseDataType.SybaseMoney
buildDataType = AseDbType.Money
Case SybaseDataType.SybaseImage
buildDataType = AseDbType.Image
Case SybaseDataType.SybaseFloat
buildDataType = AseDbType.Double
End Select
buildParameter = New ASEProcParameter(ParameterName,
Value, buildDataType, Size, Direction)
Dim dr As DataRow = dtParameterList.NewRow
'dr("ParamDetail") = buildParameter
Dim ser As XmlSerializer = New XmlSerializer(GetType
(ASEProcParameter))
Dim sb As New StringBuilder
Dim writer As New StringWriter(sb)
ser.Serialize(writer, buildParameter)
writer.Close()
dr("ParamDetail") = writer.ToString
dtParameterList.Rows.Add(dr)
End Sub
The above method simply accepts a parameter name, value, data type, size, direction, and so on as parameters. To easily work with data types, I defined my own enumeration as follows:
Public Enum SybaseDataType
SybaseString
SybaseChar
SybaseInteger
SybaseBit
SybaseDateTime
SybaseDecimal
SybaseMoney
SybaseImage
SybaseFloat
End Enum
I will be mapping these data types to the original Sybase data types with the huge “select…case” (as in the previous code listing) within the above method itself. The whole cache of parameters (which are going to be passed to stored procedures) gets stored in “dtParameterList.”
Next: Retrieving a data table by executing a Sybase stored procedure: the code >>
More ASP.NET Articles
More By Jagadish Chaterjee
