欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

C# 连接MySQL数据库并进行相关操作

程序员文章站 2022-05-17 15:47:41
...

C# 连接MySQL数据库并进行相关操作

 

       之前在C#使用OleDb读取Excel,生成SQL语句 中只是生成SQL语句,没有连接数据库执行。后面举得这样不方便,让改成直接插入数据库,还将了生成对应的实体类的功能。

       C#连接数据库方法有很多,在①中说道了三种连接数据方法和示例,我采用的是MySQL自己的组件mysql.data.dll来连接数据库的,并封装了一些函数,虽然只用到了GetSchema(取出数据的表)和ExecuteSQLFile(执行sql文件)两个功能,其他具体的功能可以参考官方的Document。

 

 

       当然一般都不会一次搞定了,在生成实体类的要获取表的字段信息,就出现获取的字段为null,后面自己琢磨和测试,发现是GetSchema中的字符串数组参数和文档中规定的顺序不一致,所以无法匹配,就返回null。下面是代码:

DataTable dt = dbhelper.GetSchema("Databases"),dt1;
            TreeNode tn,tn1;
            string temp;
            foreach(DataRow dr in dt.Rows)
            {
                temp = dr[1].ToString();
                tn = new MyNode(dbhelper, temp);
                //tn.Text = temp;
                dt1 = dbhelper.GetSchema("Tables",new string[4]{null,temp,null,null});
                foreach (DataRow dr1 in dt1.Rows)
                {
                    tn1 = new MyNode(dbhelper,dr1[2].ToString());
                    tn.Nodes.Add(tn1);
                }
                tv.Nodes.Add(tn);
            }

       另外一个功能,其实是Excel文件导出SQL语句,然后执行,代码很简单,在下面的ExecuteSQLFile函数可以看到,但是也碰到了一个问题:

在 MySql.Data.MySqlClient.MySqlStream.ReadPacket()

   在 MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)

   在 MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)

   在 MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)

   在 MySql.Data.MySqlClient.MySqlDataReader.NextResult()

   在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

   在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()

   在 MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()

   在 ReadXlsxData.DBConnect.ExecuteSQLFile(String fileName) 

 

       对我这个完全没有数据库经验的来说,根本看不到问题所在,只有google,虽然在*上有这个问题但是没有解决,无奈之下,只有自己琢磨,在google到②文章,看了下,好像看到有说版本的问题,然后我就索性下载最新的mysql.data.dll,竟然出现是数据库未连接成功,原因是在我XML解析的时候错误。然后修改这部分竟然就执行成功了。

 

       惊喜之余,附上网上找(支持Insert,Update,Backup,Restore,Delete,Select等操作,完全可以自己加工写一个图形界面数据库管理工具了哈)加我写ExecuteSQLFile的代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
//Add MySql Library
using MySql.Data.MySqlClient;
using System.Data;

namespace ReadXlsxData
{
    class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;
        private string port;
        //private string database;



        //Initialize values
        public void Initialize(string server, string database,string uid, string password, string port)
        {
            //server = "localhost";
            //database = "connectcsharptomysql";
            //uid = "username";
            //password = "password";
            this.server = server;
            this.uid = uid;
            this.password = password;
            this.port = port;
            this.database = database;
            string connectionString = "Data Source=" + server + ";" + "port=" + port + ";" + "Database=" + database + ";" + "User Id=" + uid + ";" + "Password=" + password + ";" + "CharSet = utf8"; ;
            connection = new MySqlConnection(connectionString);
        }


        //open connection to database
        public bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        //Close connection
        public bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public DataTable GetSchema(string str, string[] restri)
        {
            return connection.GetSchema(str, restri);
        }
        public DataTable GetSchema(string str)
        {
            return connection.GetSchema(str);
        }
        // Get Database List

        //Insert statement
        public void Insert()
        {
            string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";

            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Execute command
                cmd.ExecuteNonQuery();
                //close connection
                this.CloseConnection();
            }
        }

        //Update statement
        public void Update()
        {
            string query = "UPDATE tableinfo SET name='Joe', age='22' WHERE name='John Smith'";

            //Open connection
            if (this.OpenConnection() == true)
            {
                //create mysql command
                MySqlCommand cmd = new MySqlCommand();
                //Assign the query using CommandText
                cmd.CommandText = query;
                //Assign the connection using Connection
                cmd.Connection = connection;

                //Execute query
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

        //Delete statement
        public void Delete()
        {
            string query = "DELETE FROM tableinfo WHERE name='John Smith'";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
        }

        //Select statement
        public List<string>[] Select()
        {
            string query = "SELECT * FROM tableinfo";

            //Create a list to store the result
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();

            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["id"] + "");
                    list[1].Add(dataReader["name"] + "");
                    list[2].Add(dataReader["age"] + "");
                }
                //close Data Reader
                dataReader.Close();
                //close Connection
                this.CloseConnection();
                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }

        //Count statement
        public int Count()
        {
            string query = "SELECT Count(*) FROM tableinfo";
            int Count = -1;
            //Open Connection
            if (this.OpenConnection() == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");
                //close Connection
                this.CloseConnection();
                return Count;
            }
            else
            {
                return Count;
            }
        }

        //Backup
        public void Backup()
        {
            try
            {
                DateTime Time = DateTime.Now;
                int year = Time.Year;
                int month = Time.Month;
                int day = Time.Day;
                int hour = Time.Hour;
                int minute = Time.Minute;
                int second = Time.Second;
                int millisecond = Time.Millisecond;

                //Save file to C:\ with the current date as a filename
                string path;
                path = "C:\\" + year + "-" + month + "-" + day + "-" + hour + "-" + minute + "-" + second + "-" + millisecond + ".sql";
                StreamWriter file = new StreamWriter(path);


                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysqldump";
                psi.RedirectStandardInput = false;
                psi.RedirectStandardOutput = true;
                psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
                psi.UseShellExecute = false;

                Process process = Process.Start(psi);

                string output;
                output = process.StandardOutput.ReadToEnd();
                file.WriteLine(output);
                process.WaitForExit();
                file.Close();
                process.Close();
            }
            catch (IOException ex)
            {
                MessageBox.Show("Error , unable to backup!");
            }
        }

        //Restore
        public void Restore()
        {
            try
            {
                //Read file from C:\
                string path;
                path = "C:\\MySqlBackup.sql";
                StreamReader file = new StreamReader(path);
                string input = file.ReadToEnd();
                file.Close();


                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysql";
                psi.RedirectStandardInput = true;
                psi.RedirectStandardOutput = false;
                psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
                psi.UseShellExecute = false;

                Process process = Process.Start(psi);
                process.StandardInput.WriteLine(input);
                process.StandardInput.Close();
                process.WaitForExit();
                process.Close();
            }
            catch (IOException ex)
            {
                MessageBox.Show("Error , unable to Restore!");
            }
        }
        //Execute Sql File
        public void ExecuteSQLFile(string fileName)
        {
            string sql = File.ReadAllText(fileName, Encoding.UTF8);
            MySqlCommand myCommand = new MySqlCommand(sql);
            myCommand.Connection = connection;
            if (this.OpenConnection() == true)
            {
                myCommand.ExecuteNonQuery();
                //MessageBox.Show("..........");
                this.CloseConnection();
            }
            

        }

    }
}

       

       连接数据库,生成数据库表树(界面图),是参照网上一个软件做的,因为没有给代码,就自己参照着来山寨了一把:
C# 连接MySQL数据库并进行相关操作
            
    
    博客分类: 工作进度 mysqlc#
   

小结:

       其实我到现在对数据库和SQL语句都不了解,因为之前大学没有学过,虽然工作中在服务器业务中也有用到SQL语句,但还是不熟。所以在上面初始化数据库连接语句时:

string connectionString = "Data Source=" + server + ";" + "port=" + port + ";" + "Database=" + database + ";" + "User Id=" + uid + ";" + "Password=" + password + ";" + "CharSet = utf8"; 

 这个,我之前的一个版本不是这么写的, Data Source 原来是 Server 而 User Id 原来是uid ,因为不懂,只能是哪个行就到哪,想着等着有时间好好琢磨下。

       数据库操作或SQL语句其实不难,实际工作也很少会说考虑性能这方面的东西,对数据库的内部工作原理没有个谱,用起来只能依葫芦画瓢,跟写上面数据库连接语句,我只有去google,找下人家怎么写,或者是去找官方的Example。所以希望以后能对数据库知根知底,如果您有这方面的什么推荐,希望能收到您的留言,谢谢!

 

        转载在文首注明出处:http://dsqiu.iteye.com/blog/1964567

更多精彩请关注D.S.Qiu的博客和微博(ID:静水逐风)

 

参考:

 ①C#+Mysql+Mono:http://blog.donews.com/monoer/archive/2006/06/06/904285.aspx

easy5: http://www.cnblogs.com/easy5weikai/archive/2012/12/06/2805558.html

  • C# 连接MySQL数据库并进行相关操作
            
    
    博客分类: 工作进度 mysqlc#
  • 大小: 19 KB
相关标签: mysql c#