ado|对象
Namespace Tonton.DAL
'//----------------------------------------
'//《数据链接层》 之 《数据连接与命令行》
'//----------------------------------------
'//作者：张少棠 （Tonton）
'//时间：2005年8月29日
'//邮编：tonton@yeah.net
'//主页：http://www.tonton.cn
'//博客：http://blog.tonton.cn
'//----------------------------------------
'//----------------------------------------
'//例子：
'//----------------------------------------
'Dim Conn As New Tonton.DAL.Connection
'Dim Cmd As Tonton.DAL.Command
'
' Try
' '//打开ACCESS数据库，也可以用连接字符串并采用Open方法，
' '//Conn.Open("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("db.mdb"))
' '//当然，如果知道是用ACCESS，你会用上面这个麻烦的方法吗？
' '//如果是SQL SERVER的话，可以用 Conn.OpenSqlServer 方法。
' '
' Conn.OpenAccess(Server.MapPath("db.mdb"))
' '//添加记录
' Cmd = Conn.Execute("Insert Into [Item]([Value]) VALUES (?)")
' Cmd.AddParam("添加的内容")
' Cmd.Update()
' '//删除记录
' Cmd = Conn.Execute("Delete From [Item] WHERE Id=?")
' Cmd.AddParam(6) '//要删除的记录号
' Cmd.Update()
' '//更新记录
' Cmd = Conn.Execute("Update [Item] Set [Value]=? WHERE Id=?")
' Cmd.AddParam("新的内容")
' Cmd.AddParam(5)
' Cmd.Update()
' '//读取记录
' Cmd = Conn.Execute("Select * From [Item]")
' If Cmd.Read Then
' Response.Write(Cmd("Value"))
' Else
' Response.Write("OK")
' End If
' Catch ex As Exception
' '//出错处理
' Response.Write(ex)
' Finally
' '关闭连接
' Conn.Close()
' Cmd = Nothing
' End Try
'//----------------------------------------
'//例子结束
'//----------------------------------------
'//----------------------------------------
'//类定义开始
'//----------------------------------------
'数据连接类型枚举
Public Enum ConnectionType As Integer
OleDb = 1
SqlServer = 2
'Oracle = 3
'MySql = 4
End Enum
'连接字符串构造器类
Public Class ConnectStringBuilder
Public Shared Function JetOleDb(ByVal DataBasePath As String, Optional ByVal PassWord As String = "") As String
If DataBasePath <> "" Then
JetOleDb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataBasePath & ";"
If PassWord <> "" Then
JetOleDb &= "User ID='admin';Password=;Jet OLEDB:Database Password=" & PassWord
End If
End If
End Function
Public Shared Function SqlOleDb(Optional ByVal HostName As String = "localhost", Optional ByVal Catalog As String = "", Optional ByVal UserName As String = "sa", Optional ByVal PassWord As String = "") As String
SqlOleDb = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=" & HostName & ";Password=" & PassWord & ";User ID=" & UserName & ";"
If Catalog <> "" Then SqlOleDb &= "Initial Catalog=" & Catalog & ";"
End Function
Public Shared Function SqlClient(Optional ByVal HostName As String = "localhost", Optional ByVal Catalog As String = "", Optional ByVal UserName As String = "sa", Optional ByVal PassWord As String = "") As String
SqlClient = "Persist Security Info=False;Data Source=" & HostName & ";Password=" & PassWord & ";User ID=" & UserName & ";"
If Catalog <> "" Then SqlClient &= "Initial Catalog=" & Catalog & ";"
End Function
Public Shared Function Dsn(ByVal DsnName As String) As String
Return "DSN=" & DsnName
End Function
End Class
'连接对象类
Public Class Connection
Private _dbConn As IDbConnection
Private _ConnStr As String
Private _dbType As ConnectionType = ConnectionType.OleDb
Public Sub New(Optional ByVal ConnectType As ConnectionType = ConnectionType.OleDb)
_dbType = ConnectType
End Sub
Public Sub New(ByRef Connect As IDbConnection)
If TypeOf Connect Is SqlClient.SqlConnection Then
_dbType = ConnectionType.SqlServer
ElseIf TypeOf Connect Is OleDb.OleDbConnection Then
_dbType = ConnectionType.OleDb
End If
End Sub
Public Sub New(ByVal ConnString As String, Optional ByVal ConnectType As ConnectionType = ConnectionType.OleDb)
_dbType = ConnectType
Me.ConnectString = ConnString
End Sub
'设置/返回连接字符串，设置的同时生成新的连接对象实例
Public Property ConnectString() As String
Get
Return _ConnStr
End Get
Set(ByVal Value As String)
_ConnStr = Value
Try
_dbConn.Close()
Catch ex As Exception
Finally
If Value <> "" Then
Select Case _dbType
Case ConnectionType.OleDb
_dbConn = New OleDb.OleDbConnection(_ConnStr)
Case ConnectionType.SqlServer
_dbConn = New SqlClient.SqlConnection(_ConnStr)
Case Else
_dbConn = Nothing
End Select
Else
_dbConn = Nothing
End If
End Try
End Set
End Property
'设置/返回连接类型
Public Property ConnectType() As ConnectionType
Get
Return _dbType
End Get
Set(ByVal Value As ConnectionType)
_dbType = Value
Me.ConnectString = _ConnStr
End Set
End Property
Protected Overrides Sub Finalize()
Try
_dbConn.Close()
_dbConn.Dispose()
Catch ex As Exception
Finally
MyBase.Finalize()
End Try
End Sub
'返回连接对象
Public ReadOnly Property Connection() As IDbConnection
Get
Return _dbConn
End Get
End Property
'打开一个数据库连接，要指定连接类型（默认为OLEDB）
Public Function Open(Optional ByVal ConnString As String = "", Optional ByVal ConnType As ConnectionType = ConnectionType.OleDb) As Boolean
Try
If ConnString <> "" Then
_ConnStr = ConnString
_dbType = ConnType
End If
_dbConn.Open()
Return True
Catch ex As Exception
Throw ex
Return False
End Try
End Function
'打开一个ACCESS连接
Public Function OpenAccess(ByVal DbPath As String, Optional ByVal PassWord As String = "") As Boolean
If DbPath <> "" Then
Try
_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DbPath & ";"
If PassWord <> "" Then
_ConnStr &= "User ID='admin';Password=;Jet OLEDB:Database Password=" & PassWord
End If
_dbType = ConnectionType.OleDb
_dbConn = New OleDb.OleDbConnection(_ConnStr)
Return True
Catch ex As Exception
Throw ex
Return False
End Try
End If
End Function
'打开一个SQL SERVER连接
Public Function OpenSqlServer(Optional ByVal HostName As String = "localhost", Optional ByVal Catalog As String = "", Optional ByVal UserName As String = "sa", Optional ByVal PassWord As String = "") As Boolean
Try
_ConnStr = "Persist Security Info=False;Data Source=" & HostName & ";Password=" & PassWord & ";User ID=" & UserName & ";"
If Catalog <> "" Then _ConnStr &= "Initial Catalog=" & Catalog & ";"
_dbType = ConnectionType.SqlServer
_dbConn = New SqlClient.SqlConnection(_ConnStr)
Return True
Catch ex As Exception
Throw ex
Return False
End Try
End Function
'关闭数据连接
Public Sub Close()
Try
_dbConn.Close()
_dbConn = Nothing
Catch ex As Exception
End Try
End Sub
'执行一个SQL语句，生成或不生成一个Command对象
Public Function Execute(ByVal Sql As String, Optional ByVal NonQuery As Boolean = False) As Command
Execute = New Command(_dbConn, Sql)
If NonQuery Then Execute.Update()
End Function
'生成一个DataSet，不再需要手工生成DataAdapter对象了。
Public ReadOnly Property DataSource(ByVal Sql As String, Optional ByVal Name As String = "", Optional ByVal Schema As Boolean = False) As DataSet
Get
Dim Ds As IDataAdapter
Select Case _dbType
Case ConnectionType.OleDb
Ds = New OleDb.OleDbDataAdapter(Sql, _dbConn)
Case ConnectionType.SqlServer
Ds = New SqlClient.SqlDataAdapter(Sql, _dbConn)
End Select
If Name = "" Then
DataSource = New DataSet
Else
DataSource = New DataSet(Name)
End If
If Schema Then Ds.FillSchema(DataSource, SchemaType.Source)
Ds.Fill(DataSource)
End Get
End Property
End Class
'命令行类
Public Class Command
Private _Cmd As IDbCommand
Private _Sql As String
Private _Conn As IDbConnection
Private _rdr As IDataReader
Private _dbType As ConnectionType
Public Sub New(ByRef Connect As IDbConnection)
_Conn = Connect
If TypeOf Connect Is OleDb.OleDbConnection Then
_dbType = ConnectionType.OleDb
ElseIf TypeOf Connect Is SqlClient.SqlConnection Then
_dbType = ConnectionType.SqlServer
End If
End Sub
Public Sub New(ByRef Connect As IDbConnection, ByVal Sql As String)
_Conn = Connect
If TypeOf Connect Is OleDb.OleDbConnection Then
_dbType = ConnectionType.OleDb
ElseIf TypeOf Connect Is SqlClient.SqlConnection Then
_dbType = ConnectionType.SqlServer
End If
Me.Sql = Sql
End Sub
Protected Overrides Sub Finalize()
Try
_Cmd.Dispose()
_rdr.Dispose()
_Conn.Dispose()
Catch ex As Exception
Finally
MyBase.Finalize()
End Try
End Sub
'设置或返回连接对象
Public Property Connect() As IDbConnection
Get
Return _Conn
End Get
Set(ByVal Value As IDbConnection)
_Conn = Value
End Set
End Property
'读取或设置SQL语句
Public Property Sql() As String
Get
Return _Sql
End Get
Set(ByVal Value As String)
_Sql = Value
If _dbType = ConnectionType.SqlServer Then
_Cmd = New SqlClient.SqlCommand(Sql, _Conn)
Else
_Cmd = New OleDb.OleDbCommand(_Sql, _Conn)
End If
End Set
End Property
'读取下一记录行，如果记录集没有打开，则自动打开。
Public Function Read(Optional ByVal Behavior As System.Data.CommandBehavior = CommandBehavior.Default) As Boolean
If _rdr Is Nothing Then
Try
_rdr = _Cmd.ExecuteReader(Behavior)
Return _rdr.Read
Catch ex As Exception
Return False
End Try
Return False
Else
Return _rdr.Read
End If
End Function
'//返回字段数
Public Function FieldCount() As Integer
Try
Return _rdr.FieldCount
Catch ex As Exception
Return 0
End Try
End Function
'//执行命令
Public Function Update() As Boolean
Try
If _Conn.State <> ConnectionState.Open Then
_Conn.Open()
End If
_Cmd.ExecuteNonQuery()
Return True
Catch ex As Exception
Throw ex
Return False
End Try
End Function
'//读取字段或添加SQL的命名参数
Default Public Property Item(ByVal Name As String)
Get
Return _rdr(Name)
End Get
Set(ByVal Value)
AddParam(Value, Name)
End Set
End Property
'//读取字段
Default Public ReadOnly Property Item(ByVal Index As Integer)
Get
Return _rdr(Index)
End Get
End Property
'//添加参数
Public Function AddParam(ByRef Value As Object, Optional ByVal Name As String = "")
If _dbType = ConnectionType.OleDb Then
Return CType(_Cmd, OleDb.OleDbCommand).Parameters.Add(Name, Value)
Else
Return _Cmd.Parameters.Add(New SqlClient.SqlParameter("@" & Name, Value))
End If
End Function
End Class
End Namespace