Imports System.Data.OleDb

Public Class DBConnection
  Private OledbConn As OleDbConnection
  Private OleDbComm As OleDbCommand
  Private OleDbString As String
  Private SqlString As String

  Public Sub New(ByVal strDatabase As String, ByVal strWorkgroup As String)
    'OleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Jet OLEDB:Database Password='g01ftr@(k3r'"
    'OleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Jet OLEDB:System Database=" & strWorkgroup & "; User ID=nick; Password=Pekay;"
    OleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.AppDomain.CurrentDomain.BaseDirectory & "\AscotData.mdb"
  End Sub

  Public Sub New()
    'OleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Jet OLEDB:Database Password='g01ftr@(k3r'"
    'OleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Jet OLEDB:System Database=" & strWorkgroup & "; User ID=nick; Password=Pekay;"
    OleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.AppDomain.CurrentDomain.BaseDirectory & "\AscotData.mdb;Jet OLEDB:Database Password=Bl@dd3r"
    'OleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & strDatabase & ";User Id=" & strUserName & ";Password=" & strPassword
  End Sub

  Public Sub Connect()
    ConnectOleDb()
  End Sub

  Public Function connState() As ConnectionState
    Return OledbConn.State
  End Function

  Public Sub Close()
    Try
      OledbConn.Close()
    Catch ex As Exception
    End Try
  End Sub

  Private Sub ConnectOleDb()
    Try
      OledbConn = New OleDbConnection
      OledbConn.ConnectionString = OleDbString
      OledbConn.Open()
    Catch ex As Exception
      MsgBox(ex.Message)
    End Try
  End Sub

  Public Function Query(ByVal sql As String) As DataTable
    Return QueryOleDB(sql)
  End Function

  Public Function query_return_ds(ByVal strName As String, ByVal sql As String) As DataSet
    Return QueryOleDB_DS(strName, sql)
  End Function

  Private Function QueryOleDB(ByVal sql As String) As DataTable
    Try
      Dim DataAdapter As New OleDbDataAdapter
      Dim MyDataSet As New DataSet
      DataAdapter.SelectCommand = New OleDbCommand(sql, OledbConn)
      Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)
      DataAdapter.Fill(MyDataSet)
      Dim rt As DataTable
      rt = MyDataSet.Tables(0)
      Return rt
    Catch ex As Exception
      Return New DataTable
    End Try
  End Function

  Private Function QueryOleDB_DS(ByVal strName As String, ByVal sql As String) As DataSet
    Try
      Dim DataAdapter As New OleDbDataAdapter
      Dim MyDataSet As New DataSet(strName)
      DataAdapter.SelectCommand = New OleDbCommand(sql, OledbConn)
      Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)
      DataAdapter.Fill(MyDataSet)
      MyDataSet.Tables(0).TableName = strName
      Return MyDataSet
    Catch ex As Exception
      Return New DataSet(strName)
    End Try
  End Function

  Public Function Execute(ByVal sql As String) As Integer
    Return UpdateOleDB(sql)
  End Function

  Public Function Execute(ByVal dt As DataTable) As Integer
    Return UpdateOleDB(dt)
  End Function

  Public Function Execute(ByVal dt As DataTable, ByVal sql As String) As Integer
    Return UpdateOleDB(dt, sql)
  End Function

  Private Function UpdateOleDB(ByVal sql As String) As Integer
    Try
      OleDbComm = OledbConn.CreateCommand()
      OleDbComm.CommandText = sql
      Return OleDbComm.ExecuteNonQuery
    Catch ex As Exception
      MsgBox(ex.Message)
    End Try
  End Function

  Private Function UpdateOleDb(ByVal dt As DataTable) As Integer
    Try
      Dim DataAdapter As New OleDbDataAdapter
      Dim MyDataSet As New DataSet
      DataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM " & dt.TableName, OledbConn)
      Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)
      DataAdapter.Update(dt)
    Catch ex As Exception
      System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try
  End Function

  Public Function UpdateOleDb(ByVal dt As DataTable, ByVal sql As String) As Boolean
    Try
      Dim DataAdapter As New OleDbDataAdapter
      Dim MyDataSet As New DataSet
      DataAdapter.SelectCommand = New OleDbCommand(sql, OledbConn)
      Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)
      DataAdapter.Update(dt)
      Return True
    Catch ex As Exception
      System.Windows.Forms.MessageBox.Show(ex.Message & " " & sql)
      Return False
    End Try
  End Function

  Public Function getSchema(ByVal sql As String) As DataTable
    Return getSchemaOleDb(sql)
  End Function

  Private Function getSchemaOleDb(ByVal sql As String) As DataTable
    Dim dt As DataTable
    Dim rdr As OleDbDataReader
    Dim cmd As New OleDbCommand(sql, OledbConn)
    Try
      rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo)
      dt = rdr.GetSchemaTable
      rdr.Close()
    Catch ex As Exception
      MsgBox(ex.Message & Chr(13) & Chr(10) & sql)
    End Try
    Return dt
  End Function

  Public Function getLastID() As Long
    Dim dt As DataTable
    dt = QueryOleDB("SELECT @@IDENTITY")
    If dt.Rows.Count > 0 Then
      Return Nz(dt.Rows(0).Item(0), 0)
    Else
      Return 0
    End If
  End Function
End Class