C# 连接MySQL

1. 添加MySQL引用

安装完MySQL之后，在安装的默认目录 C:\Program Files (x86)\MySQL\Connector NET 8.0 中查找MySQLData.dll文件。

在Visual Studio 中为项目中添加引用。

2. 引入命名空间

<code>using MySql.Data.MySqlClient;

3. 构建连接

private static MySqlConnection m_Connect = null;

private static void Connect()

{ -- -->

string connectStr = "server=127.0.0.1;port=3306;database=test;user=root;password=root;";

m_Connect = new MySqlConnection(connectStr);

}

server=IP地址;

port=端口号;

database=数据库名字;

user=管理员账号;

password=账号的密码;

4. 增删改查

增

private static void Insert()

{

try

{

m_Connect.Open();

//string sqlStr = "Insert into users(username, password) values('takil', '789')";

//string sqlStr = "Insert into users(username, password, registerdate) values('taksil', '789789', '2015-05-09')";

string sqlStr = "Insert into users(username, password, registerdate) values('wilhelm', '78889', '" + DateTime.Now + "')";

MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);

int result = cmd.ExecuteNonQuery();

Console.WriteLine("成功影响了{0}条数据", result);

}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

finally

{

m_Connect?.Close();

}

}

删

private static void Delete()

{

try

{

m_Connect.Open();

string sql = "delete from users where username = 'takil'";

MySqlCommand cmd = new MySqlCommand(sql, m_Connect);

int result = cmd.ExecuteNonQuery();

Console.WriteLine("成功影响了{0}条数据", result);

}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

finally

{ m_Connect?.Close(); }

}

改

private static void Update()

{

try

{

m_Connect.Open();

string sql = "Update users set username = 'wqrwq', password = '123' where id = 5";

MySqlCommand cmd = new MySqlCommand( sql, m_Connect);

int result = cmd.ExecuteNonQuery();

Console.WriteLine("成功影响了{0}条数据", result);

}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

finally

{ m_Connect?.Close(); }

}

查

private static void Read()

{

try

{

m_Connect.Open();

string sqlStr = "select * from users";

//string sqlStr = "select id, username, registerdate from users";

MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);

MySqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

//Console.WriteLine(reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString());

//Console.WriteLine(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetString(2));

Console.WriteLine(reader.GetInt32("id") + " " + reader.GetString("username") + " " + reader.GetString("password"));

}

}

catch(Exception ex)

{

Console.WriteLine(ex.ToString());

}

finally

{

m_Connect?.Close();

}

}

private static void ReadCount()

{

try

{

m_Connect.Open();

string sqlStr = "select Count(*) from users";

MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);

//MySqlDataReader reader = cmd.ExecuteReader();

//reader.Read();

//int count = Convert.ToInt32(reader[0]);

//Console.WriteLine("总数为{0}", count);

object result = cmd.ExecuteScalar();

int count = Convert.ToInt32(result);

Console.WriteLine("总数为{0}", count);

}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

finally

{ m_Connect?.Close(); }

}

验证

private static bool ValifyUser(string username, string password)

{

try

{

m_Connect.Open();

//string sqlStr = "select * from users where username = '" + username + "' and password = '" + password + "'";

string sqlStr = "select * from users where username = @para1 and password = @para2";

MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);

cmd.Parameters.AddWithValue("para1", username);

cmd.Parameters.AddWithValue("para2", password);

MySqlDataReader reader = cmd.ExecuteReader();

if(reader.Read())

{

return true;

}

}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

finally

{

m_Connect?.Close();

}

return false;

}

因为作者精力有限，文章中难免出现一些错漏，敬请广大专家和网友批评、指正。