winfrom增删改查

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            

        }
        string connstr = "server=DESKTOP-QQGOIKH;uid=sa;pwd=123;database=stuDB";
        private void Form1_Load(object sender, EventArgs e)
        {
            //1、查询语句
            string sql = "select * from student where 1=1";
            //2、创建连接对象
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            //3、创建执行对象
            SqlCommand cmd = new SqlCommand(sql, conn);
            //4、创建适配器对象，Command对象不能一次处理多条数据
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //5、创建数据集，用来接收数据表、可以装多张表的数据
            DataSet ds = new DataSet();
            //6、填充数据、将查询上的数据放到数据集中
            sda.Fill(ds,"aa");
            //7、关闭连接
            conn.Close();
            //DataSet中每一个元素是一个DataTable对象，与数据库中的Table相似
            DataTable dt = ds.Tables["aa"];
            dataGridView1.DataSource = dt;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //新增
            //1、取数据
            string stuName = textBox1.Text;
            string stuNo = textBox2.Text;
            string sex = "女";
            if (radioButton1.Checked)
            {
                sex = "男";
            }
            string remark = radioButton1.Text;
            //2、准备数据
            string sql = "insert into student values(@a,@b,@c,@d)";
            SqlParameter[] pms = new SqlParameter[4];
            pms[0] = new SqlParameter("@a", stuName);
            pms[1] = new SqlParameter("@b", stuNo);
            pms[2] = new SqlParameter("@c", sex);
            pms[3] = new SqlParameter("@d", remark);
            //ADO.NET
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(pms);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            if (i>0)
            {
                MessageBox.Show("新增成功");
            }
            else
            {
                MessageBox.Show("新增失败");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //修改


        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //找到选中行的数据ID号，通过id把student表的数据删除
            if (dataGridView1.SelectedRows.Count == 0)
            {
                MessageBox.Show("没有要删除的数据");
                return;
            }
            DialogResult dr = MessageBox.Show("确定删除吗", "友情提示", MessageBoxButtons.YesNo);
            if (dr == DialogResult.Yes)
            {
                //ID获取
                string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                string sql = "delete from student where stuid=@a";
                SqlParameter pm = new SqlParameter("@a",id);
                //ADO.NET********删除操作
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(pm);//删除
                //ExecuteNonQuery方法可以在命令是insertdeleteupdate用
                //执行sql命令返回受影响行数
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                if (i > 0)
                {
                    MessageBox.Show("删除成功");
                }
                else
                {
                    MessageBox.Show("删除失败");
                }
                
            }
        }

        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //编辑操作，把选中的行id找到，后去取数据库id对应学生数据
            //把取到的内容放到控件中
            if (dataGridView1.SelectedRows.Count==0)
            {
                MessageBox.Show("没有选中项");
                return;
            }
            string stuid = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
            //取stuid对应的数据
            string sql = "select * from student where stuID=@a";
            SqlParameter pm = new SqlParameter("@a", stuid);
            //*************ADO.NET操作*************
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql,conn);
            cmd.Parameters.Add(pm);
            //执行
            SqlDataReader sdr = cmd.ExecuteReader();
            //读取    Read()方法，每读一次就取结果中的下一条记录
            bool b = sdr.Read();
            if (b==true)
            {
                //显示数据，读取到的数据从dataReader对象上拿
                textBox1.Text = sdr["stuName"].ToString();
                textBox2.Text = sdr["stuNo"].ToString();
                textBox3.Text = sdr["stuID"].ToString();
                string sex = sdr["sex"].ToString();
                radioButton1.Checked = sex == "男";
                radioButton2.Checked = sex == "女";
                richTextBox1.Text = sdr["remark"].ToString();
            }
            conn.Close();
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            //修改
            //根据选中的学生编号修改
            string sql = "update student set stuName=@a,stuNo=@b,sex=@c,remark=@d where stuID=@e";
            SqlParameter pm = new SqlParameter("@e", textBox3.Text);
            string stuName = textBox1.Text;
            string stuNo = textBox2.Text;
            string sex = "女";
            if (radioButton1.Checked)
            {
                sex = "男";
            }
            string remark = radioButton1.Text;
            string id = textBox3.Text;
            //2、准备数据
            
            SqlParameter[] pms = new SqlParameter[5];
            pms[0] = new SqlParameter("@a", stuName);
            pms[1] = new SqlParameter("@b", stuNo);
            pms[2] = new SqlParameter("@c", sex);
            pms[3] = new SqlParameter("@d", remark);
            pms[4] = new SqlParameter("@e", id);
            //ADO.NET
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(pms);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            if (i > 0)
            {
                MessageBox.Show("修改成功");
            }
            else
            {
                MessageBox.Show("修改失败");
            }


        }
    }
}