#Region "  Imports  "

Imports System.Configuration.ConfigurationManager
Imports System.Data
Imports System.Data.SqlClient
Imports Statistics.AppException

#End Region

Public Class Ado

#Region "  Static Methods  "

    Public Shared Function Adapter(ByRef cmd As IDbCommand) As IDbDataAdapter

        Dim adpt As SqlDataAdapter
        adpt = New SqlDataAdapter(cmd)
        Return DirectCast(adpt, IDbDataAdapter)

    End Function

    Public Shared Function Command(ByRef cn As IDbConnection, ByVal sql As String, Optional ByVal isSP As Boolean = False) As IDbCommand

        Dim baseCommand As IDbCommand = DirectCast(New SqlCommand(sql, cn), IDbCommand)
        If isSP Then baseCommand.CommandType = CommandType.StoredProcedure
        Return baseCommand

    End Function

    Public Shared Function Connection(ByVal cnName As String) As IDbConnection

        Dim cn As SqlConnection
        cn = New SqlConnection(AppSettings(cnName))
        Return DirectCast(cn, IDbConnection)

    End Function

    Public Shared Function Datareader(ByRef cmd As IDbCommand) As IDataReader

        Dim dr As SqlDataReader
        If cmd.Connection.State = ConnectionState.Open Then
            dr = cmd.ExecuteReader()
            Return DirectCast(dr, IDataReader)
        Else
            Try
                cmd.Connection.Open()
            Catch ex As Exception
                Throw New AppException("ERROR ADO1 - DataReader method failed to open a connection: ", ex)
                HttpContext.Current.Response.Write("ERROR: Unable to open a connection to the Database.")
                HttpContext.Current.Response.End()
            End Try
            dr = cmd.ExecuteReader()
            Return DirectCast(dr, IDataReader)
            cmd.Connection.Close()
        End If

    End Function

    Public Shared Function Dataset(ByRef cmd As IDbCommand) As DataSet

        Dim ds As New DataSet
        Dim adpt As IDbDataAdapter = Ado.Adapter(cmd)
        If cmd.Connection.State = ConnectionState.Open Then
            adpt.Fill(ds)
            Return ds
        Else
            Try
                cmd.Connection.Open()
            Catch ex As Exception
                Throw New AppException("ERROR ADO2 - DataSet method failed to open a connection: ", ex)
                HttpContext.Current.Response.Write("ERROR: Unable to open a connection to the Database.")
                HttpContext.Current.Response.End()
            End Try
            adpt.Fill(ds)
            Return ds
            cmd.Connection.Close()
        End If

    End Function

    Public Shared Sub Dispose(ByRef cn As IDbConnection)

        If Not cn.State = ConnectionState.Closed Then cn.Close()

    End Sub

    Public Shared Sub Execute(ByRef cmd As IDbCommand)
        Dim sqlcmd As SqlCommand
        sqlcmd = DirectCast(cmd, SqlCommand)
        If sqlcmd.Connection.State = ConnectionState.Open Then
            sqlcmd.ExecuteNonQuery()
        Else
            Try
                sqlcmd.Connection.Open()
            Catch ex As Exception
                Throw New AppException("ERROR ADO3 - Execute method failed to open a connection: ", ex)
                HttpContext.Current.Response.Write("ERROR: Unable to open a connection to the Database.")
                HttpContext.Current.Response.End()

            End Try
            sqlcmd.ExecuteNonQuery()
            sqlcmd.Connection.Close()
        End If

    End Sub

    Public Shared Function HasData(ByRef cmd As IDbCommand) As Boolean

        Dim dr As SqlDataReader
        Dim sqlcmd As SqlCommand
        sqlcmd = DirectCast(cmd, SqlCommand)

        Dim count As Integer = 0
        If sqlcmd.Connection.State = ConnectionState.Open Then
            dr = sqlcmd.ExecuteReader()
            If dr.HasRows Then
                Return True
                dr.Close()
            Else
                Return False
                dr.Close()
            End If
        Else
            Try
                sqlcmd.Connection.Open()
            Catch ex As Exception
                Throw New AppException("ERROR ADO4 - HasData method failed to open a connection.", ex)
                HttpContext.Current.Response.Write("ERROR: Unable to open a connection to the Database.")
                HttpContext.Current.Response.End()
            End Try
            dr = sqlcmd.ExecuteReader()
            If dr.HasRows Then
                Return True
                dr.Close()
            Else
                Return False
                dr.Close()
            End If
            Return count
            sqlcmd.Connection.Close()
        End If

    End Function

    Public Overloads Shared Sub Parameter(ByRef cmd As IDbCommand, ByVal name As String, ByVal value As Object)

        Dim param As IDataParameter = New SqlParameter()
        param.ParameterName = name
        param.Value = value
        param.Direction = ParameterDirection.Input
        cmd.Parameters.Add(param)
        
    End Sub

    Public Overloads Shared Sub Parameter(ByRef cmd As IDbCommand, ByVal name As String, ByVal dbtype As DbType)

        Dim param As IDataParameter = New SqlParameter()
        param.ParameterName = name
        param.Direction = ParameterDirection.Output
        param.DbType = dbtype
        cmd.Parameters.Add(param)
        
    End Sub

    Public Shared Function RowCount(ByRef cmd As IDbCommand) As Integer

        Dim dr As SqlDataReader
        Dim sqlcmd As SqlCommand
        sqlcmd = DirectCast(cmd, SqlCommand)
        Dim count As Integer = 0
        If sqlcmd.Connection.State = ConnectionState.Open Then
            dr = sqlcmd.ExecuteReader()
            If dr.HasRows Then
                While dr.Read
                    count += 1
                End While
            End If
            Return count
            dr.Close()
        Else
            Try
                sqlcmd.Connection.Open()
            Catch ex As Exception
                Throw New AppException("ERROR ADO4 - RowCount method failed to open a connection: ", ex)
                HttpContext.Current.Response.Write("ERROR: Unable to open a connection to the Database.")
                HttpContext.Current.Response.End()
            End Try
            dr = sqlcmd.ExecuteReader()
            If dr.HasRows Then
                While dr.Read
                    count += 1
                End While
            End If
            Return count
            dr.Close()
            sqlcmd.Connection.Close()
        End If

    End Function

#End Region

End Class