Odbc to oleDb necessary changes in MS Access

Question

I hava a class in .net to connect Access using Odbc but I want to make an upgrade changing to OleDb

The Odbc code is:

Imports System.Data.Odbc
Imports System

Public Class DatabaseFunctions
    Shared con As OdbcConnection

    Const conString = "Dsn=name;uid=sa;pwd=password;"

    Public Shared Function GetDataReader(ByVal SQL As String) As OdbcDataReader
        Dim dr As OdbcDataReader
        Try
            con = New OdbcConnection
            con.ConnectionString = conString
            con.Open()
            Dim cmd As New OdbcCommand(SQL, con)
            dr = cmd.ExecuteReader
            cmd.Connection.Close()
            cmd.Connection.Dispose()
            cmd.Connection = Nothing
            cmd.Parameters.Clear()
            cmd.Dispose()
            cmd = Nothing
            con.Close()
            con.Dispose()
            Return dr
        Catch ex As Exception
            MessageBox.Show("Error:" & ex.Message, "TITLE", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If (con.State = System.Data.ConnectionState.Open) Then
                con.Close()
                con.Dispose()
            End If
        End Try
        dr = Nothing
        Return dr
    End Function

    Public Shared Function GetDataReaderVal(ByVal SQL As String) As String
        Dim result As String
        Try
            con = New OdbcConnection
            con.ConnectionString = conString
            con.Open()
            Dim cmd As New OdbcCommand(SQL, con)
            Dim dr As OdbcDataReader
            result = ""
            dr = cmd.ExecuteReader
            dr.Read()
            resultado = dr.GetValue(0).ToString
            cmd.Connection.Close()
            cmd.Connection.Dispose()
            cmd.Connection = Nothing
            cmd.Parameters.Clear()
            cmd.Dispose()
            cmd = Nothing
            con.Close()
            con.Dispose()
            Return resultado
        Catch ex As Exception
            MessageBox.Show("Error GETTING DATA:" & ex.Message, "TITLE", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If (con.State = System.Data.ConnectionState.Open) Then
                con.Close()
                con.Dispose()
            End If
        End Try
        resultado = "0"
        Return resultado
    End Function


    Public Shared Function GetDataTable(ByVal SQL As String) As DataTable
        Dim table As New DataTable
        Try
            con = New OdbcConnection
            con.ConnectionString = conString
            con.Open()
            Dim cmd As New OdbcCommand(SQL, con)

            Dim da As New OdbcDataAdapter(cmd)
            da.Fill(table)
            cmd.Connection.Close()
            cmd.Connection.Dispose()
            cmd.Connection = Nothing
            cmd.Parameters.Clear()
            cmd.Dispose()
            cmd = Nothing
            con.Close()
            con.Dispose()
            Return table
        Catch ex As Exception
            MessageBox.Show("Error GETTING TABLE:" & ex.Message, "TITLE", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If (con.State = System.Data.ConnectionState.Open) Then
                con.Close()
                con.Dispose()
            End If
        End Try
        Return table
    End Function



    Public Shared Sub ExecuteQuery(ByVal SQL As String)
        Try
            con = New OdbcConnection
            con.ConnectionString = conString
            con.Open()
            Dim cmd As New OdbcCommand(SQL, con)
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()
            cmd.Connection.Dispose()
            cmd.Connection = Nothing
            cmd.Parameters.Clear()
            cmd.Dispose()
            cmd = Nothing
            con.Close()
            con.Dispose()
        Catch ex As Exception
            MessageBox.Show("Error EXE QUERY:" & ex.Message, "TITLE", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If (con.State = System.Data.ConnectionState.Open) Then
                con.Close()
                con.Dispose()
            End If
        End Try
    End Sub
End Class

I am doing:

Imports System.Data.OleDb

Public Class DatabaseFunctions

    Dim Provider As String
    Public OleCn As New OleDbConnection()
    Public OleDa As New OleDbDataAdapter()


    Public Shared Function OledbConnectionString() As String
        Provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath & "/dbFile.Accdb;Jet OLEDB:Database Password=password;"
        Return Provider
    End Function


    Public Shared Sub start()
        If OleCn.State <> ConnectionState.Open Then
            OleCn.ConnectionString = OledbConnectionString()
            OleCn.Open()
        End If
    End Sub

    Public Sub finish()
        OleCn.Close()
    End Sub

  Public Shared Sub ExecuteQuery(ByVal SQL As String)
        Dim OleDr As OleDbDataReader
        OleDa.SelectCommand = New OleDbCommand()
        OleDa.SelectCommand.CommandText = SQL 
        OleDa.SelectCommand.Connection = OleCn
        OleDr = OleDa.SelectCommand.ExecuteReader()     
        OleDr.Close()
  End Sub

End Class

How can I return a table, a single value and the reader as in odbc, I am getting confused...

Answer 1

This is good code, take a look at it:

Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.IO

Public Class DbConn
    Private DataCon As DbConnection
    Private DBConnectionOpen As Boolean = False

    ''' <summary>
    ''' Submits a SQL query to an Access database
    ''' </summary>
    ''' <param name="sql">The SQL query</param>
    ''' <returns>A datatable (result set) containing the data requested by the query; returns Nothing if there is a SQL error.</returns>
    ''' <remarks>Use for SELECT queries; use ExecuteSQL for action queries.
    ''' This function does not permanently change whether the connection is open or not.
    ''' If the connection is already open, it gets the data and leaves it open.
    ''' If the connection is closed, it opens it, gets the data, and closes it again.</remarks>
    Public Function GetDataTable(ByVal sql As String) As DataTable
        Dim rsData As New DataTable()
        ' 
        Dim ConnectionWasAlreadyOpen As Boolean = DBConnectionOpen
        If Not ConnectionWasAlreadyOpen Then OpenDatabaseConnection()
        If DBConnectionOpen Then
            Dim cmd As DbCommand = DataCon.CreateCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            Try
                Dim rdr As DbDataReader = cmd.ExecuteReader()
                rsData.Load(rdr, LoadOption.Upsert)
                rdr.Close()
                rdr = Nothing
            Catch ex As Exception
                Throw New System.Exception(ex.Message, ex)
                GetDataTable = Nothing
                Exit Function
            End Try
            GetDataTable = rsData
        Else
            GetDataTable = Nothing
        End If
        If Not ConnectionWasAlreadyOpen Then CloseDatabaseConnection()
    End Function

    ''' <summary>
    ''' Executes a SQL action query (INSERT, UPDATE, DELETE, CREATE, etc.)
    ''' </summary>
    ''' <param name="sql">The text of the SQL action query</param>
    ''' <remarks>Use for action queries; use GetDataTable for SELECT queries
    ''' This sub does not permanently change whether the connection is open or not.
    ''' If the connection is already open, it gets the data and leaves it open.
    ''' If the connection is closed, it opens it, gets the data, and closes it again.</remarks>
    Public Sub ExecuteSQL(ByVal sql As String)
        Dim ConnectionWasAlreadyOpen As Boolean = DBConnectionOpen
        If Not ConnectionWasAlreadyOpen Then OpenDatabaseConnection()
        If DBConnectionOpen Then
            Dim cmd As DbCommand = DataCon.CreateCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
        End If
        If Not ConnectionWasAlreadyOpen Then CloseDatabaseConnection()
    End Sub


    ''' <summary>
    ''' Creates a new connection to an Access database
    ''' </summary>
    ''' <param name="FileName">The full path of the Access file</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal FileName As String)
        'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;
        Dim fileData As FileInfo = My.Computer.FileSystem.GetFileInfo(FileName)
        DataCon = New OleDb.OleDbConnection
        Dim csb As OleDb.OleDbConnectionStringBuilder = New OleDb.OleDbConnectionStringBuilder
        csb.ConnectionString = "Data Source=" & FileName
        Select Case fileData.Extension.ToLower
            Case ".mdb" : csb.Add("Provider", "Microsoft.Jet.Oledb.4.0")
            Case ".accdb" : csb.Add("Provider", "Microsoft.ACE.OLEDB.12.0")
        End Select
        DataCon.ConnectionString = csb.ConnectionString
        Try
            DataCon.Open()
            DataCon.Close()
        Catch ex As Exception
            Throw New System.Exception("Unable to connect to database.", ex.InnerException)
        End Try
    End Sub

    ''' <summary>
    ''' Connects to a SQL Server database
    ''' </summary>
    ''' <param name="ServerName">SQL Server server</param>
    ''' <param name="DatabaseName">Default database</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal ServerName As String, ByVal DatabaseName As String)
        DataCon = New SqlConnection()
        Dim csb As SqlConnectionStringBuilder = New SqlConnectionStringBuilder
        csb.DataSource = ServerName
        csb.InitialCatalog = DatabaseName
        csb.IntegratedSecurity = True
        csb.MultipleActiveResultSets = True
        DataCon.ConnectionString = csb.ConnectionString
        Try
            DataCon.Open()
            DataCon.Close()
        Catch ex As Exception
            Throw New System.Exception("Unable to connect to database.", ex.InnerException)
        End Try
    End Sub

    ''' <summary>
    ''' Makes certain that the database connection is closed when the object is destroyed
    ''' </summary>
    ''' <remarks></remarks>
    Protected Overrides Sub finalize()
        Try
            DataCon.Close()
        Catch ex As Exception

        End Try
    End Sub

    ''' <summary>
    ''' Opens a database connection to an access file
    ''' </summary>
    ''' <remarks>Since the procedures in this module share the "DataCon" DbConnection, it is impossible to open multiple connections using these procedures.</remarks>
    Public Sub OpenDatabaseConnection()
        Try
            DataCon.Open()
        Catch ex As Exception
            Throw New System.Exception("Error opening data connection.", ex.InnerException)
            DBConnectionOpen = False
            Exit Sub
        End Try
        DBConnectionOpen = True
    End Sub

    ''' <summary>
    ''' Closes the DataCon DbConnection
    ''' </summary>
    ''' <remarks>Note that the GetDataTable and ExecuteSQL procedures leave (or return) the connection to its previous open/close state. To keep the connection open while running several queries, call OpenDatabaseConnection before the queries and CloseDatabaseConnection after. When running one query at a time, there is no need to explicitly call either function, because GetDataTable and ExecuteSQL will open and close the database if it was closed to begin with.</remarks>
    Public Sub CloseDatabaseConnection()
        DataCon.Close()
        DBConnectionOpen = False
    End Sub
End Class