Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb

Public Class DBase
  Private conn As OleDbConnection
  '-----    
  Public Sub New(ByVal connect As String)
    conn = New OleDbConnection(connect)
  End Sub
  '-----
  Public Sub New(ByVal dbName As String, ByVal connectionType As String)
    Dim connectionString As String
    connectionType = connectionType.ToLower
    Select Case connectionType
      Case "access"
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbName
      Case "sqlserver"
        connectionString = "Persist Security Info = False;" & _
        "Initial Catalog =" & dbName & ";" & _
        "Data Source = myDataServer;User ID = myName;" & _
        "password="
      Case Else
        connectionString = dbName
    End Select
    conn = New OleDbConnection(connectionString)
  End Sub
  '-----
  Public Sub New(ByVal dbname As String, ByVal userid As String, _
      ByVal servername As String, ByVal password As String, _
      ByVal connectionType As String)
    Dim connection As String
    connectionType = connectionType.ToLower
    Select Case connectionType
      Case "sqlserver"
        connection = "Persist Security Info = False;" & _
        "Initial Catalog =" & dbname & ";" & _
        "Data Source =" & servername & ";" & _
        "User ID =" & userid & ";" & _
        "password=" & password
      Case Else
        connection = dbname
    End Select
    conn = New OleDbConnection(connection)
  End Sub
  '-------
  Private Sub openConnection()
    If conn.State = ConnectionState.Closed Then
      conn.Open()
    End If
  End Sub
  '-------
  Private Sub closeConnection()
    If conn.State = ConnectionState.Open Then
      conn.Close()
    End If
  End Sub
  '-------
  Public Function openTable(ByVal tbName As String) As DataTable
    Dim adapter As New OleDbDataAdapter()
    Dim query As String
    query = "Select * From " & tbName
    adapter.SelectCommand = New OleDbCommand(query, conn)
    'create the desintination dataset
    Dim dset As New DataSet()
    'open the connection and fill the table
    Try
      openConnection()
      adapter.Fill(dset)
    Catch e As Exception
      MsgBox(e.Message)
    End Try
    closeConnection()
    'get the table from the dataset
    Dim dtable As DataTable = dset.Tables(0)
    Return dtable   'return the table we read
  End Function

  '-------    
  Public Function openQuery(ByVal query As String) As DataTable
    Dim dsCmd As New OleDbDataAdapter()
    Try
      dsCmd.SelectCommand = New OleDbCommand(query, conn)
      Dim dset As New DataSet()
      openConnection()
      'fill the dataset
      dsCmd.Fill(dset, "mine")
      'get the table
      Dim dtable As DataTable = dset.Tables(0)
      closeConnection()
      Return dtable   'and return it
    Catch e As Exception
      MessageBox.Show(e.Message)
    End Try
  End Function
  '-----
  Public Function getConnection() As OleDbConnection
    Return conn
  End Function
End Class
