C# 连接MySQL
1. 添加MySQL引用
安装完MySQL之后,在安装的默认目录 C:\Program Files (x86)\MySQL\Connector NET 8.0 中查找MySQLData.dll文件。
在Visual Studio 中为项目中添加引用。
2. 引入命名空间
- 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;
- }
复制代码 因为作者精力有限,文章中难免出现一些错漏,敬请广大专家和网友品评、指正。
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
|