Students Grouping Problem C#

See more:

C#

SQL

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())
                {
                 // 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;
        }

    }            

}

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.