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();
        }
Exemple #2
0
        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();
        }