连接数据库需要引入MySql.Data.dll
数据库查询:
class Program
{
static void Main(string[] args)
{
string connStr = "Database=test;Data Source=127.0.0.1;port=3306;User Id = root;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from user", conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string username = reader.GetString("acc");
string password = reader.GetString("pass");
Console.WriteLine(username + ":" + password);
}
reader.Close();
conn.Close();
Console.ReadKey();
}
}
数据库插入
class Program
{
static void Main(string[] args)
{
string connStr = "Database=test;Data Source=127.0.0.1;port=3306;User Id = root;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
string acc = "123";
string pass = "123";
MySqlCommand cmd = new MySqlCommand("insert into user set acc='"+acc+"' ,pass ='"+pass+"' ", conn);
cmd.ExecuteNonQuery();
conn.Close();
Console.ReadKey();
}
}
以上代码会被SQL注入
例如:将pass改为 string pass = “123;delet from user”;
执行后将删除所有信息
解决办法:使用cmd.Parameters.AddWithValue添加参数
MySqlCommand cmd = new MySqlCommand("insert into user set acc = @acc,password=@pass", conn);
cmd.Parameters.AddWithValue("acc", 456);
cmd.Parameters.AddWithValue("pass", 456);
数据库删除
class Program
{
static void Main(string[] args)
{
string connStr = "Database=test;Data Source=127.0.0.1;port=3306;User Id = root;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = new MySqlCommand("delete from user where id =@id", conn);
cmd.Parameters.AddWithValue("id", 20);
cmd.ExecuteNonQuery();
conn.Close();
Console.ReadKey();
}
}
数据库更新
class Program
{
static void Main(string[] args)
{
string connStr = "Database=test;Data Source=127.0.0.1;port=3306;User Id = root;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = new MySqlCommand("update user set pass = @pass where id =10", conn);
cmd.Parameters.AddWithValue("pass", "789");
cmd.ExecuteNonQuery();
conn.Close();
Console.ReadKey();
}
}