chsap连接Mysql

前置操作

 添加引用MySql.Data.dll

 数据库查询数据

 1 using Mysql.Data.MyAqlClient;
 2 static void Main(string[] args)
 3 {
 4     string connStr="Database=test007;Data Source=127.0.0.1;port=3306;User Id=root;Password=root;";//datasource、user和pwd也是可以的，想大写小写都行，都能识别到
 5     MySqlConnection conn = new MySqlConnection(connStr);
 6     conn.Open();
 7 ///查询   
 8     MySqlCommand cmd = new MySqlCommand("select * from user where id=1",coon);
 9     MySqlDataReader reader=cmd.ExecuteReader();
10     if(reader.HasRows)
11     {
12         reader.Read();
13         string username=reader.GetString("username");
14         string password=reader.GetString("password");    
15         Console.WriteLine(username+":"+password);
16     }
17     reader.Close();
18     Conn.Close();
19     Console.ReadKey();
20 }

 打印多条数据(第一行第二行有数据返回true,第三行没数据返回false跳出循环)

1 while( reader.Read())
2 {
3     reader.Read();
4     string username=reader.GetString("username");
5     string password=reader.GetString("password");    
6     Console.WriteLine(username+":"+password); 
7 }

 数据库插入

1 ///插入  
2     string username="cwer";
3     string password="lcker';delete from user;"; //刷新会发现恶意删除数据库了已经（sql注入，使用SQL的安全问题）
4     //MySqlCommand cmd = new MySqlCommand("insert into user set username='"+username+"'"+",password='"+password+"'",coon);
5     MySqlCommand cmd = new MySqlCommand("insert into user set username=@un , password = @pwd",coon);
6     cmd.Parameters.AddWithValue("un",username);
7     cmd.Parameters.AddWithValue("pwd",password);
8     cmd.ExecuteNonQuery();

 数据的删除

1 ///删除
2   MySqlCommand cmd = new MySqlCommand("delete from user where id=@id",conn);
3   cmd.Parameters.AddWithValue("id",18);
4   cmd.ExecuteNonQuery();
5   conn.Close();
6   Console.ReadKey();

 数据库更新

1 MySqlCommand cmd = new MySqlCommand("update user set password=@pwd where id=11",conn);
2 cmd.Parameters.AddWithValue("pwd","hahaha");
3 cmd.ExecuteNonQuery();
4 conn.Close();
5 Console.ReadKey();