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

小小243 2013-12-13

展开全文

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

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

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

C#代码

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，虽然在stackoverflow上有这个问题但是没有解决，无奈之下，只有自己琢磨，在google到②文章，看了下，好像看到有说版本的问题，然后我就索性下载最新的mysql.data.dll，竟然出现是数据库未连接成功，原因是在我XML解析的时候错误。然后修改这部分竟然就执行成功了。

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

C#代码

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语句，但还是不熟。所以在上面初始化数据库连接语句时：

C#代码

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./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