C#第四次作业：MySQL数据库及C#操作MySQL数据库

雨点打透心脏的1/2处 2022-08-08 12:46 218阅读 0赞

信管1121，201211671117，聂双燕

**第一部分：作业要求**（完整要求见[作业要求][Link 1]）  
目标2：C\#操作MySQL数据库，包括基本步骤和具体的编码实现（90分）。  
**第二部分：程序说明及代码**  
1．功能：  
1.1主要功能：主界面里包含多个控件，当grades表中已添加数据时候，datagridView控件显示grades表的数据，具体功能如主界面图所示；  
1.2数据录入功能：根据要求在Mysql中创建三个表（studentss、gradess、students）,并把文件List\_Class1\_Submit.xls、Grade\_FirstExercise.xls、和List\_Class1.txt的内容分别一次性插入对应的数据表中；  
1.3查询功能：查找所有 出现在“List\_Class1”（表studentss）中，未出现在“List\_Class1\_Submit”（表students） 的学生名单（姓名）保存到D:/name.txt文件中并显示在datagridView控件中；  
1.4修改功能：通过选中datagridView控件要修改的行数据来修改表grades中的一条记录，单击“刷新”按钮进行刷新并查看修改是否成功；  
1.5添加功能：通过多次按“添加”按钮向grades表添加多条记录，单击“刷新”按钮进行刷新并查看添加是否成功；  
1.6删除功能：通过选中datagridView控件的一条或者多条记录，一次性删除grades表一条或者多条记录，单击“刷新”按钮进行刷新并查看删除是否成功；

2.主要实现代码：  
2.1类Connector：主要封装访问Mysql数据库的方法

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using MySql.Data.MySqlClient;
    using System.Data;
    
    namespace mysqlToFile
    {
        class Connector
        {
            /// <summary>
            /// 建立MySql数据库连接.
            /// </summary>
            /// <returns>返回MySqlConnection对象</returns>
            public MySqlConnection getmysqlcon()
            {
                string M_str_sqlcon = "server=127.0.0.1;user=root;database=tt;port=3306;password=;"; //根据自己的设置
                MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
                return myCon;
            }
    
            // 执行MySqlCommand命令
            /// <summary>
            /// 执行MySqlCommand命令
            /// </summary>
            /// <param name="M_str_sqlstr">SQL语句</param>
            public void getmysqlcom(string M_str_sqlstr)
            {
                MySqlConnection mysqlcon = this.getmysqlcon();
                mysqlcon.Open();
                MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
                MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
                mysqlcom.ExecuteNonQuery();
                mysqlcom.Dispose();
                mysqlcon.Close();
                mysqlcon.Dispose();
            }
    
            /// <summary>
            /// 创建一个DataTable对象,以便用于控制台及dataGridView控件输出表的内容
            /// </summary>
            /// <param name="M_str_sqlstr">SQL语句</param>
            /// <returns>返回MySqlDataReader对象</returns>
            public DataTable getsqlread(string M_str_sqlstr)
            {
                MySqlConnection mysqlcon = this.getmysqlcon();
                mysqlcon.Open();
                MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
                MySqlDataAdapter mda = new MySqlDataAdapter(M_str_sqlstr, mysqlcon);
                DataTable dt = new DataTable();
                mda.Fill(dt);
                return dt;
            }
    
    
        }
    }

2.2类MainOpereteForm：主窗体类，主要包含各种功能的按钮触发事件

using MySql.Data.MySqlClient;
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using System.Drawing;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace mysqlToFile
    {
        public partial class MainOpereteForm : Form
        {
            public MainOpereteForm()
            {
                InitializeComponent();
            }
    
            Connector connector = new Connector();
    
            /// <summary>
            /// 读取excel文件内容并存放在DataSet中.
            /// </summary>
            /// <returns>返回DataSet对象</returns>
            public DataSet ExcelToDS(string path)
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
                    + "Data Source=" + @path + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = "select * from [sheet1$]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                DataTable table1 = new DataTable();
                ds = new DataSet();
                myCommand.Fill(table1);
                myCommand.Fill(ds);
                return ds;
            }
    
            private void btnInsertXls_Click(object sender, EventArgs e)
            {
                InsertXls();
            }
    
            /// <summary>
            /// 读取List_Class1_Submit.xls文件的内容并一次性把内容插入到Mysql数据库tt的students表中.
            /// </summary>
            private void InsertXls()
            {
                OpenFileDialog openfile = new OpenFileDialog();
                openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*|工作薄(*.xlsx)|*.xlsx";
                if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
                {
                    DataSet dataSet = ExcelToDS(openfile.FileName);
                    String sqlSearch = "select * from students";
                    int count = 0;
                    string[] str = new string[4];
                    string sql;
                    foreach (DataTable table in dataSet.Tables)
                    {
                        foreach (DataRow row in table.Rows)
                        {
                            count = 0;
                            for (int i = 0; i < 4; i++)
                            {
                                str[i] = "*";
                            }
                            foreach (DataColumn column in table.Columns)
                            {
                                if (row[column] != null)
                                    str[count] = row[column].ToString();
                                count++;
                            }
                            sql = string.Format("insert into students values('{0}','{1}','{2}','{3}')",
                                str[0], str[1], str[2], str[3]);
                            if (!str[0].Equals("*"))
                            {
                                connector.getmysqlcom(sql);
                            }
                        }
                    }
                    Reflash(sqlSearch);
                }
            }
    
            private void btnInsertTxt_Click(object sender, EventArgs e)
            {
                InsertTxt();
    
            }
    
            /// <summary>
            /// 读取List_Class1.txt文件的内容并一次性把内容插入到Mysql数据库tt的studentss表中.
            /// </summary>
            private void InsertTxt()
            {
                OpenFileDialog openfile = new OpenFileDialog();
                openfile.Filter = "文本文件(*.txt)|*.txt|所有文件(*.*)|*.*|word文档(*.doc)|*.doc";
                if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
                {
                    String sqlSearch = "select * from studentss";
                    string[] str = new string[5];
                    string sql;
                    using (StreamReader sr = new StreamReader(openfile.FileName, Encoding.Default, false))
                    {
                        string line;
                        while ((line = sr.ReadLine()) != null)
                        {
                            line.Trim();
                            str = line.Split(new char[] { ' ' });
                            sql = string.Format("insert into studentss values('{0}','{1}','{2}','{3}','{4}')",
                                    str[0], str[1], str[2], str[3], str[4]);
                            connector.getmysqlcom(sql);
                        }
                    }
                    Reflash(sqlSearch);
                }
            }
    
            private void selectBtn_Click(object sender, EventArgs e)
            {
                String sqlSearch = "select studentss.stuName as 未提交作业网址学生 from studentss "+
                    "where studentss.stuNo not in (select students.stuNo from students)";
                Reflash(sqlSearch);
            }
    
            /// <summary>
            /// 读取Grade_FirstExercise.xls文件的内容并一次性把内容插入到Mysql数据库tt的grades表中.
            /// </summary>
            private void btnInsertGrade_Click(object sender, EventArgs e)
            {
                OpenFileDialog openfile = new OpenFileDialog();
                openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*|工作薄(*.xlsx)|*.xlsx";
                if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
                {
                    DataSet dataSet = ExcelToDS(openfile.FileName);
                    String sqlSearch = "select * from grades";
                    int count = 0;
                    double grade = 0;
                    string[] str = new string[3];
                    string sql;
                    foreach (DataTable table in dataSet.Tables)
                        {
                            foreach (DataRow row in table.Rows)
                            {
                                for (int i = 0; i < 3; i++)
                                {
                                    str[i] = "*";
                                }
                                foreach (DataColumn column in table.Columns)
                                {
                                    if (column.ColumnName == "学号")
                                    {
                                        str[0] = row[column].ToString();
                                    }
                                    if (column.ColumnName == "互评学生姓名")
                                    {
                                        str[1] = row[column].ToString();
                                    }
                                    count = 1;
                                    if (column.ColumnName == "互评学生分数")
                                    {
                                        str[2] = row[column].ToString();
                                        grade = Convert.ToDouble(str[2]);
                                    }
                                }
                                sql = string.Format("insert into grades values('{0}','{1}',{2},{3})",
                                    str[0], str[1], count, grade);
                                if (!str[0].Equals("*") && !str[2].Equals("*"))
                                {
                                    connector.getmysqlcom(sql);
                                }
                            }
                        }
                        Reflash(sqlSearch);
    
                }
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                int line = dataGridView1.SelectedRows.Count;
                if (line == 1)
                {
                    Update update = new Update();
                    update.Show();
                    string[] strs = new string[4];
                    for (int i = 0; i < 4; i++)
                    {
                        strs[i] = dataGridView1.CurrentRow.Cells[i].Value.ToString();
                    }
                    update.addText(strs[0], strs[1], strs[2], strs[3]);
                }
                else
                {
                    MessageBox.Show("请选择需要修改那行的学号", "系统提示");
                }
    
            }
    
            private void MainOpereteForm_Load(object sender, EventArgs e)
            {
                string sqlSearch = "select stuNo as 学号,stuName as 互评学生姓名,homeworkNo as " +
                       "作业次数,grade as 互评学生分数 from grades";
                Reflash(sqlSearch);
            }
    
            /// <summary>
            /// 没次执行完sql语句后的刷新操作.
            /// </summary>
            private void Reflash(string sqlSearch)
            {
                DataTable dataTable = connector.getsqlread(sqlSearch);
                dataGridView1.DataSource = dataTable;
            }
    
            private void button4_Click(object sender, EventArgs e)
            {
                Insert insert = new Insert();
                insert.Show();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
    
                if (dataGridView1.DataSource == null || dataGridView1.CurrentRow == null)
                {
                    return;
                }
                else
                {
                    if (this.dataGridView1.SelectedRows.Count > 0)
                    {
                        DialogResult dr = MessageBox.Show("确定删除选中的记录? ", "提示",
                            MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                        if (dr == DialogResult.OK)
                        {
                            try
                            {
                                string sql = "";
                                //遍历所选中的dataGridView记录行 
                                foreach (DataGridViewRow row in this.dataGridView1.SelectedRows)
                                {
                                    //取dataGridView1中的第三列的值 
                                    string strNo = row.Cells[0].Value.ToString();
                                    sql = "delete from grades where stuNo='" + strNo + "'";
                                    connector.getmysqlcom(sql);
                                }
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.ToString(), "提示");
                            }
                        }
                        else
                        {
                            return;
                        }
                    }
                }
            }
    
            private void btnFlash_Click(object sender, EventArgs e)
            {
                string sqlSearch = "select stuNo as 学号,stuName as 互评学生姓名,homeworkNo as " +
                       "作业次数,grade as 互评学生分数 from grades";
                Reflash(sqlSearch);
            }
        }
    
    }

2.3类Update:具体修改数据窗体

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;
    
    namespace mysqlToFile
    {
        public partial class Update : Form
        {
            Connector connector = new Connector();
            public Update()
            {
                InitializeComponent();
            }
    
            public void addText(string strNo, string strName, string i, string grade)
            {
                textBox1.Text = strNo;
                textBox2.Text = strName;
                textBox3.Text = i;
                textBox4.Text = grade;
                textBox1.ReadOnly = true;
            }
    
            private void btnNo_Click(object sender, EventArgs e)
            {
                this.Visible = false;
            }
    
            private void btnYes_Click_1(object sender, EventArgs e)
            {
                string strNo = textBox1.Text.ToString();
                string strName = textBox2.Text.ToString();
                int classs = Convert.ToInt32(textBox3.Text.ToString());
                double grades = Convert.ToDouble(textBox4.Text.ToString());
                string sql = "update grades set stuName='" + strName + "'," + 
                    "homeworkNo=" + classs + "," + "grade=" +
                    grades + " where stuNo='" + strNo + "'";
                connector.getmysqlcom(sql);
                this.Visible = false;
            }
        }
    }

2.4类Insert:具体插入数据窗体

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;
    
    namespace mysqlToFile
    {
        public partial class Insert : Form
        {
            Connector connector = new Connector();
            public Insert()
            {
                InitializeComponent();
            }
    
            private void btnNo_Click(object sender, EventArgs e)
            {
                this.Visible = false;
            }
    
            private void btnYes_Click(object sender, EventArgs e)
            {
                string strNo = textBox1.Text.ToString();
                string strName = textBox2.Text.ToString();
                int classs = Convert.ToInt32(textBox3.Text.ToString());
                double grades = Convert.ToDouble(textBox4.Text.ToString());
                string sql = "insert into grades values('" + strNo + "','" + strName + "',"+classs +","+grades+");";
                connector.getmysqlcom(sql);
                this.Visible = false;
            }
        }
    }

3参考资料链接：  
3.1[Winform中DataGridView多行删除][Winform_DataGridView]

**第三部分：运行结果**  
1.批量录入数据后的主界面功能图：  
![这里写图片描述][20150517152039777]

2.查询功能：  
![这里写图片描述][20150517152112690]

3.修改功能：  
![这里写图片描述][20150517152238786]  
![这里写图片描述][20150517152358631]

4.添加功能：  
![这里写图片描述][20150517152443793]

5.删除功能：  
![这里写图片描述][20150517152506538]  
![这里写图片描述][20150517152438984]  
![这里写图片描述][20150517152554789]

**第四部分：收获体会**  
目前自己还是只能简单操作数据库，还要好好努力。在写代码的过程中自己也遇到了很多问题，都是一步步在网上寻找需要的答案，其实遇到问题不可怕，可怕的是自己没有欲望去解决。

[Link 1]: http://user.qzone.qq.com/303727350/blog/1430966678
[Winform_DataGridView]: http://blog.csdn.net/xingqingxiang/article/details/7168267
[20150517152039777]: /images/20220805/33324272873043c2a479626a52fcc0ae.png
[20150517152112690]: /images/20220805/1eb14e5ac9854b4ea234a6e26fe31564.png
[20150517152238786]: /images/20220805/98cdaad6977a4db0a9fbee67246aa1a0.png
[20150517152358631]: /images/20220805/b5cc94363e444be382853d862a3aff8a.png
[20150517152443793]: /images/20220805/8ec84a93c08f4c4db2ef28a216eb757b.png
[20150517152506538]: /images/20220805/3b1090e7fe8c40e987520fc039f18c0c.png
[20150517152438984]: /images/20220805/37fb6fa8cd4e42f8b0b94b5fe12d4077.png
[20150517152554789]: /images/20220805/4d521c625f264c7fb97dbe467b74bae0.png