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

namespace WindowsApplication1
{
    public partial class Form7 : Form
    {
        public Form7()
        {
            InitializeComponent();
        }
        DataBase mydata = new DataBase();
        /// <summary>
        /// combobox1数据变换事件
        /// </summary>
        private void combobox1change()
        {
            //查询所有的班级名称
            //第一种写法：
            /*DataSet classdataset= mydata.GetDataSet("select distinct CLASS from student");
            for (int i = 0; i < classdataset.Tables[0].Rows.Count; i++)
            {
                comboBox1.Items.Add(classdataset.Tables[0].Rows[i]["class"].ToString());
            }*/
            //第二种写法
            /* DataSet ds = mydata.GetDataSet("select sno,sname from student");
             if (ds.Tables[0].Rows.Count > 0)
             {
                 comboBox1.DataSource = ds.Tables[0].DefaultView;
                 comboBox1.DisplayMember = "sname";
                 comboBox1.ValueMember = "sno";
                 comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
             }*/
            //第三种写法
            DataTable dt = mydata.GetDataTable("select distinct class from student");
            if (dt.Rows.Count > 0)
            {
                comboBox1.DataSource = dt.DefaultView;
                comboBox1.DisplayMember = "class";
                comboBox1.ValueMember = "class";
                comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            }
        }
        /// <summary>
        /// combobox2数据变换事件
        /// </summary>
        private void combobox2change(string sqlstring)
        {
            DataTable dt = mydata.GetDataTable(sqlstring);
            if (dt.Rows.Count > 0)
            {
                comboBox2.DataSource = dt.DefaultView;
                comboBox2.DisplayMember = "sname";
                comboBox2.ValueMember = "sno";
                comboBox2.DropDownStyle = ComboBoxStyle.DropDownList;
            }
        }
        /// <summary>
        /// 初始化数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form7_Load(object sender, EventArgs e)
        {
            combobox1change();
            object classone = mydata.GetScalar("select top 1 class from student");
            string sqlstring = "select sno,sname from student where class='" + classone.ToString().Trim() + "'";
            combobox2change(sqlstring);
        }


        /// <summary>
        /// 切换班级时候，连动combobox2姓名栏目的信息。
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sqlstring = "select sno,sname from student where class='" + comboBox1.Text.Trim()+"'";
            combobox2change(sqlstring);
        }
        /// <summary>
        /// 查询某个选中的学生信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            if (comboBox2.Items.Count > 0)
            {
                string sqlstring = "select top 1 * from student where sname='" + comboBox2.Text.Trim()+"'";
                DataTable dt = mydata.GetDataTable(sqlstring);
                label20.Text = dt.Rows[0]["sno"].ToString();
                label11.Text = dt.Rows[0]["sname"].ToString();
                label12.Text = dt.Rows[0]["sex"].ToString();
                label14.Text = dt.Rows[0]["birthday"].ToString();
                label13.Text = dt.Rows[0]["class"].ToString();

                textBox3.Text = dt.Rows[0]["sno"].ToString();
                textBox4.Text = dt.Rows[0]["sname"].ToString();
                comboBox3.Text = dt.Rows[0]["sex"].ToString();
                textBox5.Text = dt.Rows[0]["birthday"].ToString();
                textBox6.Text = dt.Rows[0]["class"].ToString();

            }
        }
        /// <summary>
        /// 按照学号查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text != string.Empty)
            {
                if (mydata.GetdataRow("select sno from student where sno=" + textBox1.Text.Trim()) > 0)
                {
                    DataTable dt = mydata.GetDataTable("select * from student where sno=" + textBox1.Text.Trim());
                    label20.Text = dt.Rows[0]["sno"].ToString();
                    label11.Text = dt.Rows[0]["sname"].ToString();
                    label12.Text = dt.Rows[0]["sex"].ToString();
                    label14.Text = dt.Rows[0]["birthday"].ToString();
                    label13.Text = dt.Rows[0]["class"].ToString();

                    textBox3.Text = dt.Rows[0]["sno"].ToString();
                    textBox4.Text = dt.Rows[0]["sname"].ToString();
                    comboBox3.Text = dt.Rows[0]["sex"].ToString();
                    textBox5.Text = dt.Rows[0]["birthday"].ToString();
                    textBox6.Text = dt.Rows[0]["class"].ToString();
                }
                else
                {
                    MessageBox.Show("查无此人","信息提示");
                }
            }
        }
        /// <summary>
        /// 删除学生信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            if (label20.Text.Trim() != "未显示")
            {
                string sqlstring = "delete from student where sno=" + label20.Text.Trim();
                int k = mydata.ExecuteSQL(sqlstring);
                if (k > 0)
                {
                    MessageBox.Show("已有"+k.ToString()+"条数据被删除", "信息提示");
                    label20.Text = "未显示";
                    label11.Text = "未显示";
                    label12.Text = "未显示";
                    label14.Text = "未显示";
                    label13.Text = "未显示";
                    string sqlstring1 = "select sno,sname from student where class='" + comboBox1.Text.Trim() + "'";
                    combobox2change(sqlstring1);
                    combobox1change();
                    clearmess();
                }
            }
        }
        /// <summary>
        /// 按照姓名查询数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            if (textBox2.Text != string.Empty)
            {
                if (mydata.GetdataRow("select sno from student where sname='" + textBox2.Text.Trim()+"'") > 0)
                {
                    DataTable dt = mydata.GetDataTable("select * from student where sname='" + textBox2.Text.Trim()+"'");
                    label20.Text = dt.Rows[0]["sno"].ToString();
                    label11.Text = dt.Rows[0]["sname"].ToString();
                    label12.Text = dt.Rows[0]["sex"].ToString();
                    label14.Text = dt.Rows[0]["birthday"].ToString();
                    label13.Text = dt.Rows[0]["class"].ToString();

                    textBox3.Text = dt.Rows[0]["sno"].ToString();
                    textBox4.Text = dt.Rows[0]["sname"].ToString();
                    comboBox3.Text = dt.Rows[0]["sex"].ToString();
                    textBox5.Text = dt.Rows[0]["birthday"].ToString();
                    textBox6.Text = dt.Rows[0]["class"].ToString();
                }
                else
                {
                    MessageBox.Show("查无此人", "信息提示");
                }
            }
        }
        /// <summary>
        /// 插入信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button6_Click(object sender, EventArgs e)
        {
            if (textBox3.Text != "" && textBox4.Text != "" && (comboBox3.Text == "男" || comboBox3.Text == "女"))
            {
                if (mydata.GetdataRow("select sno from student where sno=" + textBox3.Text.Trim()) > 0)
                {
                    MessageBox.Show("学号禁止重复！", "信息提示");
                }
                else
                {
                    string sqlstring = "insert into student(sno,sname,sex,birthday,class) values(" + textBox3.Text.Trim() + ",'" + textBox4.Text.Trim() + "','" + comboBox3.Text.Trim() + "','" + textBox5.Text.Trim() + "','" + textBox6.Text.Trim() + "')";
                    int k = mydata.ExecuteSQL(sqlstring);
                    if (k > 0)
                    {
                        MessageBox.Show("已经成功插入" + k.ToString() + "条数据", "信息提示");
                        clearmess();
                        combobox1change();
                    }
                    else
                    {
                        MessageBox.Show("没有插入数据，插入数据失败", "信息提示");
                    }
                }

            }
            else
            {
                MessageBox.Show("禁止学号、姓名为空；或者没有选择性别。", "信息提示");
            }
        }
        /// <summary>
        /// 清除显示的基本信息
        /// </summary>
        private void clearmess()
        { 
            textBox3.Text = "";
            textBox4.Text = "";
            comboBox3.Text = "男";
            textBox5.Text = "";
            textBox6.Text = "";
            textBox3.Focus();
        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button5_Click(object sender, EventArgs e)
        {
            if (textBox3.Text != "" && textBox4.Text != "" && (comboBox3.Text == "男" || comboBox3.Text == "女"))
            {
                
                    string sqlstring = "update student set sname='" + textBox4.Text.Trim() + "',sex='" + comboBox3.Text.Trim() + "',birthday='" + textBox5.Text.Trim() + "',class='" + textBox6.Text.Trim() + "' where sno="+textBox3.Text.Trim();
                    int k = mydata.ExecuteSQL(sqlstring);
                    if (k > 0)
                    {
                        MessageBox.Show("已经成功修改" + k.ToString() + "条数据", "信息提示");
                        clearmess();
                        combobox1change();
                    }
                    else
                    {
                        MessageBox.Show("没有修改数据，修改数据失败", "信息提示");
                    }

            }
            else
            {
                MessageBox.Show("禁止学号、姓名为空；或者没有选择性别。", "信息提示");
            }
        }
        
        
    }
}