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

 

posted @ 2013-04-10 17:21  NexArchi  阅读(889)  评论(0编辑  收藏  举报