SqlHelper

大家好，又见面了，我是你们的朋友全栈君。

    在机房重构的过程中，几乎所有调用D层的过程中，都需要连接数据库，如果每个功能都写一段连接的代码，这不就重复了好多吗？所以我们就可以把这些重复的代码抽象出来写到一个类中，这样就可以实现代码的复用。

我们的功能和数据库的连接大体上可以分为四种情况：

如何写SQL Helper类：

1.有参数的查询：

方法一：

'执行有参的查询操作
Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
    Using conn As New SqlConnection(strConnection)
        Dim cmd As SqlCommand = conn.CreateCommand()
        Dim adp As SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Parameters.AddRange(paras)
        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

方法二：

'执行有参的查询操作
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.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Parameters.AddRange(paras)
        sqlAdapter = New SqlDataAdapter(cmd)
        cmd.Connection = conn
        Try
            conn.Open()
            sqlAdapter.Fill(ds)
            dt = ds.Tables(0)
            cmd.Parameters.Clear()
            Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally
            Call CloseConn(conn)
            Call CloseCmd(cmd)
        End Try
        Return dt
 End Function
 '关闭连接
 Public Shared Sub CloseConn(ByVal conn As SqlConnection)
        If (conn.State <> ConnectionState.Closed) Then
            conn.Close()
            conn = Nothing
        End If
 End Sub
 '释放命令
 Public Shared Sub CloseCmd(ByVal cmd As SqlCommand)
        If Not IsNothing(cmd) Then
            cmd.Dispose()
            cmd = Nothing
        End If
 End Sub

    

    对比上面两种方法，都可以实现对数据库的查询操作，方法一使用using连接池，在使用完成后就会自动关闭数据库连接，然而第二种方法定义了关闭数据库的方法，每次都需要调用CloseConn（）方法才能关闭数据库连接，这样在程序运行时，又是就会出现数据库未关闭的错误，所以个人认为使用using连接池比较好。

2.无参数的查询：

'执行无参的查询操作
Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
    Using conn As New SqlConnection(strConnection)
        Dim cmd As SqlCommand = conn.CreateCommand()
        Dim adp As SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        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

3.有参数的增删改：

    Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer
        Using conn As New SqlConnection(strConnection)
            Dim cmd As SqlCommand = conn.CreateCommand
            cmd.CommandText = cmdText
            cmd.CommandType = cmdType
            cmd.Parameters.AddRange(paras)
            Try
                conn.Open()
                Return cmd.ExecuteNonQuery
            Catch ex As Exception
                Return 0
                Throw ex
            End Try
        End Using
    End Function

4.无参数的增删改：

'无参的增删改
Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
    Using conn As New SqlConnection(strConnection)
        Dim cmd As New SqlCommand
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        Try
            conn.Open()
            Return cmd.ExecuteNonQuery
        Catch ex As Exception
            Return 0
            Throw ex
        End Try
    End Using
End Function

以登录时判断用户是否存在为例，在D层调用SQLHelper

    Public Function SelectUser(enUser As Entity.UsersEntity) As DataTable Implements IDAL.IUser.SelectUser    
        Dim helper As New Helper.SqlHelper  
        Dim dt As New DataTable  
        Dim cmdText As String = "select userPassword,level from [T-Users] where userId=@userId and isOn='是'"  
        Dim sqlParams As SqlParameter() = {New SqlParameter("@userId", enUser.UserId)}  
        dt = helper.ExecSelect(cmdText, CommandType.Text, sqlParams)  
        Return dt  
    End Function 

代码很容易就可以实现，重要是一开始自己的思考。总结上面四种情况，也可以简单的分为查询和增删改两种方法，关于参数如何处理，正在研究中……