主要Access数据库连接代码:
代码语言：javascript复制Imports System.Data.OleDb
''' <summary>
'''
''' 数据库连接类
'''
''' Author:OuHuanHua
''' Date:2023/01/29
'''
''' </summary>
Public Class Class_AccessDb
''' <summary>
''' 数据库连接对象
''' </summary>
Private ReadOnly DbConnection As OleDbConnection
''' <summary>
''' 日志事件
''' </summary>
''' <param name="msg"></param>
Public Event CmdLog(msg As String)
''' <summary>
''' 初始化类
''' </summary>
''' <param name="DbPath"></param>
Public Sub New(DbPath As String)
Dim database As String = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & DbPath
DbConnection = New OleDbConnection(database)
Try
''检测数据库状态
If DbConnection.State.Equals(ConnectionState.Closed) Then
DbConnection.Open()
End If
Catch ex As Exception
RaiseEvent CmdLog("数据库初始化异常:" & ex.Message)
End Try
End Sub
''' <summary>
''' 查询数据并返回DataTable对象
''' </summary>
''' <param name="SqlStr">sql语句</param>
''' <returns></returns>
Public Function ExecuteDataTable(SqlStr As String) As DataTable
Try
''-------再次判断数据库连接状态
If DbConnection.State.Equals(ConnectionState.Closed) Then
DbConnection.Open()
End If
''--------查询数据
Using CmdObject As New OleDbCommand With {
.CommandText = SqlStr,
.CommandType = CommandType.Text,
.Connection = DbConnection,
.CommandTimeout = 0
}
Using AdpObject As New OleDbDataAdapter With {.SelectCommand = CmdObject}
Using Dt As New DataTable()
AdpObject.Fill(Dt)
CmdObject.Parameters.Clear()
Return Dt
End Using
End Using
End Using
Catch ex As OleDbException
RaiseEvent CmdLog("查询数据异常:" & ex.Message)
Return Nothing
End Try
End Function
''' <summary>
''' 执行SQL指令语句
''' </summary>
''' <param name="SqlStr">sql语句</param>
''' <returns></returns>
Public Function ExecuteNonQuery(SqlStr As String) As Integer
Try
''-------再次判断数据库连接状态
If DbConnection.State.Equals(ConnectionState.Closed) Then
DbConnection.Open()
End If
''--------执行指令
Using CmdObject As New OleDbCommand With {
.CommandText = SqlStr,
.CommandType = CommandType.Text,
.Connection = DbConnection,
.CommandTimeout = 0
}
Dim rows As Integer = CmdObject.ExecuteNonQuery()
CmdObject.Parameters.Clear()
Return rows
End Using
Catch ex As OleDbException
RaiseEvent CmdLog("执行指令异常:" & ex.Message)
Return 0
End Try
End Function
''' <summary>
''' 获取当前连接的mdb中的所有表名
''' </summary>
''' <returns></returns>
Public Function GetTableNames() As List(Of String)
Try
''-------再次判断数据库连接状态
If DbConnection.State.Equals(ConnectionState.Closed) Then
DbConnection.Open()
End If
''-------获取表名
Dim dt As DataTable = DbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim tableNameList As New List(Of String)
For i As Integer = 0 To dt.Rows.Count - 1
Dim tableName As String = dt.Rows(i)("TABLE_NAME").ToString()
tableNameList.Add(tableName)
Next
Return tableNameList
Catch ex As Exception
RaiseEvent CmdLog("查询所有表名异常:" & ex.Message)
Return Nothing
End Try
End Function
End Class
