ado整理小demo

准备工作：导入包

建立一个.config文件，添加System.configuration引用。

整体目录

 配置文件

<configuration>
	<connectionStrings>
		<add name="mysql" connectionString="Database=db1;Data Source=localhost;User Id=root;Password=20121120sh;pooling=true;charset=utf8;port=3306" />
	</connectionStrings>
   
</configuration>

登录验证核心代码：

  private void btnLogin_Click(object sender, EventArgs e)
        {
            string sql = "select count(*) from admin where name = @name and password = @password";
            MySqlParameter[] pam = new MySqlParameter[] {
            new MySqlParameter("@name",MySqlDbType.VarChar,20){Value=txtUser.Text.Trim()},
            new MySqlParameter("@password",MySqlDbType.VarChar,20)
            {Value=txtPassword.Text}
            };
            int r =Convert.ToInt32( SqlHelper.ExecuteScalar(sql, pam));
            if (r > 0)
            {
                MessageBox.Show("登录成功");
                //解决第二个窗体的展示
                new frmpet().Show();

            }
            else
            {
                MessageBox.Show("登陆失败");
            }
        }

数据展示核心代码

 private void frmpet_Load(object sender, EventArgs e)
        {

            List<PetA> list = new List<PetA>();
            //将查询出来的数据绑定到数据源中
            string sql = "select * from pet";
            MySqlParameter[] pam= { };
           
            using (MySqlDataReader mySqlDataReader = SqlHelper.ExecuteReader(sql, pam))
            {
                if (mySqlDataReader.HasRows)
                {
                    while (mySqlDataReader.Read())
                    {
                        PetA pet = new PetA();
                        pet.pet_id = mySqlDataReader.GetString(0);
                        pet.type = mySqlDataReader.GetString(1);
                        pet.name= mySqlDataReader.GetString(2);
                        pet.vc=mySqlDataReader.GetString(3);

                        list.Add(pet);
                    }
                }
            }
            this.dataGridView1.DataSource= list;//数据绑定
        }

sqlHelper工具类核心代码

 public static class SqlHelper
    {
        private static readonly string conStr = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;

        //1.执行增删改
        public static int ExcuteNonQuery(string sql, params MySqlParameter[] pms)//参数化数组
        {
            using (MySqlConnection con = new MySqlConnection(conStr))//连接对象
            {
                using (MySqlCommand cmd = new MySqlCommand(sql,con))//命令对象
                {
                    if (pms!=null)
                    {
                        cmd.Parameters.AddRange(pms);//将参数化数组添加到命令对象里面
                    }
                    con.Open();//打开
                    return cmd.ExecuteNonQuery();//执行
                }
            }
        }
        //2.执行查询，返回单个值的方法，eg.使用聚合函数查询总人数，平均分等等。
        public static object ExecuteScalar(string sql,params MySqlParameter[] pms)
        {
            using (MySqlConnection con = new MySqlConnection(conStr))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql,con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        //3.执行查询返回多行多列
        public static MySqlDataReader ExecuteReader(string sql, params MySqlParameter[] pms)
        {
            MySqlConnection con = new MySqlConnection(conStr);//不能使用using,因为这样只要出了{}就会把con连接对象关闭。
            {
                using (MySqlCommand cmd  = new MySqlCommand(sql,con))
                {
                    if (pms!=null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    try
                    {
                        con.Open();
                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                    catch 
                    {
                        con.Close();
                        con.Dispose();
                        throw;
                    }  
                }
            }
        }

    }