抽出SqlHelper

什么叫SqlHelper，通过简单的翻译，能够获得这是对Sql的帮助，那么它究竟对我们的Sql做出了什么样的帮助呢？

在一款软件的设计编程中，总是会对数据库有连接的。假设你用的是SqlServer的数据库，那么SqlHelper就会对你的编程降低一定的代码。假设你用别的数据库，当然，还会有对应数据库的Helper来帮助我们的。

对于数据的处理，无非就是查询和更新（加入，删除和更新）这两大类。对于每类又有分为有參操作和无參操作。这样我们就把对数据库的操作抽象出四个方法，放在一起，就构成了我们的这个Helper。

以下就来看看我的SqlHelper是怎样生成的吧。

<span style="font-size:18px;">Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
''' <summary>
''' SqlHelper类为程序猿编写訪问数据库抽象出反复的代码
''' </summary>
''' <remarks></remarks>
Public Class SqlHelper
    '定义数据库的连接字符串
    Private ReadOnly connStr As String = System.Configuration.ConfigurationSettings.AppSettings("ConnStr")
    '设置连接
    Dim conn As SqlConnection = New SqlConnection(connStr)
    '定义命令对象
    Dim cmd As New SqlCommand

    ''' <summary>
    ''' 运行增删改三个操作，有參
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="cmdType"></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 Boolean
        '将传入的值分别为cmd的属性赋值
        cmd.CommandText = cmdText '设置查询语句
        cmd.CommandType = cmdType '设置一个值，解释cmdText，一般为默认值
        cmd.Connection = conn '设置连接
        '将參数传入
        cmd.Parameters.AddRange(paras)
        Try
            conn.Open()
            Dim flag As Boolean
            If cmd.ExecuteNonQuery() > 0 Then
                flag = True
            Else
                flag = False
            End If

            cmd.Parameters.Clear()
            Return flag

        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        Finally
            Call CloseConn(conn)
            Call CloseCmd(cmd)
        End Try
        
    End Function
    ''' <summary>
    ''' 运行增删改三个操作，无參
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="cmdType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Boolean
        '将传入的值分别为cmd的属性赋值
        cmd.CommandText = cmdText '设置查询语句
        cmd.CommandType = cmdType '设置一个值，解释cmdText，一般为默认值
        cmd.Connection = conn '设置连接

        Try
            conn.Open()
            Dim flag As Boolean
            flag = cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
            Return flag

        Catch ex As Exception
            Return False
        Finally
            Call CloseConn(conn)
            Call CloseCmd(cmd)
        End Try
       
    End Function
    ''' <summary>
    ''' 运行查询的操作，有參，參数不限
    ''' </summary>
    ''' <param name="cmdText">须要运行语句，一般为SQL语句，也有存储过程</param>
    ''' <param name="cmdType"></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
            Call CloseConn(conn)
            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
            MsgBox(ex.Message)
        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.Dispose()    '销毁
            cmd = Nothing
        End If
    End Sub
End Class
</span>

抽象封装好了一个函数，那么我们该怎样去调用呢？

<span style="font-size:18px;"> Dim cmdText As String
        cmdText = "select * from card_student where CardID =@CardID"    '查询语句

        Dim helper As New SqlHelper
        Dim sqlparameter As SqlParameter() = {New SqlParameter("@CardID", card.CardID)}   '加入參数

        Dim dt As DataTable
        dt = helper.ExecSelect(cmdText, CommandType.Text, sqlparameter)    '调用sqlHelper</span>

<span style="font-size:18px;"><span style="white-space:pre">	</span>Return dt    '返回DataTable</span>

对于SqlHelper的叙述就先到此结束，听说SqlHelper的强大不只在与此，更强大的还等着我去探寻。加油。