通过ADO.NET连接Access库要引入System.Data.OleDb 命名空间:
Imports
System.Data.OleDb
OleDbConnection类类似ADO的Connection,用于实现连接,程序将OleDbConnection以及若干类封装进一个类DataControl里面,通过该类的实现,可以实现各种数据库的连接、SQL的执行等操作,详细代码如下:
Imports
System.Data
Imports System.Data.OleDb
Public Class Form1 Class Form1
Private Sub Form1_Load()Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
Dim aa As New DataSet
'Dim bb As OleDbDataReader
Dim conn As New DataControl
conn.ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & " est.mdb"
conn.Open()
aa = conn.Execute("select * from iuser", DataControl.ExecuteType.DataSet)
'bb = conn.Execute("select * from iuser", DataControl.ExecuteType.DataReader)
DataGridView1.DataSource = aa.Tables(0)
'DataGridView1.DataSource = bb
'DataGridView1.Columns.Add("haha", "haha")
'While bb.Read
'DataGridView1.Rows.Add(bb(0).ToString)
'End While
conn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
Public Class DataControl Class DataControl
Private Conn As OleDbConnection
Private iConnstr As String
Private Cmd As OleDbCommand
Private DR As OleDbDataReader
Private DA As OleDbDataAdapter
Private DS As DataSet
Public Enum ExecuteTypeEnum ExecuteType
DataReader = 0
DataSet = 1
End Enum
Public Sub New()Sub New()
iConnstr = ""
Conn = New OleDbConnection()
End Sub
Public Sub Open()Sub Open()
Open(iConnstr)
End Sub
Public Sub Open()Sub Open(ByVal Connstr As String)
iConnstr = Connstr
If iConnstr.Trim = "" Then
Throw New Exception("连接字符串为空!")
End If
Try
Conn = New OleDbConnection(iConnstr)
Conn.Open()
Catch ex As Exception
Throw ex
End Try
End Sub
Public Function Execute()Function Execute(ByVal sql As String) As OleDbDataReader
Return Execute(sql, ExecuteType.DataReader)
End Function
Public Function Execute()Function Execute(ByVal sql As String, ByVal type As ExecuteType)
If Not Conn.State = ConnectionState.Open Then
Throw New Exception("未建立连接!")
Return Nothing
End If
Try
If type = ExecuteType.DataReader Then
Cmd = New OleDbCommand(sql, Conn)
'Dim aa As OleDbDataReader
'aa = Cmd.ExecuteReader
Return Cmd.ExecuteReader
ElseIf type = ExecuteType.DataSet Then
DA = New OleDbDataAdapter(sql, Conn)
DS = New DataSet
DA.Fill(DS)
Return DS
End If
Catch ex As Exception
Throw ex
Return Nothing
End Try
End Function
Public Property ConnString()Property ConnString() As String
Get
Return iConnstr
End Get
Set(ByVal value As String)
iConnstr = value
End Set
End Property
Public ReadOnly Property ConnState()Property ConnState()
Get
Return Conn.State.ToString
End Get
End Property
Public Sub Close()Sub Close()
Conn.Close()
End Sub
End Class
Imports System.Data.OleDb
Public Class Form1 Class Form1
Private Sub Form1_Load()Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
Dim aa As New DataSet
'Dim bb As OleDbDataReader
Dim conn As New DataControl
conn.ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & " est.mdb"
conn.Open()
aa = conn.Execute("select * from iuser", DataControl.ExecuteType.DataSet)
'bb = conn.Execute("select * from iuser", DataControl.ExecuteType.DataReader)
DataGridView1.DataSource = aa.Tables(0)
'DataGridView1.DataSource = bb
'DataGridView1.Columns.Add("haha", "haha")
'While bb.Read
'DataGridView1.Rows.Add(bb(0).ToString)
'End While
conn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
Public Class DataControl Class DataControl
Private Conn As OleDbConnection
Private iConnstr As String
Private Cmd As OleDbCommand
Private DR As OleDbDataReader
Private DA As OleDbDataAdapter
Private DS As DataSet
Public Enum ExecuteTypeEnum ExecuteType
DataReader = 0
DataSet = 1
End Enum
Public Sub New()Sub New()
iConnstr = ""
Conn = New OleDbConnection()
End Sub
Public Sub Open()Sub Open()
Open(iConnstr)
End Sub
Public Sub Open()Sub Open(ByVal Connstr As String)
iConnstr = Connstr
If iConnstr.Trim = "" Then
Throw New Exception("连接字符串为空!")
End If
Try
Conn = New OleDbConnection(iConnstr)
Conn.Open()
Catch ex As Exception
Throw ex
End Try
End Sub
Public Function Execute()Function Execute(ByVal sql As String) As OleDbDataReader
Return Execute(sql, ExecuteType.DataReader)
End Function
Public Function Execute()Function Execute(ByVal sql As String, ByVal type As ExecuteType)
If Not Conn.State = ConnectionState.Open Then
Throw New Exception("未建立连接!")
Return Nothing
End If
Try
If type = ExecuteType.DataReader Then
Cmd = New OleDbCommand(sql, Conn)
'Dim aa As OleDbDataReader
'aa = Cmd.ExecuteReader
Return Cmd.ExecuteReader
ElseIf type = ExecuteType.DataSet Then
DA = New OleDbDataAdapter(sql, Conn)
DS = New DataSet
DA.Fill(DS)
Return DS
End If
Catch ex As Exception
Throw ex
Return Nothing
End Try
End Function
Public Property ConnString()Property ConnString() As String
Get
Return iConnstr
End Get
Set(ByVal value As String)
iConnstr = value
End Set
End Property
Public ReadOnly Property ConnState()Property ConnState()
Get
Return Conn.State.ToString
End Get
End Property
Public Sub Close()Sub Close()
Conn.Close()
End Sub
End Class