学习记录——C#实现数据库的增删改查

static MySqlConnection conn = null;

conn = new MySqlConnection("server=localhost;User Id = root;password=;Database = studymysql;Charset = utf8");

conn.Open();//开启连接
        //增
Add();

        //删
Delete();

        //改
Update();

        //查
Query();

Console.ReadKey();
conn.Close();//关闭连接

static void Add()//增
    {
        MySqlCommand cmd = new MySqlCommand("insert into userinfo set name = 'xixi',age=96", conn);//语句内容
        cmd.ExecuteNonQuery();//执行语句
        int id = (int) cmd.LastInsertedId;
        Console.WriteLine("Sql Insert Key{0}",id);
    }

    static void Delete()
    {
        MySqlCommand cmd = new MySqlCommand("delete from userinfo where id = @id",conn);
        cmd.Parameters.AddWithValue("id",10);
        cmd.ExecuteNonQuery();
        Console.WriteLine("delete done");
    }

    static void Update()
    {
        MySqlCommand cmd = new MySqlCommand("update userinfo set name=@name,age=@age where id =@id",conn);
        cmd.Parameters.AddWithValue("name","lll");
        cmd.Parameters.AddWithValue("age", 11);
        cmd.Parameters.AddWithValue("id", 1);

        cmd.ExecuteNonQuery();
        Console.WriteLine("update done");
    }

    static void Query()//查
    {
        MySqlCommand cmd = new MySqlCommand("select * from userinfo where name ='plane'",conn);
        //解析数据
        MySqlDataReader reader = cmd.ExecuteReader();
        while(reader.Read())
        {
            int id = reader.GetInt32("id");
            string name = reader.GetString("name");
            int age = reader.GetInt32("age");
            
            Console.WriteLine(string.Format("sql result:id:{0} name:{1} age:{2}",id,name,age));
        }
    }