集中整理ArcEngin在VB环境下开发时用到的资料.docx

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