机房收费系统总结之3——SqlHelper

        刚一开始着手机房收费系统时，涉及数据库连接，每个D层类的方法都要单独访问一次数据库。正所谓无知者无谓，不厌其烦的去弄，精神可嘉。再看别人博客时，才发现还有SqlHelper这么强的“思想”。高人啊。
        SqlHelper是一个基于.NET Framework的数据库操作组件。组件中包含数据库操作方法。SqlHelper用于简化你重复的去写那些数据库连接（SqlConnection）,SqlCommand,SqlDataReader等等。SqlHelper 封装过后通常是只需要给方法传入一些参数如数据库连接字符串，SQL参数等，就可以访问数据库了，很方便。

        在SQLHelper中主要有四类，分别是：
        不带参数的sql增删改语句或存储过程（无返回行或值）
        带参数的sql增删改语句或存储过程（无返回行或值）
        不带参数的sql查询语句或存储过程，返回DataTable结果集（有返回行或值）
        带参数的sql查询语句或存储过程，返回DataTable结果集（有返回行或值）

1.通过配置文件获取连接字符串（步骤及优点上篇博客已介绍）

Imports System.Data.SqlClient
Imports System.Configuration        '添加对配置文件的引用
Public Class SqlHelper

    '通过配置文件获取连接字符串并赋值给所声明的变量strConnection
    Dim strConnection As String = ConfigurationManager.AppSettings("strSqlConnection")

    Dim conn As SqlConnection = New SqlConnection(strConnection)                    '设置连接 
    Dim cmd As New SqlCommand                                                       '声明SqlCommand类变量cmd

2.提取重复代码部分（每次数据库连接之后都需要关闭连接、关闭命令）

    ''' <summary>
    ''' 关闭连接
    ''' </summary>
    ''' <param name="conn">需要关闭的连接</param>
    ''' <remarks></remarks>
    Private Sub CloseConn(ByVal conn As SqlConnection)
        '如果没有关闭，则关闭连接
        If (conn.State <> ConnectionState.Closed) Then
            conn.Close()
            conn = Nothing
        End If
    End Sub

    ''' <summary>
    ''' 关闭命令
    ''' </summary>
    ''' <param name="cmd">需要关闭的命令</param>
    ''' <remarks></remarks>
    Private Sub CloseCmd(ByVal cmd As SqlCommand)
        '如果没有关闭，则关闭命令
        If Not IsNothing(cmd) Then
            cmd.Dispose()
            cmd = Nothing
        End If
    End Sub

3.具体操作。分别是有参增删改、无参增删改、有参查询、无参查询。

    ''' <summary>
    ''' 关闭命令
    ''' </summary>
    ''' <param name="cmd">需要关闭的命令</param>
    ''' <remarks></remarks>
    Private Sub CloseCmd(ByVal cmd As SqlCommand)
        '如果没有关闭，则关闭命令
        If Not IsNothing(cmd) Then
            cmd.Dispose()
            cmd = Nothing
        End If
    End Sub
    ''' <summary>  
    ''' 有参数的 增删改 操作
    ''' </summary>  
    ''' <param name="cmdText">需要执行的命令</param>  
    ''' <param name="cmdType">所执行命令的类型，一般是Sql语句,也有可能是存储过程，或表</param>  
    ''' <param name="sqlParams">参数数组</param>  
    ''' <returns>返回执行 增删改 语句受影响的行数，为Integer类型</returns>  
    Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlParams As SqlParameter()) As Integer

        '将传入的值,分别为cmd的属性赋值  
        cmd.Parameters.AddRange(sqlParams)      '将参数传入  
        cmd.CommandType = cmdType               '设置一个值,解释cmdText  
        cmd.Connection = conn                   '设置连接  
        cmd.CommandText = cmdText               '设置查询的语句  

        '执行操作
        Try
            conn.Open()                         '打开连接  
            Return cmd.ExecuteNonQuery()        '执行增删改操作并返回受影响的行数  
            cmd.Parameters.Clear()              '清除参数  
        Catch ex As Exception
            Return 0                            '如果出错,返回0  
        Finally
            Call CloseConn(conn)                '关闭连接
            Call CloseCmd(cmd)                  '关闭连接
        End Try

    End Function




    ''' <summary>
    ''' 无参数的 增删改 操作
    ''' </summary>
    ''' <param name="cmdText">需要执行的命令</param>
    ''' <param name="cmdType">所执行命令的类型，一般是Sql语句,也有可能是存储过程，或表</param>
    ''' <returns>返回执行 增删改 语句受影响的行数，为Integer类型</returns>
    Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer

        '将传入的参数，分别为cmd的属性赋值
        cmd.CommandType = cmdType               '设置一个值,解释cmdText  
        cmd.Connection = conn                   '设置连接  
        cmd.CommandText = cmdText               '设置查询的语句  

        '执行操作
        Try
            conn.Open()                         '打开连接  
            Return cmd.ExecuteNonQuery()        '执行增删改操作并返回受影响的行数  
            cmd.Parameters.Clear()              '清除参数  
        Catch ex As Exception
            Return 0                            '如果出错,返回0  
        Finally
            Call CloseConn(conn)                '关闭连接
            Call CloseCmd(cmd)                  '关闭连接
        End Try

    End Function




    ''' <summary>
    ''' 有参数的 查询 操作
    ''' </summary>
    ''' <param name="cmdText">需要执行的命令</param>  
    ''' <param name="cmdType">所执行命令的类型，一般是Sql语句,也有可能是存储过程，或表</param>  
    ''' <param name="sqlParams">参数数组</param>  
    ''' <returns>返回执行 查询 得到的结果,为DataTable类型</returns>  
    Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlParams As SqlParameter()) As DataTable

        Dim sqlAdapter As SqlDataAdapter        '声明适配器
        Dim dt As New DataTable                 '声明数据表
        Dim ds As New DataSet                   '声明数据缓存

        '将传入的值,分别为cmd的属性赋值  
        cmd.Parameters.AddRange(sqlParams)      '将参数传入  
        cmd.CommandType = cmdType               '设置一个值,解释cmdText  
        cmd.Connection = conn                   '设置连接  
        cmd.CommandText = cmdText               '设置查询的语句  

        sqlAdapter = New SqlDataAdapter(cmd)    '实例化适配器

        '执行操作
        Try
            sqlAdapter.Fill(ds)                 '用适配器对ds进行填充
            dt = ds.Tables(0)                   '返回数据集的第一个表
            cmd.Parameters.Clear()              '清除参数
        Catch ex As Exception
            MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告")
        Finally
            Call CloseConn(conn)                '关闭连接
            Call CloseCmd(cmd)                  '关闭连接
        End Try
        Return dt

    End Function




    ''' <summary>
    ''' 无参数的 查询 操作,返回值为DataTable类型
    ''' </summary>
    ''' <param name="cmdText">需要执行的命令</param>  
    ''' <param name="cmdType">所执行命令的类型，一般是Sql语句,也有可能是存储过程，或表</param>  
    ''' <returns>返回执行 查询 得到的结果,为DataTable类型</returns>  
    Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable

        Dim sqlAdapter As SqlDataAdapter        '声明适配器
        Dim dt As New DataTable                 '声明数据表
        Dim ds As New DataSet                   '声明数据缓存

        '将传入的值,分别为cmd的属性赋值  
        cmd.CommandType = cmdType               '设置一个值,解释cmdText  
        cmd.Connection = conn                   '设置连接  
        cmd.CommandText = cmdText               '设置查询的语句  

        sqlAdapter = New SqlDataAdapter(cmd)    '实例化适配器

        '执行操作
        Try
            sqlAdapter.Fill(ds)                 '用适配器对ds进行填充
            dt = ds.Tables(0)                   '返回数据集的第一个表
        Catch ex As Exception
            MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告")
        Finally
            Call CloseConn(conn)                '关闭连接
            Call CloseCmd(cmd)                  '关闭连接
        End Try
        Return dt

    End Function

End Class

          可见SqlHelper是对数据库访问的一次抽象、一次打包，更是一次思想的升华。