DataAccess Class - VB

Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration

Public Class DataAccess

    ''#If DEBUG Then
    ''    Private Shared _strConnectionString As String = Configuration.ConfigurationSettings.AppSettings.Get("ConnStringDebug") 'My.Settings.Item("ConnStringDebug")
    ''         '"Server =.;Initial Catalog=IPDB;Integrated security = true"
    ''#Else
    ''    Private Shared _strConnectionString As String = My.Settings.Item("ConnString") 'Configuration.ConfigurationSettings.AppSettings.Get("ConnString")
    ''#End If

#If DEBUG Then
    Private Shared _strConnectionString As String = ConfigurationManager.AppSettings("ConnStringDebug")  '"Server =.;Initial Catalog=IPDB;Integrated security = true"
#Else
    Private Shared _strConnectionString As String = ConfigurationManager.AppSettings("ConnString")
#End If

    Public Property ConnectionString() As String
        Get
            ConnectionString = _strConnectionString
        End Get
        Set(ByVal Value As String)
            _strConnectionString = Value
        End Set
    End Property

    Public Function TestDBConnection() As Boolean
        Dim objConnection As New SqlConnection(_strConnectionString)
        Try
            objConnection.Open()
            objConnection.Close()
            Return True
        Catch ex As SqlException
            Throw ex
            Return False
        Catch ex As Exception
            Throw ex
            Return False
        Finally
            objConnection.Dispose()
        End Try
    End Function

    Public Function QueryIn_DataSet(ByVal sSPName As String, _
                                 ByVal arrInputValues() As String) As DataSet

        Dim objConnection As SqlConnection = Nothing
        Dim objAdapter As SqlDataAdapter
        Dim objDataSet As New DataSet
        Dim objCommand As New SqlCommand
        Dim iCount As Integer
        Dim strConnectionString As String

        Try
            strConnectionString = _strConnectionString
            objAdapter = New SqlDataAdapter
            objConnection = New SqlConnection(strConnectionString)
            objConnection.Open()
            objCommand.CommandTimeout = 900
            objAdapter.SelectCommand = objCommand
            objAdapter.SelectCommand.Connection = objConnection
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            objAdapter.SelectCommand.CommandText = sSPName

            Try
                SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
                For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
                    If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.Input) Or _
                        (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.InputOutput) Then

                        objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
                    End If
                    If iCount - 1 = UBound(arrInputValues) Then Exit For
                Next

                objAdapter.Fill(objDataSet)
                objConnection.Close()
                Return objDataSet

            Catch ex As SqlException
                objConnection.Close()
                Throw ex
            End Try
        Catch ex As Exception
            objConnection.Close()
            Throw ex
        Finally
            objConnection.Dispose()
        End Try

    End Function

    Public Function QueryIn_DataTable(ByVal sSPName As String, _
            ByVal arrInputValues() As String) As DataTable

        Dim objConnection As SqlConnection
        Dim objAdapter As SqlDataAdapter
        Dim objCommand As New SqlCommand
        Dim objDataTable As New DataTable
        Dim iCount As Integer
        Dim strConnectionString As String

        Try
            strConnectionString = _strConnectionString
            objAdapter = New SqlDataAdapter
            objConnection = New SqlConnection(strConnectionString)
            objConnection.Open()
            objCommand.CommandTimeout = 900
            objAdapter.SelectCommand = objCommand
            objAdapter.SelectCommand.Connection = objConnection
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            objAdapter.SelectCommand.CommandText = sSPName

            Try
                SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
                For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
                    If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.Input) Or _
                        (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.InputOutput) Then

                        objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
                    End If
                    If iCount - 1 = UBound(arrInputValues) Then Exit For
                Next

                objAdapter.Fill(objDataTable)
                objConnection.Close()
                Return objDataTable

            Catch ex As SqlException
                objConnection.Close()
                Throw ex
            End Try
        Catch ex As Exception
            objConnection.Close()
            Throw ex
        Finally
            objConnection.Dispose()
        End Try

    End Function

    Public Function QueryIn_DataView(ByVal sSPName As String, _
            ByVal arrInputValues() As String) As DataView

        Dim objConnection As SqlConnection
        Dim objAdapter As SqlDataAdapter
        Dim objCommand As New SqlCommand
        Dim objDataSet As New DataSet
        Dim iCount As Integer
        Dim strConnectionString As String

        Try
            strConnectionString = _strConnectionString
            objAdapter = New SqlDataAdapter
            objConnection = New SqlConnection(strConnectionString)
            objConnection.Open()
            objCommand.CommandTimeout = 900
            objAdapter.SelectCommand = objCommand
            objAdapter.SelectCommand.Connection = objConnection
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            objAdapter.SelectCommand.CommandText = sSPName

            Try
                SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
                For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
                    If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.Input) Or _
                        (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.InputOutput) Then

                        objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
                    End If
                    If iCount - 1 = UBound(arrInputValues) Then Exit For
                Next

                objAdapter.Fill(objDataSet, "Default")
                objConnection.Close()
                Return objDataSet.Tables("Default").DefaultView

            Catch ex As SqlException
                objConnection.Close()
                Throw ex
            End Try
        Catch ex As Exception
            objConnection.Close()
            Throw ex
        Finally
            objConnection.Dispose()
        End Try

    End Function

    Public Function QueryIn_SqlDataReader(ByVal sSPName As String, _
          ByVal arrInputValues() As String) As SqlClient.SqlDataReader

        Dim objConnection As SqlConnection = Nothing
        Dim objAdapter As SqlDataAdapter
        Dim objCommand As New SqlCommand
        Dim objDataReader As SqlClient.SqlDataReader
        'Dim strScalar As String
        Dim iCount As Integer
        Dim strConnectionString As String

        Try
            strConnectionString = _strConnectionString
            objAdapter = New SqlDataAdapter
            objConnection = New SqlConnection(strConnectionString)
            objConnection.Open()
            objCommand.CommandTimeout = 900
            objAdapter.SelectCommand = objCommand
            objAdapter.SelectCommand.Connection = objConnection
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            objAdapter.SelectCommand.CommandText = sSPName

            Try
                SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
                For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
                    If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.Input) Or _
                        (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.InputOutput) Then

                        objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
                    End If
                    If iCount - 1 = UBound(arrInputValues) Then Exit For
                Next

                objDataReader = objAdapter.SelectCommand.ExecuteReader
                objConnection.Close()
                Return objDataReader

            Catch ex As SqlException
                objConnection.Close()
                Throw ex
            End Try
        Catch ex As Exception
            objConnection.Close()
            Throw ex
        Finally
            objConnection.Dispose()
        End Try

    End Function

    Public Function NonQuery_ExecuteScalar(ByVal sSPName As String, _
            ByVal arrInputValues() As String) As String

        Dim objConnection As SqlConnection = Nothing
        Dim objAdapter As SqlDataAdapter
        Dim objCommand As New SqlCommand
        Dim strScalar As String
        Dim iCount As Integer
        Dim strConnectionString As String

        Try
            strConnectionString = _strConnectionString
            objAdapter = New SqlDataAdapter
            objConnection = New SqlConnection(strConnectionString)
            objConnection.Open()
            objCommand.CommandTimeout = 900
            objAdapter.SelectCommand = objCommand
            objAdapter.SelectCommand.Connection = objConnection
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            objAdapter.SelectCommand.CommandText = sSPName

            Try
                SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
                For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
                    If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.Input) Or _
                        (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.InputOutput) Then

                        objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
                    End If
                    If iCount - 1 = UBound(arrInputValues) Then Exit For
                Next

                strScalar = objAdapter.SelectCommand.ExecuteScalar()
                objConnection.Close()
                Return strScalar

            Catch ex As SqlException
                objConnection.Close()
                Throw ex
            End Try
        Catch ex As Exception
            objConnection.Close()
            Throw ex
        Finally
            objConnection.Dispose()
        End Try

    End Function

    Public Function NonQuery_ExecuteNonQuery(ByVal sSPName As String, _
           ByVal arrInputValues() As String) As Integer

        Dim objConnection As SqlConnection = Nothing
        Dim objAdapter As SqlDataAdapter
        Dim objCommand As New SqlCommand
        Dim intRowsEffected As String
        Dim iCount As Integer
        Dim strConnectionString As String

        Try
            strConnectionString = _strConnectionString
            objAdapter = New SqlDataAdapter
            objConnection = New SqlConnection(strConnectionString)
            objConnection.Open()
            objCommand.CommandTimeout = 900
            objAdapter.SelectCommand = objCommand
            objAdapter.SelectCommand.Connection = objConnection
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            objAdapter.SelectCommand.CommandText = sSPName

            Try
                SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
                For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
                    If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.Input) Or _
                        (objAdapter.SelectCommand.Parameters(iCount).Direction = _
                        ParameterDirection.InputOutput) Then

                        objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
                    End If
                    If iCount - 1 = UBound(arrInputValues) Then Exit For
                Next

                intRowsEffected = objAdapter.SelectCommand.ExecuteNonQuery
                objConnection.Close()
                Return intRowsEffected

            Catch ex As SqlException
                objConnection.Close()
                Throw ex
            End Try
        Catch ex As Exception
            objConnection.Close()
            Throw ex
        Finally
            objConnection.Dispose()
        End Try

    End Function
End Class