C#连接MySql数据库

C#连接MySql数据库

连接数据库

private void btn(object sender, RoutedEventArgs e)
        {
            string connect = "server=localhost;database=book;uid=root;pwd=123456";
            MySqlConnection conn = new MySqlConnection(connect);
            try
            {
                conn.Open();
                MessageBox.Show("连接成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("连接失败！");
                throw;
            }
        }

运行结果

查询

查询条件固定

				#region 查-查询条件固定
                string sql = "select * from book";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader reader = cmd.ExecuteReader();   //执行executeReader()返回一个MySqlReader对象
                string str = "";
                while (reader.Read())
                {
                    str += reader.GetInt32("id") + "===" + reader.GetString("name") + "===" + reader.GetString("writer") + "\n";
                }
                MessageBox.Show(str);
                #endregion

查询条件不固定

				#region 查-查询条件不固定
                string sql = "select * from book where writer='籽月'";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader reader = cmd.ExecuteReader();   //执行executeReader()返回一个MySqlReader对象
                string str = "";
                while (reader.Read())
                {
                    str += reader.GetInt32("id") + "===" + reader.GetString("name") + "===" + reader.GetString("writer") + "\n";
                }
                MessageBox.Show(str);
                #endregion

查询返回一个值

                string sql = "select count(*) from book";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                object result = cmd.ExecuteScalar();//执行查询，并返回查询结果集中的第一行的第一列，所有其他的行和列将被忽略，select语句无记录返回时，executeScalar()返回null值
                if (result != null)
                {
                    int count = int.Parse(result.ToString());
                    MessageBox.Show(count.ToString());
                }

增删改

#region 增
                //string sql = "insert into book(id,name,price,count,writer) values('5','深入浅出WPF','33','2','刘铁猛')";
                //MySqlCommand cmd = new MySqlCommand(sql, conn);
                //int result = cmd.ExecuteNonQuery();
                //if (result == 1)
                //{
                //    MessageBox.Show("添加成功！");
                //}
                #endregion
                #region 删
                //string sql = "delete from book where id=5";
                //MySqlCommand cmd = new MySqlCommand(sql, conn);
                //int result = cmd.ExecuteNonQuery();
                //if (result == 1)
                //{
                //    MessageBox.Show("删除成功！");
                //}
                #endregion
                #region 改
                //string sql = "update  book set price=99 where writer='刘铁猛'";
                //MySqlCommand cmd = new MySqlCommand(sql, conn);
                //int result = cmd.ExecuteNonQuery();
                //if (result == 1)
                //{
                //    MessageBox.Show("修改成功！");
                //}
                #endregion