VB.NET 模組：SQL Server 連線與執行封裝 (支援查詢/增刪改)

模組代碼 (MdlCommon.vb)

Module MdlCommon
    Public txtSQL As String
    Public DBSet As DataSet
    Public ErrorMsg As String

    Public Function ExecuteSQL(ByVal strSQL As String, ByRef errMsg As String) As DataSet
        Dim cnn As New SqlClient.SqlConnection("Data Source=(local);Initial Catalog=urp;User ID=sa;Pwd=1234")
        Dim cmd As New SqlClient.SqlCommand()
        Dim adpt As SqlClient.SqlDataAdapter
        Dim rst As New DataSet()
        Dim SplitSQL() As String
        errMsg = ""

        Try
            SplitSQL = Split(strSQL)
            cnn.Open()
            cmd.Connection = cnn
            cmd.CommandText = strSQL

            ' 判斷是否為 DML (INSERT/DELETE/UPDATE)
            If InStr("INSERT,DELETE,UPDATE", UCase(SplitSQL(0))) > 0 Then
                cmd.ExecuteNonQuery()
            Else
                adpt = New SqlClient.SqlDataAdapter(strSQL, cnn)
                adpt.Fill(rst)
                ExecuteSQL = rst
            End If
        Catch ex As Exception
            errMsg = ex.Message
        Finally
            rst = Nothing
            cnn.Close()
            cnn = Nothing
        End Try
    End Function
End Module

呼叫方式

' 後台程式碼
txtSQL = "SELECT * FROM Members WHERE Status = 1"
DBSet = ExecuteSQL(txtSQL, ErrorMsg)
If ErrorMsg <> "" Then
    ' 錯誤處理
Else
    ' 繫結 GridView 等
    GridView1.DataSource = DBSet.Tables(0)
    GridView1.DataBind()
End If

改進建議

• 連線字串移至 web.config，使用 ConfigurationManager.ConnectionStrings 讀取。
• 使用 Using 區塊自動釋放 SqlConnection/SqlCommand/SqlDataAdapter。
• 參數化查詢：傳入 SqlParameter 陣列，避免 SQL 注入。
• 區分 ExecuteScalar (單值)、ExecuteNonQuery (DML)、ExecuteReader (流式讀取) 以提升效能。
• 例外處理記錄完整堆疊 ex.ToString()，便於除錯。