public void Refresh()
{
try
{
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Value, RegistrationNo from (Person JOIN Student ON Person.Id = Student.Id) JOIN Lookup ON Person.Gender = Lookup.Id ";
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;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public Student()
{
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
try
{
String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Value, RegistrationNo from (Person JOIN Student ON Person.Id = Student.Id) JOIN Lookup ON Person.Gender = Lookup.Id ";
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;
DataGridViewButtonColumn bcol = new DataGridViewButtonColumn();
bcol.HeaderText = "";
bcol.Text = "Edit";
bcol.Name = "btnEdit";
bcol.UseColumnTextForButtonValue = true;
StudentDataGrid.Columns.Add(bcol);
DataGridViewButtonColumn bcol1 = new DataGridViewButtonColumn();
bcol1.HeaderText = "";
bcol1.Text = "Delete";
bcol1.Name = "btnDelete";
bcol1.UseColumnTextForButtonValue = true;
StudentDataGrid.Columns.Add(bcol1);
cmd = "Select FirstName, LastName, Contact, Email, Value AS Genders, RegistrationNo from (Person JOIN Student ON Person.Id = Student.Id) JOIN Lookup ON Lookup.Id = Person.Gender";
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 SearchButtonS_Click(object sender, EventArgs e)
{
try
{
if (SearchTextBoxS.Text == "")
{
Refresh();
return;
}
String cmd = "Select Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, RegistrationNo, GroupId, AssignmentDate, Statuses from (Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, RegistrationNo, GroupId, AssignmentDate, Value AS Statuses 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() + ") A WHERE " + SearchCategoryS.Text + " = '" + SearchTextBoxS.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.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;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public StudentEdit(int id, MainPage parent)
{
par = parent;
InitializeComponent();
this.MinimumSize = new Size(this.Width, this.Height);
FNError.Text = "";
LNError.Text = "";
ConError.Text = "";
EmailError.Text = "";
DBError.Text = "";
RegError.Text = "";
String cmd = "Select Value from Lookup Where Category = 'GENDER'";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
GenderComboBox.Items.Add(reader.GetString(0));
}
cmd = "Select Value from Lookup Where Id = (SELECT Gender FROM Person where Id = " + id.ToString() + ")";
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
GenderComboBox.SelectedIndex = GenderComboBox.Items.IndexOf(reader.GetString(0));
}
cmd = "SELECT Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, RegistrationNo FROM Person JOIN Student ON Person.Id = Student.Id where Person.Id = " + id.ToString();
reader = DatabaseConnection.getInstance().getData(cmd);
stuold = new StudentClass();
while (reader.Read())
{
stuold.Id = (int)reader.GetValue(0);
stuold.FirstName = reader.GetString(1);
stuold.LastName = reader.GetString(2);
stuold.Contact = reader.GetString(3);
stuold.Email = reader.GetString(4);
stuold.DateOfBirth = (DateTime)reader.GetValue(5);
stuold.Gender = (int)reader.GetValue(6);
stuold.RegistrationNo = reader.GetString(7);
}
FirstNameTextBox.Text = stuold.FirstName;
LastNameTextBox.Text = stuold.LastName;
ContactTextBox.Text = stuold.Contact;
EmailTextBox.Text = stuold.Email;
RegNoTextBox.Text = stuold.RegistrationNo;
DateOfBirthPick.Value = (DateTime)stuold.DateOfBirth;
}
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);
}
}
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);
}
}