从SQLHelper看代码重构

          SQLHelper很好地体现了抽象和封装的思想，为了避免每次连接数据库时都要创建连接并实例化连接对象和得到连接对象，我用构造函数对conn进行初始化，并写了GetConn（）函数，把重复的内容提取出来是重构的重要工作。

 

Imports System.Data.SqlClient
Imports System.Configuration

Public Class SQLHelper
    Private conn As SqlConnection
    Private cmd As SqlCommand
    Private sdr As SqlDataReader
    '构造函数
    Sub New()
        'Dim connStr As String = "Data Source=localhost;Initial Catalog=NewCharge;Persist Security Info=True;User ID=sa;Password=123456"
        Dim connstr As String = System.Configuration.ConfigurationManager.AppSettings("connStr")
        conn = New SqlClient.SqlConnection(connstr)
    End Sub

    Private Function GetConn() As SqlConnection
        If conn.State = ConnectionState.Closed Then
            conn.Open()
            Return conn
        Else
            Return conn
        End If
    End Function

    因为在 DAL 层，每个类除了要进行数据库的访问，更多的是对数据库进行增删改查的操作，而每次都做重复的工作就造成代码的大量重复，实在不够专业，所以我们写完代码后往往要进行重构和优化。 SQLHelper 主要是用于简化重复的去写 SqlConnection 、 Sql Command 、 SqlDataReader 等，它的应用就对整个 DAL 层进行了优化，原因在于每个类只要实例化一个 sqlhelper 对象就可以直接调用它了，而只需要给方法传入一些参数如 SQL 参数、存储过程等，极大地减少了代码量。

 

在SQLHelper中主要有四类，分别是

不带参数的sql增删改语句或存储过程（无返回行或值）

带参数的sql增删改语句或存储过程（无返回行或值）

不带参数的sql查询语句或存储过程，返回DataTable结果集（有返回行或值）

带参数的sql查询语句或存储过程，返回DataTable结果集（有返回行或值）

 

下面是我写的第一个不带参数的sql增删改语句

 

 ''' <summary>
    ''' 执行不带参数的sql增删改语句
    ''' </summary>
    ''' <param name="sql">sql语句</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteNonQuery(ByVal sql As String) As Boolean
        Dim conn = GetConn()
        Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        If cmd.ExecuteNonQuery() > 0 Then
            conn.Close()
            Return True
        Else
            Return False
        End If
    End Function

 

          事实上我们常使用的不只有sql语句，还包括存储过程。所以，sql语句变成了cmdText，而究竟是sql语句还是存储过程则要看cmdType，需要给函数增加一个参数。因此对该函数做如下重构

  

  ''' <summary>
    ''' 执行不带参数的sql增删改语句或存储过程
    ''' </summary>
    ''' <param name="cmdtxt">增删改语句或存储过程</param>
    ''' <param name="cmdtype">命令类型文本或存储过程</param>
    ''' <returns>受影响的行数</returns>
    ''' <remarks></remarks>

    Public Function ExecuteNonQuery(ByVal cmdtxt As String, ByVal cmdtype As CommandType) As Integer
        Dim conn = GetConn() '定义并得到一个数据库连接对象
        Dim cmd As SqlCommand = New SqlCommand(cmdtxt, conn) '定义一个命令对象
        cmd.CommandType = cmdtype
        Dim res As Integer
        Try
            res = cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally
            If conn.State = ConnectionState.Open Then '关闭数据库连接
                conn.Close()
            End If
        End Try
        Return res
    End Function

 

在第二个方法中我用到了异常处理Try…Catch语句，其实抛异常主要是在BLL层，这里没多大必要。

 

其余三个方法如下：

 

 ''' <summary>
    ''' 执行带参数的sql增删改语句或存储过程
    ''' </summary>
    ''' <param name="cmdtext">增删改语句或存储过程</param>
    ''' <param name="cmdtype">命令类型文本或存储过程</param>
    ''' <param name="paras">参数数组</param>
    ''' <returns>受影响的行数</returns>
    ''' <remarks></remarks>
    Public Function ExecuteNonQuery(ByVal cmdtext As String, ByVal cmdtype As CommandType, ByVal paras As SqlParameter()) As Integer
        Dim conn = GetConn()
        Dim cmd As SqlCommand = New SqlCommand(cmdtext, conn)
        Dim res As Integer
        cmd.CommandType = cmdtype
        cmd.Parameters.AddRange(paras)
        Try
            res = cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
        Return res

    End Function
    ''' <summary>
    ''' 执行不带参数的sql查询语句或存储过程，返回DataTable结果集
    ''' </summary>
    ''' <param name="cmdtext">sql查询语句或存储过程</param>
    ''' <param name="cmdtype">命令类型文本或存储过程</param>
    ''' <returns>返回DataTable结果集</returns>
    ''' <remarks></remarks>
    Public Function ExecuteQuery(ByVal cmdtext As String, ByVal cmdtype As CommandType) As DataTable
        Dim conn = GetConn()
        Dim cmd As SqlCommand = New SqlCommand(cmdtext, conn)
        Dim mydt As DataTable = New DataTable
        Dim myReader As SqlDataReader
        cmd.CommandType = cmdtype
        myReader = cmd.ExecuteReader()
        mydt.Load(myReader)
        Return mydt
        conn.Close()

    End Function
    ''' <summary>
    ''' 执行带参数的sql查询语句或存储过程，返回DataTable结果集
    ''' </summary>
    ''' <param name="cmdtext">sql查询语句或存储过程</param>
    ''' <param name="cmdtype">命令类型文本或存储过程</param>
    ''' <param name="paras">参数集合</param>
    ''' <returns>返回DataTable结果集</returns>
    ''' <remarks></remarks>
    Public Function ExecuteQuery(ByVal cmdtext As String, ByVal cmdtype As CommandType, ByVal paras As SqlParameter()) As DataTable
        Dim conn = GetConn()
        Dim cmd As SqlCommand = New SqlCommand(cmdtext, conn)
        Dim mydt As DataTable = New DataTable
        Dim myReader As SqlDataReader
        cmd.CommandType = cmdtype
        cmd.Parameters.Add(paras)
        myReader = cmd.ExecuteReader()
        mydt.Load(myReader)
        Return mydt
        conn.Close()
    End Function

 

     其实在三层架构的思想下开发系统，既然我们定义了实体类，严格的说就要全部返回实体类而不是DataTable、DataSet等，那我们都知道用DataTable非常简单只需要load（sqlDatareader）一下就可以了，而返回实体类则需要写N多行代码，但是正如三层的划分，UI层是不知道各个表的字段的，所以传DataTable违背了三层的原则。那怎么办呢？办法就是在DAL层将结果赋值给实体类，然后再传回UI层。当然很费事了，如果要学习就老老实实传实体吧，如果要效率，传DataTable也未可厚非。