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