【个人机房重构】——SqlHelper

一时失言乱红尘 2022-08-10 17:56 193阅读 0赞

一、SqlHelper 出场

不是因为大家都在用SqlHelper所以才用，是因为连接数据库关闭数据库查询数据库的多了也就加上了SqlHelper。当你的很多需求都有一个相同的方法的时候我们没有必要完成一个需要就要写一遍代码，完成一个需求写一遍代码，这样不仅会把人累死（当然除了复制粘贴），这样的话会造成代码的重复增加了耦合，产生了冗余。为什么不把相同的代码单独拉出来封装成一个类，这样每个需求使用时只需调用这个被封装的类的相应的方法就好了。

当有多个关于数据库增删改成操作的时候就用到了SqlHelper，因为D层是直接操作数据库，完成数据库的增删改查的，所以SqlHelper是属于D层的，但是我是单独拿出来成为一层的。把D层的所有对数据库的操作方法都封装起来。分为两种操作，一种是增删改，一种是查询操作，而每种又分为有参数的和无参数的。这样减轻了D的负担，使D层不会显得太臃肿。

在做机房之前一直在看别人的博客，总是有提到SqlHelper，所以就在众博客中多看了它一眼。只因为在人群中多看了你一眼，再也不能忘掉你的容颜，在这里写下我一个人对你的情有独钟……

SQLHelper工具类

Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Configuration  '必须要在管理器中添加引用  
    ''' <summary>
    ''' SqlHelper类是专门提供给广大用户用于高性能、可升级和最佳练习的sql数据操作
    ''' </summary>
    ''' <remarks></remarks>
    Public Class SqlHelper
        '定义变量  
        '获得数据库的连接字符串  
        Private ReadOnly strConnection As String = "Data Source=192.168.24.207;Initial Catalog=ChargeSystem;User ID=sa;Pwd=123"
    
        'ConfigurationManager.AppSettings("ConnStr")
    
        '设置连接  
        Dim conn As SqlConnection = New SqlConnection(strConnection)
        '定义cmd命令  
        Dim cmd As New SqlCommand
    
        ''' <summary>  
        ''' 执行增删改三个操作,(有参)返回值为Boolean类型,确认是否执行成功  
        ''' </summary>  
        ''' <param name="cmdText">需要执行语句,一般是Sql语句,也有存储过程</param>  
        ''' <param name="cmdType">判断Sql语句的类型,一般都不是存储过程</param>  
        ''' <param name="paras">参数数组,无法确认有多少参数</param>  
        ''' <returns></returns>  
        ''' <remarks></remarks>  
        Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer
            '将传入的值,分别为cmd的属性赋值  
            cmd.Parameters.AddRange(paras)   '将参数传入  
            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">需要执行语句,一般是Sql语句,也有存储过程</param>  
        ''' <param name="cmdType">判断Sql语句的类型,一般都不是存储过程</param>  
        ''' <returns>Interger,受影响的行数</returns>  
        ''' <remarks>2013年2月2日8:19:59</remarks>  
        Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
            '为要执行的命令cmd赋值  
            cmd.CommandText = cmdText       '先是查询的sql语句  
            cmd.CommandType = cmdType       '设置Sql语句如何解释  
            cmd.Connection = conn           '设置连接  
    
            '执行操作  
            Try
                conn.Open()
                Return cmd.ExecuteNonQuery()
            Catch ex As Exception
                Return 0
            Finally
                Call CloseConn(conn)
                Call CloseCmd(cmd)
            End Try
        End Function
    
        ''' <summary>  
        ''' 执行查询的操作,(有参),参数不限  
        ''' </summary>  
        ''' <param name="cmdText">需要执行语句,一般是Sql语句,也有存储过程</param>  
        ''' <param name="cmdType">判断Sql语句的类型,一般都不是存储过程</param>  
        ''' <param name="paras">传入的参数</param>  
        ''' <returns></returns>  
        ''' <remarks></remarks>  
        Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
    
            Dim sqlAdapter As SqlDataAdapter
            Dim dt As New DataTable
            Dim ds As New DataSet
            '还是给cmd赋值  
            cmd.CommandText = cmdText
            cmd.CommandType = cmdType
            cmd.Connection = conn
            cmd.Parameters.AddRange(paras)  '参数添加  
            sqlAdapter = New SqlDataAdapter(cmd)  '实例化adapter  
            Try
                sqlAdapter.Fill(ds)           '用adapter将dataSet填充   
                dt = ds.Tables(0)             'datatable为dataSet的第一个表  
                cmd.Parameters.Clear()        '清除参数  
            Catch ex As Exception
                MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告")
            Finally                            '最后一定要销毁cmd  
                Call CloseCmd(cmd)
            End Try
            Return dt
        End Function
    
    
        ''' <summary>  
        ''' 执行查询的操作,(无参)  
        ''' </summary>  
        ''' <param name="cmdText">需要执行语句,一般是Sql语句,也有存储过程</param>  
        ''' <param name="cmdType">判断Sql语句的类型,一般都不是存储过程</param>  
        ''' <returns>dataTable,查询到的表格</returns>  
        ''' <remarks></remarks>  
        Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
            Dim sqlAdapter As SqlDataAdapter
            Dim ds As New DataSet
            '还是给cmd赋值  
            cmd.CommandText = cmdText
            cmd.CommandType = cmdType
            cmd.Connection = conn
            sqlAdapter = New SqlDataAdapter(cmd)  '实例化adapter  
            Try
                sqlAdapter.Fill(ds)           '用adapter将dataSet填充   
                Return ds.Tables(0)             'datatable为dataSet的第一个表  
            Catch ex As Exception
                Return Nothing
            Finally                            '最后一定要销毁cmd  
                Call CloseCmd(cmd)
            End Try
        End Function
    
        ''' <summary>  
        ''' 关闭连接  
        ''' </summary>  
        ''' <param name="conn">需要关闭的连接</param>  
        ''' <remarks></remarks>  
        Public 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>  
        Public Sub CloseCmd(ByVal cmd As SqlCommand)
    
            If Not IsNothing(cmd) Then          '如果cmd命令存在  
                cmd.Dispose()                   '销毁  
                cmd = Nothing
            End If
        End Sub
    End Class

二、SqlHelper 用法

1、首先，它的方法

（1）为什么要分成有参数和没有参数的呢？

这就是一种思想了，当想要获取数据库的全部信息和部分信息时，需要全部信息直接给它指定查找哪个表就行，但是当要在一个表中获取一部分信息就要有查询条件，就要有参数。

（2）为什么要分为查询和增删改两种呢？

查询是要返回给用户想要查询到的信息，增删改都可以归纳为改或者更新，更新要返回给用户更新的是否成功或失败，不需要返回具体信息。所以查询返回的是DataTable类型，而更新返回受影响的行就行，当更新好一行数据就返回整数1。这就和它的返回值产生了关系，既然有相同的就要合并起来成为一个方法，这样减少冗余，使代码简单化。学了面向对象，其实就是怎么简单怎么来。

2、然后，SqlHelper的每一个方法都要有一个返回值，因为返回值的不同会造成相应调用此方法的方法也要有一样的返回值。

（1）返回DataTable

DataTable又是什么呢？

DataTable是一个临时保存数据的网络虚拟表，可以被创建和访问，当SqlHelper查询后就要返回到DataTable虚拟表中，判断它的第一行是否有数据来判断是否查到记录。

（2）返回Integer

返回受影响的行数，如果受影响的行数大于0说明查到的记录，如果为0说明没有查询到数据。如果受影响的行数大于0说明增删改操作成功，如果为0，说明操作没有成功。我们通过返回受影响的行数到B层进行逻辑判断，然后返回相应的提示给U层，这样就很好的再一条线中实现。思路清晰。

三、SqlHelper举例应用

下面就举一个返回值是DataTable型的有参数的方法。

'方法（2）有参数的查询
        'return DataTable 查询出的表格
        Public Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlParams As SqlParameter()) As DataTable
            Using conn As New SqlConnection(strConnection)   '使用连接池，可以在使用完成后自动关闭连接
                Dim cmd As SqlCommand = conn.CreateCommand() '
                cmd.CommandText = cmdText  '需要执行的SQL语句
                cmd.CommandType = cmdType '给出Sql语句的类型
                cmd.Parameters.AddRange(sqlParams) '参数数组，参数个数根据实际情况而定
                adp = New SqlDataAdapter(cmd)
                Try
                    conn.Open()
                    adp.Fill(ds)
                    Return ds.Tables(0)
                Catch ex As Exception
                    Return Nothing
                    Throw ex
                End Try
            End Using
        End Function

它的使用，就拿D层来说，当判断一个用户是否存在时，输入用户名之后，这个用户名就成为了参数，调用SqlHelper的ExecuteNonQuery（）方法，通过返回的DataTable的行数是否大于0来判断此用户是否存在。

Public Function SelectUser(ByVal user As Entity.UserInfo) As DataTable Implements IDAL.IUser.SelectUser
            Dim strUserName As String = user.UserName
            Dim helper As New Helper.SqlHelper
            Dim dt As New DataTable            '声明一个DataTable类型变量
            Dim cmdText As String = "select * from User_Info where UserName=@UserName"   '声明并实例化需要执行的SQL语句
            Dim sqlParams As SqlParameter() = {New SqlParameter("@UserName", strUserName)} '声明并实例化参数
            dt = helper.ExecuteNonQuery(cmdText, CommandType.Text, sqlParams)  '调用SqlHelper类中的ExecSelect（）方法来执行查询，并获取返回值
            Return dt                                                             '返回查询结果
        End Function

四、我的SqlHelper的问题

这样就又出现了一个问题，当我们删除用户的时候，或者需要查询到多条记录显示出来的时候，虽然返回的是DataTable类型，但是它默认了返回DataTable的第一行数据，出现这样情况解决方法有两种：

（1）通过遍历思想循环查数据库中的记录，返回一条和上一次不同的记录的第一行。

例如返回某一级别的用户

If dt.Rows.Count > 0 Then            For i = 0 To dt.Rows.Count - 1  '查到内容显示
                    DataGridView.Rows.Add()  '一开始没有行和列，所以要添加一行和一列，避免发生错误
                    For j = 0 To dt.Columns.Count - 1
                        DataGridView(j, i).Value = dt.Rows(i).Item(j)
                    Next j
                    'DataGridView.AllowUserToAddRows = False    '如果放在这里，还是会有最后一行
                Next i

（2）利用绑定数据集

直接dataGridView=table

[http://blog.csdn.net/liutengteng130/article/details/8643863][http_blog.csdn.net_liutengteng130_article_details_8643863]

第二种方法显然是比第一种好很多，但为什么很多人还是要只返回DataTable的第一行数据呢？

这是因为：……^^……… 我就是不把它循环遍历出来不罢休的人，然后再去尝试第二种方法，

五、我的感悟——SqlHelper

就像设计模式一样，其实一开始并不存在什么设计模式，大加就只是在写代码啊写代码，但是需要写的代码也多了，当时会编程的人又少，就要提高写代码的效率，所以一些设计模式就出现了，面向对象的出现也是有一定的时间积累的，人们需要复用代码，又先要少写代码就能够实现要求，而且是系统软件看起来更简洁，达到高内聚低耦合的效果。如果说设计模式的出现是认识思想的升华，那SqlHelper就是人们思想的结晶。

当我用了SqlHelper的时候一开始会觉得很难，但是会慢慢的发现它的思想它的好处。人们最大的进步就是会使用工具，当有人给我们创造出了工具后我们要做的就是好好的使用工具，然后创造更好的工具给别人使用。

转载自：[http://blog.csdn.net/xdd19910505/article/details/28469205][http_blog.csdn.net_xdd19910505_article_details_28469205]

[http_blog.csdn.net_liutengteng130_article_details_8643863]: http://blog.csdn.net/liutengteng130/article/details/8643863
[http_blog.csdn.net_xdd19910505_article_details_28469205]: http://http://blog.csdn.net/xdd19910505/article/details/28469205