private void button4_Click(object sender, EventArgs e) { this.dataGridView2.DataSource = null; this.dataGridView3.DataSource = null; this.dataGridView2.Rows.Clear(); this.dataGridView3.Rows.Clear(); //DataTable nochoosestudent = SQLDBhelper.ExecuteDataTable(string.Format("select captainid,captainname from team where grade='{0}' and major='{1}' and account not in (select stuid from ischoose)", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString())); DataTable nochoosestudent = SQLDBhelper.ExecuteDataTable(string.Format("select captainname,captainid from team where captainid in (select account from stupasswordtable where grade='{0}' and major='{1}') and captainid not in (select stuid from finish)", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString())); if (nochoosestudent.Rows.Count != 0) { for (int i = 0; i < nochoosestudent.Rows.Count; i++) { dataGridView2.Rows.Add(nochoosestudent); dataGridView2.Rows[i].Cells[0].Value = nochoosestudent.Rows[i]["captainid"].ToString(); dataGridView2.Rows[i].Cells[1].Value = nochoosestudent.Rows[i]["captainname"].ToString(); } } this.dataGridView3.DataSource = null; DataTable tea = SQLDBhelper.ExecuteDataTable(string.Format("select name,id,number from teapasswordtable where grade='{0}' and major='{1}' and number>0", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString())); if (tea.Rows.Count != 0) { for (int i = 0; i < tea.Rows.Count; i++) { dataGridView3.Rows.Add(tea); dataGridView3.Rows[i].Cells[0].Value = tea.Rows[i]["id"].ToString(); dataGridView3.Rows[i].Cells[1].Value = tea.Rows[i]["name"].ToString(); dataGridView3.Rows[i].Cells[2].Value = tea.Rows[i]["number"].ToString(); } } }
//对应的年级专业的学生信息,使用函数以便后面使用 public void select_stu() { this.dataGridView1.DataSource = null; this.dataGridView1.Rows.Clear(); //dataGridView1.DataSource = bll.select(comboBox1.Text.Trim(), comboBox2.Text.Trim()); DataTable selectmessage = SQLDBhelper.ExecuteDataTable(string.Format("select * from stupasswordtable where grade='{0}' and major='{1}' ", comboBox1.Text.Trim(), comboBox2.Text.Trim())); if (selectmessage.Rows.Count != 0) { for (int i = 0; i < selectmessage.Rows.Count; i++) { dataGridView1.Rows.Add(dt); dataGridView1.Rows[i].Cells[0].Value = selectmessage.Rows[i]["account"].ToString(); dataGridView1.Rows[i].Cells[1].Value = selectmessage.Rows[i]["password"].ToString(); dataGridView1.Rows[i].Cells[2].Value = selectmessage.Rows[i]["name"].ToString(); dataGridView1.Rows[i].Cells[3].Value = selectmessage.Rows[i]["sex"].ToString(); dataGridView1.Rows[i].Cells[4].Value = selectmessage.Rows[i]["id"].ToString(); dataGridView1.Rows[i].Cells[5].Value = selectmessage.Rows[i]["grade"].ToString(); dataGridView1.Rows[i].Cells[6].Value = selectmessage.Rows[i]["major"].ToString(); dataGridView1.Rows[i].Cells[7].Value = selectmessage.Rows[i]["class"].ToString(); dataGridView1.Rows[i].Cells[8].Value = selectmessage.Rows[i]["phone"].ToString(); dataGridView1.Rows[i].Cells[9].Value = selectmessage.Rows[i]["email"].ToString(); dataGridView1.Rows[i].Cells[10].Value = selectmessage.Rows[i]["introduce"].ToString(); dataGridView1.Rows[i].Cells[11].Value = selectmessage.Rows[i]["academy"].ToString(); dataGridView1.Rows[i].Cells[12].Value = selectmessage.Rows[i]["groupid"].ToString(); } } }
private void button4_Click(object sender, EventArgs e) { this.dataGridView1.DataSource = null; this.dataGridView1.Rows.Clear(); DataTable stuidandteaid = SQLDBhelper.ExecuteDataTable(string.Format("select * from team where Teamnumber in (select account from stupasswordtable where grade='{0}' and major='{1}') and Teamnumber in (select stuid from finish)", comboBox4.SelectedItem.ToString(), comboBox3.SelectedItem.ToString())); stuidandteaid.Columns.Add("teaname"); stuidandteaid.Columns.Add("teaid"); for (int i = 0; i < stuidandteaid.Rows.Count; i++) { SqlDataReader readteaname = SQLDBhelper.ExecuteReader(string.Format("select t.name ,a.teaid from system2.dbo.teapasswordtable t left join system2.dbo.finish a on a.teaid=t.account where a.stuid='{0}'", stuidandteaid.Rows[i]["captainid"].ToString())); readteaname.Read(); stuidandteaid.Rows[i]["teaname"] = readteaname[0].ToString(); stuidandteaid.Rows[i]["teaid"] = readteaname[1].ToString(); dataGridView1.Rows.Add(stuidandteaid); dataGridView1.Rows[i].Cells[0].Value = stuidandteaid.Rows[i]["captainname"].ToString(); dataGridView1.Rows[i].Cells[1].Value = stuidandteaid.Rows[i]["captainid"].ToString(); dataGridView1.Rows[i].Cells[2].Value = stuidandteaid.Rows[i]["memberonename"].ToString(); dataGridView1.Rows[i].Cells[3].Value = stuidandteaid.Rows[i]["memberoneid"].ToString(); dataGridView1.Rows[i].Cells[4].Value = stuidandteaid.Rows[i]["membertwoname"].ToString(); dataGridView1.Rows[i].Cells[5].Value = stuidandteaid.Rows[i]["membertwoid"].ToString(); dataGridView1.Rows[i].Cells[6].Value = stuidandteaid.Rows[i]["memberthreename"].ToString(); dataGridView1.Rows[i].Cells[7].Value = stuidandteaid.Rows[i]["memberthreeid"].ToString(); dataGridView1.Rows[i].Cells[8].Value = stuidandteaid.Rows[i]["teaname"].ToString(); dataGridView1.Rows[i].Cells[9].Value = stuidandteaid.Rows[i]["teaid"].ToString(); } }
//Excel表中的列名和数据库中的列名一定要对应 private void insertToSql_stu(DataRow dr) { string account = dr["账号"].ToString(); string password = dr["密码"].ToString(); string name = dr["姓名"].ToString(); string sex = dr["性别"].ToString(); string id = dr["学号"].ToString(); string grade = dr["年级"].ToString(); string major = dr["专业"].ToString(); string Class = dr["班别"].ToString(); string phone = dr["电话号码"].ToString(); string email = dr["邮箱地址"].ToString(); string introduce = dr["自我介绍"].ToString(); string academy = dr["学院"].ToString(); string groupID = dr["团队编号"].ToString(); SQLDBhelper.ExecuteNonQuery(string.Format("insert into stupasswordtable values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", account, password, name, sex, id, grade, major, Class, phone, email, introduce, academy, groupID)); }
//button1的查询功能所对应的函数 public void selectmess() { this.dataGridView1.DataSource = null; this.dataGridView1.Rows.Clear(); DataTable selectmessage = SQLDBhelper.ExecuteDataTable(string.Format("select * from ischoose where stuid in (select account from stupasswordtable where grade='{0}' and major='{1}') and stuid not in (select stuid from finish)", comboBox1.SelectedItem.ToString(), comboBox2.SelectedItem.ToString())); if (selectmessage.Rows.Count != 0) //dataGridView1.DataSource = selectmessage.DefaultView; { for (int i = 0; i < selectmessage.Rows.Count; i++) { dataGridView1.Rows.Add(selectmessage); dataGridView1.Rows[i].Cells[0].Value = selectmessage.Rows[i]["stuid"].ToString(); dataGridView1.Rows[i].Cells[1].Value = selectmessage.Rows[i]["stuname"].ToString(); dataGridView1.Rows[i].Cells[2].Value = selectmessage.Rows[i]["teaid"].ToString(); dataGridView1.Rows[i].Cells[3].Value = selectmessage.Rows[i]["teaname"].ToString(); } } }
//Excel表中的列名和数据库中的列名一定要对应 private void insertToSql_tea(DataRow dr) { string Account = dr["账号"].ToString(); string Password = dr["密码"].ToString(); string Name = dr["姓名"].ToString(); string Sex = dr["性别"].ToString(); string ID = dr["工号"].ToString(); string Position = dr["职称"].ToString(); string Number = dr["名额"].ToString(); string groupnumber = dr["名额"].ToString(); string Phone = dr["电话号码"].ToString(); string Email = dr["邮箱地址"].ToString(); string Academy = dr["学院"].ToString(); string Research = dr["研究方向"].ToString(); string grade = dr["年级"].ToString(); string major = dr["专业"].ToString(); SQLDBhelper.ExecuteNonQuery(string.Format("insert into teapasswordtable values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')", Account, Password, Name, Sex, ID, Position, Number, groupnumber, Phone, Email, Academy, Research, grade, major)); }
private void find_Click(object sender, EventArgs e) { this.dataGridView1.DataSource = null; this.dataGridView1.Rows.Clear(); DataTable stu = SQLDBhelper.ExecuteDataTable(string.Format("select name ,id ,class,phone,email from stupasswordtable where grade='{0}' and major='{1}' and groupid='0'", grade.SelectedItem.ToString(), major.SelectedItem.ToString())); if (stu.Rows.Count != 0) { for (int i = 0; i < stu.Rows.Count; i++) { dataGridView1.Rows.Add(stu); dataGridView1.Rows[i].Cells[0].Value = stu.Rows[i]["name"].ToString(); dataGridView1.Rows[i].Cells[1].Value = stu.Rows[i]["id"].ToString(); dataGridView1.Rows[i].Cells[2].Value = stu.Rows[i]["class"].ToString(); dataGridView1.Rows[i].Cells[3].Value = stu.Rows[i]["phone"].ToString(); dataGridView1.Rows[i].Cells[4].Value = stu.Rows[i]["email"].ToString(); } } }
private void button2_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count == 0) { MessageBox.Show("列表中暂无学生互选信息"); return; } DialogResult ischoose = MessageBox.Show("是否对列表的师生进行匹配?", "自动匹配", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (ischoose == DialogResult.No) { return; } string[] stuid = new string[this.dataGridView1.Rows.Count]; string[] teaid = new string[this.dataGridView1.Rows.Count]; //int stucount = 0; bool issame = false; #region for (int i = 0; i < this.dataGridView1.Rows.Count; i++) { //if (i == 0) // stuid[stucount] = dataGridView1.Rows[i].Cells[0].Value.ToString(); for (int j = i; j >= 0; j--) { if (j == i) { continue; } if (dataGridView1.Rows[j].Cells[0].Value.ToString() == dataGridView1.Rows[i].Cells[0].Value.ToString()) { issame = true; } } if (issame) { issame = false; } else { stuid[stucount] = dataGridView1.Rows[i].Cells[0].Value.ToString(); stucount++; } }//读取已经被老师预选的学生学号 进入到stuid字符串数组中 #endregion//读取团队编号进入数组stuid[] stucount #region for (int i = 0; i < this.dataGridView1.Rows.Count; i++) { for (int j = i; j >= 0; j--) { if (j == i) { continue; } if (dataGridView1.Rows[j].Cells[2].Value.ToString() == dataGridView1.Rows[i].Cells[2].Value.ToString()) { issame = true; } } if (issame) { issame = false; } else { teaid[teacount] = dataGridView1.Rows[i].Cells[2].Value.ToString(); teacount++; } }//读取已完成互选的导师工号 #endregion//读取导师工号进入数组 teaid[] teacount teacarrynumber = new int[teacount];//记录导师可带多少团队 for (int i = 0; i < teacount; i++) { SqlDataReader read = SQLDBhelper.ExecuteReader(string.Format("select number from teapasswordtable where account='{0}'", teaid[i])); read.Read(); teacarrynumber[i] = (int)read[0]; } used = new bool[teacount]; //标记是否已经进行访问 cnt = new int[teacount]; //记录已有多少匹配了 for (int i = 0; i < teacount; i++) { cnt[i] = 0; used[i] = false; } line = new int[stucount, teacount];//声明一个二维数组 用于存储匹配的关系 //int k = 0; for (int i = 0; i < dataGridView1.Rows.Count; i++) { int x = stuid.ToList().IndexOf(dataGridView1.Rows[i].Cells[0].Value.ToString()); int y = teaid.ToList().IndexOf(dataGridView1.Rows[i].Cells[2].Value.ToString()); line[x, y] = 1; }//读取标记已经完成互选的关系图 进入line数组 //读取导师表中导师能带最大团队数 SqlDataReader readmaxnumber = SQLDBhelper.ExecuteReader("select max(groupnumber) groupnumber from teapasswordtable"); readmaxnumber.Read(); maxnum = Convert.ToInt32(readmaxnumber[0]); match = new int[teacount, maxnum]; //button1.Text = max_match().ToString(); int number = max_match(); for (int i = 0; i < teacount; i++) { for (int j = 0; j < maxnum; j++) { if (match[i, j] != -1) { SqlConnection conn = new SqlConnection(SQLDBhelper.ConnectionString); conn.Open(); SqlCommand insert = new SqlCommand(string.Format("insert into finish values('{0}','{1}','0')", stuid[match[i, j]], teaid[i]), conn);//导师id insert.ExecuteNonQuery(); conn.Close(); } } } MessageBox.Show(string.Format("匹配完成,当前学生团队数{0},已完成匹配数{1}", stucount, number)); using (SqlConnection con = new SqlConnection(SQLDBhelper.ConnectionString)) { con.Open(); for (int i = 0; i < teacount; i++) { SqlCommand command = new SqlCommand(string.Format("update teapasswordtable set number-={0} where account='{1}'", cnt[i], teaid[i]), con); command.ExecuteNonQuery(); } } stucount = 0; teacount = 0; //调用函数,重新刷新dataGridView1,显示的是未分配到老师的学生团队 selectmess(); //this.dataGridView1.DataSource = null; //DataTable selectmessage = SQLDBhelper.ExecuteDataTable(string.Format("select * from ischoose where stuid in (select account from stupasswordtable where grade='{0}' and major='{1}') and stuid not in (select stuid from finish)", comboBox1.SelectedItem.ToString(), comboBox2.SelectedItem.ToString())); //if (selectmessage.Rows.Count != 0) // dataGridView1.DataSource = selectmessage.DefaultView; }