第六章 数据筛选和排序

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

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
      
        DBHelper db = new DBHelper();
        
        DataSet ds = new DataSet();
        
        SqlDataAdapter der = null;



        public Form2()
        {
            InitializeComponent();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        public void getstudent()
        {
            //清空原有数据
            if (ds.Tables["student"] != null) {
                ds.Tables["student"].Clear();
            
            }

            string ent =@"SELECT [StudentNo] as 学号
      ,[LoginPwd]  as 密码
      ,[StudentName]  as 姓名
      ,[Sex] 
      ,[GradeId]  
      ,[Phone]  as  电话
      ,[Address] as  地址
      ,[BornDate]  as 出生日期
      ,[Email]
  FROM [MySchool].[dbo].[Student] ";

            der = new SqlDataAdapter(ent,db.Connection);
            der.Fill(ds,"student");

         this.dataGridView1.DataSource=ds.Tables["student"];
           
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            getstudent();
          
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
          //  string message = string.Format("选中了{0}节点,深度是{1}",treeView1.SelectedNode.Text,treeView1.SelectedNode.Level);
          //  MessageBox.Show(message,"提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
            
            shitu();
            
        }

        public  void  shitu()
        {
          DataView dv = new DataView(ds.Tables["student"]);

          string filer = "";

            if(treeView1.SelectedNode.Text.Equals("S1")){
                filer = "[GradeId]=1";              

            }else if(treeView1.SelectedNode.Text.Equals("S2"))
            {
               filer = "[GradeId]=2";
            }else if(treeView1.SelectedNode.Text.Equals("Y2"))
            {
               filer="[GradeId]=3";

            }

          if(treeView1.SelectedNode.Level==2){
              MessageBox.Show(treeView1.SelectedNode.Level.ToString());

           string gender = treeView1.SelectedNode.Tag.ToString();

           int num = Convert.ToInt32(treeView1.SelectedNode.Parent.Tag);

           MessageBox.Show(gender);
            
           filer = "GradeId="+num+" and Sex='"+gender+"'";

            }
            dv.RowFilter = filer;

            dv.Sort = "GradeId  desc";

          
          this.dataGridView1.DataSource = dv;


        }


        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult fell;

            fell = MessageBox.Show("确定要删除该学生吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            MessageBox.Show(fell.ToString());

            if (fell == DialogResult.Yes)
            {
              
                if (dataGridView1.SelectedRows.Count > 0)
                {
               
                   string studentNo = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();

                   MessageBox.Show(studentNo);
                    try
                    {
                        db.OpenConnection();

                        string sql = string.Format("delete  from dbo.Student where StudentNo='{0}'", studentNo);
                        SqlCommand command = new SqlCommand(sql, db.Connection);

                        int result = command.ExecuteNonQuery();
                        MessageBox.Show(result.ToString());
                        if (result == 1)
                        {
                            MessageBox.Show("删除成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            getstudent();

                        }

                    }
                    catch (Exception r)
                    {
                        MessageBox.Show(r.Message);
                        MessageBox.Show("系统出现错误!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                    finally
                    {

                        db.CloseConnection();
                    }

                }
            }
        }

        private void 获得idToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show(this.dataGridView1.SelectedCells[0].Value.ToString());


        }



      




            }

        }