對ADO.NET幾個對象的第一次加工

來源:互聯網
上載者:User
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



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。