使用c#连接mysql的简单学生管理系统

寒假的时候在家无聊的时候做的,主要是使用c#连接mysql数据库所作的一个简单的学生管理系统.今天无意中翻了出来,于是就贴出来分享给大家,就当作是抛砖引玉了.
首先要使用csharp能够成功的连接mysql数据库,我以前写了一篇文章,请点击下面的传送门
c#使用vs2010连接mysql数据库的增删改查操作

一.表结构

数据库名是student,然后表名是students,具体如下图所示:

mysql

mysql

二.程序

1.程序主界面

zhujiemian

zhujiemian

然后是代码,首先是Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using MySql.Data;

namespace student_contral_system
{
    static class Program
    {
        ///
/// 应用程序的主入口点。
        ///
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);

            string mysql_str = "Database=student;Data Source = 192.168.142.129;User Id=root;password=mysqlpass;
charset=gbk;port=3306";
            try
            {
               MySqlConnection mysql = new MySqlConnection(mysql_str);
                mysql.Open();
                Application.Run(new Form1(mysql));
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error! "+ex.Message);
            }
        }
    }
}

然后是Form1.cs,也就是主界面的代码

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

namespace student_contral_system
{
    public partial class Form1 : Form
    {
        public MySqlConnection mysql;
        public Form1(MySqlConnection mysqltest)
        {
            InitializeComponent();
            mysql = mysqltest;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
    //        string mysql_str = "Database=student;Data Source = 192.168.142.129;User Id=root;password=cll19900714;charset=gbk;port=3306";
      //      MySqlConnection mysql = new MySqlConnection(mysql_str);

            string sqlSearch = "select * from students";
            MySqlCommand comm = new MySqlCommand(sqlSearch, mysql);
            output.ResetText();
            try
            {
        //        mysql.Open();
                MySqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        output.Text += "姓名:  " + reader.GetString(1) + "  编号  " + reader.GetInt32(0) + "  地址  " + reader.GetString(2) + "\n";
                    }
                }
                reader.Close();

            }
            catch (MySqlException ex)
            {
                output.Text += ex.Message;
            }

        }

        private void get_delete_Click(object sender, EventArgs e)
        {
            Form2 f = new Form2(mysql);
            f.Show();
        }

        private void get_select_Click(object sender, EventArgs e)
        {
            this.Form1_Load(sender,e);
        }

        private void get_add_Click(object sender, EventArgs e)
        {
            Form3 f = new Form3(mysql);
            f.Show();
        }

        private void get_modify_Click(object sender, EventArgs e)
        {
            Form4 f = new Form4(mysql);
            f.Show();
        }

        private void selected_Click(object sender, EventArgs e)
        {
            if (getid.Text == "")
            {
                MessageBox.Show("ID不能为空!");
            }
            else
            {
                int the_id = Convert.ToInt32(getid.Text);
                string sqlselect = "select * from students where id = " + the_id;
                MySqlCommand comm = new MySqlCommand(sqlselect, mysql);
                MySqlDataReader reader = comm.ExecuteReader();
                //      reader.Read();

                if (!reader.Read())
                {
                    MessageBox.Show("id错误!");
                    //     this.Close();
                }
                else
                {
                    output.ResetText();
                    output.Text += "姓名:  " + reader.GetString(1) + "  编号  " + reader.GetInt32(0) + "  地址  " + reader.GetString(2) + "\n";
                }

                reader.Close();
            }

        }

        private void tuichuToolStripMenuItem_Click(object sender, EventArgs e)
        {
            mysql.Close();
            this.Close();
        }

        private void guanyuToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            AboutBox1 about = new AboutBox1();
            about.Show();
        }

        private void wenjianToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void 关于作者ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form5 f = new Form5();
            f.Show();
        }

        private void getid_TextChanged(object sender, EventArgs e)
        {

        }

    }
}

2.删除界面

删除界面如下图

delete

delete

然后是代码,也就是Form2.cs

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

namespace student_contral_system
{
    public partial class Form2 : Form
    {
        public MySqlConnection mysql;
        public Form2(MySqlConnection mysqltest)
        {
            InitializeComponent();
            mysql = mysqltest;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (delete_id.Text == "")
            {
                MessageBox.Show("ID不能为空!");
            }
            else
            {
                string sql = "delete from students where id = " + delete_id.Text;
                string select = "select * from students where id = " + delete_id.Text;
                MySqlCommand comm_test = new MySqlCommand(select, mysql);
                MySqlDataReader reader = comm_test.ExecuteReader();
                if (!reader.Read())
                {
                    MessageBox.Show("ID错误!");
                    reader.Close();
                }

                else
                {
                    reader.Close();
                    try
                    {
                        MySqlCommand comm = new MySqlCommand(sql, mysql);
                        comm.ExecuteNonQuery();
                        MessageBox.Show("删除成功!");
                    }
                    catch (MySqlException ex)
                    {
                        MessageBox.Show("Error!" + ex.Message);
                    }
                    //    reader.Close();
                    this.Close();
                }

                reader.Close();
            }
        }
    }
}

3.添加界面

添加界面如下图所示

add

add

然后是代码,也就是Form3.cs

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

namespace student_contral_system
{
    public partial class Form3 : Form
    {
        public MySqlConnection mysql;
        public Form3(MySqlConnection mysqltest)
        {
            InitializeComponent();
            mysql = mysqltest;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (setid.Text == "" || setname.Text == "" || setaddress.Text == "")
            {
                MessageBox.Show("ID,姓名,地址都不能为空!");
            }
            else
            {
                int id = Convert.ToInt32(setid.Text);
                string name = setname.Text;
                string address = setaddress.Text;
                // string text = "insert into student(name,id,address) values('测试',122,'北京')";
                string sql = "insert into students(id,name,address) values('" + id + "','" + name + "','" + address + "')";
                try
                {
                    MySqlCommand comm = new MySqlCommand(sql, mysql);
                    comm.ExecuteNonQuery();
                    MessageBox.Show(sql);
                    MessageBox.Show("添加成功!");
                    this.Close();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Error! " + ex.Message);
                }

            }
        }
    }
}

4.修改界面

修改界面如下图所示

modify

modify

然后是代码,也就是Form4.cs

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

namespace student_contral_system
{
    public partial class Form4 : Form
    {
        public MySqlConnection mysql;
        public Form4(MySqlConnection mysqltest)
        {
            InitializeComponent();
            mysql = mysqltest;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (getid.Text == "")
            {
                MessageBox.Show("ID不能为空!");
            }
            else
            {
                int id = Convert.ToInt32(getid.Text);
                string sqlselect = "select * from students where id = " + id;
                MySqlCommand comm = new MySqlCommand(sqlselect, mysql);
                MySqlDataReader reader = comm.ExecuteReader();
                //      reader.Read();

                if (!reader.Read())
                {
                    MessageBox.Show("id错误!");
                    this.Close();
                }
                else
                {
                    name.Text = reader.GetString(1);
                    address.Text = reader.GetString(2);
                    button2.Visible = true;
                }

                reader.Close();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (getid.Text == "" || name.Text == "" || address.Text == "")
            {
                MessageBox.Show("ID,姓名,地址都不能为空!");
            }
            else
            {
                string modify_name = name.Text;
                string modify_address = address.Text;
                int id = Convert.ToInt32(getid.Text);
                string sql = "update students set name = '" + modify_name + "', address = '" + modify_address + "' where id = " + id;
           //     MessageBox.Show(sql);
                try
                {
                    MySqlCommand comm = new MySqlCommand(sql, mysql);
                    comm.ExecuteNonQuery();
                    MessageBox.Show("修改成功!");
                    this.Close();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Error! " + ex.Message);
                }
            }
        }
    }
}

这里说明一下,当你输入正确的要修改的id,然后点击确定之后,才会出现下面的从数据库中查找出的信息以及修改按钮,如果输入的id没有在数据库中查找到的话是不会出现下面的信息以及修改按钮的.
好了.差不多就这样吧,主界面上面的菜单栏是我无聊加上去的,代码就不贴了.
以前看一个范例的时候发现在做每次查询的时候都要新建一个MySqlConnection,但是经过我测试发现程序运行会非常慢,因为你所做的每次操作都会新建一个MySqlConnection来连接数据库,所以我做了一个修改,就是在程序的主入口,也就是Program.cs里面创建一个MySqlConnection,然后后面的操作都是通过参数传递来传递过去的,这样操作数据库的话就没必要每次都创建一个MySqlConnection.这样就不会降低程序的运行速度.
本人也是初学csharp,不到之处还希望大家能够指正.谢谢了!

发表评论

电子邮件地址不会被公开。 必填项已用*标注