分享
 
 
 

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

王朝other·作者佚名  2006-01-10
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有