1直在用SQLHelper，但是直到現(xiàn)在才稍稍明白1點(diǎn)數(shù)據(jù)連接類的妙用 真實(shí)的體現(xiàn)了代碼重復(fù)利用。

SQL 語(yǔ)句的 更新 插入 查找 刪除
包括有參數(shù)的、無(wú)參數(shù)的更新 插入 查找 刪除 都包括了

下面是代碼的詳細(xì)解釋，現(xiàn)在先看看明白，等到以后寫(xiě)的時(shí)候就方便多了。

“`
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration ‘必須要在管理器中添加援用
”’
”’ SqlHelper類是專門提供給廣大用戶用于高性能、可升級(jí)和最好練習(xí)的sql數(shù)據(jù)操作
”’
”’
Public Class SqlHelper
‘定義變量
‘取得數(shù)據(jù)庫(kù)的連接字符串
Private ReadOnly strConnection As String = ConfigurationManager.AppSettings(“ConnStr”)
‘設(shè)置連接
Dim conn As SqlConnection = New SqlConnection(strConnection)
‘定義cmd命令
Dim cmd As New SqlCommand

''' <summary>  
''' 履行增刪改3個(gè)操作,(有參)返回值為Boolean類型,確認(rèn)是不是履行成功  
''' </summary>  
''' <param name="cmdText">需要履行語(yǔ)句,1般是Sql語(yǔ)句,也有存儲(chǔ)進(jìn)程</param>  
''' <param name="cmdType">判斷Sql語(yǔ)句的類型,1般都不是存儲(chǔ)進(jìn)程</param>  
''' <param name="paras">參數(shù)數(shù)組,沒(méi)法確認(rèn)有多少參數(shù)</param>  
''' <returns></returns>  
''' <remarks></remarks>  
Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer
    '將傳入的值,分別為cmd的屬性賦值  
    cmd.Parameters.AddRange(paras)   '將參數(shù)傳入  
    cmd.CommandType = cmdType            '設(shè)置1個(gè)值,解釋cmdText  
    cmd.Connection = conn                '設(shè)置連接,全局變量  
    cmd.CommandText = cmdText            '設(shè)置查詢的語(yǔ)句  

    Try
        conn.Open()                      '打開(kāi)連接  
        Return cmd.ExecuteNonQuery()     '履行增刪改操作  
        cmd.Parameters.Clear()           '清除參數(shù)  
    Catch ex As Exception
        Return 0                         '如果出錯(cuò),返回0  
    Finally
        Call CloseConn(conn)
        Call CloseCmd(cmd)
    End Try
End Function
''' <summary>  
''' 履行增刪改3個(gè)操作,(無(wú)參)  
''' </summary>  
''' <param name="cmdText">需要履行語(yǔ)句,1般是Sql語(yǔ)句,也有存儲(chǔ)進(jìn)程</param>  
''' <param name="cmdType">判斷Sql語(yǔ)句的類型,1般都不是存儲(chǔ)進(jìn)程</param>  
''' <returns>Interger,受影響的行數(shù)</returns>  
''' <remarks>2013年2月2日8:19:59</remarks>  
Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
    '為要履行的命令cmd賦值  
    cmd.CommandText = cmdText       '先是查詢的sql語(yǔ)句  
    cmd.CommandType = cmdType       '設(shè)置Sql語(yǔ)句如何解釋  
    cmd.Connection = conn           '設(shè)置連接  

    '履行操作  
    Try
        conn.Open()
        Return cmd.ExecuteNonQuery()
    Catch ex As Exception
        Return 0
    Finally
        Call CloseConn(conn)
        Call CloseCmd(cmd)
    End Try
End Function

''' <summary>  
''' 履行查詢的操作,(有參),參數(shù)不限  
''' </summary>  
''' <param name="cmdText">需要履行語(yǔ)句,1般是Sql語(yǔ)句,也有存儲(chǔ)進(jìn)程</param>  
''' <param name="cmdType">判斷Sql語(yǔ)句的類型,1般都不是存儲(chǔ)進(jìn)程</param>  
''' <param name="paras">傳入的參數(shù)</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)  '參數(shù)添加  
    sqlAdapter = New SqlDataAdapter(cmd)  '實(shí)例化adapter  
    Try
        sqlAdapter.Fill(ds)           '用adapter將dataSet填充   
        If ds.Tables.Count = 0 Then

        Else
            dt = ds.Tables(0)             'datatable為dataSet的第1個(gè)表  
            cmd.Parameters.Clear()        '清除參數(shù)  
        End If

    Catch ex As Exception
        MsgBox("查詢失敗", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "正告")
    Finally                            '最后1定要燒毀cmd  
        Call CloseCmd(cmd)
    End Try
    Return dt
End Function


''' <summary>  
''' 履行查詢的操作,(無(wú)參)  
''' </summary>  
''' <param name="cmdText">需要履行語(yǔ)句,1般是Sql語(yǔ)句,也有存儲(chǔ)進(jìn)程</param>  
''' <param name="cmdType">判斷Sql語(yǔ)句的類型,1般都不是存儲(chǔ)進(jìn)程</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)  '實(shí)例化adapter  
    Try
        sqlAdapter.Fill(ds)           '用adapter將dataSet填充   
        Return ds.Tables(0)             'datatable為dataSet的第1個(gè)表  
    Catch ex As Exception
        Return Nothing
    Finally                            '最后1定要燒毀cmd  
        Call CloseCmd(cmd)
    End Try
End Function

''' <summary>  
''' 關(guān)閉連接  
''' </summary>  
''' <param name="conn">需要關(guān)閉的連接</param>  
''' <remarks></remarks>  
Public Sub CloseConn(ByVal conn As SqlConnection)
    If (conn.State <> ConnectionState.Closed) Then  '如果沒(méi)有關(guān)閉  
        conn.Close()                    '關(guān)閉連接  
        conn = Nothing                  '不指向原對(duì)象  
    End If

End Sub
''' <summary>  
''' 關(guān)閉命令  
''' </summary>  
''' <param name="cmd">需要關(guān)閉的命令</param>  
''' <remarks></remarks>  
Public Sub CloseCmd(ByVal cmd As SqlCommand)

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

這個(gè)數(shù)據(jù)庫(kù)鏈接類非常全面既可以查詢sql語(yǔ)句，又可以查詢視圖存儲(chǔ)進(jìn)程。觸發(fā)器沒(méi)有試過(guò)，不知道如何？