SqlHelper

class SqlHelper
    {
        string sqlConnStr = ConfigurationManager.ConnectionStrings["str"].ConnectionString;
        public DataTable ExcuteDataTable(string sql, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {

                SqlCommand cmd = new SqlCommand(sql, conn);
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    
                    sda.Fill(dt);
                }
            } return dt;
        }
        public int ExcuteNoneQuery(string sql, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(param);
                    conn.Open();
                    return cmd.ExecuteNonQuery();

                }
            }
        }

        public object ExcuteScalar(string sql, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    return  cmd.ExecuteScalar();
                }
            }
        }
        //类里面不能有方法，只能有字段、属性、方法的定义。
        SqlConnection conn ;
        public SqlHelper()
        {
            conn = new SqlConnection(sqlConnStr);
        }
        public SqlDataReader ExcuteReader(string sql, params SqlParameter[] param)
        {
            
            SqlCommand cmd = new SqlCommand(sql,conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);//当sqlDataReader关的时候连接就关
        }
    }

        public Form1()
        {
            InitializeComponent();
        }
        SqlHelper help = new SqlHelper();
        private void button1_Click(object sender, EventArgs e)
        {
            string sql = "select *from student";
            
            dataGridView1.DataSource= help.ExcuteDataTable(sql);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql="select *from student where sSex=@sex";
            dataGridView1.DataSource= help.ExcuteDataTable(sql,new SqlParameter("@sex","女"));
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string sql = "select *from student where sSex=@sex and sAge>@age and sClassId=@class";
            SqlParameter[] sps={
                                   new SqlParameter("@sex","男"),
                                   new SqlParameter("@age",18),
                                   new SqlParameter("@class",4)
                               };
            dataGridView1.DataSource = help.ExcuteDataTable(sql, sps);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string sql = "insert into student(sName,sAge,sSex,sClassId) values(@name,@age,@sex,@class)";
            SqlParameter[] param={
                                     new SqlParameter("@name","张三"),
                                     new SqlParameter("@age",22),
                                     new SqlParameter("@sex","男"),
                                     new SqlParameter("@class",4)
                                 };
            int result=help.ExcuteNoneQuery(sql,param);
            if (result > 0)
            {
                MessageBox.Show("插入成功");
            }
            else
            {
                MessageBox.Show("插入失败");
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            string sql = "delete from student where sName=@name";
            SqlParameter param = new SqlParameter("@name", "关羽");
            int result= help.ExcuteNoneQuery(sql, param);
            if (result > 0)
            {
                MessageBox.Show("插入成功");
            }
            else
            {
                MessageBox.Show("插入失败");
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            string sql = "select count(*) from student";
            object o= help.ExcuteScalar(sql);
            MessageBox.Show("查询结果:"+o.ToString());
        }

        private void button7_Click(object sender, EventArgs e)
        {
            string sql = "select *from student";
            SqlDataReader sdr= help.ExcuteReader(sql);
            while (sdr.Read())
            
            {
                Console.WriteLine(sdr["sName"]);
                Console.WriteLine(sdr["sAge"]);
                Console.WriteLine(sdr["sSex"]);
            }
            sdr.Close();


            string sql1 = "select *from class";
            SqlDataReader sdr1 = help.ExcuteReader(sql1);
            while (sdr1.Read())
            {
                Console.WriteLine(sdr1["cId"]);
                Console.WriteLine(sdr1["cName"]);
                
            }
        }