C#实现类似navicat一样操作MySQL数据库的界面（MyBatis逆向工程思路）

 

在我之前学习到java数据库框架mybatis逆向工程时惊叹了一会儿，想了想这个思路理应早该想到。我们使用navicat工具创建了数据表还要写SQL语句跟表字段一一对应，多么费劲的一件事。SQL语句的增删查改万变不离其宗，变的只是库、表、字段等，也就是说我们动态获取到库、表、字段这些名字后动态插入SQL语句中不就完事了？

于是今天呈有空就用C#实现了一个可以获取所有库、表、字段的demo，可以像navicat一样通过点击事件查看任意库任意表任意数据，还能像任意表进行增删查改功能。

以下只展示其中一个小模块：查询任意库中的任意表中的数据

导入Dll：MySql.Data.dll

窗口设计：

Form1:

Form2:

 

代码：

Form1：

 public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }
       
        //重置
        private void button2_Click(object sender, EventArgs e)
        {
            this.textBox1.Text = "";
            this.textBox2.Text = "";
            this.textBox3.Text = "";
        }
        //连接
        private void button1_Click(object sender, EventArgs e)
        {
            string server = this.textBox1.Text;
            string userid = this.textBox2.Text;
            string password = this.textBox3.Text;


            Form2 f = new Form2(server, userid, password , out bool status);
            if (status)
            {
                f.Show();
                return;
            }
            MessageBox.Show("连接失败！");
        }
    }

Form2：

 public partial class Form2 : Form
    {
        string server,  userid,  password;
        MySqlConnection conn;
        public Form2(string server, string userid, string password , out bool status)
        {
            InitializeComponent();

            this.server = server;
            this.userid = userid;
            this.password = password;

            if (OpenConnection())
            {
                status = true;
            }
            else
            {
                status = false;
            }
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            //加载库名
            this.comboBox1.Items.AddRange(GetDatabases());
        }
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.comboBox2.Items.Clear();
            string item = (sender as ComboBox).Text;
            this.comboBox2.Items.AddRange(GetTables(item));
        }
        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string database = this.comboBox1.Text;
            string table = this.comboBox2.Text;

            this.dataGridView1.Columns.Clear();
            this.dataGridView1.Rows.Clear();

            string[] datas = GetField(database, table);
            //加载字段
            dataGridView1.ColumnCount = datas.Length;
            for (int i = 0; i < datas.Length; i++)
            {
                this.dataGridView1.Columns[i].Name = datas[i];
            }
            //加载数据
            List<string[]> dataList = GetDatas(database,datas, table);
            int listCount = dataList.Count;
            for (int i = 0; i < dataList[0].Length; i++)
            {
                for (int j = 0; j < listCount; j++)
                {
                    this.dataGridView1.Rows.Add();
                    try
                    {
                        dataGridView1.Rows[i].Cells[j].Value = dataList[j][i];
                    }
                    catch (Exception)
                    {
                    }
                }
            }
        }
        #region 操作数据库
        //打开mysql连接
        private bool OpenConnection()
        {
            try
            {
                string strConn = "server=" + server +
                                ";userid=" + userid +
                                ";password=" + password;
                conn = new MySqlConnection(strConn);
                conn.Open();
            }
            catch (Exception)
            {

                return false;
            }
            return true;
        }
        private bool OpenConnection(string database)
        {
            try
            {
                string strConn = "server=" + server +
                                ";userid=" + userid +
                                ";password=" + password +
                                ";Database=" +database;
                conn = new MySqlConnection(strConn);
                conn.Open();
            }
            catch (Exception)
            {

                return false;
            }
            return true;
        }
        //关闭mysql连接
        private bool CloseConnection()
        {
            try
            {
                conn.Close();
                return true;
            }
            catch (Exception)
            {

                return false;
            }
        }
        //增删改
        private bool AddUpdateDelet(string sql)
        {
            if (CloseConnection())
            {
                MySqlCommand msc = new MySqlCommand(sql, conn);
                int status = msc.ExecuteNonQuery();
                CloseConnection();
                if (status > 0)
                {
                    return true;
                }
                return false;
            }
            return false;
        }
        //查询
        private MySqlDataReader Select(string sql)
        {
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                dataReader.Read();
                return dataReader;
            }
            return null;
        }
        private MySqlDataReader Select(string database,string sql)
        {
            if (OpenConnection(database))
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                dataReader.Read();
                return dataReader;
            }
            return null;
        }
        //获取数据库名
        private string[] GetDatabases() {
            try
            {
                string getDatabaseSql = "SELECT  GROUP_CONCAT(DISTINCT Table_schema) FROM information_schema.`TABLES`";
                MySqlDataReader data = Select(getDatabaseSql);
                MessageBox.Show("" + data[0]);
                return data[0].ToString().Split(',');
            }
            finally
            {
                CloseConnection();
            }
        }
        //获取数据表名
        private string[] GetTables(string database) {
            try
            {
                string getTabesSql = "SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema='"+ database + "'";
                MySqlDataReader data = Select(getTabesSql);
                return data[0].ToString().Split(',');
             }
            finally
            {
                CloseConnection();
            }
        }
        //获取表字段名
        private string[] GetField(string database , string table) {
            try
            {
                string getFieldSql = "SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.Columns WHERE table_name='"+ table + "' AND table_schema='"+ database + "'";
                MySqlDataReader data = Select(getFieldSql);
                return data[0].ToString().Split(',');
            }
            finally
            {
                CloseConnection();
            }
        }
        //获取表数据
        private List<string[]> GetDatas(string database, string[] fields ,string  table) {
            List<string[]> dataList = new List<string[]>();
            foreach (string field in fields)
            {
                try
                {
                    string getDataSql = "SELECT GROUP_CONCAT(" + field + ") FROM " + table + "";
                    MySqlDataReader data = Select(database,getDataSql);

                    dataList.Add(data[0].ToString().Split(','));
                }
                finally
                {
                    CloseConnection();
                }
            }
            return dataList;
        }
        #endregion
    }

效果：

（数据库工具中的mysql数据库内容）

 

（demo效果）