WPF DataGrid显示MySQL查询信息，且可删除、修改、插入 （原发布 csdn 2018-10-13 20:07:28）

1、入行好几年了，工作中使用数据库几率很小(传统行业)。借着十一假期回家机会，学习下数据库。

2、初次了解数据库相关知识，如果本文有误，还望告知。

3、本文主要目的，记录下wpf界面显示数据库信息，且可进行删除、修改、插入命令。并反馈数据到mysql。做个记录，以便以后工作中使用到时没个头绪。

4、mysql的基本讲解不再介绍，安装过程什么的，我也是按照网上教程一步一步进行的，假定mysql已安装成功，且新建有数据库，见下图:

废话不多说，直接上代码

界面代码xaml

    <grid>
        <datagrid x:name="datagrid1" horizontalalignment="left" height="400" margin="10,10,0,0" verticalalignment="top" width="537" loadingrow="datagrid_loadingrow">
            <datagrid.columns>
                <datagridtextcolumn header="id" width="50" binding="{binding path=id}"/>
                <datagridtextcolumn header="name" width="*" binding="{binding path=name}"/>
                <datagridtextcolumn header="phone" width="*" binding="{binding path=phone}"/>
                <datagridtextcolumn header="email" width="*" binding="{binding path=email}"/>
            </datagrid.columns>
        </datagrid>
        <button x:name="deletebutton" content="删除" margin="0,40,10,0" verticalalignment="top" click="deletebutton_click" horizontalalignment="right" width="75"/>
        <button x:name="updatebutton" content="修改" margin="0,80,10,0" verticalalignment="top" click="updatebutton_click" horizontalalignment="right" width="75"/>
        <button x:name="insertbutton" content="插入" margin="0,120,10,0" verticalalignment="top" click="insertbutton_click" horizontalalignment="right" width="75"/>
    </grid>

后端代码cs

    public partial class mainwindow : window
    {
        //sqlbulkcopy
        random rd = new random();
        string sqlstr = "data source=127.0.0.1;user id=root;password=root;database=test;charset=utf8;";
        mysql.data.mysqlclient.mysqlconnection con;
        mysql.data.mysqlclient.mysqldataadapter adapter;
        system.data.dataset ds;
        system.data.datatable dt;


        public mainwindow()
        {
            initializecomponent();

            updatemysqldata();
        }

        private void datagrid_loadingrow(object sender, system.windows.controls.datagridroweventargs e)
        {
            e.row.header = e.row.getindex() + 1;
        }

        private void updatemysqldata()
        {
            if (con == null)
            {
                con = new mysql.data.mysqlclient.mysqlconnection(sqlstr);
                con.open();
            }
            if (adapter == null)
            {
                adapter = new mysql.data.mysqlclient.mysqldataadapter("select * from user", con);
            }
            if (ds == null)
            {
                ds = new system.data.dataset();
            }
            ds.clear();
            adapter.fill(ds, "user");
            if (dt == null)
            {
                dt = ds.tables["user"];
            }
            datagrid1.itemssource = dt.defaultview;
        }

        private void deletebutton_click(object sender, routedeventargs e)
        {
            int index = datagrid1.selectedindex;
            if (index == -1) return;
#if mysqlcommand
            string deletesqlcommand = string.format("delete from user where id = '{0}'", dt.rows[index]["id"]);
            mysql.data.mysqlclient.mysqlcommand cmd = new mysql.data.mysqlclient.mysqlcommand(deletesqlcommand, con);
            cmd.executenonquery();

            updatemysqldata();
#else
            dt.rows[index].delete();
            //dt.rows.removeat(index);==dt.rows[index].delete() + dt.acceptchanges()
            mysql.data.mysqlclient.mysqlcommandbuilder builder = new mysql.data.mysqlclient.mysqlcommandbuilder(adapter);
            adapter.update(dt);
            dt.acceptchanges();
#endif
        }

        private void updatebutton_click(object sender, routedeventargs e)
        {
#if mysqlcommand
            int index = datagrid1.selectedindex;
            string updatesqlcommand = string.format("update user set id = '{0}', name = '{1}', phone = '{2}', email = '{3}' where id = '{0}'",
                dt.rows[index]["id"], dt.rows[index]["name"], dt.rows[index]["phone"], dt.rows[index]["email"]);
            mysql.data.mysqlclient.mysqlcommand cmd = new mysql.data.mysqlclient.mysqlcommand(updatesqlcommand, con);
            cmd.executenonquery();

            updatemysqldata();
#else
            mysql.data.mysqlclient.mysqlcommandbuilder builder = new mysql.data.mysqlclient.mysqlcommandbuilder(adapter);
            adapter.update(dt);
            dt.acceptchanges();
#endif
        }

        private void insertbutton_click(object sender, routedeventargs e)
        {
#if mysqlcommand
            string insertsqlcommand = string.format("insert into user(id, name, phone,email) values('{0}','{1}','{2}','{3}')", rd.next(100), "zhangsan", 12332112345, "[e-mail]");
            mysql.data.mysqlclient.mysqlcommand cmd = new mysql.data.mysqlclient.mysqlcommand(insertsqlcommand, con);
            cmd.executenonquery();

            string insertsqlcommand2 = string.format("insert into user(id, name, phone,email) values('{0}','{1}','{2}','{3}')", rd.next(100), "lisi", 12332112345, "[e-mail]");
            mysql.data.mysqlclient.mysqlcommand cmd2 = new mysql.data.mysqlclient.mysqlcommand(insertsqlcommand2, con);
            cmd2.executenonquery();

            updatemysqldata();
#else
            system.data.datarow dr = dt.newrow();
            dr[0] = rd.next(100);
            dr[1] = "zhangsan";
            dr[2] = "12332112345";
            dr[3] = "[e-mail]";
            dt.rows.add(dr);

            system.data.datarow dr2 = dt.newrow();
            dr2[0] = rd.next(100);
            dr2[1] = "lisi";
            dr2[2] = "12332154321";
            dr2[3] = "[e-mail]";
            dt.rows.add(dr2);

            mysql.data.mysqlclient.mysqlcommandbuilder builder = new mysql.data.mysqlclient.mysqlcommandbuilder(adapter);
            adapter.update(ds, "user");
            dt.acceptchanges();
#endif
        }

    }

软件打开界面

删除时一直不失败，网上找了好久才找到答案
参考资料
https://blog.csdn.net/sz101/article/details/5837950
https://bbs.csdn.net/wap/topics/390845652
http://www.cnblogs.com/perfect/archive/2007/08/06/844634.html