这个是在昨天的“第一次加工”的基础上改进的,第一次完整的把面向对象模型的各种要素,包括接口(多态)、继承、封装,都用上了,也算是自已的一个小小的进步吧!
Namespace Tonton.Data
'//--------------------------------------------------------------------------
'//《数据链接层》 之 《数据连接对象》
'//--------------------------------------------------------------------------
'//作者:张少棠 (Tonton)
'//时间:2005年8月30日
'//邮编:tonton@yeah.net
'//主页:http://www.tonton.cn
'//博客:http://blog.tonton.cn
'//--------------------------------------------------------------------------
'//--------------------------------------------------------------------------
'//例子:
'//--------------------------------------------------------------------------
'Dim Conn As New Tonton.Data.AccessConnect(Server.MapPath("db.mdb"))
'Dim Cmd As Tonton.Data.Command
'Try
' '//打开ACCESS数据库
' Conn.Open()
' '//添加记录
' Cmd = Conn.Execute("Insert Into [Item]([Value]) VALUES (?)")
' Cmd.AddParam("添加的内容")
' Cmd.Update()
' '//删除记录
' Cmd = Conn.Execute("Delete From [Item] WHERE Id=?")
' Cmd.AddParam(4) '//要删除的记录号
' Cmd.Update()
' '//更新记录
' Cmd = Conn.Execute("Update [Item] Set [Value]=? WHERE Id=?")
' Cmd.AddParam("sss")
' 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 Interface IConnect
'//释放对象
Sub Dispose()
'//打开数据连接
Sub Open()
'//关闭数据连接
Sub Close()
'//设置或返回连接字符串
Property ConnectString() As String
'//返回数据连接对象
ReadOnly Property Connection() As IDbConnection
'//返回连接状态
ReadOnly Property State() As System.Data.ConnectionState
'//返回当前数据库名称
ReadOnly Property DataBase() As String
'//返回一个命令行对象
Function Execute(ByVal Sql As String) As ICommand
'//返回一个数据集对象
Function DataSource(ByVal Sql As String, Optional ByVal Name As String = "", Optional ByVal Schema As Boolean = False) As DataSet
'//返回一个事务对象
ReadOnly Property Transaction() As IDbTransaction
'//开始事务处理
Sub Begin(Optional ByVal iL As System.Data.IsolationLevel = IsolationLevel.Chaos)
'//回滚事务处理
Sub RollBack()
'//提交事务处理
Sub Commit()
End Interface
'连接对象类
Public MustInherit Class Connection
Implements IConnect
Private _dbConn As IDbConnection
Private _ConnStr As String
Private _dbTrans As IDbTransaction
Protected MustOverride Function NewConnect() As IDbConnection
Public MustOverride Function Execute(ByVal Sql As String) As ICommand Implements IConnect.Execute
Public MustOverride Function DataSource(ByVal Sql As String, Optional ByVal Name As String = "", Optional ByVal Schema As Boolean = False) As System.Data.DataSet Implements IConnect.DataSource
Private Sub SetConnect(ByRef NewConn As IDbConnection)
With NewConn
.ConnectionString = _ConnStr
End With
_dbConn = NewConn
End Sub
Public Sub New(Optional ByRef ConnString As String = "")
_ConnStr = ConnString
End Sub
Protected Overrides Sub Finalize()
Try
_dbConn.Close()
_dbConn.Dispose()
Catch ex As Exception
Finally
MyBase.Finalize()
End Try
End Sub
Public Sub Dispose() Implements IConnect.Dispose
Me.Finalize()
Me.Dispose()
End Sub
Public Overridable Sub Open() Implements IConnect.Open
Close()
If _dbConn Is Nothing Then
SetConnect(NewConnect)
Else
SetConnect(_dbConn)
End If
_dbConn.Open()
End Sub
Public Sub Close() Implements IConnect.Close
Try
Me.RollBack()
_dbConn.Close()
Catch ex As Exception
End Try
End Sub
Public ReadOnly Property Connection() As System.Data.IDbConnection Implements IConnect.Connection
Get
Return _dbConn
End Get
End Property
Public Property ConnectString() As String Implements IConnect.ConnectString
Get
Return _ConnStr
End Get
Set(ByVal Value As String)
_ConnStr = Value
If Not _dbConn Is Nothing Then _dbConn.ConnectionString = Value
End Set
End Property
Public ReadOnly Property State() As System.Data.ConnectionState Implements IConnect.State
Get
If _dbConn Is Nothing Then
Return ConnectionState.Closed
Else
Return _dbConn.State
End If
End Get
End Property
Public Sub Begin(Optional ByVal iL As System.Data.IsolationLevel = IsolationLevel.Chaos) Implements IConnect.Begin
If Not _dbTrans Is Nothing Then
Throw New Exception("事务处理正在进行!")
Else
_dbTrans = _dbConn.BeginTransaction(iL)
End If
End Sub
Public Sub RollBack() Implements IConnect.RollBack
If Not _dbTrans Is Nothing Then
_dbTrans.Rollback()
_dbTrans.Dispose()
End If
End Sub
Public Sub Commit() Implements IConnect.Commit
If Not _dbTrans Is Nothing Then
_dbTrans.Commit()
_dbTrans.Dispose()
End If
End Sub
Public ReadOnly Property Transaction() As System.Data.IDbTransaction Implements IConnect.Transaction
Get
Return _dbTrans
End Get
End Property
Public ReadOnly Property DataBase() As String Implements IConnect.DataBase
Get
Return _dbConn.Database
End Get
End Property
End Class
'//适用于Access的连接对象
Public Class AccessConnect
Inherits Connection
'构造连接字符串
Public Shared Function BuildConnectString(Optional ByRef DbPath As String = "", Optional ByRef PassWord As String = "") As String
If DbPath = "" Then Return ""
BuildConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DbPath & ";"
If PassWord <> "" Then BuildConnectString &= "User ID='admin';Password=;Jet OLEDB:Database Password=" & PassWord
End Function
Public Sub New(Optional ByRef DbPath As String = "", Optional ByRef PassWord As String = "")
MyBase.New(BuildConnectString(DbPath, PassWord))
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
Public Overrides Function Execute(ByVal Sql As String) As ICommand
Execute = New OleCommand(Sql, Me.Connection)
End Function
Protected Overrides Function NewConnect() As System.Data.IDbConnection
Return New OleDb.OleDbConnection
End Function
Public Overrides Function DataSource(ByVal Sql As String, Optional ByVal Name As String = "", Optional ByVal Schema As Boolean = False) As System.Data.DataSet
Dim Ds As New OleDb.OleDbDataAdapter(Sql, Me.Connection)
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 Function
End Class
'//适用于SqlServer的连接对象
Public Class SqlServerConnect
Inherits Connection
'构造连接字符串
Public Shared Function BuildConnectString(Optional ByVal HostName As String = "localhost", Optional ByVal Catalog As String = "", Optional ByVal UserName As String = "sa", Optional ByVal PassWord As String = "") As String
BuildConnectString = "Persist Security Info=False;Data Source=" & HostName & ";Password=" & PassWord & ";User ID=" & UserName & ";"
If Catalog <> "" Then BuildConnectString &= "Initial Catalog=" & Catalog & ";"
End Function
Public Sub New(Optional ByVal HostName As String = "localhost", Optional ByVal Catalog As String = "", Optional ByVal UserName As String = "sa", Optional ByVal PassWord As String = "")
MyBase.New(BuildConnectString(HostName, Catalog, UserName, PassWord))
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
Public Overrides Function Execute(ByVal Sql As String) As ICommand
Execute = New SqlCommand(Sql, Me.Connection)
End Function
Protected Overrides Function NewConnect() As System.Data.IDbConnection
Return New SqlClient.SqlConnection
End Function
Public Overrides Function DataSource(ByVal Sql As String, Optional ByVal Name As String = "", Optional ByVal Schema As Boolean = False) As System.Data.DataSet
Dim Ds As New SqlClient.SqlDataAdapter(Sql, Me.Connection)
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 Function
End Class
End Namespace
Namespace Tonton.Data
'//--------------------------------------------------------------------------
'//《数据链接层》 之 《命令对象》
'//--------------------------------------------------------------------------
'//作者:张少棠 (Tonton)
'//时间:2005年8月30日
'//邮编:tonton@yeah.net
'//主页:http://www.tonton.cn
'//博客:http://blog.tonton.cn
'//--------------------------------------------------------------------------
'//--------------------------------------------------------------------------
'//例子:
'//--------------------------------------------------------------------------
'Dim Conn As New Tonton.Data.AccessConnect(Server.MapPath("db.mdb"))
'Dim Cmd As Tonton.Data.Command
'Try
' '//打开ACCESS数据库
' Conn.Open()
' '//添加记录
' Cmd = Conn.Execute("Insert Into [Item]([Value]) VALUES (?)")
' Cmd.AddParam("添加的内容")
' Cmd.Update()
' '//删除记录
' Cmd = Conn.Execute("Delete From [Item] WHERE Id=?")
' Cmd.AddParam(4) '//要删除的记录号
' Cmd.Update()
' '//更新记录
' Cmd = Conn.Execute("Update [Item] Set [Value]=? WHERE Id=?")
' Cmd.AddParam("sss")
' 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 Interface ICommand
'//数据连接
Property Connection() As IDbConnection
'//命令的几个常见参数设置
Property CommandText() As String
Property CommandTimeOut() As Integer
Property CommandType() As System.Data.CommandType
Property UpdatedRowSource() As System.Data.UpdateRowSource
'//打开一个新命令
Sub Open(Optional ByVal Sql As String = "")
'//中断命令的执行
Function Cancel() As Boolean
'//关闭命令获取的记录集
Sub Close()
'//移到新的一行记录,返回该行记录是否存在
Function Read(Optional ByVal Behavior As System.Data.CommandBehavior = CommandBehavior.Default) As Boolean
'//执行命令,并返回第一行第一列,适用于统计查询
Function ReadValue(Optional ByVal Sql As String = "") As Object
'//在执行插入、更新、删除操作时,用本方法来执行命令
Function Update() As Boolean
'//设置命令行参数,或者返回记录行字段值
Default Property Fields(ByVal FieldName As String) As Object
'//返回记录行字段值
Default ReadOnly Property Fields(ByVal Index As Integer) As Object
'//返回字段个数
ReadOnly Property FieldCount() As Integer
'//设置命令行参数
Function AddParam(ByRef Value As Object, Optional ByVal Name As String = "") As Object
'//释放对象
Sub Dispose()
End Interface
'//命令对象基类
Public MustInherit Class Command
Implements ICommand
Private _Cmd As IDbCommand
Private _Sql As String
Private _Conn As IDbConnection
Private _Rdr As IDataReader
Private _cmdType As System.Data.CommandType = CommandType.Text
Private _cmdTimeOut As Integer = 30
Private _cmdUpdatedRowSource As System.Data.UpdateRowSource = UpdateRowSource.None
'//这个类不对错误进行处理,把它交由调用者决定!
Public Sub New()
End Sub
Public Sub New(ByRef Connect As IDbConnection)
_Conn = Connect
Open()
End Sub
Public Sub New(ByRef Connect As IDbConnection, ByVal Sql As String)
_Conn = Connect
Open(Sql)
End Sub
Protected Overrides Sub Finalize()
Try
_Cmd.Dispose()
_Conn = Nothing
_Rdr.Close()
_Rdr.Dispose()
Catch ex As Exception
Finally
MyBase.Finalize()
End Try
End Sub
Public Overloads Sub Dispose() Implements ICommand.Dispose
Me.Finalize()
Me.Dispose()
End Sub
'//打开数据连接
Private Function OpenConnect() As Boolean
If _Conn.State <> ConnectionState.Open Then
_Conn.Open()
Return True
Else
Return True
End If
End Function
'//设置命令参数
Private Sub SetCommand(ByRef NewCmd As IDbCommand)
With NewCmd
.CommandType = _cmdType
.CommandTimeout = _cmdTimeOut
.CommandText = _Sql
.Connection = _Conn
.UpdatedRowSource = _cmdUpdatedRowSource
End With
_Cmd = NewCmd
Close()
End Sub
'//返回命令对象,以供子类调用。
Protected ReadOnly Property Command() As IDbCommand
Get
Return _Cmd
End Get
End Property
'//必须重载,获取新命令对象
Protected MustOverride Function NewCommand() As IDbCommand
'//必须重载,添加命令行参数(因为SQL SERVER与ACCESS的参数方法不一样)
Public MustOverride Function AddParam(ByRef Value As Object, Optional ByVal Name As String = "") As Object Implements ICommand.AddParam
Public Function Cancel() As Boolean Implements ICommand.Cancel
_Cmd.Cancel()
End Function
Public Property CommandText() As String Implements ICommand.CommandText
Get
Return _Sql
End Get
Set(ByVal Value As String)
_Sql = Value
If Not _Cmd Is Nothing Then _Cmd.CommandText = _Sql
End Set
End Property
Public Property Connection() As System.Data.IDbConnection Implements ICommand.Connection
Get
Return _Conn
End Get
Set(ByVal Value As System.Data.IDbConnection)
_Conn = Value
If Not _Cmd Is Nothing Then _Cmd.Connection = _Conn
End Set
End Property
Public ReadOnly Property FieldCount() As Integer Implements ICommand.FieldCount
Get
If Not _Rdr Is Nothing Then
Return _Rdr.FieldCount
Else
Return 0
End If
End Get
End Property
Default Public Overloads ReadOnly Property Fields(ByVal Index As Integer) As Object Implements ICommand.Fields
Get
Return _Rdr(Index)
End Get
End Property
Default Public Overloads Property Fields(ByVal FieldName As String) As Object Implements ICommand.Fields
Get
Return _Rdr(FieldName)
End Get
Set(ByVal Value As Object)
AddParam(FieldName, Value)
End Set
End Property
Public Sub Open(Optional ByVal Sql As String = "") Implements ICommand.Open
If Sql <> "" Then
_Sql = Sql
SetCommand(NewCommand)
End If
End Sub
Public Sub Close() Implements ICommand.Close
Try
_Rdr.Close()
Catch ex As Exception
Finally
_Rdr = Nothing
End Try
End Sub
Public Function Read(Optional ByVal Behavior As System.Data.CommandBehavior = CommandBehavior.Default) As Boolean Implements ICommand.Read
If _Rdr Is Nothing Then
_Rdr = _Cmd.ExecuteReader(Behavior)
End If
Return _Rdr.Read
End Function
Public Function ReadValue(Optional ByVal Sql As String = "") As Object Implements ICommand.ReadValue
If Sql = "" Then
Sql = _Sql
Else
_Sql = Sql
End If
If Sql <> "" Then
If OpenConnect() Then
Return _Cmd.ExecuteScalar
End If
End If
End Function
Public Function Update() As Boolean Implements ICommand.Update
If OpenConnect() Then
_Cmd.ExecuteNonQuery()
Return True
End If
End Function
Public Property CommandTimeOut() As Integer Implements ICommand.CommandTimeOut
Get
Return _cmdTimeOut
End Get
Set(ByVal Value As Integer)
_cmdTimeOut = Value
If Not _Cmd Is Nothing Then _Cmd.CommandTimeout = Value
End Set
End Property
Public Property CommandType() As System.Data.CommandType Implements ICommand.CommandType
Get
Return _cmdType
End Get
Set(ByVal Value As System.Data.CommandType)
_cmdType = Value
If Not _Cmd Is Nothing Then _Cmd.CommandType = Value
End Set
End Property
Public Property UpdatedRowSource() As System.Data.UpdateRowSource Implements ICommand.UpdatedRowSource
Get
Return _cmdUpdatedRowSource
End Get
Set(ByVal Value As System.Data.UpdateRowSource)
_cmdUpdatedRowSource = Value
If Not _Cmd Is Nothing Then _Cmd.UpdatedRowSource = Value
End Set
End Property
End Class
'//适用于OleDb连接的命令对象
Public Class OleCommand
Inherits Command
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal Sql As String, ByRef Connect As OleDb.OleDbConnection)
MyBase.New(Connect, Sql)
End Sub
Public Overloads Overrides Function AddParam(ByRef Value As Object, Optional ByVal Name As String = "") As Object
Return CType(Me.Command, OleDb.OleDbCommand).Parameters.Add(Name, Value)
End Function
Protected Overrides Function NewCommand() As System.Data.IDbCommand
Return New OleDb.OleDbCommand
End Function
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
'//适用于SQLSERVER的命令对象
Public Class SqlCommand
Inherits Command
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal Sql As String, ByRef Connect As SqlClient.SqlConnection)
MyBase.New(Connect, Sql)
End Sub
Public Overloads Overrides Function AddParam(ByRef Value As Object, Optional ByVal Name As String = "") As Object
Return CType(Me.Command, SqlClient.SqlCommand).Parameters.Add("@" & Name, Value)
End Function
Protected Overrides Function NewCommand() As System.Data.IDbCommand
Return New SqlClient.SqlCommand
End Function
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
End Namespace