ado在vb.net中的数据访问类

Imports ADODB.ConnectionClass
Imports ADODB.RecordsetClass
Imports ADODB.CursorLocationEnum
Imports System.DBNull
'Imports AxMSFlexGridLib

'Imports MSFlexGridLib.MSFlexGridClass

Public Class ADOAccess
    ' 在类中定义一局部连接对象变量。
    Private mCnnDB As New ADODB.Connection()
    ' Public mcnn 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")
        '  mcnn = mCnnDB

    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
    '返回：记录集
    'add edit delete 记录集
    'select
    'sqltxt=UPDATE table_name SET column1='x' WHERE conditions
    ' sqltxt = "insert into 菜分类 (名称,打折) values('热菜',1)"
    ' sqltxt = "delete from 菜分类 where 名称='热菜'"
    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) '打开记录集
        '是否返回记录集
        If Left(strSql, 6) = "select" Then ExecuteSQL = rec

    End Function

 

 

    '作用：用记录集的数据填充网格
    '参数：MSGrid 网格对象，rec 记录集对象

    Public Sub FillMsGrid(ByVal MSGrid As AxMSFlexGridLib.AxMSFlexGrid, 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.MoveFirst()

    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

 

End Class