'**********************************************
'说明: 用于封装增 删 改 查 的 操作，在DAL层用的时候直接调用即可
'命名空间：sqlHelper
'机器名称：晓
'创建日期：2014/12/15 16:41:31
'作者：郑浩
'版本号：V1.00
'**********************************************
Imports System.Data
Imports System.Data.sqlClient
Imports System.Configuration  '必须要在管理器中添加引用  
Public Class sqlHelper
    '定义全局变量变量，获得数据库的连接字符串                                                          
    Private Shared ReadOnly strConnection As String = ConfigurationManager.AppSettings("ConnStr")
    '定义全局变量，设置连接    
    Shared conn As sqlConnection = New sqlConnection(strConnection)
    '定义全局cmd命令    
    Shared cmd As New sqlCommand
    ''' <summary>    
    ''' 执行非查询操作（增删改）有参数，    
    ''' </summary>    
    ''' <param name="cmdText">执行增删改查sql语句或者存储过程</param>    
    ''' <param name="cmdType">命令类型文本或者是存储过程</param>    
    ''' <param name="paras">参数数组,无法确认有多少参数</param>    
    ''' <returns></returns>    
    ''' <remarks></remarks>    
    Public Shared Function ExecuteNoQuery(ByVal cmdText As String,ByVal cmdType As CommandType,ByVal paras As sqlParameter()) As Integer
        '将传入的值,分别为cmd的属性赋值  
        Dim result As Integer   '定义变量用来存放执行的结果
        cmd.Parameters.AddRange(paras)   '将参数传入    
        cmd.CommandType = cmdType            '设置一个值,解释cmdText    
        cmd.Connection = conn                '设置连接,全局变量    
        cmd.CommandText = cmdText            '设置查询的语句    

        Try
            conn.Open()                      '打开连接    
            result = cmd.ExecuteNonQuery()     '执行增删改操作    
            cmd.Parameters.Clear()           '清除参数    
        Catch ex As Exception
            MsgBox(ex.Message,"数据库操作")                        '如果出错,返回0    
        Finally
            '关闭数据连接
            Call CloseConn(conn)
            Call CloseCmd(cmd)
        End Try
        Return result        '返回受影响的行数
    End Function
    ''' <summary>    
    ''' 执行增删改三个操作,(无参)    
    ''' </summary>    
    ''' <param name="cmdText">需要执行语句,一般是sql语句,也有存储过程</param>    
    ''' <param name="cmdType">判断sql语句的类型,一般都不是存储过程</param>    
    ''' <returns>Interger,受影响的行数</returns>    
    Public Shared Function ExecAddDelUpdateNo(ByVal cmdText As String,ByVal cmdType As CommandType) As Integer
        '为要执行的命令cmd赋值    
        cmd.CommandText = cmdText       '先是查询的sql语句    
        cmd.CommandType = cmdType       '设置sql语句如何解释    
        cmd.Connection = conn           '设置连接    
        Dim result As Integer

        '执行操作    
        Try
            conn.Open()
            result = cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message,"数据库操作")
        Finally
            Call CloseConn(conn)  
            Call CloseCmd(cmd)

        End Try
        Return result          '返回受影响的行数
    End Function
    '''' <summary>  
    '''' 执行查询的操作,(有参),参数不限    
    '''' </summary>    
    ''' <param name="cmdText">需要执行语句,一般都不是存储过程</param>    
    ''' <param name="paras">传入的参数</param>    
    ''' <returns></returns>    
    ''' <remarks></remarks>    
    Public Shared 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(ex.Message,"数据库操作")
        Finally                            '最后一定要销毁cmd    
            Call CloseCmd(cmd)
        End Try
        Return dt
    End Function

    'Public Shared Function GetDataTable(ByVal cmdTxt As String,ByVal paras As sqlParameter()) As DataTable
    '    Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
    '    定义一个数据库连接对象()
    '    Dim conn As sqlConnection = New sqlConnection(strConnStr)
    '    Dim cmd As New sqlCommand '定义一个命令对象  
    '    Dim adataset As DataSet
    '    Dim adaptor As sqlDataAdapter '定义一个适配器对象  
    '    cmd = New sqlCommand(cmdTxt,'    adaptor = New sqlDataAdapter(cmd)
    '    adataset = New DataSet
    '    cmd.CommandType = cmdType
    '    cmd.Parameters.AddRange(paras)
    '    Try
    '        打开数据库连接()
    '        If conn.State = ConnectionState.Closed Then
    '            conn.Open()

    '        End If
    '        填充数据集()
    '        adaptor.Fill(adataset)

    '    Catch ex As Exception
    '        MsgBox(ex.Message,"数据库操作")
    '    Finally
    '        关闭数据库连接()
    '        Call CloseConn(conn)
    '        Call CloseCmd(cmd)
    '    End Try
    '    Return adataset.Tables(0)

    'End Function

    ''' <summary>    
    ''' 执行查询的操作,一般都不是存储过程</param>    
    ''' <returns>dataTable,查询到的表格</returns>    
    ''' <remarks></remarks>    
    Public Shared 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 Shared 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 Shared Sub CloseCmd(ByVal cmd As sqlCommand)

        If Not IsNothing(cmd) Then          '如果cmd命令存在    
            cmd.Dispose()                   '销毁    
            cmd = Nothing
        End If
    End Sub
End Class