DBClass~

<Serializable()> _
Public Class clsDBConn
 
    //REPETITION_KEY ="2627"
    //REFERENCE_KEY ="547"

    Private m_conn As System.Data.SqlClient.SqlConnection   
    Private m_da As System.Data.SqlClient.SqlDataAdapter    

    Private m_tran As System.Data.SqlClient.SqlTransaction   
    Private strConn As System.Data.SqlClient.SqlConnection

   
    Public Sub New()
        Dim connectionString As String = ""
        connectionString = GblConnectString()
        m_conn = New System.Data.SqlClient.SqlConnection(connectionString)
    End Sub

   
    Public Sub New(ByVal strDBName As String)
        Dim connectionString As String = ""
        connectionString = GblConnectString(strDBName)
        m_conn = New System.Data.SqlClient.SqlConnection(connectionString)
    End Sub

    Public Function GblConnectString() As String
        GblConnectString = "User ID=" & ConfigurationManager.AppSettings.Get("gUserID") & _
                           ";Password=" & ConfigurationManager.AppSettings.Get("gPass") & _
                           ";Initial Catalog=" & ConfigurationManager.AppSettings.Get("sDataBase") & _
                           ";Data Source=" & ConfigurationManager.AppSettings.Get("sServerName")
    End Function

    Public Function GblConnectString(ByVal strDBName As String) As String
        GblConnectString = "User ID=" & ConfigurationManager.AppSettings.Get("gUserID") & _
                           ";Password=" & ConfigurationManager.AppSettings.Get("gPass") & _
                           ";Initial Catalog=" & strDBName & _
                           ";Data Source=" & ConfigurationManager.AppSettings.Get("sServerName")
    End Function

  
    Public ReadOnly Property Connection() As System.Data.SqlClient.SqlConnection
        Get
            Return m_conn
        End Get
    End Property

    Public Sub Open()
        Try
            m_conn.Open()
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                  
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Sub

  
    Public Sub Close()
        Try
            If Me.IsConnected Then
                If Me.InTransaction Then
                    Me.Rollback()
                End If
                m_conn.Close()
            End If
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                  
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Sub

   
   
    Public Sub BeginTransaction()
        Try
            m_tran = m_conn.BeginTransaction()
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                  
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Sub

  
    Public Sub Commit()
        Try
            m_tran.Commit()
            m_tran.Dispose()
            m_tran = Nothing
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                  
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Sub

 
    Public Sub Rollback()
        Try
            m_tran.Rollback()
            m_tran.Dispose()
            m_tran = Nothing
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                   
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Sub

  
    Public ReadOnly Property IsConnected() As Boolean
        Get
            Return (m_conn.State = ConnectionState.Open)
        End Get
    End Property

    Public ReadOnly Property InTransaction() As Boolean
        Get
            Return (Not m_tran Is Nothing)
        End Get
    End Property

  
    Public Function ExecuteSql(ByVal pv_strSql As String, ByVal flag As Integer) As Integer
        Try

            Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
            objCmd.Connection = m_conn
            objCmd.Transaction = m_tran
            objCmd.CommandType = CommandType.Text
            Return objCmd.ExecuteNonQuery()

        Catch ex As System.Data.SqlClient.SqlException
            Throw ex

        Catch ex As Exception
            Throw ex

        
        End Try

    End Function

   
    Public Function ExecuteSql(ByVal pv_strSql As String) As Integer
        Dim r As Integer = -1
        Try
            Me.Open()
            Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
            objCmd.Connection = m_conn
            objCmd.CommandType = CommandType.Text
            r = objCmd.ExecuteNonQuery()
            Me.Close()
         
            Return r
        Catch ex As System.Data.SqlClient.SqlException
            If Me.IsConnected Then
                Me.Close()
            End If
            Throw ex

        Catch ex As Exception
            If Me.IsConnected Then
                Me.Close()
            End If
            Throw ex

        End Try

    End Function

  
    Public Function ExecuteSql(ByVal pv_strSql As String, ByRef strFlag As String) As Integer
        Try
            Me.Open()
            Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
            objCmd.Connection = m_conn
            objCmd.CommandType = CommandType.Text
            ExecuteSql = objCmd.ExecuteNonQuery()
            Me.Close()
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
        
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Function

  
    Public Function ExecuteSql(ByVal pv_strSql As String, ByRef strFlag As String, ByVal intFlag As Integer) As Integer
        Try
            Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
            objCmd.Connection = m_conn
            objCmd.Transaction = m_tran
            objCmd.CommandType = CommandType.Text
            ExecuteSql = objCmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
           
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Function

  
 
    Public Function QuerySql(ByVal strSql As String, ByRef m_dt As DataTable) As Boolean
        Try
            m_da = New System.Data.SqlClient.SqlDataAdapter()
            Dim objCmd As New System.Data.SqlClient.SqlCommand(strSql)
            objCmd.Connection = m_conn
            objCmd.Transaction = m_tran
            objCmd.CommandType = CommandType.Text
            m_da.SelectCommand = objCmd
            m_da.Fill(m_dt)
            Return True
        Catch ex As System.Data.SqlClient.SqlException
            Throw ex

        Catch ex As Exception
            Throw ex

        End Try

    End Function

  
  
    Public Function QuerySql(ByVal strSql As String, ByRef m_dt As DataTable, ByVal flag As Integer) As Boolean
        Try
            m_da = New System.Data.SqlClient.SqlDataAdapter()
            Dim objCmd As New System.Data.SqlClient.SqlCommand(strSql)
            objCmd.Connection = m_conn
            objCmd.Transaction = m_tran
            objCmd.CommandType = CommandType.Text
            m_da.SelectCommand = objCmd
            m_da.Fill(m_dt)
            Return True
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Function

  
    Public Function QuerySql(ByVal strSql As String, ByRef m_dt As DataTable, ByRef err As String) As Boolean
        QuerySql = True
        Try
            Me.Open()
            m_da = New System.Data.SqlClient.SqlDataAdapter(strSql, m_conn)
            m_da.Fill(m_dt)
            Me.Close()
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                   
                 
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Function
    Public Function QuerySqlPage(ByVal strSql As String, ByRef m_dt As DataTable, ByVal flag As Integer) As Boolean
        Try
            m_da = New System.Data.SqlClient.SqlDataAdapter()
            Dim objCmd As New System.Data.SqlClient.SqlCommand(strSql)
            objCmd.Connection = m_conn
            objCmd.Transaction = m_tran
            objCmd.CommandType = CommandType.Text
            m_da.SelectCommand = objCmd
            m_da.Fill(flag, 100, m_dt)
            Return True
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
                  
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
    End Function
    Public Function getConnectString() As String
        Dim strCon As String = "User ID=" & ConfigurationManager.AppSettings.Get("gUserID") & _
                           ";Password=" & ConfigurationManager.AppSettings.Get("gPass") & _
                           ";Initial Catalog=" & "master" & _
                           ";Data Source=" & ConfigurationManager.AppSettings.Get("sServerName")
        Return strCon
    End Function
  
    Public Function EtSql(ByVal pv_strSql As String) As String
        Dim r As String = "-1"
        Try
            Me.Open()
            Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
            objCmd.Connection = m_conn
            objCmd.CommandType = CommandType.Text
            r = objCmd.ExecuteNonQuery()
            Me.Close()
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
              
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
        Return r
    End Function

   
    Public Function ExSql(ByVal pv_strSql As String) As String
        Dim dt As New Data.DataTable
        Dim Sql As String
        Sql = "select spid from master..sysprocesses where dbid=db_id('" & ConfigurationManager.AppSettings.Get("sDataBase") & "')"

        Dim r As String = "-1"
        strConn = New System.Data.SqlClient.SqlConnection(getConnectString())
        Dim dsr As New System.Data.SqlClient.SqlDataAdapter(Sql, strConn)
        Dim ds As New DataSet
        dsr.Fill(ds)
        If strConn.State = ConnectionState.Closed Then
            strConn.Open()
        End If
        Try

            Dim objCmd As New System.Data.SqlClient.SqlCommand(pv_strSql)
            objCmd.Connection = strConn
            objCmd.CommandType = CommandType.Text
            For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                objCmd.CommandText = "KILL " & ds.Tables(0).Rows(i)(0)
                r = objCmd.ExecuteNonQuery()
            Next
            objCmd.CommandText = pv_strSql
            r = objCmd.ExecuteNonQuery()
            Me.Close()
        Catch ex As System.Data.SqlClient.SqlException
            If ex.Number = Constants.REFERENCE_KEY OrElse ex.Number = Constants.REPETITION_KEY Then
                Throw ex
            Else
                If Not IsNothing(m_conn) Then
              
                    Me.Close()
                End If
                System.Web.HttpContext.Current.Response.Redirect(Tool.getWebPath & "Forms/error.aspx?errid=11")
            End If
        End Try
        Return r

    End Function

End Class