1、集中整理ArcEngin在VB环境下开发时用到的资料集中整理VB.NET操作ACCESS一,功能:1,对access数据库显示绑定(不同方式的显示)、添加、删除、更新、以及查询(不同方式的查询);2,建立新的access mdb文件,新建表,添加(删除)字段,及定义字段数据类型二,源代码如下:Imports System.Data.OleDbImports ADODBPublic Class sqlAccess#Region Private Private ConnectionStr As String = Private Conn As OleDb.OleDbConnection#End R
2、egion#Region sub new Sub New(ByVal AccessFileName As String) ConnectionStr = Provider = Microsoft.Jet.OLEDB.4.0;Data Source = & AccessFileName Conn = New OleDb.OleDbConnection(ConnectionStr) End Sub Sub New() End Sub#End Region#Region 显示数据 Public Sub ShowRecords(ByVal DG As DataGridView, ByVal Acces
3、TableName As String) Dim sqlString As String = SELECT * FROM & AccesTableName DG.ReadOnly = True Dim myDataSet As New DataSet() Using Conn Conn.Open() Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter(sqlString, Conn) myCommand.Fill(myDataSet, AccesTableName) DG.DataSource = myDataSet.Tables(
4、0) Conn.Close() End Using End Sub Public Sub ShowRecords(ByVal DG As DataGridView, ByVal AccesTableName As String, ByVal cols As Collection) Dim sqlString As String = SELECT Dim i As Integer For i = 1 To cols.Count - 1 sqlString = sqlString & cols.Item(i) & , Next sqlString = sqlString & cols.Item(c
5、ols.Count) sqlString = sqlString & from & AccesTableName DG.ReadOnly = True Dim myConn As New OleDb.OleDbConnection(ConnectionStr) Dim myDataSet As New DataSet() Using myConn myConn.Open() Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter(sqlString, myConn) myCommand.Fill(myDataSet, AccesTabl
6、eName) DG.DataSource = myDataSet.Tables(0) myConn.Close() End Using End Sub Public Function ShowRecords(ByVal AccessTableName As String) As DataTable Dim sqlString As String = SELECT * FROM & AccessTableName Dim myDataSet As New DataSet Using Conn Conn.Open() Dim myCommand As OleDbDataAdapter = New
7、OleDbDataAdapter(sqlString, Conn) myCommand.Fill(myDataSet, AccessTableName) Conn.Close() Return myDataSet.Tables(0) End Using End Function#End Region#Region 添加数据 Public Sub AddRecord(ByVal DG As DataGridView, ByVal AccesTableName As String, _ ByVal Cols As Collection, ByVal Vals As Collection) Dim
8、sqlString As String = insert into & AccesTableName & ( Dim i As Integer For i = 1 To Cols.Count - 1 sqlString = sqlString & Cols.Item(i).ToString & , Next sqlString = sqlString & Cols.Item(Cols.Count).ToString & ) values ( For i = 1 To Vals.Count - 1 sqlString = sqlString & ?, Next sqlString = sqlSt
9、ring & ?) 时间表达式2009-12-2 货币相当字符串如: 333 添加后显示 ¥333.00 ole对象,相当于二进制的数组 Dim myConn As New OleDb.OleDbConnection(ConnectionStr) Using myConn myConn.Open() Dim myCommand As OleDbCommand = New OleDbCommand(sqlString, myConn) For i = 1 To Vals.Count myCommand.Parameters.Add(New OleDb.OleDbParameter).Value
10、= Vals(i) Next myCommand.ExecuteNonQuery() myConn.Close() End Using 在dg中更新 ShowRecords(DG, AccesTableName) End Sub Public Sub AddRecord(ByVal AccesTableName As String, _ByVal Cols As Collection, ByVal Vals As Collection) Dim sqlString As String = insert into & AccesTableName & ( Dim i As Integer For
11、 i = 1 To Cols.Count - 1 sqlString = sqlString & Cols.Item(i).ToString & , Next sqlString = sqlString & Cols.Item(Cols.Count).ToString & ) values ( For i = 1 To Vals.Count - 1 sqlString = sqlString & ?, Next sqlString = sqlString & ?) 时间表达式2009-12-2 货币相当字符串如: 333 添加后显示 ¥333.00 ole对象,相当于二进制的数组 Using
12、Conn Conn.Open() Dim myCommand As OleDbCommand = New OleDbCommand(sqlString, Conn) For i = 1 To Vals.Count myCommand.Parameters.Add(New OleDb.OleDbParameter).Value = Vals(i) Next myCommand.ExecuteNonQuery() Conn.Close() End Using End Sub#End Region#Region 数据更新 Public Sub UpdateRecord(ByVal DG As Dat
13、aGridView, ByVal AccesTableName As String, _ByVal Cols As Collection, ByVal Vals As Collection, ByVal IndexColumnName As String, ByVal IndexValue As Object, ByVal isShowSqlString As Boolean) Dim sqlString As String = update & AccesTableName & set Dim i As Integer For i = 1 To Cols.Count - 1 If Vals.
14、Item(i).GetType.Name = String Then sqlString = sqlString & Cols.Item(i).ToString & = & Vals.Item(i) & , ElseIf Vals.Item(i).GetType.Name.Contains(Date) Then sqlString = sqlString & Cols.Item(i).ToString & = & Vals.Item(i) & , Else sqlString = sqlString & Cols.Item(i).ToString & = & Vals.Item(i) & ,
15、End If Next If Vals.Item(i).GetType.Name = String Then sqlString = sqlString & Cols.Item(Cols.Count).ToString & = & Vals.Item(Cols.Count) & ElseIf Vals.Item(i).GetType.Name.Contains(Date) Then sqlString = sqlString & Cols.Item(Cols.Count).ToString & = & Vals.Item(Cols.Count) & Else sqlString = sqlSt
16、ring & Cols.Item(Cols.Count).ToString & = & Vals.Item(Cols.Count) End If If IndexValue.GetType.Name = String Then sqlString = sqlString & where & IndexColumnName & = & IndexValue & Else sqlString = sqlString & where & IndexColumnName & = & IndexValue End If 目前只能实现更加数字,文本,是否来选取,时间等还没有实现,使用的时候要注意 If i
17、sShowSqlString Then MsgBox(sqlString) Using Conn Conn.Open() Dim cmd As OleDbCommand = New OleDbCommand(sqlString, Conn) cmd.ExecuteNonQuery() Conn.Close() End Using 在dg中更新 Dim dt As DataTable = DG.DataSource For Each r As DataRow In dt.Rows If r(IndexColumnName) = IndexValue Then For i = 1 To Cols.
18、Count r.Item(Cols(i) = Vals(i) Next End If Next End Sub Public Sub UpdateRecord(ByVal DG As DataGridView, ByVal AccesTableName As String, _ByVal Cols As Collection, ByVal Vals As Collection, ByVal isShowSqlString As Boolean) Dim sqlString As String = update & AccesTableName & set Dim i As Integer Fo
19、r i = 1 To Cols.Count - 1 If Vals.Item(i).GetType.Name = String Then sqlString = sqlString & Cols.Item(i).ToString & = & Vals.Item(i) & , ElseIf Vals.Item(i).GetType.Name.Contains(Date) Then sqlString = sqlString & Cols.Item(i).ToString & = & Vals.Item(i) & , Else sqlString = sqlString & Cols.Item(i
20、).ToString & = & Vals.Item(i) & , End If Next If Vals.Item(i).GetType.Name = String Then sqlString = sqlString & Cols.Item(Cols.Count).ToString & = & Vals.Item(Cols.Count) & ElseIf Vals.Item(i).GetType.Name.Contains(Date) Then sqlString = sqlString & Cols.Item(Cols.Count).ToString & = & Vals.Item(Co
21、ls.Count) & Else sqlString = sqlString & Cols.Item(Cols.Count).ToString & = & Vals.Item(Cols.Count) End If If isShowSqlString Then MsgBox(sqlString) 目前只能实现更加数字,文本,是否来选取,时间等还没有实现,使用的时候要注意 Using Conn Conn.Open() Dim cmd As OleDbCommand = New OleDbCommand(sqlString, Conn) cmd.ExecuteNonQuery() Conn.Clo
22、se() End Using Dim dt As DataTable = DG.DataSource For Each r As DataRow In dt.Rows Dim j As Integer For j = 1 To Cols.Count r.Item(Cols(i) = Vals(j) Next Next End Sub#End Region#Region 删除数据 Public Sub DeleteRecord(ByVal DG As DataGridView, ByVal AccesTableName As String, ByVal Id As Integer) Dim sq
23、lString As String = delete from & AccesTableName & where id= & Id Try Dim myConn As New OleDb.OleDbConnection(ConnectionStr) myConn.Open() Dim inst As OleDbCommand = New OleDbCommand(sqlString, myConn) inst.ExecuteNonQuery() myConn.Close() Catch ex As Exception MsgBox(ex.Message) End Try ShowRecords
24、(DG, AccesTableName) End Sub#End Region#Region 查找 开头是 Public Function SelectRecordsByString(ByVal AccesTableName As String, _ ByVal cols As Collection, ByVal vals As Collection) As DataTable Dim sqlString As String = SELECT * FROM & AccesTableName & where ( Dim i As Integer If vals.Count = 1 Then Re
25、turn Nothing For i = 1 To vals.Count - 1 sqlString = sqlString & cols(i) & = & vals.Item(i) & or Next sqlString = sqlString & cols(i) & = & vals.Item(vals.Count) & ) Dim myConn As New OleDb.OleDbConnection(ConnectionStr) Dim myDataSet As New DataSet() MsgBox(sqlString) Using myConn myConn.Open() Dim myCommand As OleDbDataAdapter = New OleDbDa
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1