Creating a database driver for StoredProcedures

[Mako1985]

Hi,
I want to make a database driver for stored procedures.
The Function will look this this:

Function executeStoredProcedureQuery(ByVal storedProcedureName As String, ByVal parameterList As ArrayList) As SqlDataReader
Dim dr As SqlDataReader
Try
sqlConnection.Open() 'open connection
Dim sqlCommand As New SqlCommand
sqlCommand.Connection = sqlConnection
sqlCommand.CommandType = Data.CommandType.StoredProcedure
sqlCommand.CommandText = storedProcedureName

For i = 0 To parameterList.Count - 1
sqlCommand.Parameters.Add("@" + parameterList.Item(i).name, parameterList.Item(i).value)
Next
.....
End Function

My problem is, as you can see, is PASSING THE PARAMETERS.
I wanted to do it as an ArrayList of a custom class called "parameter.vb" which stores the name and value of a given parameter. I am not sure how to do this in VB. Or, there could be a better way to do it. Are there any suggestions?

Mark.

 

[jmcilhinney]

Given that you can already create an SqlParameter object by specifying the name and the value your Parameter class seems kinda pointless. I guess it would allow you to use the same class to execute sprocs against non-SQL Server databases without the user having to know what the data source was. That said, you're returning an SqlDataReader so they'd tend to know that SqlParameters were needed. Anyway, it might look like this:

Public Class DbParameter

    Private _name As String
    Private _value As Object

    Public Property Name() As String
        Get
            Return Me._name
        End Get
        Set(ByVal value As String)
            Me._name = value
        End Set
    End Property

    Public Property Value() As Object
        Get
            Return Me._value
        End Get
        Set(ByVal value As Object)
            Me._value = value
        End Set
    End Property

    Public Sub New(ByVal name As String, ByVal value As Object)
        Me._name = name
        Me._value = value
    End Sub

End Class

Public Function ExecuteReaderOnStoredProcedure(ByVal connection As SqlConnection, _
                                               ByVal procedureName As String, _
                                               ByVal parameters As IList(Of DbParameter)) As SqlDataReader
    Dim command As New SqlCommand(procedureName, connection)

    command.CommandType = CommandType.StoredProcedure

    For Each parameter As DbParameter In parameters
        command.Parameters.AddWithValue("@" & parameter.Name, parameter.Value)
    Next parameter

    If connection.State <> ConnectionState.Open Then
        connection.Open()
    End If

    Return command.ExecuteReader()
End Function

Note that I've declared the 'parameters' argument as type IList(Of DbParameter). That enables you to pass any object that implements that interface, the most common of which would be a DbParameter array or a List(Of DbParameter). That is far superior to using the ArrayList type. In fact, you shouldn't be using the ArrayList type at all in .NET 2.0.

 

[Mako1985]

Thanks, that works great. However I have run into a problem.. It works fine of my local host server. But when I put it on our live web server. It comes up with this error:

...
Dim parameters As IList(Of [B]DbParameter[/B]) = New List(Of DbParameter)
...

Compilation Error
Compiler Error Message: BC32045: 'System.Collections.IList' has no type parameters and so cannot have type arguments.

Any ideas?

 

[jmcilhinney]

Do you have .NET 2.0 installed on your server? That error message says that IList is being interpreted as System.Collections.IList when it should be System.Collections.Generic.IList(Of T).

 

[Mako1985]

Good find! Thanks very much, its all working. I just had to directly address IList and List so it used generics. The server was running .NET 2.0 but I still had to do it this way.

     Dim parameters As System.Collections.Generic.IList(Of DbParameter) = New System.Collections.Generic.List(Of DbParameter)

Thanks for the help!

 

[JohnH]

List(Of T) implements IList(Of T) so you create one such and supply as parameter, as mentioned also other type collections implements IList(Of T) and can be used as parameters so the ExecuteReaderOnStoredProcedure method is very versatile.

Dim parameters As New List(Of DbParameter)
Dim reader As SqlDataReader = ExecuteReaderOnStoredProcedure(conn, proc, parameters)

The "I" prefix letter in IList means Interface, this is something that is used in Object Oriented Programming to give polymorphism, multiple different classes can implement the same interface and in cases like this be treates as equals.