SQLHelper初见

机房重构第一次用到SQLHelper类，下面看一下其代码。

Public Class SqlHelper

    '数据库连接  
    Private Shared strConnection As String = System.Configuration.ConfigurationManager.AppSettings("connString")  '利用配置文件 连接数据库

    Shared conn As SqlConnection = New SqlConnection(strConnection)
    Shared cmd As New SqlCommand



    ''' <summary>
    ''' 查询 操作（有参）1
    ''' </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)  '实例化Ada

        Try
            sqlAdapter.Fill(ds)  '用 adapter填充  dataset
            dt = ds.Tables(0)   'datatable为dataSet的第一个表     


        Catch ex As Exception
            MsgBox("数据库操作")
        Finally
            cmd.Parameters.Clear()  '清除参数
            Call CloseCmd(cmd)  '销毁cmd命令    
        End Try

        Return dt
        
    End Function


    ''' <summary>
    ''' 查询 操作（无参)
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="cmdType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
        Dim sqlAdapter As SqlDataAdapter
        Dim dt As New DataTable
        Dim ds As New DataSet

        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn 
        sqlAdapter = New SqlDataAdapter(cmd)  '实例化Adapter  
        Try
            sqlAdapter.Fill(ds)  '用adapter填充dataset
            dt = ds.Tables(0)   'datatable为dataSet的第一个表   

        Catch ex As Exception
            Return Nothing
        Finally
            Call CloseConn(conn)
            Call CloseCmd(cmd)  '销毁cmd命令  
        End Try

        Return dt


    End Function



    ''' <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 Integer
        '给cmd赋值  
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn
        cmd.Parameters.AddRange(paras)  '参数添加  

        Try
            conn.Open()  '打开链接
            Return cmd.ExecuteNonQuery() '执行增删改操作
            'cmd.Parameters.Clear()  '清除参数  

        Catch ex As Exception  '如有异常，返回值为0                  '此处有错误
            Return 0
        Finally
            cmd.Parameters.Clear()  '清除参数 
            Call CloseConn(conn) '关闭连接
            Call CloseCmd(cmd)  '销毁cmd命令  
        End Try
    End Function

    Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer

        '给cmd赋值  
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn
        Try
            conn.Open()  '打开链接

            Return cmd.ExecuteNonQuery() '执行增删改操作

        Catch ex As Exception  '如有异常，返回值为0
            Return 0
        Finally
            Call CloseConn(conn) '关闭连接
            Call CloseCmd(cmd)  '销毁cmd命令  
        End Try


    End Function

    Public Sub CloseCmd(ByVal cmd As SqlCommand)
        If Not IsNothing(cmd) Then  '如果cmd命令存在  
            cmd.Dispose()           '销毁  
            cmd = Nothing
        End If
    End Sub

    Public Sub CloseConn(ByVal conn As SqlConnection)
        If (conn.State <> ConnectionState.Closed) Then  '如果没有关闭
            conn.Close()       '关闭连接
            conn = Nothing
        End If

    End Sub

End Class

第一次接触，有些地方不是很明白，以后慢慢学习。