This calls a stored procedure and returns a dataset.
The connection is opened and closed on every call.
DalOLEDb isn't complete but should be easy to finish off.

It has hard coded connection strings which you should replace.


Option Strict On
#Const DalSQL = True
#Const DalOLEDb = False
#Const Dev = False


        Dim dal As New DAL
        Dim sqlparams() As System.Data.SqlClient.SqlParameter
        Dim Param As New DALSQLParam
        Dim Resultset As System.Data.DataSet

        ReDim Preserve sqlparams(0)
        sqlparams(UBound(sqlparams)) = Param.Param("@system_id", "int", CType(id, String), "input")
        ReDim Preserve sqlparams(UBound(sqlparams) + 1)
        sqlparams(UBound(sqlparams)) = Param.Param("@value", "string", CType(value, String), "input")

        ' get the data - call stored procedure mysp
        Resultset = dal.GetResultset("mySP", sqlparams)
        sqlparams = Nothing

#Region "DAL"
Friend Class DAL
#If DalOLEDb = True Then
    Friend Function OpenConnection() As OleDb.OleDbConnection
        Dim cn As New OleDb.OleDbConnection
        Dim param As OleDb.OleDbParameter

        cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);UID=sa;PWD=mypwd;Initial Catalog=mydatabase"
        ' cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI;Initial Catalog=mydatabase"
        cn.Open()
        Return cn
    End Function
#End If
#If DalSQL = True Then
    Private Function OpensqlConnection() As System.Data.SqlClient.SqlConnection
        Dim cn As System.Data.SqlClient.SqlConnection
        Dim param As OleDb.OleDbParameter

        cn = New System.Data.SqlClient.SqlConnection
#if Dev = true then
        cn.ConnectionString = "Data Source=(local);UID=sa;PWD=mypwd;Initial Catalog=mydatabase"
        'cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);UID=sa;PWD=mypwd;Initial Catalog=mydatabase"
        ' cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI;Initial Catalog=mydatabase"
#Else
        cn.ConnectionString = "Data Source=myserver;Initial Catalog=mydatabase;Integrated Security=SSPI"
#End If
        cn.Open()
        Return cn
    End Function
    Friend Function GetResultset(ByVal spname As String, ByVal params() As System.Data.sqlclient.SqlParameter) As System.Data.DataSet
        Dim cn As System.Data.SqlClient.SqlConnection
        Dim ds As System.Data.DataSet
        Dim da As System.Data.SqlClient.SqlDataAdapter
        Dim param As System.Data.SqlClient.SqlParameter
        Dim cmd As System.Data.SqlClient.SqlCommand

        ' create command object
        cmd = New System.Data.SqlClient.SqlCommand
        'get parameters
        If Not params Is Nothing Then
            For Each param In params
                cmd.Parameters.Add(param)
            Next
        End If
        ' set sp name
        cmd.CommandText = spname
        cmd.CommandType = CommandType.StoredProcedure
        ' connect to database
        cn = OpensqlConnection()
        cmd.Connection = cn
        ' Fill DataSet from DataAdapter
        da = New System.Data.SqlClient.SqlDataAdapter
        da.SelectCommand = cmd
        ds = New System.Data.DataSet
        da.Fill(ds)
        ' clear up
        da = Nothing
        cmd = Nothing
        cn.Close()

        Return ds
    End Function
#End If
End Class
#If DalSQL = True Then
Friend Class DALSQLParam
    Friend Function Param(ByVal name As String, ByVal Type As String, ByVal value As String, ByVal direction As String) As System.Data.SqlClient.SqlParameter
        Dim oParam As New System.Data.SqlClient.SqlParameter
        If direction = "output" Then
            oParam.Direction = ParameterDirection.InputOutput
        Else
            oParam.Direction = ParameterDirection.Input
        End If
        If Type = "int" Then
            oParam.SqlDbType = SqlDbType.Int
        ElseIf Type = "string" Then
            oParam.SqlDbType = SqlDbType.VarChar
            If Len(value) = 0 Then
                oParam.Size = 1
            Else
                oParam.Size = Len(value)
            End If
        ElseIf Type = "date" Then
            oParam.SqlDbType = SqlDbType.DateTime
        End If
        oParam.ParameterName = name
        oParam.Value = value
        Return oParam
    End Function
#End If
End Class
#If DalOLEDb = True Then
    Friend Class DALParam
        Friend Function Param(ByVal name As String, ByVal Type As String, ByVal value As String, ByVal direction As String) As OleDb.OleDbParameter
            Dim oParam As New OleDb.OleDbParameter
            If direction = "output" Then
                oParam.Direction = ParameterDirection.InputOutput
            Else
                oParam.Direction = ParameterDirection.Input
            End If
            If Type = "int" Then
                oParam.OleDbType = System.Data.OleDb.OleDbType.Integer
            ElseIf Type = "string" Then
                oParam.OleDbType = System.Data.OleDb.OleDbType.VarChar
                If Len(value) = 0 Then
                    oParam.Size = 1
                Else
                    oParam.Size = Len(value)
                End If
            ElseIf Type = "date" Then
                oParam.OleDbType = System.Data.OleDb.OleDbType.DBTimeStamp
            End If
            oParam.ParameterName = name
            oParam.Value = value
            Return oParam
        End Function
    End Class
#End If
#End Region