iteamr_show.aspx.cs

vc.net编程实现的教师数据库管理系统的实现

using System;
using System.Data;
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;
using System.Data.SqlClient;

public partial class research_iteamR_show : System.Web.UI.Page
{
    private string strConnect = ConfigurationSettings.AppSettings["ConnectionString"];
    int startIndex = 0;
    string iteam = "";
    string name = "";
    string jibie = "";
    string start = "";
    string end = "";
    string dept = "";
    string shenhe = "";
    string strCmd = "";
    string strCmdNum = "";
    string strCmdCHN = "";
    int sum = 0;
    int page = 1;
    protected void Page_Load(object sender, EventArgs e)
    {
        //页面初始化
        if (!IsPostBack)
        {
            //绑定单位到dropdownlist控件
            DropDownListDataBind();
            //构造sql语句 各个查询条件应为空，查询所有的人文社科项目
            strCmd = createSQL(iteam,name,jibie,start,end, dept,shenhe);
            strCmdCHN = createSQLCHN(iteam, name, jibie, start, end, dept, shenhe);
            strCmdNum = createSQLTotal(iteam, name, jibie, start, end, dept, shenhe);

            Label1.Text = strCmdCHN;
            //显示查询结果的个数
            sum = Convert.ToInt16(SqlTotal(strCmdNum));
            Label3.Text = sum.ToString();
            //显示当前第几页
            Label6.Text = page.ToString();
            //显示总共多少页
            if (sum % DataGrid1.PageSize == 0)
                Label9.Text = (sum / DataGrid1.PageSize).ToString();
            else
                Label9.Text = (sum / DataGrid1.PageSize+1).ToString();
            //根据sql绑定数据集，通过datagrid显示查询结果
            DataGridDataBind(strCmd);
        }
    }

    //构造查询的sql语句
    private string createSQL(string iteam,string name,string jibie,string start,string end,string dept,string shenhe)
    {
        string sqlwhere = "";
        if (!iteam.Equals(""))
        {
            sqlwhere += " AND 项目名称 LIKE '%" + iteam + "%' AND 署名位次='1'";
        }

        if (!name.Equals(""))
        {
            sqlwhere += " AND 姓名 LIKE '%" + name + "%'";
        }

        if (!jibie.Equals(""))
        {
            sqlwhere += " AND 级别 = '" + jibie + "' AND 署名位次='1'";
        }

        if((!start.Equals(""))&&(!end.Equals("")))
        {
            sqlwhere += " AND CONVERT(int, 项目年度) BETWEEN " + start + " AND " + end + " AND 署名位次='1'";
        }
        else
        {
          if (!start.Equals(""))
          {
              sqlwhere += " AND  CONVERT(int, 项目年度)=" + start + " AND 署名位次='1'";
          }
          if (!end.Equals(""))
          {
              sqlwhere += " AND  CONVERT(int, 项目年度)=" + end + " AND 署名位次='1'";
          }

        }

        if (!dept.Equals(""))
        {
            sqlwhere += " AND 教师单位 = '" + dept + "' AND 署名位次='1'";
        }

        if (!shenhe.Equals(""))
        {
            sqlwhere += " AND 审核标志 = '" + shenhe + "' AND 署名位次='1'";
        }

        string str1 =
            "SELECT * " +
            "FROM S_项目视图 WHERE 1=1" + sqlwhere + " AND 项目类型='人文社科' order by 项目编号";
        return str1;
    }

    //构造查询个数的sql语句
    private string createSQLTotal(string iteam, string name, string jibie, string start, string end, string dept, string shenhe)
    {
        string sqlwhere = "";
        if (!iteam.Equals(""))
        {
            sqlwhere += " AND 项目名称 LIKE '%" + iteam + "%' AND 署名位次='1'";
        }

        if (!name.Equals(""))
        {
            sqlwhere += " AND 姓名 LIKE '%" + name + "%'";
        }

        if (!jibie.Equals(""))
        {
            sqlwhere += " AND 级别 = '" + jibie + "' AND 署名位次='1'";
        }

        if ((!start.Equals("")) && (!end.Equals("")))
        {
            sqlwhere += " AND CONVERT(int, 项目年度) BETWEEN " + start + " AND " + end + " AND 署名位次='1'";
        }
        else
        {
            if (!start.Equals(""))
            {
                sqlwhere += " AND  CONVERT(int, 项目年度)=" + start + " AND 署名位次='1'";
            }
            if (!end.Equals(""))
            {
                sqlwhere += " AND  CONVERT(int, 项目年度)=" + end + " AND 署名位次='1'";
            }

        }

        if (!dept.Equals(""))
        {
            sqlwhere += " AND 教师单位 = '" + dept + "' AND 署名位次='1'";
        }

        if (!shenhe.Equals(""))
        {
            sqlwhere += " AND 审核标志 = '" + shenhe + "' AND 署名位次='1'";
        }

        string str1 =
            "SELECT count(*) " +
            "FROM S_项目视图 WHERE 1=1" + sqlwhere + " AND 项目类型='人文社科'";
        return str1;
    }

    //构造查询的sql语句，显示给用户以汉语的形式
    private string createSQLCHN(string iteam, string name, string jibie, string start, string end, string dept, string shenhe)
    {
        string sqlwhere = "";
        if (!iteam.Equals(""))
        {
           sqlwhere += " 项目名称为" + iteam ;
        }

        if (!name.Equals(""))
        {
            sqlwhere += "  姓名为" + name;
        }

        if (!jibie.Equals(""))
        {
            sqlwhere += "  级别是" + jibie ;
        }

        if ((!start.Equals("")) && (!end.Equals("")))
        {
            sqlwhere += "  年度在 " + start + "年和 " + end + "年之间";
        }
        else
        {
            if (!start.Equals(""))
            {
                sqlwhere += "  年份为" + start + "年";
            }
            if (!end.Equals(""))
            {
                sqlwhere += "  年份为" + end + "年";
            }

        }

        if (!dept.Equals(""))
        { 
            sqlwhere += "  项目单位为" + dept;
        }

        if (!shenhe.Equals(""))
        {
            sqlwhere += "  审核标志为" + shenhe;
        }

        string str1 =
            "您选择的是人文社科项目。"  + sqlwhere;
        return str1;
    }

    //执行查询搜索结果显示查询个数
    public static object SqlTotal(string strCmd)
    {
        //创建数据适配器对象
        SqlConnection objConnection = new SqlConnection("Data Source=localhost;Initial Catalog=new;Persist Security Info=True;User ID=sa;Password=teacherdb");
        SqlCommand objCommand = new SqlCommand(strCmd, objConnection);
        //objCommand.Connection = objConnection;
       try
       {
           if (objConnection.State == ConnectionState.Closed)
           {
               objConnection.Open();
            }
            return objCommand.ExecuteScalar();
       }
      catch (Exception e)
       {
            throw e;
       }
       finally
       {
          if (objConnection.State == ConnectionState.Open)
            {
                objConnection.Close();
          }
       }
    }

    //把按条件查询的结果绑定到datagrid上，并显示出来
    private void DataGridDataBind(string strCmd)
    {


        //创建数据适配器对象
        SqlConnection objConnection = new SqlConnection(strConnect);
        //objCommand.Connection = objConnection;
        SqlDataAdapter da = new SqlDataAdapter(strCmd, objConnection);
        //创建DataSet对象
        DataSet ds = new DataSet();
        try
        {
            //从指定的索引开始取PageSize条记录
            da.Fill(ds, startIndex, DataGrid1.PageSize, "CurDataTable");
            //填充数据集
            da.Fill(ds, "AllDataTable");
            //设置DataGrid控件实际要显示的项数
            DataGrid1.VirtualItemCount = ds.Tables["AllDataTable"].Rows.Count;
            //进行数据绑定
            DataGrid1.DataSource = ds.Tables["CurDataTable"];
            DataGrid1.DataBind();
        }
        catch (Exception error)
        {
            Response.Write(error.ToString());
        }
        finally
        {
            objConnection.Close();
            ds.Clear();
        }
    }

    //绑定到dropdownlist上所有的单位
    private void DropDownListDataBind()
    {

        //创建数据适配器对象
        SqlConnection objConnection = new SqlConnection(strConnect);
        //objCommand.Connection = objConnection;
        SqlDataAdapter da = new SqlDataAdapter("select * from H_Code_所在单位", objConnection);
        //创建DataSet对象
        DataSet ds = new DataSet();
        try
        {
            //从指定的索引开始取PageSize条记录
            da.Fill(ds, "CurDataTable");
            //填充数据集
            da.Fill(ds, "AllDataTable");
            //设置DataGrid控件实际要显示的项数
            DataGrid1.VirtualItemCount = ds.Tables["AllDataTable"].Rows.Count;
            //进行数据绑定
            DropDownList4.DataSource = ds.Tables["CurDataTable"];
            DropDownList4.DataTextField = "content";
            DropDownList4.DataValueField = "content";
            DropDownList4.DataBind();
            DropDownList4.Items.Insert(0, "");

        }
        catch (Exception error)
        {
            Response.Write(error.ToString());
        }
        finally
        {
            objConnection.Close();
            // ds.Clear();
        }
    }

    //当单击查询的按钮的时候，按照查询的条件重新绑定datagrid 并重新显示查询的个数
    protected void Button1_Click(object sender, EventArgs e)
    {
        //得到各个控件的值（即查询条件）
        iteam = TextBox1.Text;
        name = TextBox2.Text;
        jibie = DropDownList1.SelectedItem.Text;
        start = DropDownList2.SelectedItem.Text;
        end = DropDownList3.SelectedItem.Text;
        dept = DropDownList4.SelectedItem.Text;
        shenhe = DropDownList5.SelectedItem.Text;


        //根据上一步设置的条件重新构造sql语句
        strCmd = createSQL(iteam, name, jibie, start, end, dept, shenhe);
        strCmdCHN = createSQLCHN(iteam, name, jibie, start, end, dept, shenhe);
        strCmdNum = createSQLTotal(iteam, name, jibie, start, end, dept, shenhe);
        //显示按条件搜索的记录数 strCmdNum构造的sql语句 
        sum = Convert.ToInt16(SqlTotal(strCmdNum));
        Label3.Text = sum.ToString();

        //根据sql绑定数据集，通过datagrid显示查询结果
        DataGrid1.CurrentPageIndex = 0;
        //Label2.Text = DataGrid1.CurrentPageIndex.ToString();
        startIndex = DataGrid1.PageSize * DataGrid1.CurrentPageIndex;  //取得当前页为止总共有多少条记录，以便在下一页就从该记录开始读取
        DataGridDataBind(strCmd);   //根据 strCmd（sql语句）重新绑定数据集，通过datagrid显示查询结果

        //显示当前第几页
        page = DataGrid1.CurrentPageIndex+1;
        Label6.Text = page.ToString();
        //显示总共多少页
        if (sum % DataGrid1.PageSize == 0)
            Label9.Text = (sum / DataGrid1.PageSize).ToString();
        else
            Label9.Text = (sum / DataGrid1.PageSize + 1).ToString();

        Label1.Text = strCmdCHN;
    }

    //datagtid按条件查询时，分页执行的动作
    protected void DataGrid1_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
    {
        //设置DataGrid当前页的索引值为用户选择的页的索引
        DataGrid1.CurrentPageIndex = e.NewPageIndex;
        //取得当前页为止总共有多少条记录，以便在下一页就从该记录开始读取
        startIndex = DataGrid1.PageSize * DataGrid1.CurrentPageIndex;
        //取得当前控件的值（搜索条件）
        iteam = TextBox1.Text;
        name = TextBox2.Text;
        jibie = DropDownList1.SelectedItem.Text;
        start = DropDownList2.SelectedItem.Text;
        end = DropDownList3.SelectedItem.Text;
        dept = DropDownList4.SelectedItem.Text;
        shenhe = DropDownList5.SelectedItem.Text;
        //构造sql语句
        strCmd = createSQL(iteam, name, jibie, start, end, dept, shenhe);
        //重新绑定数据
        DataGridDataBind(strCmd);
        //Label1.Text = strCmd;
        //显示当前第几页
        page = DataGrid1.CurrentPageIndex+1;
        Label6.Text = page.ToString();
    }
}