WPF+DataGrid+MySQL实现增删改查

1、前台文件代码

<Window x:Class="MySql_Demo.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:MySql_Demo"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="720">
    <Grid>
        <DataGrid x:Name="MySqlDataGrid" HorizontalAlignment="Left" Height="400" Margin="10,10,0,10" HorizontalScrollBarVisibility="Auto" VerticalAlignment="Top" Width="600" AutoGenerateColumns="False" LoadingRow="DataGrid_LoadingRow">
            <DataGrid.Columns>
                <DataGridTextColumn Header="编号" Width="50" IsReadOnly="True" Binding="{Binding Path=id}"/>
                <DataGridTextColumn Header="姓名" Width="100" Binding="{Binding Path=name}"/>
                <DataGridTextColumn Header="年龄" Width="50" Binding="{Binding Path=age}"/>
                <DataGridTextColumn Header="身高" Width="50" Binding="{Binding Path=high}"/>
                <DataGridTextColumn Header="性别" Width="50" Binding="{Binding Path=gender}"/>
                <DataGridTextColumn Header="出生日期" Width="*" Binding="{Binding Path=birthday, StringFormat='yyyy-MM-dd'}"/>
            </DataGrid.Columns>
        </DataGrid>
        <Button x:Name="DeleteButton" Content="删除" Margin="0,10,10,0" VerticalAlignment="Top" Click="DeleteButton_Click" HorizontalAlignment="Right" Width="75"/>
        <Button x:Name="UpdateButton" Content="修改" Margin="0,40,10,0" VerticalAlignment="Top" Click="ModifyButton_Click" HorizontalAlignment="Right" Width="75"/>
        <Button x:Name="InsertButton" Content="插入" Margin="0,70,10,0" VerticalAlignment="Top" Click="InsertButton_Click" HorizontalAlignment="Right" Width="75"/>
    </Grid>
</Window>

2、后台代码

using System.Data;
using System.Windows;
using System.Windows.Controls;
using MySql.Data.MySqlClient;

namespace MySql_Demo
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        string mysqlstr = "Data Source=127.0.0.1;User ID=root;Password=root;DataBase=grafaninfo;Charset=utf8;";
        MySqlConnection mysqlcon;
        MySqlDataAdapter mysqladapter;
        DataTable mysqldataTable;
        MySqlCommand mysqlcmd;


        public MainWindow()
        {
            InitializeComponent();
            UpdateMySqlData();
        }

        private void UpdateMySqlData()
        {
            mysqlcon = new MySqlConnection(mysqlstr);
            
            try
            {
                string upsql = "select * from students";
                mysqlcon.Open();
                mysqlcmd = new MySqlCommand(upsql, mysqlcon);
                mysqldataTable = new DataTable();
                mysqladapter = new MySqlDataAdapter(mysqlcmd);
                mysqladapter.Fill(mysqldataTable);
                MySqlDataGrid.ItemsSource = mysqldataTable.DefaultView;

            }
            catch(MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if(mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                }
            }
        }

        private void DeleteButton_Click(object sender, RoutedEventArgs e)
        {
            int selectIndex = MySqlDataGrid.SelectedIndex;
            if (selectIndex == -1)
            {
                return;
            }

            mysqlcon = new MySqlConnection(mysqlstr);
            try
            {
                mysqlcon.Open();
                mysqlcmd = mysqlcon.CreateCommand();
                mysqlcmd.CommandText = "delete from students where id = @stuid";
                mysqlcmd.Parameters.AddWithValue("@stuid", mysqldataTable.Rows[selectIndex]["id"]);
                mysqlcmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                    UpdateMySqlData();
                }
            }
        }

        private void ModifyButton_Click(object sender, RoutedEventArgs e)
        {           
            int selectIndex = MySqlDataGrid.SelectedIndex;
            if (selectIndex == -1)
            {
                return;
            }
            MySqlDataGrid.CommitEdit();

            mysqlcon = new MySqlConnection(mysqlstr);
            try
            {
                mysqlcon.Open();
                mysqlcmd = mysqlcon.CreateCommand();
                mysqlcmd.CommandText = "update students set name = @stuname, age = @stuage, high = @stuhigh, gender = @studgender, birthday = @stubirthday where id = @stuid";
                mysqlcmd.Parameters.AddWithValue("@stuname", mysqldataTable.Rows[selectIndex]["name"]);
                mysqlcmd.Parameters.AddWithValue("@stuage", mysqldataTable.Rows[selectIndex]["age"]);
                mysqlcmd.Parameters.AddWithValue("@stuhigh", mysqldataTable.Rows[selectIndex]["high"]);
                _ = mysqlcmd.Parameters.AddWithValue("@studgender", mysqldataTable.Rows[selectIndex]["gender"]);
                _ = mysqlcmd.Parameters.AddWithValue("@stubirthday", mysqldataTable.Rows[selectIndex]["birthday"]);
                mysqlcmd.Parameters.AddWithValue("@stuid", mysqldataTable.Rows[selectIndex]["id"]);
                mysqlcmd.ExecuteNonQuery();                
            }
            catch (MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                    UpdateMySqlData();
                }
            }
        }

        private void InsertButton_Click(object sender, RoutedEventArgs e)
        {
            int selectIndex = MySqlDataGrid.SelectedIndex;
            if (selectIndex == -1)
            {
                return;
            }
            MySqlDataGrid.CommitEdit();

            mysqlcon = new MySqlConnection(mysqlstr);
            try
            {
                mysqlcon.Open();
                mysqlcmd = mysqlcon.CreateCommand();
                mysqlcmd.CommandText = "insert into students(id, name, age, high, gender, birthday) values(@stuid, @stuname, @stuage, @stuhigh, @stugender, @stubirthday)";
                mysqlcmd.Parameters.AddWithValue("@stuname", mysqldataTable.Rows[selectIndex]["name"]);
                mysqlcmd.Parameters.AddWithValue("@stuage", mysqldataTable.Rows[selectIndex]["age"]);
                mysqlcmd.Parameters.AddWithValue("@stuhigh", mysqldataTable.Rows[selectIndex]["high"]);
                mysqlcmd.Parameters.AddWithValue("@stugender", mysqldataTable.Rows[selectIndex]["gender"]);
                mysqlcmd.Parameters.AddWithValue("@stubirthday", mysqldataTable.Rows[selectIndex]["birthday"]);
                mysqlcmd.Parameters.AddWithValue("@stuid", null);
                mysqlcmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                    UpdateMySqlData();
                }
            }
        }

        private void DataGrid_LoadingRow(object sender, DataGridRowEventArgs e)
        {
            e.Row.Header = e.Row.GetIndex() + 1;  //
        }
    }
}

*************************************************************************************