C#窗体实现MysqlBean封装和DataGridView数据源绑定（学习笔记）

MysqlBean封装

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

namespace WindowsFormsApp1
{
    class MysqlBase
    {
        private MySqlConnection conn = null;
        private MySqlCommand command = null;
        private MySqlDataReader reader = null;
        private static string  connstr = "Database=ssm;Data Source=localhost;User Id=root;Password=root;pooling=false;CharSet=utf8;port=3306;";
        /// <summary>
        /// 构造方法里建议连接
        /// </summary>
        /// <param name="connstr"></param>
        public MysqlBase()
        {
            conn = new MySqlConnection(connstr);
        }
        /// <summary>
        /// 检测数据库连接状态
        /// </summary>
        /// <returns></returns>
        public bool CheckConnectStatus()
        {
            bool result = false;
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    result = true;
                }

            }
            catch
            {
                result = false;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        /// <summary>
        /// 增、删、改公共方法
        /// </summary>
        /// <returns></returns>
        public int commonExecute(string sql)
        {
            int res = -1;
            try
            {
                //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Open();
                command = new MySqlCommand(sql, conn);
                res = command.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
            }
            conn.Close();
            return res;
        }
        /// <summary>
        /// 查询方法
        /// 注意：尽量不要用select * from table表（返回的数据过长时，DataTable可能会出错），最好指定要查询的字段。
        /// </summary>
        /// <returns></returns>
        public DataTable query(string sql)
        {
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            command = new MySqlCommand(sql, conn);
            DataTable dt = new DataTable();
            using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(reader);
            }
            return dt;
        }
        /// <summary>
        /// 获取DataSet数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, string tablename)
        {
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            command = new MySqlCommand(sql, conn);
            DataSet dataset = new DataSet();
            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            adapter.Fill(dataset, tablename);
            conn.Close();
            return dataset;
        }
        /// <summary>
        /// 实现多SQL语句执行的数据库事务
        /// </summary>
        /// <param name="SQLStringList">SQL语句集合（多条语句）</param>
        public bool ExecuteSqlTran(List<string> SQLStringList)
        {
            bool flag = false;
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            MySqlCommand cmd = conn.CreateCommand();
            //开启事务
            MySqlTransaction tran = this.conn.BeginTransaction();
            cmd.Transaction = tran;//将事务应用于CMD
            try
            {
                foreach (string strsql in SQLStringList)
                {
                    if (strsql.Trim() != "")
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tran.Commit();//提交事务（不提交不会回滚错误）
                flag = true;
            }
            catch (Exception)
            {
                tran.Rollback();
                flag = false;
            }
            finally
            {
                conn.Close();
            }
            return flag;
        }

    }
}

登录功能代码

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;

namespace WindowsFormsApp1
{
    public partial class login : Form
    {
        public login()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)  
        {
            try
            {
                if (txtName.Text.Trim() == "")
                {
                    errorUsername.SetError(txtName, "用户名不能为空");
                    txtName.Focus();
                    return;
                }
                else if (txtPassword.Text.Trim() == "")
                {
                    errorPassword.SetError(txtPassword, "密码不能为空");
                    txtPassword.Focus();
                    return;
                }
                else
                {
                    string sqlSel = "select * from user where username = '" + this.txtName.Text + "' and password = '" + this.txtPassword.Text + "'";
                    MysqlBase mysqlBase= new MysqlBase();
                    DataTable dt=mysqlBase.query(sqlSel);
                    if (dt.Rows.Count > 0)
                    {
                        MessageBox.Show("恭喜您已成功登录", "确定", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        test test = new test();
                        this.Hide();
                        test.ShowDialog();
                        Application.ExitThread();
                    }
                    else
                    {
                        MessageBox.Show("用户名或密码错误！");
                    }
                }
               
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString() + "打开数据库失败");
            }
        }
    }
}

test测试用普通方法和封装后的MysqlBean类

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;

namespace WindowsFormsApp1
{
    public partial class test : Form
    {
        MySqlConnection conn; //连接数据库对象
        MySqlDataAdapter mda; //适配器变量
        DataSet ds;  //临时数据集
        public test()
        {
            InitializeComponent();
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            string M_str_sqlcon = "server=localhost;user id=root;password=root;database=myshop";
            conn = new MySqlConnection(M_str_sqlcon);
            try
            {
                conn.Open();
                MessageBox.Show("数据库连接成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = "select * from login";
            mda = new MySqlDataAdapter(sql, conn);
            ds = new DataSet();
            mda.Fill(ds, "login");
            //显示数据
            dataGridView1.DataSource = ds.Tables["login"];
            conn.Close();
        }

        private void 查询_Click(object sender, EventArgs e)
        {
            MysqlBase mysqlBase = new MysqlBase();
            //查询
            string sql = "select * from account";
            dataGridView2.DataSource =mysqlBase.GetDataSet(sql, "account").Tables["account"];
        }
    }
}