Designing a database connection class in VS 2005

Question

I am working on designing a SQL Server database connection class in VB.net 2005. The idea behind doing this will be so a developer can can call the class, pass it a stored procedure name along with the parameters, and get return values back (if any).

My question is, how would I design the class so the stored proc parameters are dynamic? My second question is, how would I account for the data type being passed to the stored procedure?

Answer 1

This assumes SQL Server, but the SqlParameter type could be changed to match the connection type. As items are added to this list the data type would have to be identified.

    Imports System.Data.SqlClient
    Dim Params As List(Of SqlParameter)

    Public Property ParameterList() As List(Of SqlParameter)
        Get
            Return Params
        End Get
        Set(ByVal value As List(Of SqlParameter))
            Params = value
        End Set
    End Property

You'll have to loop through the list and add each parameter to a command object.