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(); } } } }
连接数据库,生成数据库表树(界面图),是参照网上一个软件做的,因为没有给代码,就自己参照着来山寨了一把:
小结:
其实我到现在对数据库和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