c#第四次作业

目的:用C#操作MySQL数据库

代码如下:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data.OleDb;

namespace WindowsFormsApplication12
{
    public partial class Form1 : Form
    {
        public string Query = "";
        public Form1()
        {
            InitializeComponent();
        }

        private void label6_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            Query = "select * from txt";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Query = "select * from xls";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            MySqlConnection myConnection = new MySqlConnection("server=localhost;user id=root;password=123456;database=lq;");
            MySqlCommand myCommand = new MySqlCommand(Query, myConnection);
            DataSet ds1 = new DataSet();
            MySqlDataAdapter adap = new MySqlDataAdapter(myCommand);
            switch (Query)
            {
                case "select * from txt":
                    adap.Fill(ds1);
                    dataGridView1.DataSource = ds1.Tables[0].DefaultView;
                    break;
                case "select * from xls":
                    adap.Fill(ds1);
                    dataGridView2.DataSource = ds1.Tables[0].DefaultView;
                    break;
                default: MessageBox.Show("请选择要操作的表");
                    break;
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                string id = textBox1.Text;
                string sno = textBox2.Text;
                string name = textBox3.Text;
                string Class = textBox4.Text;
                string sex = textBox5.Text;
                string gno = textBox6.Text;
                string website = textBox7.Text;
                string query1 = "set names utf8";
                string query2 = "insert into txt values(" + id + ",'" + sno + "','" + name + "','" + Class + "','" + sex + "')";
                string query3 = "insert into xls values(" + id + ",'" + sno + "','" + name + "'," + gno + ",'" + website + "')";
                MySqlConnection myConnection = new MySqlConnection("server=localhost;user id=root;password=123456;database=lq;");
                MySqlCommand myCommand = new MySqlCommand(Query, myConnection);
                MySqlCommand myCommand1 = new MySqlCommand(query1, myConnection);
                MySqlCommand myCommand2 = new MySqlCommand(query2, myConnection);
                MySqlCommand myCommand3 = new MySqlCommand(query3, myConnection);
                DataSet ds1 = new DataSet();
                MySqlDataAdapter adap = new MySqlDataAdapter(myCommand);
                MySqlDataAdapter adap1 = new MySqlDataAdapter(myCommand1);
                MySqlDataAdapter adap2 = new MySqlDataAdapter(myCommand2);
                MySqlDataAdapter adap3 = new MySqlDataAdapter(myCommand3);
                switch (Query)
                {
                    case "select * from txt":
                        adap1.Fill(ds1);
                        adap2.Fill(ds1);
                        adap.Fill(ds1);
                        dataGridView1.DataSource = ds1.Tables[0].DefaultView;
                        break;
                    case "select * from xls":
                        adap1.Fill(ds1);
                        adap3.Fill(ds1);
                        adap.Fill(ds1);
                        dataGridView2.DataSource = ds1.Tables[0].DefaultView;
                        break;
                    default: MessageBox.Show("请选择要操作的表");
                        break;
                }
            }
            catch
            {
                MessageBox.Show("插入有误!");
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            try
            {
                string id = textBox1.Text;
                string query1 = "delete from txt where id=" + id;
                string query2 = "delete from xls where id=" + id;
                MySqlConnection myConnection = new MySqlConnection("server=localhost;user id=root;password=123456;database=lq;");
                MySqlCommand myCommand = new MySqlCommand(Query, myConnection);
                MySqlCommand myCommand1 = new MySqlCommand(query1, myConnection);
                MySqlCommand myCommand2 = new MySqlCommand(query2, myConnection);
                DataSet ds1 = new DataSet();
                MySqlDataAdapter adap = new MySqlDataAdapter(myCommand);
                MySqlDataAdapter adap1 = new MySqlDataAdapter(myCommand1);
                MySqlDataAdapter adap2 = new MySqlDataAdapter(myCommand2);
                switch (Query)
                {
                    case "select * from txt":
                        adap1.Fill(ds1);
                        adap.Fill(ds1);
                        dataGridView1.DataSource = ds1.Tables[0].DefaultView;
                        break;
                    case "select * from xls":
                        adap2.Fill(ds1);
                        adap.Fill(ds1);
                        dataGridView2.DataSource = ds1.Tables[0].DefaultView;
                        break;
                    default: MessageBox.Show("请选择要操作的表");
                        break;
                }
            }
            catch
            {
                MessageBox.Show("删除有误!");
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            try
            {
                int i = 0;
                string query = "";
                string query1 = "set names utf8";
                string id = textBox1.Text;
                switch (Query)
                {
                    case "select * from txt":
                        query = "update txt set ";
                        if (checkBox2.Checked)
                        {
                            i++;
                            query = query + "sno=" + "'" + textBox2.Text + "'";
                        }
                        if (checkBox3.Checked)
                        {
                            i++;
                            if (i == 2)
                            {
                                query = query + ",";
                                i = 1;
                            }
                            query = query + "name=" + "'" + textBox3.Text + "'";
                        }
                        if (checkBox4.Checked)
                        {
                            i++;
                            if (i == 2)
                            {
                                query = query + ",";
                                i = 1;
                            }
                            query = query + "class=" + "'" + textBox4.Text + "'";
                        }
                        if (checkBox5.Checked)
                        {
                            i++;
                            if (i == 2)
                            {
                                query = query + ",";
                                i = 1;
                            }
                            query = query + "sex=" + "'" + textBox5.Text + "'";
                        }
                        query = query +" "+"where id=" + textBox1.Text;
                        break;

                    case "select * from xls":
                        query = "update xls set ";
                        if (checkBox2.Checked)
                        {
                            i++;
                            query = query + "sno=" + "'" + textBox2.Text + "'";
                        }
                        if (checkBox3.Checked)
                        {
                            i++;
                            if (i == 2)
                            {
                                query = query + ",";
                                i = 1;
                            }
                            query = query + "name=" + "'" + textBox3.Text + "'";
                        }
                        if (checkBox6.Checked)
                        {
                            i++;
                            if (i == 2)
                            {
                                query = query + ",";
                                i = 1;
                            }
                            query = query + "gno=" + textBox6.Text;
                        }
                        if (checkBox7.Checked)
                        {
                            i++;
                            if (i == 2)
                            {
                                query = query + ",";
                                i = 1;
                            }
                            query = query + "website=" + "'" + textBox7.Text + "'";
                        }
                        query = query +" "+"where id=" + textBox1.Text;
                        break;

                    default: MessageBox.Show("请选择要操作的表");
                        break;
                }
                MySqlConnection myConnection = new MySqlConnection("server=localhost;user id=root;password=123456;database=lq;");
                MySqlCommand myCommand = new MySqlCommand(Query, myConnection);
                MySqlCommand myCommand1 = new MySqlCommand(query, myConnection);
                MySqlCommand myCommand2 = new MySqlCommand(query1, myConnection);
                DataSet ds1 = new DataSet();
                MySqlDataAdapter adap = new MySqlDataAdapter(myCommand);
                MySqlDataAdapter adap1 = new MySqlDataAdapter(myCommand1);
                MySqlDataAdapter adap2 = new MySqlDataAdapter(myCommand2);
                switch (Query)
                {
                    case "select * from txt":
                        adap2.Fill(ds1);
                        adap1.Fill(ds1);
                        adap.Fill(ds1);
                        dataGridView1.DataSource = ds1.Tables[0].DefaultView;
                        break;
                    case "select * from xls":
                        adap2.Fill(ds1);
                        adap1.Fill(ds1);
                        adap.Fill(ds1);
                        dataGridView2.DataSource = ds1.Tables[0].DefaultView;
                        break;
                    default: MessageBox.Show("请选择要操作的表");
                        break;
                }
            }
            catch
            {
                MessageBox.Show("修改有误!");
            }
        }



        private void label1_Click(object sender, EventArgs e)
        {

        }
    }
}

结果截图

打开程序

显示txt 和xls表的内容

插入功能

删除功能

指定删除表中特定id的数据

然后刚才插入的那条数据就没了

修改功能

指定修改表中特定id的数据,并且在你要修改的数据前面打勾

例如修改下图id为29的数据

请在选择了你想操作的表后再点击操作功能按钮,否则会弹出警告