实验三：增、删、改、查

在上一个实验中利用dataGridView添加数据源，将Student中的数据导入进来，并添加好需要的控件。

1. 增加

SqlConnection con = new SqlConnection("Data Source =.; Initial Catalog = Text;  Persist Security Info = True;User ID = sa; Password = 110023");
        private void buttonInsert_Click(object sender, EventArgs e)//增加
        {
            string StuSno = textBox1.Text.Trim();
            string StuSname = textBox2.Text.Trim();
            string StuSsex = textBox3.Text.Trim();
            string StuSage = textBox4.Text.Trim();
            string StuSdept = textBox4.Text.Trim();
            //SqlConnection con = new SqlConnection("Data Source =.; Initial Catalog = Student;  Persist Security Info = True;User ID = sa; Password = 123"); //连接数据库
            try
            {
                con.Open();     //打开数据库
                string insertStr = "INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)" + "VALUES('" + StuSno + "','" + StuSname + "','" + StuSsex + "'," + StuSage + ",'" + StuSdept + "')";
                SqlCommand cmd = new SqlCommand(insertStr, con);
                cmd.ExecuteNonQuery();      //将增加后的信息直接出来
            }
            catch
            {
                MessageBox.Show("输入数据违法要求！");
            }
            finally
            {
                con.Dispose();      //关闭数据库
            }
            this.studentTableAdapter.Fill(this.textDataSet.Student);

        }

2.删除

 private void button2_Click(object sender, EventArgs e)//删除
        {
            try
            {
                con.Open();     //打开数据库
                string select_Sno = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值，也就是Sno
                string delete_by_Sno = "DELETE FROM Student WHERE Sno='" + select_Sno+"'";//sql删除语句
                SqlCommand cmd = new SqlCommand(delete_by_Sno, con);
                cmd.ExecuteNonQuery();      //将增加后的信息直接出来
            }
            catch
            {
                MessageBox.Show("请选择正确行！");
            }
            finally
            {
                con.Dispose();      //关闭数据库
            }
            this.studentTableAdapter.Fill(this.studentDataSet.Student);

        }

选中


3.修改

 private void button3_Click(object sender, EventArgs e)//修改
        {
            string StuSno = textBox1.Text.Trim();
            string StuSname = textBox2.Text.Trim();
            try
            {
                con.Open();     //打开数据库
                string update_sname = "UPDATE Student SET Sname='" + StuSname + "'WHERE Sno='" + StuSno + "'";
                SqlCommand cmd = new SqlCommand(update_sname, con);
                cmd.ExecuteNonQuery();      //将增加后的信息直接出来
            }
            catch
            {
                MessageBox.Show("输入数据违反要求");
            }
            finally
            {
                con.Dispose();      //关闭数据库
            }
            this.studentTableAdapter.Fill(this.textDataSet.Student);

        }

根据学号改姓名

4.查询

private void button4_Click(object sender, EventArgs e)
        {
            string StuSno = textBox1.Text.Trim();
            String conn = "Data Source =.; Initial Catalog = Text;  Persist Security Info = True;User ID = sa; Password = 110023";
            SqlConnection sqlconnection = new SqlConnection(conn);//实例化连接对象
            try
            {
                sqlconnection.Open();
                String select_by_sno = "select * from Student where Sno='" + StuSno + "'";
                SqlCommand sqlcommand = new SqlCommand(select_by_sno, sqlconnection);
                SqlDataReader sqldatareader = sqlcommand.ExecuteReader();
                BindingSource bindingsource = new BindingSource();
                bindingsource.DataSource = sqldatareader;
                dataGridView1.DataSource = bindingsource;
            }
            catch
            {
                MessageBox.Show("查询语句有误，请认真检查SQL语句");
            }
            finally
            {
                sqlconnection.Close();
            }
        }

查询201215121