学生分组问题C＃ [英] Students Grouping Problem C#
问题描述
大家好，我有两张桌子根据学生添加的偏好创建学生小组。
Hi guys, I have these two tables to create groups of students based on preferences added by students.
Students_Table
-------------------
Stud_Name Group_ID
-------------------
Stud A
Stud B
Stud C
Stud D
Stud E
Stud F
Stud G
Stud H
Stud I
Stud J
Stud K
--------------------
Preference Tables
---------------------------------------------------------------------
Stud_Name Pref 1 Pref 2 Pref 3 Pref 4 Pref 5 Pref 6 Pref 7
---------------------------------------------------------------------
Stud A Stud B Stud C Stud D Stud E Stud F Stud G Stud H
Stud B Stud A Stud H Stud K Stud F Stud J Stud N
Stud C Stud I Stud A
Stud D Stud H Stud K
Stud E
Stud F
Stud G
Stud H
Stud I
Stud J
Stud K
---------------------------------------------------------------------
我遵循的规则是：
每组最少5名，最多8名学生。以下是我现在的代码，
The rules i am following is:
Each group can have minimum 5 and maximum 8 students. Following is the code i have for now,
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace InsertTeamIdIntoTable
{
class Program
{
const string str = @"Data Source=localhost;Initial Catalog=Items;Integrated Security=True";
static void Main(string[] args)
{
InsertItemData(str);
}
private static void InsertItemData(string connectionString)
{
string queryString =
"Use Items Select Pref1,Pref2,Pref3,Pref4, Pref5,Pref6,Pref7 FROM dbo.Preference_Table;";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
int Group_ID = 1;
while (reader.Read())
{
// This function Checks each row and returns where 5 columns or more have values including stud_name column, so we can add them to group
bool flag = CheckValueNumber((IDataRecord)reader);
if (flag)
{
for (int i = 0; i < ((IDataRecord)reader).FieldCount; i++)
{
string StudentName = ((IDataRecord)reader)[i].ToString();
if (string.IsNullOrWhiteSpace(StudentName) == false)
{
//This function checks if student exist in student_table
if (CheckStudentExists(str, StudentName))
{
//This function checks if Group_ID already exist for student in student_table(because if there is already group_ID than we are not fgoing to modify that)
if (CheckGroupIdExists(str, StudentName) == false)
{
UpdateTableStudents(str, Group_ID, StudentName);
}
}
else
{
//This i might have to remove because i don't want to insert any new records in students_table
InsertTableStudents(str, Group_ID, StudentName);
}
}
Console.WriteLine(StudentName);
Console.WriteLine();
}
Group_ID++;
}
}
Console.ReadLine();
reader.Close();
}
}
public static void UpdateTableStudents(string connectionString, int Group_ID, string StudentName)
{
string updateString = string.Format("Update dbo.Students_Table set item_id ={0} WHERE Stud_Name ='{1}';", Group_ID, StudentName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
command.ExecuteNonQuery();
}
}
public static void InsertTableStudents(string connectionString, int Group_ID, string StudentName)
{
string updateString = string.Format("use Items Update dbo.Students_Table Set Stud_ID = '{0}' where Stud_Name = '{1}';", Group_ID, StudentName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
command.ExecuteNonQuery();
}
}
public static bool CheckStudentExists(string connectionString, string StudentName)
{
string updateString = string.Format("Use Items Select count(Stud_ID) From dbo.Students_Table WHERE Stud_Name ='{0}';", StudentName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
return (Int32)command.ExecuteScalar() > 0;
}
}
public static bool CheckGroupIdExists(string connectionString, string StudentName)
{
string updateString = string.Format("Use Items Select Group_Id From dbo.Students_Table WHERE Stud_Name ='{0}';", StudentName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (string.IsNullOrWhiteSpace(((IDataRecord)reader)[0].ToString()) == false)
{
return true;
}
}
reader.Close();
return false;
}
}
public static bool CheckValueNumber(IDataRecord record)
{
int count = 0;
for (int i = 0; i < record.FieldCount; i++)
{
if (string.IsNullOrWhiteSpace(record[i].ToString()) == false)
{
count++;
}
}
return count >= 5;
}
}
}
这就是我实际在做的事情。检查Preferences_table中的每一行，其中学生已添加至少4个首选项，并从1开始创建group_ID，并将该行中的所有学生插入到Group_ID列中的Students_Table。
它正在运行，但代码存在一些问题。
问题1 ：由于返回次数> = 4，并非所有学生都获得了组ID。但我想不出另一种方法来实现这一点。许多学生不会添加偏好并且他们无法通过此条件（返回计数> = 4;）但仍然需要将它们添加到组中。
问题2 ：如果第一行有Stud B且行满足条件（返回计数> = 4;）而不是我从第1行分配Group_ID，但是如果任何其他行也有螺柱B和行通过条件（返回计数> = 4;）比我有问题，因为螺柱B已经有一个Group_ID，我将不会为它插入新的ID，这将使我的团队少于5个。
我在代码中添加了评论。
欢迎任何建议。
请不要数据库建议，除非你有一些代码来解决问题。
This is what I am actually doing. Check for each row in Preferences_table, where student has added at least 4 preferences and create a group_ID starting from 1 and insert it for all the students in that row to Students_Table in Group_ID column.
It is working, but there are some issues with code.
Problem 1: Not all students are getting a group ID because of return count >= 4; but i can't think of another way to implement this. Many students will not add preferences and they can't pass this condition (return count >= 4;) but they are still need to be added to a group.
Problem 2: If row one has "Stud B" and the row met condition (return count >= 4;)than i assign Group_ID fro row 1, but if any other row also has "Stud B" and the row passes condition (return count >= 4;) than i have a problem because "Stud B"already has a Group_ID and i will not insert new ID for it, which will make my group less than 5.
I have added comments in code.
Any suggestions are welcome.
Please no database suggestions, unless you have some code to solve the problem.
推荐答案
我理解你的代码：
I understand your code as such:
groupId = FirstGroupId();
foreach (row in studentsTable)
{
if (row.Matches(predicate))
{
foreach (related in row.RelatedFrom(studentsTable))
{
related.SetGroupId(groupId);
}
groupId = NextGroupId();
}
}
您可能需要调整如下：
You might need to adjust as follows:
// Group students that are related.
// Greedy approach: the first one found that fulfils the predicate (e.g. more than three relations)
// grabs its relations into one group.
// Already grouped students are not regrouped.
// The remaining students are grouped into arbirtary sized groups as given by the NeedsNewGroup() predicate.
groupId = FirstGroupId();
foreach (row in studentsTable)
{
if (not row.IsGroupIdSet())
{
if (row.Matches(predicate))
{
row.SetGroupId(groupId);
foreach (related in row.RelatedFrom(studentsTable))
{
related.SetGroupId(groupId);
}
groupId = NextGroupId();
}
}
}
count = 0;
forwach (row in studentTable where not row.IsGroupIsSet())
{
if (NeedsNewGroup(groupId, count))
{
groupId = NextGroupId();
}
count++;
row.SetGroupId(groupId);
}
HTH
Andi
PS：我没有在你的代码中看到你决定组的大小。您只需根据参考数量进行选择。
HTH
Andi
PS: I don't see in your code where you decide on the group size. You just select based on the number of references.
这篇关于学生分组问题C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
