Imports System.Data
Imports System.Data.SqlClient

'------------------------------------------------------------------------
'           Class: sqlDatabaseConn and mdbDatabseConn 
'
'   Functions: Create(New), open(), close(), getData(), execSql(), 
'              execStoredProcedure()
'
'   SQL databases use sqlConnection for the best preformance
'   Access databases use oledConnection
'
'   This functions will save time when switch tables from Access to SQL
'   (The Interface for both Access and SQL is the same)
'   
'    Created by Daoying Ma, 2/19/07  
'
'------------------------------------------------------------------------

Public Class sqlDatabaseConn
    'connStr will have info of Server and Database
    Private connStr As String

    'dbConn is the connection to database, will keep open
    Public dbConn As SqlConnection = Nothing

    'Init a sqlDatabaseConn class
    Public Sub New(ByVal dbServerName As String, ByVal dbName As String)
        Try
            'save sql database info
            connStr = "DATA SOURCE=" & dbServerName & ";DATABASE=" & dbName & ";Trusted_Connection=yes"
        Catch e As Exception
            MsgBox("Error" & Err.Description)
        End Try
    End Sub

    Public Function open() As Boolean
        Try
            If dbConn Is Nothing Then
                'first time open the database
                dbConn = New SqlConnection(connStr)
                dbConn.Open()
            End If

            If dbConn.State = ConnectionState.Open Then
                Return True
            Else
                'connect closed, reopen it
                dbConn = New SqlConnection(connStr)
                dbConn.Open()

                If dbConn.State = ConnectionState.Open Then
                    Return True
                Else
                    Return False
                End If
            End If
        Catch e As Exception
            MsgBox("Error:" & e.Message)
            Return False
        End Try
    End Function

    Public Sub close()
        If Not dbConn Is Nothing Then
            dbConn.Close()
        End If
    End Sub

    Public Function getData(ByVal sqlStr As String) As DataSet
        Dim myDA As SqlDataAdapter, myDataSet As DataSet
        'run select query and return the result to a dataset
        'this will transfer all you selected data from sql server to client side

        'return the selected dataset if success, return an empty dataset if failed

        myDataSet = New DataSet()
        Try
            myDA = New SqlDataAdapter(sqlStr, dbConn)

            myDA.Fill(myDataSet)

            Return myDataSet
        Catch e As Exception
            MsgBox("Error:" & e.Message)
            myDataSet.Clear()
            Return myDataSet
        End Try
    End Function

    Public Function execSql(ByVal sqlStr As String) As Boolean
        Dim myCom As SqlCommand
        'run insert, update, delete with no return queries
        Try
            myCom = New SqlCommand(sqlStr, dbConn)
            myCom.ExecuteNonQuery()

            Return True
        Catch e As Exception
            MsgBox("Error:" & e.Message)
            Return False
        End Try

    End Function

    Public Function execStoredProcedure(ByVal proName As String) As Boolean
        Return True
    End Function
End Class


Public Class mdbDatabaseConn
    'connStr will have info of Server and Database
    Private connStr As String

    'dbConn is the connection to database, will keep open
    Public dbConn As New OleDb.OleDbConnection

    'Init a mdbDatabaseConn class
    Public Sub New(ByVal dbName As String)
        Try
            'access database
            connStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & dbName
        Catch e As Exception
            MsgBox("Error" & Err.Description)
        End Try
    End Sub

    Public Function open() As Boolean
        Try
            If dbConn Is Nothing Then
                'first time open the database
                dbConn = New OleDb.OleDbConnection(connStr)
                dbConn.Open()
            End If

            If dbConn.State = ConnectionState.Open Then
                Return True
            Else
                'connect closed, reopen it
                dbConn = New OleDb.OleDbConnection(connStr)
                dbConn.Open()

                If dbConn.State = ConnectionState.Open Then
                    Return True
                Else
                    Return False
                End If
            End If
        Catch e As Exception
            MsgBox("Error:" & e.Message)
            Return False
        End Try
    End Function

    Public Sub close()
        If Not dbConn Is Nothing Then
            dbConn.Close()
        End If
    End Sub

    Public Function getData(ByVal sqlStr As String) As DataSet
        Dim myDA As OleDb.OleDbDataAdapter, myDataSet As DataSet
        'run select query and return the result to a dataset
        'this will transfer all you selected data from sql server to client side

        'return the selected dataset if success, return an empty dataset if failed

        myDataSet = New DataSet()
        Try
            myDA = New OleDb.OleDbDataAdapter(sqlStr, dbConn)

            myDA.Fill(myDataSet)

            Return myDataSet
        Catch e As Exception
            MsgBox("Error:" & e.Message)
            myDataSet.Clear()
            Return myDataSet
        End Try
    End Function

    Public Function execSql(ByVal sqlStr As String) As Boolean
        Dim myCom As OleDb.OleDbCommand
        'run insert, update, delete with no return queries
        Try
            myCom = New OleDb.OleDbCommand(sqlStr, dbConn)
            myCom.ExecuteNonQuery()

            Return True
        Catch e As Exception
            MsgBox("Error:" & e.Message)
            Return False
        End Try

    End Function

    Public Function execStoredProcedure(ByVal proName As String) As Boolean
        Return True
    End Function
End Class