抽出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 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>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!