欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-09 17:29:46
1、入行好几年了,工作中使用数据库几率很小(传统行业)。借着十一假期回家机会,学习下数据库。 2、初次了解数据库相关知识,如果本文有误,还望告知。 3、本文主要目的,记录下wpf界面显示数据库信息,且可进行删除、修改、插入命令。并反馈数据到MySQL。做个记录,以便以后工作中使用到时没个头绪。 4、 ......

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

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

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

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

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

废话不多说,直接上代码

界面代码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, "zhangsan@qq.com");
            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, "lisi@yahoo.com");
            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] = "zhangsan@qq.com";
            dt.rows.add(dr);

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

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

    }

软件打开界面
WPF DataGrid显示MySQL查询信息,且可删除、修改、插入 (原发布 csdn 2018-10-13 20:07:28)

删除时一直不失败,网上找了好久才找到答案
参考资料
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