Basic Data access layer for MSAccess

In our app, if database is MSAccess then we need to connect database and execute query. Here is the class which is used to connect database and execute query.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.OleDb
Imports System.Data

Public Class DAL
'//For execute select query
Public Shared Function Exec(ByVal SQL As String) As DataTable
Dim nowConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "DataBase\\MyDatabase.mdb;"

Dim nowCon As System.Data.OleDb.OleDbConnection
nowCon = New System.Data.OleDb.OleDbConnection(nowConString)
Dim nowDataAdapter As System.Data.OleDb.OleDbDataAdapter
nowDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQL, nowCon)
Dim nowDataTable As DataTable = New DataTable()
nowDataAdapter.Fill(nowDataTable)
nowCon.Close()
nowCon.Dispose()
nowDataAdapter.Dispose()
Return nowDataTable
End Function

'for exectuing insert,update,delete query
Public Shared Function ExecN(ByVal SQL As String) As Integer

Dim ret As Integer = -1
Dim nowConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "DataBase\\ MyDatabase.mdb"
Dim nowCon As System.Data.OleDb.OleDbConnection
nowCon = New System.Data.OleDb.OleDbConnection(nowConString)
Dim cmd As New OleDb.OleDbCommand(SQL, nowCon)
nowCon.Open()
ret = cmd.ExecuteNonQuery()
nowCon.Close()
nowCon.Dispose()
cmd.Dispose()
Return ret
End Function
End Class

No comments:

Post a Comment