对ADO。NET几个对象的第一次加工。

对ADO。NET几个对象的第一次加工。 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 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