C#连接数据库

连接数据库需要引入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();
        }
    }