Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Diagnostics
Imports System.Configuration

Namespace DataAccess
    Public Class Configuration
        Public Shared ConnectionString = System.Configuration.ConfigurationManager.AppSettings("vipConnectionString").ToString()
        'Add the connection string using the following template to the web.config file
        '   <appSettings>
        '    <add key="vipConnectionString" value="Provider=microsoft.Jet.OLEDB.4.0; Data Source=C:\usr\dev\src\c13\vip\vip.mdb"/>
        '   </appSettings>

    End Class

    Public Class QueriesOleDb
        Public Shared Function ExecuteScalar(ByVal StoredProc As String, ByVal ParamArray commandParameters() As OleDbParameter) As Object
            'declare local variables
            Dim retval As Object = 0
            Dim cmd As OleDbCommand = New OleDbCommand
            Dim cn As OleDbConnection = New OleDbConnection(Configuration.ConnectionString)
            Try
                'set the command text (stored procedure name or SQL statement)
                cmd.CommandText = StoredProc
                cmd.CommandType = CommandType.StoredProcedure
                Dim p As OleDbParameter
                For Each p In commandParameters
                    cmd.Parameters.Add(p)
                Next
                cn.Open()
                'associate the connection with the command
                cmd.Connection = cn
                'create the DataAdapter & DataSet
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                Dim ds As DataSet = New DataSet
                'fill the DataSet using default values for DataTable names, etc.
                da.Fill(ds)
                cn.Close()
                cmd.Parameters.Clear()
                If (ds.Tables(0).Rows.Count = 0) Then
                    retval = 0
                ElseIf (System.DBNull.Value Is ds.Tables(0).Rows(0)(0)) Then
                    retval = 0
                Else
                    retval = ds.Tables(0).Rows(0)(0)
                End If
                ' -----------
                ' handle any errors and pop the routine off the stack
            Catch e As Exception
                Throw e
            Finally
                cn.Close()
            End Try

            Return retval
        End Function

        Public Shared Function ExecuteQuery(ByVal StoredProc As String, ByVal ParamArray commandParameters() As OleDbParameter) As DataSet
            'declare local variables
            Dim retval As DataSet = New DataSet
            Dim cmd As OleDbCommand = New OleDbCommand
            Dim cn As OleDbConnection = New OleDbConnection(Configuration.ConnectionString)
            Try
                'set the command text (stored procedure name or SQL statement)
                cmd.CommandText = StoredProc
                cmd.CommandType = CommandType.StoredProcedure
                Dim p As OleDbParameter
                For Each p In commandParameters
                    cmd.Parameters.Add(p)
                Next
                cn.Open()
                'associate the connection with the command
                cmd.Connection = cn
                'create the DataAdapter & DataSet
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                Dim ds As DataSet = New DataSet
                'fill the DataSet using default values for DataTable names, etc.
                da.Fill(ds)
                cn.Close()
                cmd.Parameters.Clear()
                retval = ds
                ' -----------
                ' handle any errors 
            Catch e As Exception
                Throw e
                retval = Nothing
            Finally
                cn.Close()
            End Try

            Return retval
        End Function

        Public Shared Function ExecuteNonQuery(ByVal StoredProc As String, ByVal ParamArray commandParameters() As OleDbParameter) As Boolean
            'declare local variables
            Dim retval As Boolean = False
            Dim cmd As OleDbCommand = New OleDbCommand
            Dim cn As OleDbConnection = New OleDbConnection(Configuration.ConnectionString)
            Dim rows As Integer
            Try
                'set the command text (stored procedure name or SQL statement)
                cmd.CommandText = StoredProc
                cmd.CommandType = CommandType.StoredProcedure
                Dim p As OleDbParameter
                For Each p In commandParameters
                    cmd.Parameters.Add(p)
                Next
                cn.Open()
                'associate the connection with the command
                cmd.Connection = cn
                rows = cmd.ExecuteNonQuery()
                cn.Close()
                cmd.Parameters.Clear()
                retval = rows > 0
                ' -----------
                ' handle any errors and pop the routine off the stack
            Catch e As Exception
                Throw e
            Finally
                cn.Close()
            End Try

            Return retval
        End Function
    End Class
End Namespace