adoaccess.vb

vb.net编写的数据库连接动态链接库。支持ASCCESS,SQL Server

Imports ADODB
Imports ADODB.CursorLocationEnum         '这个为了声明这个CursorLocation   的 
Imports ADODB.CursorTypeEnum             '为了声明adOpenStatic 
Imports ADODB.LockTypeEnum
Imports System.DBNull


Public Class ADOAccess
    Private mCnnDB As New ADODB.Connection()
    '作用: 连接Access数据库 
    '参数: DBName 数据库名　　 
    Public Sub ConnAccess(ByVal DBName As String)
        Dim strDB As String = ""
        mCnnDB.CursorLocation = adUseClient
        mCnnDB.Provider = "Microsoft.Jet.OLEDB.4.0"
        mCnnDB.Open(DBName, "Admin")
    End Sub

    '作用: 连接ODBC数据库(不需指定用户和密码 ) 
    '参数：dsnName为ODBC名 
    Public Sub ConnODBC(ByVal dsnName As String)
        mCnnDB.Provider = "MSDASQL"
        mCnnDB.ConnectionString = "Data Source='" & dsnName & "'"
        mCnnDB.Open()
    End Sub

    '作用: 连接ODBC数据库(需指定用户和密码 ) 
    '参数：dsnName ODBC名,UserID 用户名，UserPwd 用户密码 
    Public Sub ConnODBC(ByVal dsnName As String, ByVal UserID As String, ByVal UserPwd As String)
        mCnnDB.Provider = "MSDASQL"
        mCnnDB.ConnectionString = "Data Source='" & dsnName & "'User ID='" & UserID & "';" & _
        "Password='" & UserPwd & " "
        mCnnDB.Open()
    End Sub

    '作用: 连接SQL Server数据库 
    '参数：ServerName 服务器名,DBName 数据库名 
    Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String)
        With mCnnDB
            .ConnectionString = "uid=;pwd= ;driver={SQL Server};" & _
            "server=" & ServerName & _
            ";database=" & DBName
            .Open()
        End With
    End Sub

    '作用: 连接SQL Server数据库 
    '参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码 
    Public Sub ConnSQLServer(ByVal ServerName As String, ByVal DBName As String, ByVal UserID As String, ByVal UserPwd As String)
        With mCnnDB
            .ConnectionString = "uid='" & UserID & "';pwd='" & UserPwd & "';driver={SQL Server};" & _
            "server=" & ServerName & _
            ";database=" & DBName
            .Open()
        End With
    End Sub

    '作用: 连接Oracle数据库
    '参数：ServerName 服务器名,DBName 数据库名,UserID 用户名，UserPwd 用户密码
    Public Sub ConnOracle(ByVal ServerName As String, ByVal UserID As String, ByVal UserPwd As String)
        With mCnnDB
            .Provider = "MSDAORA"
            .ConnectionString = "User ID='" & UserID & "';" & _
            "Password='" & UserPwd & "';" & _
            "Data Source='" & ServerName & "'"
            .Open()
        End With
    End Sub

    '作用:连接表
    '参数：TableName表名
    '返回：记录集
    Public Overloads Function OpenTable(ByVal TableName) As ADODB.Recordset
        Dim strSql As String
        Dim rec As ADODB.Recordset
        rec = New ADODB.Recordset()
        rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
        rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
        strSql = "SELECT * FROM " & TableName
        rec.Open(strSql, mCnnDB) '打开记录集
        If Not rec.EOF Then
            rec.MoveLast()
            rec.MoveFirst()
        End If
        OpenTable = rec
    End Function

    Public Overloads Function OpenTable(ByVal TableName As String, ByVal strWhere As String) As ADODB.Recordset
        Dim strSql As String
        Dim rec As ADODB.Recordset
        rec = New ADODB.Recordset()
        rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
        rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
        strSql = "SELECT * FROM " & TableName & " where " & strWhere
        rec.Open(strSql, mCnnDB) '打开记录集
        If Not rec.EOF Then
            rec.MoveLast()
            rec.MoveFirst()
        End If
        Return rec
    End Function

    '作用:连接多表
    '参数：strSQL
    '返回：记录集
    Public Function ExecuteSQL(ByVal strSql As String) As ADODB.Recordset
        Dim rec As New ADODB.Recordset()
        rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
        rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
        rec.Open(strSql, mCnnDB) '打开记录集
        ExecuteSQL = rec
    End Function

    '作用：用记录集的数据填充网格
    '参数：MSGrid 网格对象，rec 记录集对象
    '只使用于VB6.0
    Public Sub FillMsGrid(ByVal MSGrid As MSFlexGridLib.MSFlexGrid, ByVal rec As ADODB.Recordset)
        Dim i, j, RecordNum As Integer
        If rec.EOF Then Exit Sub
        RecordNum = RecordCount(rec)
        MSGrid.Rows = RecordNum + 1
        MSGrid.Cols = rec.Fields.Count + 1
        For i = 0 To RecordNum - 1
            For j = 0 To rec.Fields.Count - 1
                If IsDBNull(rec(j).Value) Then
                    MSGrid.set_TextMatrix(i + 1, j + 1, "")
                Else
                    MSGrid.set_TextMatrix(i + 1, j + 1, rec(j).Value)
                End If
            Next
            MSGrid.set_TextMatrix(i + 1, 0, i)
            rec.MoveNext()
        Next
        rec.MoveFrist()
    End Sub

    '作用：取记录集的记录数
    '参数：rec 记录集对象
    '返回：记录集的记录数

    Public Function RecordCount(ByVal rec As ADODB.Recordset) As Integer
        Dim i As Integer
        If rec.EOF Then
            RecordCount = 0
            Exit Function
        End If
        With rec
            .MoveFirst()
            Do While Not .EOF
                i += 1
                .MoveNext()
            Loop
            .MoveFirst()
        End With
        RecordCount = i
    End Function

    '删除表的记录集
    '参数:TableName 表名
    '返回:ture 或 False
    Public Function DeleteTableRec(ByVal TableName As String) As Boolean
        mCnnDB.Execute("delete * from " & TableName)
    End Function

    '得到Access数据库中所有的用户表
    Public Function GetAllTableName_Access() As System.Collections.ArrayList
        Dim t As New System.Collections.ArrayList()
        Dim rs As New ADODB.Recordset()
        rs = mCnnDB.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
        Do While Not rs.EOF
            If rs("TABLE_TYPE").Value = "TABLE" Then
                t.Add(rs("TABLE_NAME").Value)
            End If
            rs.MoveNext()
            If rs.EOF Then Exit Do
        Loop
        rs.Close()
        rs = Nothing
        Return t
    End Function

    '得到SQL Server数据库中的所有表格
    Public Function GetAllTableName_SQLSever() As System.Collections.ArrayList
        Dim rec As New ADODB.Recordset()
        Dim strSql As String
        Dim t As New System.Collections.ArrayList()
        rec.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
        rec.LockType = ADODB.LockTypeEnum.adLockOptimistic
        strSql = "select name from sysobjects where type='U'"
        rec.Open(strSql, mCnnDB)
        Do While Not rec.EOF
            If rec("name").Value <> "dtproperties" Then '注：在sql server2000以前的数据库中，才存在dtproperties，是bug，2005以后没有
                t.Add(rec("name").Value)
            End If
            rec.MoveNext()
            If rec.EOF Then Exit Do
        Loop
        rec.Close()
        rec = Nothing
        Return t
    End Function
End Class