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

80酷酷网    80kuku.com

  ado|对象 

Namespace Tonton.DAL
  '//----------------------------------------
  '//《数据链接层》 之 《数据连接与命令行》
  '//----------------------------------------
  '//作者:张少棠 (Tonton)
  '//时间:2005年8月29日
  '//邮编:tontonyeah.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

分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间
点击: