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

namespace StudentManagementSystem
{
    public partial class stuUpdate : Form
    {
        private DataSet UpdClassStudSet = new DataSet();
        private string queryStrByClass;
        private SqlConnection conn;
        private SqlDataAdapter sda;

        public stuUpdate()
        {
            InitializeComponent();
        }

        //在数据库中检索班级名称并填充到班级列表
        private void stuUpdate_Load(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            string queryString = "select Class_name from class";
            conn = CommonClass.DBConn.StuMSCon();
            //conn.Open();
            sda = new SqlDataAdapter(queryString,conn);

            sda.Fill(ds,"ds");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                cbByClass.Items.Add(ds.Tables[0].Rows[i][0]);
            cbByClass.SelectedIndex = 0;    //指定初始选项，此处为第一个记录
            //conn.Close();
        }

        //按班级检索
        private void btnSerch2_Click(object sender, EventArgs e)
        {
                queryStrByClass = "select Student_id as 学号,Student_name as 姓名,Sex as 性别,Year(getdate())-Year(Birth) as 年龄," +
                    " Class_name as 班级 from student,class where student.Class_id=class.Class_id and class.Class_name='" +
                    this.cbByClass.SelectedItem.ToString().Trim() + "'";
                //conn = CommonClass.DBConn.StuMSCon();
                //conn.Open();
                sda = new SqlDataAdapter(queryStrByClass, conn);
                UpdClassStudSet.Clear(); 
                sda.Fill(UpdClassStudSet, "student2");
                dgvResult.DataSource = UpdClassStudSet.Tables["student2"];
                //conn.Close();
        }

        //保存修改
        private void btnSave_Click(object sender, EventArgs e)
        {
                if (UpdClassStudSet.HasChanges())  //如果改动是在按班级检索的结果集上进行的，则更改该结果集
                {
                    sda = new SqlDataAdapter(queryStrByClass,conn);
                    SqlCommandBuilder builder = new SqlCommandBuilder(sda);
                    sda.Update(UpdClassStudSet.GetChanges(), "student2");    //这里提示出错：对于多个基表不支持动态 SQL 生成！！

                }
        }        
    }
}