VB.NET ACCESS 数据库操作
1 Imports ADODB 2 Imports System.Text 3 Imports System.Data.OleDb 4 Imports System.IO 5 6 Public Class AccessorHelper 7 8 Private _connectionString As String = "provider=microsoft.ace.oledb.12.0;data source= " & Application.StartupPath & "\DispatchCar.db" 9 10 Public Function ExecuteNonQuery(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As OleDbParameter = Nothing) As Integer 11 Dim connection As OleDbConnection = Nothing 12 Dim transaction As OleDbTransaction = Nothing 13 Dim command As OleDbCommand = Nothing 14 Dim res As Integer = -1 15 Try 16 connection = New OleDbConnection(_connectionString) 17 command = New OleDbCommand(cmd, connection) 18 command.CommandType = cmdType 19 Me.AssignParameters(command, parameters) 20 connection.Open() 21 transaction = connection.BeginTransaction() 22 command.Transaction = transaction 23 res = command.ExecuteNonQuery() 24 transaction.Commit() 25 Catch ex As Exception 26 If Not (transaction Is Nothing) Then 27 transaction.Rollback() 28 End If 29 Throw New Exception(ex.Message, ex.InnerException) 30 Finally 31 If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close() 32 If Not (command Is Nothing) Then command.Dispose() 33 If Not (transaction Is Nothing) Then transaction.Dispose() 34 End Try 35 Return res 36 End Function 37 38 Public Function ExecuteReader(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As OleDbParameter = Nothing) As DataSet 39 Dim connection As OleDbConnection = Nothing 40 Dim command As OleDbCommand = Nothing 41 Dim dataSet As DataSet = New DataSet() 42 Try 43 connection = New OleDbConnection(_connectionString) 44 command = New OleDbCommand(cmd, connection) 45 command.CommandType = cmdType 46 Me.AssignParameters(command, parameters) 47 connection.Open() 48 Dim dataAdapter As New OleDbDataAdapter(command) 49 dataAdapter.Fill(dataSet) 50 dataAdapter.Dispose() 51 connection.Close() 52 Catch ex As Exception 53 Throw New Exception(ex.Message, ex.InnerException) 54 End Try 55 Return dataSet 56 End Function 57 58 #Region " Private Methods " 59 60 Private Sub AssignParameters(ByVal cmd As OleDbCommand, ByVal cmdParameters() As OleDbParameter) 61 If (cmdParameters Is Nothing) Then Exit Sub 62 For Each p As OleDbParameter In cmdParameters 63 cmd.Parameters.Add(p) 64 Next 65 End Sub 66 67 Private Sub AssignParameters(ByVal cmd As OleDbCommand, ByVal parameterValues() As Object) 68 If Not (cmd.Parameters.Count - 1 = parameterValues.Length) Then Throw New ApplicationException("Stored procedure's parameters and parameter values does not match.") 69 Dim i As Integer 70 For Each param As OleDbParameter In cmd.Parameters 71 If Not (param.Direction = ParameterDirection.Output) AndAlso Not (param.Direction = ParameterDirection.ReturnValue) Then 72 param.Value = parameterValues(i) 73 i += 1 74 End If 75 Next 76 End Sub 77 78 #End Region 79 80 End Class