public Advisor() { InitializeComponent(); this.MinimumSize = new Size(this.Width, this.Height); try { String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Value AS Genders, Designation ,a.Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN Lookup ON Person.Gender = Lookup.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.Id "; 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; AdvisorDataGrid.Columns[0].Visible = false; AdvisorDataGrid.Columns[6].Visible = false; AdvisorDataGrid.Columns[7].Visible = false; DataGridViewButtonColumn bcol = new DataGridViewButtonColumn(); bcol.HeaderText = ""; bcol.Text = "Edit"; bcol.Name = "btnEdit"; bcol.UseColumnTextForButtonValue = true; AdvisorDataGrid.Columns.Add(bcol); DataGridViewButtonColumn bcol1 = new DataGridViewButtonColumn(); bcol1.HeaderText = ""; bcol1.Text = "Delete"; bcol1.Name = "btnDelete"; bcol1.UseColumnTextForButtonValue = true; AdvisorDataGrid.Columns.Add(bcol1); cmd = "Select FirstName, LastName, Contact, Email, Value AS Genders, Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN Lookup ON Person.Gender = Lookup.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.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); } }
public void Refresh() { try { String cmd = "Select Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Value, Designation ,a.Designations, Salary from ((Person JOIN Advisor ON Person.Id = Advisor.Id) JOIN Lookup ON Person.Gender = Lookup.Id) JOIN (SELECT Id, Value As Designations, Category FROM Lookup) a ON Advisor.Designation = a.Id "; 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 AdvisorEdit(int id, MainPage parent) { par = parent; InitializeComponent(); this.MinimumSize = new Size(this.Width, this.Height); FNError.Text = ""; LNError.Text = ""; ConError.Text = ""; EmailError.Text = ""; SalaryError.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 Value from Lookup Where Category = 'DESIGNATION'"; reader = DatabaseConnection.getInstance().getData(cmd); while (reader.Read()) { DesComboBox.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()) { DesComboBox.SelectedIndex = GenderComboBox.Items.IndexOf(reader.GetString(0)); } cmd = "SELECT Person.Id, FirstName, LastName, Contact, Email, DateOfBirth, Gender, Designation, Salary FROM Person JOIN Advisor ON Person.Id = Advisor.Id where Person.Id = " + id.ToString(); reader = DatabaseConnection.getInstance().getData(cmd); stuold = new AdvisorClass(); 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.Designation = (int)reader.GetValue(7); stuold.Salary = (decimal)reader.GetValue(8); } FirstNameTextBox.Text = stuold.FirstName; LastNameTextBox.Text = stuold.LastName; ContactTextBox.Text = stuold.Contact; EmailTextBox.Text = stuold.Email; SalaryTextBox.Text = stuold.Salary.ToString(); DateOfBirthPick.Value = (DateTime)stuold.DateOfBirth; }
public ProjectGroup() { InitializeComponent(); this.MinimumSize = new Size(this.Width, this.Height); try { String cmd = "Select Id, Created_On, AssignmentDate from Group JOIN GroupProject"; 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; AdvisorDataGrid.Columns[0].Visible = false; AdvisorDataGrid.Columns[6].Visible = false; AdvisorDataGrid.Columns[7].Visible = false; DataGridViewButtonColumn bcol = new DataGridViewButtonColumn(); bcol.HeaderText = ""; bcol.Text = "Edit"; bcol.Name = "btnEdit"; bcol.UseColumnTextForButtonValue = true; AdvisorDataGrid.Columns.Add(bcol); DataGridViewButtonColumn bcol1 = new DataGridViewButtonColumn(); bcol1.HeaderText = ""; bcol1.Text = "Delete"; bcol1.Name = "btnDelete"; bcol1.UseColumnTextForButtonValue = true; AdvisorDataGrid.Columns.Add(bcol1); } 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, 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() + " AND " + 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.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; } 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() { 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 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); } }