机房重构 之 SqlHelper

    机房收费开始一段时间了，刚开始也是敲了一段时间，发现D层访问数据库出现了大量的重复代码，每个D层类都要

单独访问数据库。发现问题，咱们就解决问题，查阅前人的博客，发现了一个SqlHelper类，运用一下，果然好用，省

去了大量时间去写重复的代码。

    小面对SQL中的一些类方法进行简单的介绍。

    1.SQLHelper.ExecuteNonQuery    作用：用于执行语句

    2. SQLHelper.ExecuteScalar       作用：用于获取单字段值语句

    3. SQLHelper.ExecuteReader      作用：用于获取结果集语句

    ....

    我写的SqlHelper类（ChargeSqlHelperDAL）

Public Class ChargeSqlHelperDAL
    Private ReadOnly sqlConnection As String = sqlConnection

    ''' <summary>  
    ''' 执行查询操作，有参数  
    ''' </summary>  
    ''' <param name="cmdText">需要查询的Sql语句</param>  
    ''' <param name="cmdType">给出Sql语句的类型</param>  
    ''' <returns>dataTable,查询到的表格</returns>  
    ''' <remarks></remarks> 
    Public Function ExecuteDataTable(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlParams As SqlParameter()) As DataTable
        Using conn As New SqlConnection(sqlConnection)
            Dim cmd As SqlCommand = conn.CreateCommand()
            Dim adp As SqlDataAdapter
            Dim ds As New DataSet
            cmd.CommandText = cmdText                                       '设置查询的语句
            cmd.CommandType = cmdType                                        '设置一个值,解释cmdText
            cmd.Parameters.AddRange(sqlParams)
            adp = New SqlDataAdapter(cmd)
            Try
                conn.Open()                                                 '打开连接
                adp.Fill(ds)
                Return ds.Tables(0)                                         '返回DateTable
            Catch ex As Exception                                           '抛出异常
                Return Nothing                                              '返回nothing
                Throw ex
            End Try
        End Using
    End Function


    ''' <summary>  
    ''' 执行查询操作，无参数  
    ''' </summary>  
    ''' <param name="cmdText">需要查询的Sql语句</param>  
    ''' <param name="cmdType">给出Sql语句的类型</param>  
    ''' <returns>dataTable,查询到的表格</returns>  
    ''' <remarks></remarks>  
    Public Function ExecuteDataTable(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
        Using conn As New SqlConnection(sqlConnection)
            Dim cmd As SqlCommand = conn.CreateCommand()
            Dim adp As SqlDataAdapter
            Dim ds As New DataSet
            cmd.CommandText = cmdText                            '设置查询的语句
            cmd.CommandType = cmdType                            '设置一个值,解释cmdText
            adp = New SqlDataAdapter(cmd)
            Try
                conn.Open()                                      '打开连接
                adp.Fill(ds)
                Return ds.Tables(0)                              '返回DateTable
            Catch ex As Exception                                '抛出异常
                Return Nothing                                   '返回nothing
                Throw ex
            End Try
        End Using
    End Function

    ''' <summary>  
    ''' ExecuteNonQuery操作,对数据库进行增删改操作，有参数  
    ''' </summary>  
    ''' <param name="cmdText">需要执行的Sql语句</param>  
    ''' <param name="cmdType">给出Sql语句的类型</param>  
    ''' <param name="sqlParams">参数数组,参数个数根据实际情况而定</param>  
    ''' <returns>integer,受影响的行数</returns>  
    ''' <remarks></remarks>  
    Public Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlParams As SqlParameter()) As Integer
        Using conn As New SqlConnection(sqlConnection)
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = cmdText                              '设置查询的语句
            cmd.CommandType = cmdType                               '设置一个值,解释cmdText
            cmd.Parameters.AddRange(sqlParams)
            Try
                conn.Open()                                         '打开连接
                Return cmd.ExecuteNonQuery()                        '执行增删改操作并返回受影响的行数
            Catch ex As Exception
                Return 0                                            '如果出错,返回0
                Throw ex
            End Try
        End Using
    End Function

    ''' <summary>  
    ''' ExecuteNonQuery操作,对数据库进行增删改操作，无参数  
    ''' </summary>  
    ''' <param name="cmdText">需要执行的Sql语句</param>  
    ''' <param name="cmdType">给出Sql语句的类型</param>  
    ''' <returns>integer,受影响的行数</returns>  
    ''' <remarks></remarks>  
    Public Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
        Using conn As New SqlConnection(sqlConnection)             '使用连接池，可以在使用完成后自动关闭连接.  
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = cmdText                               '设置查询的语句
            cmd.CommandType = cmdType                                '设置一个值,解释cmdText
            Try
                conn.Open()                                         '打开连接
                Return cmd.ExecuteNonQuery()                        '执行增删改操作并返回受影响的行数
            Catch ex As Exception
                Return 0                                            '如果出错,返回0
                Throw ex
            End Try
        End Using
    End Function
End Class

    以用户上机为例—调用代码

Public Function SelectUser(enUser As ChargeEntity.ChargeT_UsersEntity) As DataTable
        Dim helper As New ChargeSqlHelperDAL      '实例化ChargeSqlHelperDAL
        Dim dt As New DataTable                   '定义DataTable
        Dim cmdText As String = "select * from T_Users where userName=@userName and password=@password"           '获得数据库中的字段
        Dim sqlParams As SqlParameter() = {New SqlParameter("@userName", enUser.userName And "@password", enUser.password)}    '传参
        dt = helper.ExecuteDataTable(cmdText, CommandType.Text, sqlParams)
        Return dt
    End Function
End Class

个人小结

    使用该类目的就是让使用者更方便、更安全的对数据库的操作，既是除了在SQLHelper类以外的所有类将不用引

用对数据库操作的任何类与语句，无须担心数据库的连接与关闭的问题。SqlHelper就是将D层连接数据库的重复代码

抽象成为SqlHelper类，从而有利于代码的复用。

着学习的深入，不断思考，理解的更加深入。