SQL2KConnect

Imports System.Data.SqlClient
 
Namespace DBConnect
    ''' <summary>
    ''' Use this Class to Connect to SQL Server 2000
    ''' </summary>
    ''' <remarks></remarks>
    Public Class SQL2KConnect
        Private Shared strConString As String = ""
        Private Shared sqlCon As SqlConnection
        Private Shared sqlCmd As SqlCommand
        'Private Shared sqlTrans As SqlTransaction
 
        ''' <summary>
        ''' Create New Instance With Set the Connection String
        ''' </summary>
        ''' <param name="ConnectionString">Set the Connection String for Open New Connection</param>
        Sub New(ByVal ConnectionString As String)
            strConString = ConnectionString
        End Sub
 
        ''' <summary>
        ''' Create New Instance Without Set the Connection String
        ''' </summary>
        Sub New()
 
        End Sub
 
        ''' <summary>
        ''' Get or Set the Connection String
        ''' </summary>
        Public Property ConnectionString() As String
            Get
                Return strConString
            End Get
            Set(ByVal value As String)
                strConString = value
            End Set
        End Property
 
        ''' <summary>
        ''' Get State of the Open Connection
        ''' </summary>
        Public ReadOnly Property StateConnection() As ConnectionState
            Get
                Return sqlCon.State
            End Get
        End Property
 
        ''' <summary>
        ''' Open the Connection
        ''' </summary>
        Public Function OpenConnection() As Boolean
            Try
                sqlCon = New SqlConnection(strConString)
                sqlCon.Open()
                'sqlTrans = sqlCon.BeginTransaction()
                Return True
            Catch ex As SqlException
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Function
 
        ''' <summary>
        ''' Open the Connection
        ''' </summary>
        ''' <param name="conString">The Connection String</param>
        Public Function OpenConnection(ByVal conString As String) As Boolean
            Try
                strConString = conString
                sqlCon = New SqlConnection(strConString)
                sqlCon.Open()
                'sqlTrans = sqlCon.BeginTransaction()
                Return True
            Catch ex As SqlException
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Function
 
        ''' <summary>
        ''' Close the Connection
        ''' </summary>
        Public Sub CloseConnection()
            Try
                If sqlCon.State = ConnectionState.Open Then
                    'sqlTrans.Commit()
                    sqlCon.Close()
                End If
            Catch ex As Exception
                'If Not sqlTrans.Connection Is DBNull.Value Then
                '    sqlTrans.Rollback()
                'End If
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Sub
 
        ''' <summary>
        ''' Execute Update, Delete and Insert Query
        ''' </summary>
        ''' <param name="query">Query which you want to execute</param>
        ''' <returns></returns>
        Public Function ExecuteNonQuery(ByVal query As String) As Boolean
            Try
                'sqlCmd = New SqlCommand(query, sqlCon, sqlTrans)
                sqlCmd = New SqlCommand(query, sqlCon)
                'sqlCmd.CommandTimeout = 0
                sqlCmd.CommandType = CommandType.Text
                sqlCmd.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                If sqlCon.State = ConnectionState.Open Then
                    'If Not sqlTrans.Connection Is DBNull.Value Then
                    '    sqlTrans.Rollback()
                    'End If
                    sqlCon.Close()
                End If
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Function
 
        ''' <summary>
        ''' Execute Select Query
        ''' </summary>
        ''' <param name="query">Query which you want to execute</param>
        Public Function ExecuteQuery(ByVal query As String) As SqlDataReader
            Dim reader As SqlDataReader
            Try
                'sqlCmd = New SqlCommand(query, sqlCon, sqlTrans)
                sqlCmd = New SqlCommand(query, sqlCon)
                'sqlCmd.CommandTimeout = 0
                reader = sqlCmd.ExecuteReader
                Return reader
            Catch ex As Exception
                If sqlCon.State = ConnectionState.Open Then
                    'If Not sqlTrans.Connection Is DBNull.Value Then
                    '    sqlTrans.Rollback()
                    'End If
                    sqlCon.Close()
                End If
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Function
 
        ''' <summary>
        ''' execute Select Query
        ''' </summary>
        ''' <param name="query">Query which you want to execute</param> 
        ''' <param name="tableName">Table name</param>
        Public Function ExecuteQuery(ByVal query As String, ByVal tableName As String) As DataTable
            Dim dt As DataTable
            Dim da As New SqlDataAdapter
            Try
                'sqlCmd = New SqlCommand(query, sqlCon, sqlTrans)
                sqlCmd = New SqlCommand(query, sqlCon)
                'sqlCmd.CommandTimeout = 0
                dt = New DataTable(tableName)
                sqlCmd.CommandType = CommandType.Text
                da.SelectCommand = sqlCmd
                da.Fill(dt)
                Return dt
            Catch ex As Exception
                If sqlCon.State = ConnectionState.Open Then
                    'If Not sqlTrans.Connection Is DBNull.Value Then
                    '    sqlTrans.Rollback()
                    'End If
                    sqlCon.Close()
                End If
                Throw New Exception(ex.Message.ToString)
            End Try
        End Function
 
        Public Function ExecuteStoredProcedure(ByVal spName As String) As DataTable
            Dim dt As DataTable
            Dim da As New SqlDataAdapter
            Try
                'sqlCmd = New SqlCommand(spName, sqlCon, sqlTrans)
                sqlCmd = New SqlCommand(spName, sqlCon)
                'sqlCmd.CommandTimeout = 0
                dt = New DataTable("table1")
                sqlCmd.CommandType = CommandType.StoredProcedure
                da.SelectCommand = sqlCmd
                da.Fill(dt)
                Return dt
            Catch ex As Exception
                If sqlCon.State = ConnectionState.Open Then
                    'If Not sqlTrans.Connection Is DBNull.Value Then
                    '    sqlTrans.Rollback()
                    'End If
                    sqlCon.Close()
                End If
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Function
 
        Public Function ExecuteStoredProcedure(ByVal spName As String, ByVal spParam As ArrayList) As DataTable
            Dim dt As DataTable
            Dim da As New SqlDataAdapter
            Try
                'sqlCmd = New SqlCommand(spName, sqlCon, sqlTrans)
                sqlCmd = New SqlCommand(spName, sqlCon)
                'sqlCmd.CommandTimeout = 0
                dt = New DataTable("table1")
                sqlCmd.CommandType = CommandType.StoredProcedure
                For Each obj As SqlParameter In spParam
                    sqlCmd.Parameters.Add(New SqlParameter(obj.ParameterName, obj.Value))
                Next
                da.SelectCommand = sqlCmd
                da.Fill(dt)
                Return dt
            Catch ex As Exception
                If sqlCon.State = ConnectionState.Open Then
                    'If Not sqlTrans.Connection Is DBNull.Value Then
                    '    sqlTrans.Rollback()
                    'End If
                    sqlCon.Close()
                End If
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Function
 
        Public Function ExecuteNonQueryStoredProcedure(ByVal spName As String, ByVal spParam As ArrayList) As Boolean
            Dim da As New SqlDataAdapter
            Dim str As String = ""
            Try
                'sqlCmd = New SqlCommand(spName, sqlCon, sqlTrans)
                sqlCmd = New SqlCommand(spName, sqlCon)
                'sqlCmd.CommandTimeout = 0
                sqlCmd.CommandText = spName
                For Each obj As SqlParameter In spParam
                    sqlCmd.Parameters.Add(New SqlParameter(obj.ParameterName, obj))
                Next
                sqlCmd.Connection = sqlCon
                'sqlCmd.Transaction = sqlTrans
                sqlCmd.CommandType = CommandType.StoredProcedure
                sqlCmd.ExecuteScalar()
                Return True
            Catch ex As Exception
                If sqlCon.State = ConnectionState.Open Then
                    'If Not sqlTrans.Connection Is DBNull.Value Then
                    '    sqlTrans.Rollback()
                    'End If
                    sqlCon.Close()
                End If
                Throw New Exception(ex.Message.ToString, ex)
            End Try
        End Function
    End Class
End Namespace