课程安排.aspx.cs

学生管理系统 功能不是很强大 但是很实用

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class 课程安排 : System.Web.UI.Page
{
    public
      SqlDataAdapter myadapter;
    SqlCommandBuilder scb;
    DataSet myDataSet;
    SqlConnection mysqlconnection;
    SqlCommand mysqlcommand;
    int i;
    public void Load()
    {
        SqlCommand mysqlcommand = mysqlconnection.CreateCommand();
        mysqlcommand.CommandText = "select 成绩表.学号,学生表.姓名,成绩表.任课教师编号,教师表.姓名,成绩表.课程号,课程表.课程名,成绩表.成绩,成绩表.日期 from 教师表,课程表,成绩表,学生表 where 教师表.教师编号=成绩表.任课教师编号 and 成绩表.学号=学生表.学号 and 成绩表.课程号=课程表.课程号 order by 成绩表.日期 desc";
        myadapter = new SqlDataAdapter();
        myadapter.SelectCommand = mysqlcommand;
        myDataSet = new DataSet();
        mysqlconnection.Open();
        int numberOfRow = myadapter.Fill(myDataSet, "教师表");
        this.GridView1.DataSource = new DataView(myDataSet.Tables["教师表"]);
        this.GridView1.DataBind();
        Label2.Text = "共有" + numberOfRow + "条记录";
        mysqlconnection.Close();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        String sqlconn = "data source=.;database=学生成绩智能管理系统;user id=sa;password=sa;";
        mysqlconnection = new SqlConnection(sqlconn);
        mysqlcommand = mysqlconnection.CreateCommand();
        mysqlcommand.CommandText = "select 学号 from 学生表";
        mysqlconnection.Open();
        SqlDataReader myDataReader = mysqlcommand.ExecuteReader();
        if (!IsPostBack)
        {
            while (myDataReader.Read())
            {
                this.DropDownList1.Items.Add(myDataReader.GetValue(0).ToString());
                this.DropDownList2.Items.Add(myDataReader.GetValue(0).ToString());
            }

            myDataReader.Close();
        }
        mysqlconnection.Close();
        mysqlconnection.Open();
        if (!IsPostBack)
        {
            mysqlcommand = mysqlconnection.CreateCommand();
            mysqlcommand.CommandText = "select distinct 课程号 from 课程表";
            myDataReader = mysqlcommand.ExecuteReader();
            while (myDataReader.Read())
            {
                this.DropDownList3.Items.Add(myDataReader.GetValue(0).ToString());
            }
            myDataReader.Close();
        }
        mysqlconnection.Close();
        mysqlconnection.Open();
        if (!IsPostBack)
        {
            mysqlcommand.CommandText = "select 教师编号 from 教师表";
            myDataReader = mysqlcommand.ExecuteReader();
            while (myDataReader.Read())
            {
                this.DropDownList4.Items.Add(myDataReader.GetValue(0).ToString());
            }
            myDataReader.Close();
        }
        mysqlconnection.Close();
        
        if (!IsPostBack)
        {
            for (i = 1999; i <= 2020; i++)
                this.DropDownList5.Items.Add(Convert.ToString(i));
            for (i = 1; i <= 12; i++)
                this.DropDownList6.Items.Add(Convert.ToString(i));
            for (i = 1; i <= 31; i++)
                this.DropDownList7.Items.Add(Convert.ToString(i));
        }
        if (!IsPostBack){
        mysqlcommand.CommandText = "select 课程名 from 课程表 where 课程号='001'";
        mysqlconnection.Open();
        myDataReader = mysqlcommand.ExecuteReader();
        if (myDataReader.Read())
        {
            TextBox1.Text = myDataReader.GetValue(0).ToString();
        }
        myDataReader.Close();
        mysqlconnection.Close();
        }
        mysqlcommand.CommandText = "select  姓名 from 教师表 where 教师编号='001'";
        mysqlconnection.Open();
        myDataReader = mysqlcommand.ExecuteReader();
        if (myDataReader.Read())
        {
            TextBox2.Text = myDataReader.GetValue(0).ToString();
        }
        myDataReader.Close();
        mysqlconnection.Close();
        Load();
    }
    protected void DropDownList3_SelectedIndexChanged(object sender, EventArgs e)
    {
        mysqlcommand.CommandText = "select  课程名 from 课程表 where 课程号='" + DropDownList3.SelectedValue + "'";
        mysqlconnection.Open();
        SqlDataReader myDataReader = mysqlcommand.ExecuteReader();
        if(myDataReader.Read())
        {
            TextBox1.Text=myDataReader.GetValue(0).ToString();
        }
        myDataReader.Close();
        mysqlconnection.Close();
    }
    protected void DropDownList4_SelectedIndexChanged(object sender, EventArgs e)
    {
        mysqlcommand.CommandText = "select  姓名 from 教师表 where 教师编号='" + DropDownList4.SelectedValue + "'";
        mysqlconnection.Open();
        SqlDataReader myDataReader = mysqlcommand.ExecuteReader();
        if (myDataReader.Read())
        {
            TextBox2.Text = myDataReader.GetValue(0).ToString();
        }
        myDataReader.Close();
        mysqlconnection.Close();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string cTime;
        int i=0,j=0;
        string[] command= new string[100];
        SqlCommand mysqlcommand2 = mysqlconnection.CreateCommand();
        cTime = DropDownList5.Text + "-" + DropDownList6.Text + "-" + DropDownList7.Text;
        mysqlcommand.CommandText = "select  学号 from 学生表 where 学号> ='"+DropDownList1.Text+"' and 学号<='"+DropDownList2.Text+"'";
        mysqlconnection.Open();
        SqlDataReader myDataReader = mysqlcommand.ExecuteReader();
        while(myDataReader.Read())
        {
            command[i]= myDataReader.GetValue(0).ToString();
            i++;
        }
        myDataReader.Close();
        int returnVale=0;
        for(j=0;j<i;j++)
        {
            mysqlcommand2.CommandText = "select 课程号 as 数目 from 成绩表 where 课程号='" + DropDownList3.Text + "' and  学号='" + command[j] + "' and 日期='" + cTime + "' and 任课教师编号='" + DropDownList4.Text + "'";
            myDataReader = mysqlcommand2.ExecuteReader();
            if(myDataReader.Read()==true)
            {
                myDataReader.Close();
                returnVale += 1;
            }
            else
            {
                myDataReader.Close();
                mysqlcommand2.CommandText = "insert 成绩表(课程号,学号,日期,任课教师编号) values('" + DropDownList3.Text + "','" + command[j] + "','" + cTime + "','" + DropDownList4.Text + "')";
                mysqlcommand2.ExecuteNonQuery();
            }
        }
       string str="'有"+returnVale+"条记录重复!'";
       Label1.Text = str;
        mysqlconnection.Close();
        Load();
    }  
protected void  Button2_Click(object sender, EventArgs e)
{
     string cTime;
        int i=0,j=0;
        string[] command= new string[100];
        SqlCommand mysqlcommand2 = mysqlconnection.CreateCommand();
        cTime = DropDownList5.Text + "-" + DropDownList6.Text + "-" + DropDownList7.Text;
        mysqlcommand.CommandText = "select  学号 from 学生表 where 学号> ='"+DropDownList1.Text+"' and 学号<='"+DropDownList2.Text+"'";
        mysqlconnection.Open();
        SqlDataReader myDataReader = mysqlcommand.ExecuteReader();
        while(myDataReader.Read())
        {
            command[i]= myDataReader.GetValue(0).ToString();
            i++;
        }
        myDataReader.Close();
        int returnVale=0;
        for(j=0;j<i;j++)
        {
            mysqlcommand2.CommandText = "select 课程号 as 数目 from 成绩表 where 课程号='" + DropDownList3.Text + "' and  学号='" + command[j] + "' and 日期='" + cTime + "' and 任课教师编号='" + DropDownList4.Text + "'";
            myDataReader = mysqlcommand2.ExecuteReader();
            if(myDataReader.Read()==false)
            {
                myDataReader.Close();
            }
            else
            {
                myDataReader.Close();
                mysqlcommand2.CommandText = "delete 成绩表 where 课程号='" + DropDownList3.Text + "'and  学号='" + command[j] + "'and 日期='" + cTime + "'and 任课教师编号='" + DropDownList4.Text + "'";
                mysqlcommand2.ExecuteNonQuery();
                returnVale += 1;
            }
        }
       string str="'成功删除"+returnVale+"条记录!'";
       Label1.Text = str;
        mysqlconnection.Close();
        Load();
}
}