vb操作mysql数据库代码

Imports MySql.Data.MySqlClient

Public Class Form1
    Dim connectionString As String = "server=localhost;port=3306;user=root;password=123456;database=testdb"

    Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
        Dim connection As MySqlConnection = New MySqlConnection(connectionString)
        Try
            connection.Open()
            MessageBox.Show("Connected to MySQL Database")
            connection.Close()
        Catch ex As Exception
            MessageBox.Show("Failed to connect: " + ex.Message)
        Finally
            connection.Dispose()
        End Try
    End Sub

    Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
        Dim connection As MySqlConnection = New MySqlConnection(connectionString)
        Try
            connection.Open()
            Dim query As String = "INSERT INTO users (username, password) VALUES (@username, @password)"
            Dim command As MySqlCommand = New MySqlCommand(query, connection)
            command.Parameters.AddWithValue("@username", txtUsername.Text)
            command.Parameters.AddWithValue("@password", txtPassword.Text)
            command.ExecuteNonQuery()
            MessageBox.Show("Data inserted successfully")
        Catch ex As Exception
            MessageBox.Show("Failed to insert data: " + ex.Message)
        Finally
            connection.Close()
            connection.Dispose()
        End Try
    End Sub

    Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
        Dim connection As MySqlConnection = New MySqlConnection(connectionString)
        Try
            connection.Open()
            Dim query As String = "SELECT * FROM users"
            Dim adapter As MySqlDataAdapter = New MySqlDataAdapter(query, connection)
            Dim dataSet As DataSet = New DataSet()
            adapter.Fill(dataSet, "users")
            dgvUsers.DataSource = dataSet.Tables("users")
        Catch ex As Exception
            MessageBox.Show("Failed to retrieve data: " + ex.Message)
        Finally
            connection.Close()
            connection.Dispose()
        End Try
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim connection As MySqlConnection = New MySqlConnection(connectionString)
        Try
            connection.Open()
            Dim query As String = "UPDATE users SET password = @password WHERE username = @username"
            Dim command As MySqlCommand = New MySqlCommand(query, connection)
            command.Parameters.AddWithValue("@password", txtNewPassword.Text)
            command.Parameters.AddWithValue("@username", txtUsernameToUpdate.Text)
            command.ExecuteNonQuery()
            MessageBox.Show("Data updated successfully")
        Catch ex As Exception
            MessageBox.Show("Failed to update data: " + ex.Message)
        Finally
            connection.Close()
            connection.Dispose()
        End Try
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim connection As MySqlConnection = New MySqlConnection(connectionString)
        Try
            connection.Open()
            Dim query As String = "DELETE FROM users WHERE username = @username"
            Dim command As MySqlCommand = New MySqlCommand(query, connection)
            command.Parameters.AddWithValue("@username", txtUsernameToDelete.Text)
            command.ExecuteNonQuery()
            MessageBox.Show("Data deleted successfully")
        Catch ex As Exception
            MessageBox.Show("Failed to delete data: " + ex.Message)
        Finally
            connection.Close()
            connection.Dispose()
        End Try
    End Sub
End Class