iteam_add.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_iteam_add : System.Web.UI.Page
{
    private string strConnect = ConfigurationSettings.AppSettings["ConnectionString"];
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DropDownListDataBind();

        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        DropDownList10.Items.Clear();
        //DropDownList10.Items.Insert(0, "两个以上用户，请选择");
        SqlConnection conn = new SqlConnection("data source=localhost;database=new;user=sa; password=teacherdb");
        conn.Open();
        SqlDataAdapter da = new SqlDataAdapter("select 姓名,所在单位,教师编号 from H_人事核心视图  where 姓名 like '" + TextBox9.Text + "%' order by 姓名", conn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        //读取内容文件的数据到临时数组
        string[] temp = new string[ds.Tables[0].Rows.Count];
        int i = 0;
        foreach (DataRow dr in ds.Tables[0].Rows)
        {

            DropDownList10.Items.Insert(i, dr["所在单位"].ToString()); //+dr["所在单位"].ToString();
            //DropDownList10.SelectedItem.Text = dr["所在单位"].ToString();
            //DropDownList10.Items.FindByText(dr["所在单位"].ToString()).Selected = true;
            DropDownList10.SelectedIndex = i;
            TextBox11.Text= dr["教师编号"].ToString();
            i++;
        }
        if (i == 0)
        {
            DropDownList10.Items.Insert(0, "校外");
           TextBox11.Text = "9999999";
        }
        if(i==1)
        {
            //TextBox11.Text = dr["教师编号"].ToString();
            DropDownList10.Enabled = false;
            TextBox11.Enabled = false;
        }
        //将临时数组的内容赋给返回数组
        if (conn.State == ConnectionState.Open)
            conn.Close();

    }
    protected void Button4_Click(object sender, EventArgs e)
    {
       // ListBox1.Items.Add(new ListItem(TextBox9.Text, TextBox9.Text));
        this.ListBox1.Items.Add(this.TextBox11.Text + "," + this.TextBox9.Text + "," + this.DropDownList7.Text + "," + this.DropDownList10.Text);
        TextBox9.Text = "";
        DropDownList10.Items.Clear();
        TextBox11.Text = "";
        DropDownList10.Enabled = true;
        TextBox11.Enabled = true;
       
       
    }
    protected void Button5_Click(object sender, EventArgs e)
    {
        ListBox1.Items.Remove(ListBox1.SelectedItem);
    }

    protected void DropDownList10_SelectedIndexChanged(object sender, EventArgs e)
    {
        TextBox11.Text = "";
        SqlConnection objConnection = new SqlConnection("data source=localhost;database=new;user=sa; password=teacherdb");
        SqlCommand objCommand = new SqlCommand("", objConnection);
        objCommand.CommandText = "select 姓名,所在单位,教师编号 from H_人事核心视图  where 姓名 like '" + TextBox9.Text + "%' and 所在单位 ='" + DropDownList10.SelectedItem.Text + "'";
        objConnection.Open();
         SqlDataReader result=objCommand.ExecuteReader();
        if(result.Read()==true)
        {
           TextBox11.Text = result["教师编号"].ToString();
          
        }
        if (objConnection.State == ConnectionState.Open)
            objConnection.Close();

    }
    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");
            //进行数据绑定
            DropDownList3.DataSource = ds.Tables["CurDataTable"];
            DropDownList3.DataTextField = "content";
            DropDownList3.DataValueField = "code";
            DropDownList3.DataBind();
            DropDownList3.Items.Insert(0, "");

        }
        catch (Exception error)
        {
            Response.Write(error.ToString());
        }
        finally
        {
            objConnection.Close();
            // ds.Clear();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {     //初始化流水号，并输出流水号，执行存储过程iteam_add_nrls
          string lw;
        //lw为接受流水号的变量
        //初始化sql连接
          SqlConnection objConnection3 = new SqlConnection("data source=localhost;database=new;user=sa; password=teacherdb");
          SqlCommand cm3 = new SqlCommand("iteam_add_nrls", objConnection3);
        cm3.CommandType = CommandType.StoredProcedure;
        //类型为存储过程
        //申明存储过程变量
        cm3.Parameters.Add(new SqlParameter("@xyear", SqlDbType.Char, 4));
        cm3.Parameters.Add(new SqlParameter("@lwnum", SqlDbType.Char, 5));
        cm3.Parameters.Add(new SqlParameter("@depart", SqlDbType.Char, 3));
      //给存储过程变量赋值，并确定参数是输入参数还是输出参数
        cm3.Parameters["@xyear"].Value = DropDownList2.SelectedItem.Text;
        cm3.Parameters["@xyear"].Direction = ParameterDirection.Input;
        cm3.Parameters["@depart"].Direction = ParameterDirection.Input;
        cm3.Parameters["@depart"].Value = DropDownList3.SelectedItem.Value;
        cm3.Parameters["@lwnum"].Direction = ParameterDirection.Output;
        //打开连接并执行存储过程
        cm3.Connection.Open();
        cm3.ExecuteNonQuery();
        //将输出的流水号赋给lw变量
        lw = cm3.Parameters["@lwnum"].Value.ToString();
        //关闭存储过程连接
        cm3.Connection.Close();

      
        //count变量用于记录列表框文本行数
        int count = ListBox1.Items.Count;
        int index;
        //index变量用于控制列表框文本是第几行
        String[] local;
        //local变量用于分割列表框一行中的文本
        /*   int location;
         * Response.Write(location.ToString());
         * location = this.ListBox1.Items[index].ToString().IndexOf(",");
        cm2.Parameters["@xmyear"].Value = DropDownList2.SelectedItem.Text;
        cm2.Parameters["@jsnum"].Value = this.ListBox1.Items[index].ToString().Substring(0, location);
         */
        //执行向项目人员表插入记录的存储过程
        //初始化sql连接
        SqlConnection objConnection2 = new SqlConnection("data source=localhost;database=new;user=sa; password=teacherdb");
        SqlCommand cm2 = new SqlCommand("iteam_add_xmry", objConnection2);
        cm2.CommandType = CommandType.StoredProcedure;
        //类型为存储过程
        //申明存储过程变量
        cm2.Parameters.Add(new SqlParameter("@xmbh", SqlDbType.Char, 12));
        cm2.Parameters.Add(new SqlParameter("@jsnum", SqlDbType.Char, 7));
        cm2.Parameters.Add(new SqlParameter("@smwc", SqlDbType.Char, 2));

        cm2.Parameters.Add(new SqlParameter("@department", SqlDbType.Char, 25));
       // 打开连接 
        cm2.Connection.Open();
        //每读1行数据 向项目人员表插入1行数据
        for (index = 0; index < count; index++)
        {    
            //以逗号分隔列表框1行中的文本
            local = this.ListBox1.Items[index].ToString().Split(',');
            //拼接项目编号项目年度+项目部门号+流水号
            //给存储过程输入参数赋值
            //项目编号
            cm2.Parameters["@xmbh"].Value = DropDownList2.SelectedItem.Text + DropDownList3.SelectedItem.Value + lw;
            //教师编号
            cm2.Parameters["@jsnum"].Value = local[0];
            //署名位次
            cm2.Parameters["@smwc"].Value = local[2];
           //所属部门
            cm2.Parameters["@department"].Value = DropDownList3.SelectedItem.Value; 

            //向表中插入数据，执行存储过程
            cm2.ExecuteNonQuery();
 
        }
        //关闭连接
        cm2.Connection.Close();
        int location=0;

        //location变量记录项目主持人在列表框的位置
        //初始化sql连接
        SqlConnection objConnection = new SqlConnection("data source=localhost;database=new;user=sa; password=teacherdb");
        SqlCommand cm = new SqlCommand("iteam_add_xmnr", objConnection);
            cm.CommandType = CommandType.StoredProcedure;
            //类型为存储过程
            //申明存储过程变量
            cm.Parameters.Add(new SqlParameter("@xmnum", SqlDbType.Char, 12));
            cm.Parameters.Add(new SqlParameter("@xmyear", SqlDbType.Char, 4));
            cm.Parameters.Add(new SqlParameter("@xmclass", SqlDbType.Char, 2));
            cm.Parameters.Add(new SqlParameter("@xmname", SqlDbType.Char, 300));
            cm.Parameters.Add(new SqlParameter("@xmsource", SqlDbType.Char, 60));
            cm.Parameters.Add(new SqlParameter("@stime", SqlDbType.DateTime, 8));
            cm.Parameters.Add(new SqlParameter("@pznum", SqlDbType.Char, 200));
            cm.Parameters.Add(new SqlParameter("@endtime", SqlDbType.DateTime, 8));
            cm.Parameters.Add(new SqlParameter("@zzmoney", SqlDbType.Decimal, 5));
            cm.Parameters.Add(new SqlParameter("@xmprocess", SqlDbType.Char, 10));
            cm.Parameters.Add(new SqlParameter("@ettime", SqlDbType.DateTime, 8));
            cm.Parameters.Add(new SqlParameter("@etformal", SqlDbType.Char, 2));
            cm.Parameters.Add(new SqlParameter("@etnotice", SqlDbType.Char, 20));
            cm.Parameters.Add(new SqlParameter("@sign", SqlDbType.Char, 1));
            cm.Parameters.Add(new SqlParameter("@kyclass", SqlDbType.Char, 1));
            cm.Parameters.Add(new SqlParameter("@xmzcr", SqlDbType.Char, 20));
            cm.Parameters.Add(new SqlParameter("@xmms", SqlDbType.Char, 20));

            cm.Parameters.Add(new SqlParameter("@class", SqlDbType.Char,1 ));
            cm.Parameters.Add(new SqlParameter("@depart", SqlDbType.Char, 20));
            //给存储过程输入参数赋值
            //拼接项目编号项目年度+项目部门号+流水号
            cm.Parameters["@xmnum"].Value = DropDownList2.SelectedItem.Text + DropDownList3.SelectedItem.Value+lw;
        //项目年度
            cm.Parameters["@xmyear"].Value = DropDownList2.SelectedItem.Text;
        //项目类别
            cm.Parameters["@xmclass"].Value = DropDownList1.SelectedItem.Value;
        //项目名称
            cm.Parameters["@xmname"].Value = TextBox1.Text;
        //项目来源
            cm.Parameters["@xmsource"].Value = TextBox2.Text;
        //起始时间
            cm.Parameters["@stime"].Value = System.Convert.ToDateTime(TextBox4.Text);
        //批准编号
            cm.Parameters["@pznum"].Value = TextBox3.Text;
        //截至时间
            cm.Parameters["@endtime"].Value = System.Convert.ToDateTime(TextBox5.Text);
        //资助经费
            cm.Parameters["@zzmoney"].Value = System.Convert.ToDecimal(TextBox6.Text);
        //项目进度
            cm.Parameters["@xmprocess"].Value = DropDownList4.SelectedItem.Text;
        //结束时间
            cm.Parameters["@ettime"].Value = System.Convert.ToDateTime(TextBox7.Text);
        //结题形式
            cm.Parameters["@etformal"].Value = DropDownList5.SelectedItem.Value;
        //结题意见
            cm.Parameters["@etnotice"].Value = TextBox8.Text;
        //以级别区分插入标志的不同
        /*
            if (Session["level"].ToString().Equals('2'))
            {
                cm.Parameters["@sign"].Value = '3';

            }

            if (Session["level"].ToString().Equals('3'))
            {
                cm.Parameters["@sign"].Value = '0';

            }

         */
        //有待改进，需要前面传1个参数
            cm.Parameters["@sign"].Value ='0';
        //科研类型
            cm.Parameters["@kyclass"].Value = DropDownList1.SelectedItem.Value;
        //给项目主持人赋值
            for (index = 0; index < count; index++)
            {    
                //已逗号分隔读取列表框内容
                local = this.ListBox1.Items[index].ToString().Split(',');
                //署名位次为1的是项目主持人
                if (local[2].Equals('1'))
                {
                    location = index;
                   
                }

               

            }
        //以逗号分隔读取项目主持人
            local = this.ListBox1.Items[location].ToString().Split(',');
            //将项目主持人的教师编号存入项目主持人的项
            cm.Parameters["@xmzcr"].Value =local[0];
        //项目秘书不明，初始为0
            cm.Parameters["@xmms"].Value = '0';
            //级别
             cm.Parameters["@class"].Value = DropDownList6.SelectedItem.Value;
        //项目部门
             cm.Parameters["@depart"].Value = DropDownList3.SelectedItem.Value;
           //打开连接
              cm.Connection.Open();
        //执行存储过程
              cm.ExecuteNonQuery();
        //关闭连接
              cm.Connection.Close();
        
       //以下代码可调整
        //输出提示信息
              Response.Write("添加成功");
        //输出流水号
              Response.Write(lw);
              
                  

              
    }
}