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
---------------------------------------------------------------------
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())
{
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)
{
if (CheckStudentExists(str, StudentName))
{
if (CheckGroupIdExists(str, StudentName) == false)
{
UpdateTableStudents(str, Group_ID, StudentName);
}
}
else
{
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;
}
}
}
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.