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 & 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 = "u & _

"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 = "u & 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 & 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