VB.NET_DBUtil

Imports System.Data.SqlClient

''' <summary>
''' DB连接工具
''' </summary>
''' <remarks></remarks>
Public Class CmnDBUtil

    Private _conn As SqlConnection
    Private _cmd As SqlCommand
    Private Const CONN_STR_NAME As String = "ConnectionString"
    Private _tran As SqlTransaction
    Private _adapter As SqlDataAdapter

    ''' <summary>
    ''' 初期化处理
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub New()
        Dim connStr As String = String.Empty

        '取得连接字符串
        connStr = System.Configuration.ConfigurationManager.ConnectionStrings(CONN_STR_NAME).ConnectionString

        _conn = New SqlConnection(connStr)
    End Sub

    ''' <summary>
    ''' 查询SQL
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property CommandText() As String
        Get
            Return _cmd.CommandText
        End Get
        Set(ByVal value As String)
            _cmd.CommandText = value
        End Set
    End Property

    ''' <summary>
    ''' 查询类型
    ''' </summary>
    ''' <value></value>
    ''' <remarks></remarks>
    Public WriteOnly Property CommandType() As CommandType
        Set(ByVal value As CommandType)
            _cmd.CommandType = value
        End Set
    End Property

    ''' <summary>
    ''' 打开DB
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Open()
        _conn.Open()
        _cmd = _conn.CreateCommand
        _cmd.CommandTimeout = 30
    End Sub

    ''' <summary>
    ''' 判断DB是否打开
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsOpen() As Boolean
        Select Case _conn.State
            Case ConnectionState.Open
                Return True
            Case Else
                Return False
        End Select
    End Function

    ''' <summary>
    ''' 查询数据库，返回DataReader
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteReader(Optional ByVal sql As String = Nothing) As SqlDataReader
        Dim reader As SqlDataReader

        If Not String.IsNullOrEmpty(sql) Then
            _cmd.CommandText = sql
        End If

        reader = _cmd.ExecuteReader

        Return reader
    End Function

    ''' <summary>
    ''' 执行SQL
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteNonQuery(Optional ByVal sql As String = Nothing) As Integer

        If Not String.IsNullOrEmpty(sql) Then
            _cmd.CommandText = sql
        End If

        Return _cmd.ExecuteNonQuery
    End Function

    ''' <summary>
    ''' 查询数据库，返回DataTable
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteDataTable(Optional ByVal sql As String = Nothing) As DataTable
        Dim dt As New DataTable

        If Not String.IsNullOrEmpty(sql) Then
            _cmd.CommandText = sql
        End If

        _adapter = New SqlDataAdapter(_cmd)

        _adapter.Fill(dt)

        Return dt
    End Function

    ''' <summary>
    ''' 查询数据库，返回DataSet
    ''' </summary>
    ''' <param name="ds"></param>
    ''' <param name="tableName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillDataSet(ByRef ds As DataSet, Optional ByVal tableName As String = Nothing) As Boolean
        _adapter = New SqlDataAdapter(_cmd)

        If String.IsNullOrEmpty(tableName) Then
            _adapter.Fill(ds)

        Else
            _adapter.Fill(ds, tableName)
        End If

        Return True
    End Function

    ''' <summary>
    ''' 批量更新
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Update(ByVal dt As DataTable) As Boolean
        Dim cb As New SqlCommandBuilder(_adapter)

        _adapter.UpdateCommand = cb.GetUpdateCommand
        _adapter.InsertCommand = cb.GetInsertCommand
        _adapter.DeleteCommand = cb.GetDeleteCommand

        _adapter.Update(dt)
        cb.Dispose()

        Return True
    End Function

    ''' <summary>
    ''' 设置SQL的参数
    ''' </summary>
    ''' <param name="paramName"></param>
    ''' <param name="value"></param>
    ''' <param name="valueType"></param>
    ''' <remarks></remarks>
    Public Sub AddParameter(ByVal paramName As String, ByVal value As Object, ByVal valueType As SqlDbType)
        Dim parameter As SqlParameter = New SqlParameter
        parameter.ParameterName = paramName
        parameter.Value = value
        parameter.SqlDbType = valueType

        _cmd.Parameters.Add(parameter)
    End Sub

    ''' <summary>
    ''' 清除参数
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub ClearParameter()
        _cmd.Parameters.Clear()
    End Sub

    ''' <summary>
    ''' 关闭DB
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Close(Optional ByVal clearpool As Boolean = False)

        If _adapter IsNot Nothing Then
            _adapter.Dispose()
        End If

        _cmd.Dispose()
        _conn.Close()
        If clearpool = True Then
            SqlConnection.ClearPool(_conn)
        End If
        _conn.Dispose()

    End Sub

    ''' <summary>
    ''' 开始事务处理
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub BeginTransaction()
        _tran = _conn.BeginTransaction()
        _cmd.Transaction = _tran
    End Sub

    ''' <summary>
    ''' 提交事务处理
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Commit()
        _cmd.Transaction.Commit()
    End Sub

    ''' <summary>
    ''' 回滚事务处理
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Rollback()
        _cmd.Transaction.Rollback()
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class