private void AddButton_Click(object sender, EventArgs e)
{
for (int j = 0; j < StudentDataGrid.RowCount; j++)
{
if (StudentDataGrid.Rows[j].Cells[StudentDataGrid.Columns["Select"].Index].Value == null)
{
StudentDataGrid.Rows[j].Cells[StudentDataGrid.Columns["Select"].Index].Value = false;
}
}
try
{
List <int> idi = new List <int>();
for (int i = 0; i < StudentDataGrid.RowCount; i++)
{
if ((bool)StudentDataGrid.Rows[i].Cells[StudentDataGrid.Columns["Select"].Index].Value)
{
idi.Add((int)StudentDataGrid.Rows[i].Cells[StudentDataGrid.Columns["Id"].Index].Value);
}
}
foreach (int idk in idi)
{
String cmd = String.Format("INSERT INTO ProjectAdvisor(ProjectId, AdvisorId, AssignmentDate, AdvisorRole) VALUES({0}, {1}, GETDATE(), (SELECT Id FROM Lookup WHERE Category = 'ADVISOR_ROLE' AND Value = '{2}'))", id, idk, ARComboBox.Text);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
if (idi.Count == 0)
{
MessageBox.Show("Nothing was selected.");
}
else
{
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void AddButton_Click(object sender, EventArgs e)
{
for (int j = 0; j < ProjectDataGrid.RowCount; j++)
{
if (ProjectDataGrid.Rows[j].Cells[ProjectDataGrid.Columns["Select"].Index].Value == null)
{
ProjectDataGrid.Rows[j].Cells[ProjectDataGrid.Columns["Select"].Index].Value = false;
}
}
try
{
List <int> idi = new List <int>();
for (int i = 0; i < ProjectDataGrid.RowCount; i++)
{
if ((bool)ProjectDataGrid.Rows[i].Cells[ProjectDataGrid.Columns["Select"].Index].Value)
{
idi.Add((int)ProjectDataGrid.Rows[i].Cells[ProjectDataGrid.Columns["Id"].Index].Value);
}
}
foreach (int idk in idi)
{
String cmd = String.Format("INSERT INTO GroupProject(ProjectId, GroupId, AssignmentDate) VALUES({0}, {1}, GETDATE())", idk, id);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
if (idi.Count == 0)
{
MessageBox.Show("Nothing was selected.");
}
else
{
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public StudentCreate(MainPage parent)
{
par = parent;
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
DateOfBirthPick.MaxDate = DateTime.Now;
FNError.Text = "";
LNError.Text = "";
ConError.Text = "";
EmailError.Text = "";
DBError.Text = "";
RegError.Text = "";
DateOfBirthPick.Text = null;
String cmd = "Select Value from Lookup Where Category = 'GENDER'";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
GenderComboBox.Items.Add(reader.GetString(0));
}
GenderComboBox.SelectedIndex = 0;
}
private void ProjectDataGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
int id;
if (e.ColumnIndex == 2)
{
id = (int)ProjectDataGrid.Rows[e.RowIndex].Cells[4].Value;
int truedel = Convert.ToInt32(Microsoft.VisualBasic.Interaction.MsgBox("Are you sure?", MsgBoxStyle.YesNo, "Delete"));
if (truedel == 6)
{
try
{
String cmd = String.Format("DELETE FROM Project WHERE Id = '{0}'", id);
DatabaseConnection.getInstance().exectuteQuery(cmd);
Refresh();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
if (e.ColumnIndex == 1)
{
id = (int)ProjectDataGrid.Rows[e.RowIndex].Cells[4].Value;
//ProjectEdit se = new ProjectEdit(id, this);
//se.Show();
}
if (e.ColumnIndex == 0)
{
id = (int)ProjectDataGrid.Rows[e.RowIndex].Cells[4].Value;
ProjectDetails se = new ProjectDetails(id);
se.Show();
}
if (e.ColumnIndex == 3)
{
MessageBox.Show("Groups entity is still in development phase.");
}
}
public void Refresh()
{
try
{
String cmd = "Select Id, Title, Description from Project";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <ProjectClass> students = new List <ProjectClass>();
while (reader.Read())
{
ProjectClass stu = new ProjectClass();
stu.Id = (int)reader.GetValue(0);
stu.Titlle = reader.GetString(1);
stu.Description = reader.GetString(2);
students.Add(stu);
}
ProjectDataGrid.DataSource = students;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public EvaluationEdit(int id, MainPage parent)
{
par = parent;
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
NError.Text = "";
TMError.Text = "";
TWError.Text = "";
string cmd = "Select Id, Name, TotalMarks, TotalWeightage from Evaluation where Id = " + id.ToString();
var reader = DatabaseConnection.getInstance().getData(cmd);
stuold = new EvaluationClass();
while (reader.Read())
{
stuold.Id = (int)reader.GetValue(0);
stuold.Name = reader.GetString(1);
stuold.TotalMarks = (int)reader.GetValue(2);
stuold.TotalWeightage = (int)reader.GetValue(3);
}
NameTextBox.Text = stuold.Name;
TMTextBox.Text = stuold.TotalMarks.ToString();
TWTextBox.Text = stuold.TotalWeightage.ToString();
}
private void Add_Click(object sender, EventArgs e)
{
int flag = 0;
try
{
ProjectClass stu = new ProjectClass();
stu.Titlle = null;
stu.Description = null;
if (Regexp("^[a-zA-Z]{1,100}", TitleTextBox, TitleError, "Only alphabets are allowed.", 100))
{
stu.Titlle = TitleTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(DesTextBox.Text)))
{
flag++;
}
else
{
stu.Description = DesTextBox.Text;
flag++;
}
if (flag == 2)
{
String cmd = String.Format("Update Project SET Title = '{0}', Description = '{1}' WHERE Id = '{2}'", stu.Titlle, stu.Description, stuold.Id);
DatabaseConnection.getInstance().exectuteQuery(cmd);
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SearchButton_Click(object sender, EventArgs e)
{
try
{
if (SearchTextBox.Text == "")
{
Refresh();
return;
}
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Genders, RegistrationNo from (Person JOIN Student ON Person.Id = Student.Id) JOIN (SELECT Id, Value As Genders FROM Lookup) a ON Person.Gender = a.Id Where " + SearchCategory.Text + " = '" + SearchTextBox.Text + "'";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <StudentClass> students = new List <StudentClass>();
while (reader.Read())
{
StudentClass stu = new StudentClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Contact = reader.GetString(3);
stu.Email = reader.GetString(4);
stu.DateOfBirth = (DateTime)reader.GetValue(5);
stu.Gender = (int)reader.GetValue(6);
stu.Genders = reader.GetString(7);
stu.RegistrationNo = reader.GetString(8);
students.Add(stu);
}
StudentDataGrid.DataSource = students;
//StudentDataGrid.Columns[0].Visible = false;
//StudentDataGrid.Columns[6].Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SearchButton_Click(object sender, EventArgs e)
{
try
{
if (SearchTextBox.Text == "")
{
Refresh();
return;
}
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Genders, Designation , Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN (SELECT Id, Value As Genders FROM Lookup) b ON Person.Gender = b.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.Id Where " + SearchCategory.Text + " = '" + SearchTextBox.Text + "'";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Contact = reader.GetString(3);
stu.Email = reader.GetString(4);
stu.DateOfBirth = (DateTime)reader.GetValue(5);
stu.Gender = (int)reader.GetValue(6);
stu.Genders = reader.GetString(7);
stu.Designation = (int)reader.GetValue(8);
stu.Designations = reader.GetString(9);
stu.Salary = (decimal)reader.GetValue(10);
students.Add(stu);
}
AdvisorDataGrid.DataSource = students;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Refresh()
{
try
{
String cmd = "Select Id, Name, TotalMarks, TotalWeightage from Evaluation";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <EvaluationClass> students = new List <EvaluationClass>();
while (reader.Read())
{
EvaluationClass stu = new EvaluationClass();
stu.Id = (int)reader.GetValue(0);
stu.Name = reader.GetString(1);
stu.TotalMarks = (int)reader.GetValue(2);
stu.TotalWeightage = (int)reader.GetValue(3);
students.Add(stu);
}
StudentDataGrid.DataSource = students;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public Form1()
{
InitializeComponent();
DatabaseConnection.getInstance().ConnectionString = "Data Source=HAIER-PC\\NAWAF;Initial Catalog=ProjectA;Persist Security Info=True;User ID=sa;Password=2922";
}
public void Refresh()
{
DeleteButtonP.Visible = false;
EvalDeleteButton.Visible = false;
StudentDataGrid.Columns.Clear();
SearchCategoryS.Items.Clear();
ProjectDataGrid.Columns.Clear();
SearchCategoryP.Items.Clear();
EvaluationDataGrid.Columns.Clear();
SearchCategoryE.Items.Clear();
try
{
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, RegistrationNo, GroupId, AssignmentDate, Value from ((Person JOIN Student ON Person.Id = Student.Id) JOIN GroupStudent ON Student.Id = StudentId) JOIN Lookup ON Lookup.Id = Status WHERE GroupId = " + id.ToString();
var reader = DatabaseConnection.getInstance().getData(cmd);
List <StudentClass> students = new List <StudentClass>();
while (reader.Read())
{
StudentClass stu = new StudentClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Contact = reader.GetString(3);
stu.Email = reader.GetString(4);
stu.DateOfBirth = (DateTime)reader.GetValue(5);
stu.Gender = (int)reader.GetValue(6);
stu.RegistrationNo = reader.GetString(7);
stu.GroupId = (int)reader.GetValue(8);
stu.AssignmentDate = (DateTime)reader.GetValue(9);
stu.Status = reader.GetString(10);
students.Add(stu);
}
StudentDataGrid.DataSource = students;
StudentDataGrid.Columns["Id"].Visible = false;
StudentDataGrid.Columns["Gender"].Visible = false;
StudentDataGrid.Columns["Contact"].Visible = false;
StudentDataGrid.Columns["Email"].Visible = false;
StudentDataGrid.Columns["FirstName"].Visible = false;
StudentDataGrid.Columns["LastName"].Visible = false;
StudentDataGrid.Columns["DateOfBirth"].Visible = false;
StudentDataGrid.Columns["Genders"].Visible = false;
StudentDataGrid.Columns["GroupId"].Visible = false;
cmd = "Select RegistrationNo, Status As Statuses from (Person JOIN Student ON Person.Id = Student.Id) JOIN GroupStudent ON Student.Id = StudentId";
reader = DatabaseConnection.getInstance().getData(cmd);
int i = 0;
while (reader.FieldCount > i)
{
if (!SearchCategoryS.Items.Contains(reader.GetName(i)))
{
SearchCategoryS.Items.Add(reader.GetName(i));
}
i++;
}
SearchCategoryS.SelectedIndex = 0;
DataGridViewCheckBoxColumn checkColumn = new DataGridViewCheckBoxColumn();
checkColumn.Name = "Select";
checkColumn.HeaderText = "";
checkColumn.TrueValue = true;
checkColumn.FalseValue = false;
checkColumn.Width = 20;
checkColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
checkColumn.ReadOnly = false; //if the datagridview is resized (on form resize) the checkbox won't take up too much; value is relative to the other columns' fill values
ProjectDataGrid.Columns.Add(checkColumn);
cmd = "Select Id, Title, Description, AssignmentDate from Project JOIN GroupProject ON Id = ProjectId WHERE GroupId = " + id.ToString();
reader = DatabaseConnection.getInstance().getData(cmd);
List <ProjectClass> project = new List <ProjectClass>();
while (reader.Read())
{
ProjectClass stu = new ProjectClass();
stu.Id = (int)reader.GetValue(0);
stu.Titlle = reader.GetString(1);
stu.Description = reader.GetString(2);
stu.AssignmentDate = (DateTime)reader.GetValue(3);
project.Add(stu);
}
ProjectDataGrid.DataSource = project;
ProjectDataGrid.Columns["Id"].Visible = false;
ProjectDataGrid.Columns["Description"].Visible = false;
for (int j = 0; j < ProjectDataGrid.RowCount; j++)
{
ProjectDataGrid.Rows[j].Cells[ProjectDataGrid.Columns["Select"].Index].Value = false;
}
cmd = "Select Title from Project JOIN GroupProject ON Id = ProjectId";
reader = DatabaseConnection.getInstance().getData(cmd);
i = 0;
while (reader.FieldCount > i)
{
if (!SearchCategoryP.Items.Contains(reader.GetName(i)))
{
SearchCategoryP.Items.Add(reader.GetName(i));
}
i++;
}
SearchCategoryP.SelectedIndex = 0;
cmd = "Select Id, Name, TotalMarks, TotalWeightage, ObtainedMarks, EvaluationDate from Evaluation JOIN GroupEvaluation ON Id = EvaluationId WHERE GroupId = " + id.ToString();
reader = DatabaseConnection.getInstance().getData(cmd);
DataGridViewCheckBoxColumn checkColumn2 = new DataGridViewCheckBoxColumn();
checkColumn2.Name = "Select";
checkColumn2.HeaderText = "";
checkColumn2.TrueValue = true;
checkColumn2.FalseValue = false;
checkColumn2.Width = 20;
checkColumn2.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
checkColumn2.ReadOnly = false; //if the datagridview is resized (on form resize) the checkbox won't take up too much; value is relative to the other columns' fill values
EvaluationDataGrid.Columns.Add(checkColumn2);
List <EvaluationClass> evaluation = new List <EvaluationClass>();
while (reader.Read())
{
EvaluationClass stu = new EvaluationClass();
stu.Id = (int)reader.GetValue(0);
stu.Name = reader.GetString(1);
stu.TotalMarks = (int)reader.GetValue(2);
stu.TotalWeightage = (int)reader.GetValue(3);
stu.ObtainedMarks = (int)reader.GetValue(4);
stu.EvaluationDate = (DateTime)reader.GetValue(5);
evaluation.Add(stu);
}
EvaluationDataGrid.DataSource = evaluation;
EvaluationDataGrid.Columns["Id"].Visible = false;
for (int j = 0; j < EvaluationDataGrid.RowCount; j++)
{
EvaluationDataGrid.Rows[j].Cells[EvaluationDataGrid.Columns["Select"].Index].Value = false;
}
cmd = "Select Name, TotalMarks, TotalWeightage from Evaluation JOIN GroupEvaluation ON Id = EvaluationId";
reader = DatabaseConnection.getInstance().getData(cmd);
i = 0;
while (reader.FieldCount > i)
{
if (!SearchCategoryE.Items.Contains(reader.GetName(i)))
{
SearchCategoryE.Items.Add(reader.GetName(i));
}
i++;
}
SearchCategoryE.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Edit_Click(object sender, EventArgs e)
{
int flag = 0;
try
{
StudentClass stu = new StudentClass();
stu.Gender = null;
stu.LastName = null;
stu.Contact = null;
stu.DateOfBirth = null;
if (Regexp("^[a-zA-Z]{1,100}", FirstNameTextBox, FNError, "Only alphabets are allowed.", 100))
{
stu.FirstName = FirstNameTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(LastNameTextBox.Text)))
{
flag++;
}
else if (Regexp("^[a-zA-Z]{1,100}", LastNameTextBox, LNError, "Only alphabets are allowed.", 100))
{
stu.LastName = LastNameTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(ContactTextBox.Text)))
{
flag++;
}
else if (Regexp("^[0-9]{1,20}", ContactTextBox, ConError, "Only valid numbers are allowed.", 20))
{
stu.Contact = ContactTextBox.Text;
flag++;
}
if (Regexp("^([\\w\\.\\-]+)@([\\w\\-]+)((\\.(\\w){2,3})+)$", EmailTextBox, EmailError, "Format not correct eg. [email protected]", 30))
{
stu.Email = EmailTextBox.Text;
if (stuold.Email != stu.Email)
{
String cmd1 = "Select Id from Person Where Email = '" + EmailTextBox.Text + "'";
var reader1 = DatabaseConnection.getInstance().getData(cmd1);
while (reader1.Read())
{
flag--;
EmailError.Text = "Already exists";
EmailError.ForeColor = System.Drawing.Color.Red;
}
}
flag++;
}
stu.DateOfBirth = DateOfBirthPick.Value;
flag++;
if (Regexp("^[0-9]{4}-[A-Z]{2,3}-[0-9]{3}$", RegNoTextBox, RegError, "Format not correct eg. 2016-AB-000", 20))
{
stu.RegistrationNo = RegNoTextBox.Text;
if (stuold.RegistrationNo != stu.RegistrationNo)
{
String cmd1 = "Select Id from Student Where RegistrationNo = '" + RegNoTextBox.Text + "'";
var reader1 = DatabaseConnection.getInstance().getData(cmd1);
while (reader1.Read())
{
flag--;
RegError.Text = "Already exists";
RegError.ForeColor = System.Drawing.Color.Red;
}
}
flag++;
}
String cmd = "Select Id from Lookup Where Category = 'GENDER' AND Value = '" + GenderComboBox.Text + "'";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
stu.Gender = (int)reader.GetValue(0);
}
flag++;
if (flag == 7)
{
cmd = "Select Id from Person Where Email = '" + stuold.Email + "'";
reader = DatabaseConnection.getInstance().getData(cmd);
int?id = null;
while (reader.Read())
{
id = (int)reader.GetValue(0);
}
if (id != null)
{
cmd = String.Format("Update Person SET FirstName = '{0}', LastName = '{1}', Contact = '{2}', Email = '{3}', DateOfBirth = '{4}', Gender = '{5}' WHERE Id = '{6}'", stu.FirstName, stu.LastName, stu.Contact, stu.Email, stu.DateOfBirth, stu.Gender, id);
DatabaseConnection.getInstance().exectuteQuery(cmd);
cmd = String.Format("UPDATE Student SET Id = '{0}', RegistrationNo = '{1}' WHERE Id = '{2}'", id, stu.RegistrationNo, id);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public Project()
{
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
try
{
String cmd = "Select Id, Title, Description from Project";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <ProjectClass> students = new List <ProjectClass>();
while (reader.Read())
{
ProjectClass stu = new ProjectClass();
stu.Id = (int)reader.GetValue(0);
stu.Titlle = reader.GetString(1);
stu.Description = reader.GetString(2);
students.Add(stu);
}
ProjectDataGrid.DataSource = students;
ProjectDataGrid.Columns[0].Visible = false;
ProjectDataGrid.Columns[2].Visible = false;
DataGridViewButtonColumn bcol3 = new DataGridViewButtonColumn();
bcol3.HeaderText = "";
bcol3.Text = "Details";
bcol3.Name = "btnDetails";
bcol3.UseColumnTextForButtonValue = true;
ProjectDataGrid.Columns.Add(bcol3);
DataGridViewButtonColumn bcol = new DataGridViewButtonColumn();
bcol.HeaderText = "";
bcol.Text = "Edit";
bcol.Name = "btnEdit";
bcol.UseColumnTextForButtonValue = true;
ProjectDataGrid.Columns.Add(bcol);
DataGridViewButtonColumn bcol1 = new DataGridViewButtonColumn();
bcol1.HeaderText = "";
bcol1.Text = "Delete";
bcol1.Name = "btnDelete";
bcol1.UseColumnTextForButtonValue = true;
ProjectDataGrid.Columns.Add(bcol1);
DataGridViewButtonColumn bcol2 = new DataGridViewButtonColumn();
bcol2.HeaderText = "";
bcol2.Text = "Group";
bcol2.Name = "btnGroup";
bcol2.UseColumnTextForButtonValue = true;
ProjectDataGrid.Columns.Add(bcol2);
cmd = "Select Title from Project";
reader = DatabaseConnection.getInstance().getData(cmd);
int i = 0;
while (reader.FieldCount > i)
{
if (!SearchCategory.Items.Contains(reader.GetName(i)))
{
SearchCategory.Items.Add(reader.GetName(i));
}
i++;
}
SearchCategory.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Refresh()
{
DeleteButton.Visible = false;
StudentDataGrid.Columns.Clear();
SearchCategory.Items.Clear();
try
{
String cmd = "SELECT Person.Id, FirstName, LastName, Email, AssignmentDate, Value AS AdvisorRoles FROM ((Advisor JOIN Person ON Advisor.Id = Person.Id) JOIN ProjectAdvisor ON Advisor.Id = AdvisorId) JOIN Lookup ON AdvisorRole = Lookup.Id WHERE ProjectId = " + id.ToString();
var reader = DatabaseConnection.getInstance().getData(cmd);
DataGridViewCheckBoxColumn checkColumn = new DataGridViewCheckBoxColumn();
checkColumn.Name = "Select";
checkColumn.HeaderText = "";
checkColumn.TrueValue = true;
checkColumn.FalseValue = false;
checkColumn.Width = 20;
checkColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
checkColumn.ReadOnly = false; //if the datagridview is resized (on form resize) the checkbox won't take up too much; value is relative to the other columns' fill values
StudentDataGrid.Columns.Add(checkColumn);
List <AdvisorClass> students = new List <AdvisorClass>();
while (reader.Read())
{
AdvisorClass stu = new AdvisorClass();
stu.Id = (int)reader.GetValue(0);
stu.FirstName = reader.GetString(1);
stu.LastName = reader.GetString(2);
stu.Email = reader.GetString(3);
stu.AssignmentDate = (DateTime)reader.GetValue(4);
stu.AdvisorRole = reader.GetString(5);
students.Add(stu);
}
StudentDataGrid.DataSource = students;
for (int j = 0; j < StudentDataGrid.RowCount; j++)
{
StudentDataGrid.Rows[j].Cells[StudentDataGrid.Columns["Select"].Index].Value = false;
}
StudentDataGrid.Columns["Id"].Visible = false;
StudentDataGrid.Columns["Gender"].Visible = false;
StudentDataGrid.Columns["Designation"].Visible = false;
StudentDataGrid.Columns["Contact"].Visible = false;
StudentDataGrid.Columns["DateOfBirth"].Visible = false;
StudentDataGrid.Columns["Genders"].Visible = false;
StudentDataGrid.Columns["Designations"].Visible = false;
StudentDataGrid.Columns["Salary"].Visible = false;
cmd = "SELECT FirstName, LastName, Email, Value AS AdvisorRoles FROM ((Advisor JOIN Person ON Advisor.Id = Person.Id) JOIN ProjectAdvisor ON Advisor.Id = AdvisorId) JOIN Lookup ON AdvisorRole = Lookup.Id";
reader = DatabaseConnection.getInstance().getData(cmd);
int i = 0;
while (reader.FieldCount > i)
{
if (!SearchCategory.Items.Contains(reader.GetName(i)))
{
SearchCategory.Items.Add(reader.GetName(i));
}
i++;
}
SearchCategory.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SelectButton_Click(object sender, EventArgs e)
{
try
{
if (RegCombBox.Text == "\0")
{
MessageBox.Show("Box cannot be empty.");
return;
}
if (flag == 0)
{
String ids = "-1";
int GId = 0;
foreach (int x in SId)
{
ids = ids + ", " + x.ToString();
}
String cmd = "SELECT GroupId, StudentId FROM GroupStudent WHERE GroupId = (Select GroupId FROM GroupStudent JOIN Student ON StudentId = Id WHERE RegistrationNo = '" + RegCombBox.SelectedItem.ToString() + "' AND Status = (SELECT Id FROM Lookup WHERE Category = 'STATUS' AND Value = 'Active')) AND StudentID IN (" + ids + ")";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
SId.Remove((int)reader.GetValue(1));
GId = (int)reader.GetValue(0);
}
ids = "-1";
foreach (int x in SId)
{
ids = ids + ", " + x.ToString();
}
cmd = "UPDATE GroupStudent SET Status = (SELECT Id FROM Lookup WHERE Category = 'STATUS' AND Value = 'InActive') WHERE StudentId IN (Select Id from Student JOIN GroupStudent ON Id = StudentId WHERE Id IN (" + ids + "))";
DatabaseConnection.getInstance().exectuteQuery(cmd);
foreach (int x in SId)
{
cmd = String.Format("Insert INTO GroupStudent(GroupId, StudentId, Status, AssignmentDate) values('{0}', '{1}', (SELECT Id FROM Lookup WHERE Category = 'STATUS' AND Value = 'Active'), GETDATE())", GId, x);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
Parent.Refresh();
MessageBox.Show("Students added.");
this.Close();
}
else if (flag == 1)
{
int flag2 = 0;
String data = "";
foreach (char x in RegCombBox.SelectedItem.ToString())
{
if (flag2 <= 1 && x == ' ')
{
flag2++;
}
else if (x == '\0')
{
break;
}
else if (flag2 == 2)
{
data = data + x;
}
}
string cmd = "SELECT GroupProject.GroupId FROM (Student JOIN GroupStudent ON Student.Id = StudentId) JOIN GroupProject ON GroupStudent.GroupId = GroupProject.GroupId WHERE ProjectId = " + Id.ToString() + " AND RegistrationNo = '" + data + "' AND Status = (SELECT Id FROM Lookup WHERE Category = 'STATUS' AND Value = 'Active')";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
GroupDetails gd = new GroupDetails((int)reader.GetValue(0));
this.Close();
gd.Show();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void AddButton_Click(object sender, EventArgs e)
{
int flag = 0;
int obtainmark = 0;
List <int> idi = new List <int>();
String ids = "-1";
for (int j = 0; j < ProjectDataGrid.RowCount; j++)
{
if (ProjectDataGrid.Rows[j].Cells[ProjectDataGrid.Columns["Select"].Index].Value == null)
{
ProjectDataGrid.Rows[j].Cells[ProjectDataGrid.Columns["Select"].Index].Value = false;
}
}
for (int i = 0; i < ProjectDataGrid.RowCount; i++)
{
if ((bool)ProjectDataGrid.Rows[i].Cells[ProjectDataGrid.Columns["Select"].Index].Value)
{
idi.Add((int)ProjectDataGrid.Rows[i].Cells[ProjectDataGrid.Columns["Id"].Index].Value);
ids = ids + ", " + ProjectDataGrid.Rows[i].Cells[ProjectDataGrid.Columns["Id"].Index].Value.ToString();
}
}
if ((string.IsNullOrEmpty(OMTextBox.Text)))
{
flag++;
obtainmark = 0;
}
else if (Regexp("^[0-9]{1,3}", OMTextBox, OMError, "Only valid numbers are allowed.", 3))
{
obtainmark = Convert.ToInt32(OMTextBox.Text);
string cmd = "SELECT COUNT(*) FROM Evaluation WHERE Id IN (" + ids + ") AND TotalMarks < " + OMTextBox.Text;
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
if ((int)reader.GetValue(0) > 0)
{
flag--;
OMError.ForeColor = System.Drawing.Color.Red;
OMError.Text = "Total marks is less than current obtained marks.";
}
}
flag++;
}
if (flag == 1)
{
try
{
int sum = 0;
string cmd = "SELECT SUM(TotalWeightage) FROM Evaluation JOIN GroupEvaluation ON EvaluationId = Id WHERE GroupId = " + id;
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
if (reader.GetValue(0) == null)
{
sum = (int)reader.GetValue(0) + sum;
}
}
foreach (int idk in idi)
{
cmd = "SELECT TotalWeightage FROM Evaluation WHERE Id = " + idk;
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
sum = (int)reader.GetValue(0) + sum;
if (sum > 100)
{
MessageBox.Show("Total Weightage of evaluation/s exceeds 100%.\nCurrent evaluation not added.");
flag = 0;
break;
}
}
}
if (flag != 0)
{
foreach (int idk in idi)
{
DateTime s = EDDatePicker.Value;
cmd = String.Format("INSERT INTO GroupEvaluation(EvaluationId, GroupId, ObtainedMarks, EvaluationDate) VALUES({0}, {1}, {2}, '{3}')", idk, id, obtainmark, s.Month + "/" + s.Day + "/" + s.Year);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
}
if (idi.Count == 0)
{
MessageBox.Show("Nothing was selected.");
}
else if (flag != 0)
{
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public Form1()
{
InitializeComponent();
DatabaseConnection.getInstance().ConnectionString = "Data Source=(local);Initial Catalog=TestDb;Persist Security Info=True;User ID=sa;Password=samyan123";
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
DatabaseConnection.getInstance().closeConnection();
}
private void Add_Click(object sender, EventArgs e)
{
int flag = 0;
try
{
AdvisorClass stu = new AdvisorClass();
stu.Gender = null;
stu.LastName = null;
stu.Contact = null;
stu.DateOfBirth = null;
stu.Salary = null;
if (Regexp("^[a-zA-Z]{1,100}", FirstNameTextBox, FNError, "Only alphabets are allowed.", 100))
{
stu.FirstName = FirstNameTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(LastNameTextBox.Text)))
{
flag++;
}
else if (Regexp("^[a-zA-Z]{1,100}", LastNameTextBox, LNError, "Only alphabets are allowed.", 100))
{
stu.LastName = LastNameTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(ContactTextBox.Text)))
{
flag++;
}
else if (Regexp("^[0-9]{1,20}", ContactTextBox, ConError, "Only valid numbers are allowed.", 20))
{
stu.Contact = ContactTextBox.Text;
flag++;
}
if (Regexp("^([\\w\\.\\-]+)@([\\w\\-]+)((\\.(\\w){2,3})+)$", EmailTextBox, EmailError, "Format not correct eg. [email protected]", 30))
{
stu.Email = EmailTextBox.Text;
String cmd1 = "Select Id from Person Where Email = '" + EmailTextBox.Text + "'";
var reader1 = DatabaseConnection.getInstance().getData(cmd1);
while (reader1.Read())
{
flag--;
EmailError.Text = "Already exists";
EmailError.ForeColor = System.Drawing.Color.Red;
}
flag++;
}
stu.DateOfBirth = DateOfBirthPick.Value;
flag++;
if ((string.IsNullOrEmpty(SalaryTextBox.Text)))
{
flag++;
}
else if (Regexp("[0-9]{1,18}", SalaryTextBox, SalaryError, "Only valid numbers are allowed.", 18))
{
stu.Salary = Convert.ToDecimal(SalaryTextBox.Text);
flag++;
}
String cmd = "Select Id from Lookup Where Category = 'GENDER' AND Value = '" + GenderComboBox.Text + "'";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
stu.Gender = (int)reader.GetValue(0);
}
flag++;
cmd = "Select Id from Lookup Where Category = 'DESIGNATION' AND Value = '" + DesComboBox.Text + "'";
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
stu.Designation = (int)reader.GetValue(0);
}
flag++;
if (flag == 8)
{
cmd = String.Format("INSERT INTO Person(FirstName, LastName, Contact, Email, DateOfBirth, Gender) values('{0}','{1}','{2}','{3}','{4}','{5}')", stu.FirstName, stu.LastName, stu.Contact, stu.Email, stu.DateOfBirth, stu.Gender);
DatabaseConnection.getInstance().exectuteQuery(cmd);
cmd = "Select Id from Person Where Email = '" + stu.Email + "'";
reader = DatabaseConnection.getInstance().getData(cmd);
int?id = null;
while (reader.Read())
{
id = (int)reader.GetValue(0);
}
if (id != null)
{
cmd = String.Format("Insert INTO Advisor(Id, Designation, Salary) values('{0}', '{1}', '{2}')", id, stu.Designation, stu.Salary);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Save_Click(object sender, EventArgs e)
{
int flag = 0;
try
{
EvaluationClass stu = new EvaluationClass();
if ((string.IsNullOrEmpty(NameTextBox.Text)))
{
flag++;
}
else if (Regexp("^[a-zA-Z]{1,100}", NameTextBox, NError, "Only alphabets are allowed.", 100))
{
stu.Name = NameTextBox.Text;
flag++;
}
if ((string.IsNullOrEmpty(TMTextBox.Text)))
{
flag++;
string cmd = "SELECT COUNT(*) FROM Evaluation JOIN GroupEvaluation ON Id = EvaluationId WHERE ObtainedMarks > " + TMTextBox.Text;
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
if ((int)reader.GetValue(0) > 0)
{
flag--;
TMError.ForeColor = System.Drawing.Color.Red;
TMError.Text = "Some data have obtained marks so it can't be null.";
}
}
}
else if (Regexp("^[0-9]{1,3}", TMTextBox, TMError, "Only valid numbers are allowed.", 3))
{
stu.TotalMarks = Convert.ToInt32(TMTextBox.Text);
string cmd = "SELECT COUNT(*) FROM Evaluation JOIN GroupEvaluation ON Id = EvaluationId WHERE ObtainedMarks > " + TMTextBox.Text;
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
if ((int)reader.GetValue(0) > 0)
{
flag--;
TMError.ForeColor = System.Drawing.Color.Red;
TMError.Text = "Some data have obtained marks greater then current value.";
}
}
flag++;
}
if ((string.IsNullOrEmpty(TWTextBox.Text)))
{
flag++;
}
else if (Regexp("^[0-9]{1,3}", TWTextBox, TWError, "Only valid numbers are allowed.", 3))
{
if (Convert.ToInt32(TWTextBox.Text) > 100)
{
Regexp("^[?]{1,3}", TWTextBox, TWError, "Weightage cannot exceed 100%", 3);
flag--;
}
stu.TotalWeightage = Convert.ToInt32(TWTextBox.Text);
flag++;
}
if (flag == 3)
{
if (stuold.Id != null)
{
string cmd = String.Format("Update Evaluation SET Name = '{0}', TotalMarks = '{1}', TotalWeightage = '{2}' WHERE Id = '{3}'", stu.Name, stu.TotalMarks, stu.TotalWeightage, stuold.Id);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
par.Refresh();
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}