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 [1] [2] 下一页 [C语言系列]NET 中C#的switch语句的语法 [系统软件]托拽Explore中的文件到VB.net的窗口 [系统软件]Boost库在XP+Visual C++.net中的安装 [常用软件]新配色面板:Paint.Net3.0RC1官方下载 [常用软件]用内建的“Net Meeting”聊天 [VB.NET程序]Henry的VB.NET之旅(三)—共享成员 [VB.NET程序]Henry的VB.NET之旅(二)—构造与析构 [VB.NET程序]Henry的VB.NET之旅(一)—失踪的窗体 [VB.NET程序]在托盘上显示Balloon Tooltip(VB.NET) [VB.NET程序]Henry手记-VB.NET中动态加载Treeview节点(二)
|