VB.NET 連接 MySQL 完整教學：Connector/NET 安裝、參考添加與資料操作封裝

前置準備

1. 下載 MySQL Connector/NET：官網下載頁
2. 解壓縮，記錄 MySql.Data.dll 路徑
3. Visual Studio 2012+ 專案已建立

專案添加參考

1. 方案總管 → 專案右鍵 →「加入參考」
2. 「瀏覽」頁籤 → 選取 MySql.Data.dll
3. 確定，專案 References 下出現 MySql.Data

建立資料庫存取類

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class MySqlDB
    Private connectionString As String = "Server=localhost;Database=testdb;Uid=root;Pwd=password;Charset=utf8mb4;"

    ''' 
取得連線物件
    Private Function GetConnection() As MySqlConnection
        Return New MySqlConnection(connectionString)
    End Function

    ''' 
執行查詢，返回 DataSet
    Public Function ExecuteQuery(sql As String, ByRef errorMsg As String) As DataSet
        Dim ds As New DataSet()
        Try
            Using conn As New MySqlConnection(connectionString)
                Using cmd As New MySqlCommand(sql, conn)
                    Using da As New MySqlDataAdapter(cmd)
                        conn.Open()
                        da.Fill(ds)
                    End Using
                End Using
            End Using
            errorMsg = "SUCCESS"
        Catch ex As Exception
            errorMsg = ex.Message
            ds = Nothing
        End Try
        Return ds
    End Function

    ''' 
執行非查詢，返回影響行數
    Public Function ExecuteNonQuery(sql As String, ByRef errorMsg As String) As Integer
        Dim rows As Integer = 0
        Try
            Using conn As New MySqlConnection(connectionString)
                Using cmd As New MySqlCommand(sql, conn)
                    conn.Open()
                    rows = cmd.ExecuteNonQuery()
                End Using
            End Using
            errorMsg = "SUCCESS"
        Catch ex As Exception
            errorMsg = ex.Message
            rows = -1
        End Try
        Return rows
    End Function

    ''' 
執行標量查詢
    Public Function ExecuteScalar(sql As String, ByRef errorMsg As String) As Object
        Dim result As Object = Nothing
        Try
            Using conn As New MySqlConnection(connectionString)
                Using cmd As New MySqlCommand(sql, conn)
                    conn.Open()
                    result = cmd.ExecuteScalar()
                End Using
            End Using
            errorMsg = "SUCCESS"
        Catch ex As Exception
            errorMsg = ex.Message
            result = Nothing
        End Try
        Return result
    End Function
End Class

WebForm 使用範例：GridView 綁定查詢結果

Partial Class _Default
    Inherits System.Web.UI.Page

    Private db As New MySqlDB()

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindData()
        End If
    End Sub

    Private Sub BindData()
        Dim errorMsg As String = ""
        Dim ds As DataSet = db.ExecuteQuery("SELECT id, name, email FROM users LIMIT 20", errorMsg)
        
        If errorMsg = "SUCCESS" AndAlso ds IsNot Nothing AndAlso ds.Tables.Count > 0 Then
            GridView1.DataSource = ds.Tables(0)
            GridView1.DataBind()
        Else
            lblError.Text = "查詢失敗: " & errorMsg
        End If
    End Sub
End Class

執行更新/刪除範例

Dim errorMsg As String = ""
Dim sql As String = "UPDATE users SET email = 'new@example.com' WHERE id = 1"
Dim rows As Integer = db.ExecuteNonQuery(sql, errorMsg)

If errorMsg = "SUCCESS" AndAlso rows > 0 Then
    lblMsg.Text = "更新成功，影響 " & rows & " 行"
Else
    lblMsg.Text = "更新失敗: " & errorMsg
End If

連接字串關鍵參數

最佳實踐建議

1. 參數化查詢：防 SQL 注入，cmd.Parameters.AddWithValue("@id", id)
2. 連線池：預設開啟，高併發場景調整 MinPoolSize/MaxPoolSize
3. 異步操作：.NET 4.5+ 使用 ExecuteNonQueryAsync、ExecuteReaderAsync
4. 錯誤日誌：記錄 SQL、參數、堆疊追蹤，便於排查
5. 配置外部化：連接字串放 Web.config <connectionStrings>

常見問題排查