private void BackButton_Click(object sender, EventArgs e)
{
// deleting groups having no students
string getid = "SELECT Id FROM [Group] WHERE Id NOT IN (SELECT GroupId FROM GroupStudent) ";
SqlConnection connection = new SqlConnection(connString);
connection.Open();
SqlCommand command = new SqlCommand(getid, connection);
SqlDataReader GroupIDs = command.ExecuteReader();
while (GroupIDs.Read())
{
string delGroup = string.Format("DELETE FROM [Group] WHERE Id = '{0}'", GroupIDs[0]);
SqlConnection sql = new SqlConnection(connString);
sql.Open();
SqlCommand cmd = new SqlCommand(delGroup, sql);
cmd.ExecuteNonQuery();
sql.Close();
}
connection.Close();
ManageGroups form = new ManageGroups();
form.Show();
this.Close();
}
private void GoToGroupsForm_Click_1(object sender, EventArgs e)
{
ManageGroups form = new ManageGroups();
form.Show();
this.Hide();
}
private void CreateGroupButton_Click(object sender, EventArgs e)
{
List <String> list = new List <string>();
string regno;
SqlConnection con = new SqlConnection(connString);
con.Open();
if (val != "edit")
{
foreach (DataGridViewRow r in StudentsGrid.Rows)
{
if (r.Index != StudentsGrid.NewRowIndex)
{
regno = r.Cells["Registration Number"].Value.ToString();
list.Add(regno);
}
}
foreach (String s in list)
{
string query = string.Format("SELECT Id FROM Student WHERE RegistrationNo = '{0}'", s);
SqlCommand cmd = new SqlCommand(query, con);
int id1 = (Int32)cmd.ExecuteScalar();
cmd.CommandText = string.Format("INSERT INTO GroupStudent(GroupId, StudentId, Status, AssignmentDate) Values('{0}', '{1}', '{2}', '{3}')", int.Parse(id), id1, 3, DateTime.Now);
cmd.ExecuteNonQuery();
}
MessageBox.Show(string.Format("Students Added to the group '{0}'", id));
}
else if (val == "edit")
{
foreach (DataGridViewRow r in StudentsGrid.Rows)
{
if (r.Index >= Count)
{
regno = r.Cells["Registration Number"].Value.ToString();
list.Add(regno);
}
}
foreach (String s in list)
{
string query = string.Format("SELECT Id FROM Student WHERE RegistrationNo = '{0}'", s);
SqlConnection sql = new SqlConnection(connString);
sql.Open();
SqlCommand cmd = new SqlCommand(query, sql);
int id1 = (Int32)cmd.ExecuteScalar();
string q2 = string.Format("SELECT StudentId FROM GroupStudent WHERE GroupId = '{0}'", id);
cmd.CommandText = q2;
SqlDataReader read = cmd.ExecuteReader();
List <int> ids = new List <int>();
while (read.Read())
{
ids.Add(Convert.ToInt32(read[0].ToString()));
}
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand comm = new SqlCommand("", conn);
if (ids.Contains(id1))
{
comm.CommandText = string.Format("UPDATE GroupStudent SET Status = 3 WHERE GroupId = '{0}' AND StudentId = '{1}'", id, id1);
comm.ExecuteNonQuery();
}
else
{
comm.CommandText = string.Format("INSERT INTO GroupStudent(GroupId, StudentId, Status, AssignmentDate) Values('{0}', '{1}', '{2}', '{3}')", int.Parse(id), id1, 3, DateTime.Now);
comm.ExecuteNonQuery();
}
conn.Close();
sql.Close();
}
MessageBox.Show(string.Format("Group Updated '{0}'", id));
}
// deleting groups having no students
string getid = "SELECT Id FROM [Group] WHERE Id NOT IN (SELECT GroupId FROM GroupStudent) ";
SqlConnection connection = new SqlConnection(connString);
connection.Open();
SqlCommand command = new SqlCommand(getid, connection);
SqlDataReader GroupIDs = command.ExecuteReader();
while (GroupIDs.Read())
{
string delGroup = string.Format("DELETE FROM [Group] WHERE Id = '{0}'", GroupIDs[0]);
SqlConnection sql = new SqlConnection(connString);
sql.Open();
SqlCommand cmd = new SqlCommand(delGroup, sql);
cmd.ExecuteNonQuery();
sql.Close();
}
connection.Close();
con.Close();
ManageGroups form = new ManageGroups();
form.Show();
this.Close();
}