private void button9_Click(object sender, EventArgs e)
{
this.Hide();
advisor f1 = new advisor();
f1.Show();
}
private void button2_Click(object sender, EventArgs e)
{
this.Hide();
advisor r = new advisor();
r.Show();
}
private void button7_Click(object sender, EventArgs e)
{
this.Refresh();
Refresh();
this.Hide();
advisor ss = new advisor();
ss.Show();
}
public addadvics(int id)
{
InitializeComponent();
ide = id;
createbtn.Hide();
adadd.Text = "EDIT ADVISORS";
DatabaseConnection.getInstance().ConnectionString = "Data Source=HIBA\\SQLSERVER;Initial Catalog=ProjectA;Integrated Security=False;User ID=sa;Password=05feb1999;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
String cmd = String.Format("SELECT Value FROM Lookup WHERE (Category = 'GENDER')");
SqlDataReader read = DatabaseConnection.getInstance().getData(cmd);
while (read.Read())
{
addgendercombo.Items.Add(read["Value"].ToString());
}
cmd = "Select Value from Lookup Where Id = (SELECT Gender FROM Person where Id = " + id.ToString() + ")";
var reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
addgendercombo.SelectedIndex = addgendercombo.Items.IndexOf(reader.GetString(0));
}
cmd = String.Format("SELECT Value FROM Lookup WHERE (Category = 'DESIGNATION')");
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
degtext.Items.Add(reader["Value"].ToString());
}
cmd = "Select Value from Lookup Where Id = (SELECT Designation FROM Advisor where Id = " + id.ToString() + ")";
reader = DatabaseConnection.getInstance().getData(cmd);
while (reader.Read())
{
degtext.SelectedIndex = degtext.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);
advisor s = new advisor();
while (reader.Read())
{
s.Id = (int)reader.GetValue(0);
s.FirstName = reader.GetString(1);
s.LastName = reader.GetString(2);
s.Contact = reader.GetString(3);
s.Email = reader.GetString(4);
s.DateOfBirth = (DateTime)reader.GetValue(5);
s.salary = Convert.ToDecimal(reader.GetValue(8));
}
addfnametext.Text = s.FirstName;
addlnametext.Text = s.LastName;
addcontacttxt.Text = s.Contact;
addemailtext.Text = s.Email;
addobtext.Value = (DateTime)s.DateOfBirth;
addsaltext.Text = s.salary.ToString();
}
private void button1_Click(object sender, EventArgs e)
{
advisor p = new advisor();
Regexp(@"^[a-zA-Z]{1,100}", addfnametext, addfnme, "First name");
Regexp(@"^[a-zA-Z]{1,100}", addlnametext, addlnme, "Last name");
Regexp(@"^[0-9]{1,20}", addcontacttxt, adcon, "Contact");
Regexp(@"^([\\w\\.\\-]+)@([\\w\\-]+)((\\.(\\w){2,3})+)$", addemailtext, emailad, "Email");
p.FirstName = addfnametext.Text;
p.LastName = addlnametext.Text;
p.Contact = addcontacttxt.Text;
p.Email = addemailtext.Text;
p.DateOfBirth = addobtext.Value;
String cmd = String.Format("Select Id from Lookup Where Value = '{0}'", addgendercombo.Text);
SqlCommand a = new SqlCommand(cmd, DatabaseConnection.getInstance().getConnection());
int Gender;
Gender = (Int32)a.ExecuteScalar();
cmd = String.Format("INSERT INTO Person(FirstName,LastName,Contact,Email,DateOfBirth,Gender) values('{0}','{1}','{2}','{3}','{4}','{5}' )", p.FirstName, p.LastName, p.Contact, p.Email, p.DateOfBirth, Gender);
DatabaseConnection.getInstance().exectuteQuery(cmd);
cmd = "Select MAX(Id) from Person ";
SqlDataReader reader = DatabaseConnection.getInstance().getData(cmd);
int? id = null;
while (reader.Read())
{
id = (int)reader.GetValue(0);
}
if (id != null)
{
p.salary = Convert.ToDecimal(addsaltext.Text);
cmd = String.Format("Select Id from Lookup Where Value = '{0}'", degtext.Text);
a = new SqlCommand(cmd, DatabaseConnection.getInstance().getConnection());
int designation;
designation = (Int32)a.ExecuteScalar();
cmd = String.Format("Insert INTO Advisor(Id,Designation,Salary) values('{0}', '{1}','{2}')", id, designation, p.salary);
DatabaseConnection.getInstance().exectuteQuery(cmd);
}
}
public advisors()
{
InitializeComponent();
DatabaseConnection.getInstance().ConnectionString = "Data Source=HIBA\\SQLSERVER;Initial Catalog=ProjectA;Integrated Security=False;User ID=sa;Password=05feb1999;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
String cmd = "Select Person.Id, FirstName , LastName,Designation,Salary ,Contact,Email,DateOfBirth ,Person.Gender from Advisor JOIN Person ON Advisor.Id = Person.Id";
var reader = DatabaseConnection.getInstance().getData(cmd);
List <advisor> persons = new List <advisor>();
while (reader.Read())
{
advisor pro = new advisor();
pro.FirstName = reader.GetString(1);
pro.LastName = reader.GetString(2);
pro.Id = (int)reader.GetValue(0);
pro.Email = reader.GetString(6);
pro.Contact = reader.GetString(5);
pro.DateOfBirth = (DateTime)reader.GetValue(7);
pro.salary = Convert.ToDecimal(reader.GetValue(4));
cmd = "Select Value from Lookup Where Id = (SELECT Designation FROM Advisor where Id = " + pro.Id + ")";
SqlCommand a = new SqlCommand(cmd, DatabaseConnection.getInstance().getConnection());
pro.designation = a.ExecuteScalar().ToString();
cmd = "Select Value from Lookup Where Id = (SELECT Gender FROM Person where Id = " + pro.Id + ")";
a = new SqlCommand(cmd, DatabaseConnection.getInstance().getConnection());
pro.Gender = a.ExecuteScalar().ToString();
persons.Add(pro);
}
dataGridadd.DataSource = persons;
}
private void saveupadd_Click(object sender, EventArgs e)
{
advisor p = new advisor();
Regexp(@"^[a-zA-Z]{1,100}", addfnametext, addfnme, "First name");
Regexp(@"^[a-zA-Z]{1,100}", addlnametext, addlnme, "Last name");
Regexp(@"^[0-9]{1,20}", addcontacttxt, adcon, "Contact");
Regexp(@"^([\\w\\.\\-]+)@([\\w\\-]+)((\\.(\\w){2,3})+)$", addemailtext, emailad, "Email");
Regexp(@"^[0-9]{1,30}", addsaltext, saladd, "Salary");
p.FirstName = addfnametext.Text;
p.LastName = addlnametext.Text;
p.Contact = addcontacttxt.Text;
p.Email = addemailtext.Text;
p.DateOfBirth = addobtext.Value;
p.salary = Convert.ToDecimal(addsaltext.Text);
String cmd = String.Format("Select Id from Lookup Where Value = '{0}'", addgendercombo.Text);
SqlCommand a = new SqlCommand(cmd, DatabaseConnection.getInstance().getConnection());
int Gender;
Gender = (Int32)a.ExecuteScalar();
cmd = String.Format("Update Person SET FirstName = '{0}', LastName = '{1}', Contact = '{2}', Email = '{3}', DateOfBirth = '{4}', Gender = '{5}' WHERE Id = '{6}'", p.FirstName, p.LastName, p.Contact, p.Email, p.DateOfBirth, Gender, ide);
DatabaseConnection.getInstance().exectuteQuery(cmd);
cmd = String.Format("Select Id from Lookup Where Value = '{0}'", degtext.Text);
a = new SqlCommand(cmd, DatabaseConnection.getInstance().getConnection());
int designation;
designation = (Int32)a.ExecuteScalar();
cmd = String.Format("UPDATE Advisor SET Id = '{0}', Designation = '{1}', Salary = '{2}' WHERE Id = '{2}'", ide, designation, p.salary, ide);
DatabaseConnection.getInstance().exectuteQuery(cmd);
advisors s = new advisors();
this.Hide();
s.Show();
}
private void button6_Click(object sender, EventArgs e)
{
conn.Open();
string fn = textBox1.Text;
string ln = textBox2.Text;
string con = textBox3.Text;
string email = textBox4.Text;
string salary = textBox6.Text;
if (string.IsNullOrEmpty(fn) || !Regex.IsMatch(textBox1.Text, @"[A-Za-z]+$"))
{
MessageBox.Show("Invalid First Name.Should be letters");
textBox1.BackColor = Color.DodgerBlue;
textBox1.Clear();
conn.Close();
return;
}
textBox1.BackColor = Color.White;
if (string.IsNullOrEmpty(ln) || !Regex.IsMatch(textBox2.Text, @"[A-Za-z]+$"))
{
MessageBox.Show("Invalid last Name.Should be letters");
textBox2.BackColor = Color.DodgerBlue;
textBox2.Clear();
conn.Close();
return;;
}
textBox2.BackColor = Color.White;
if (string.IsNullOrEmpty(con) || !Regex.IsMatch(textBox3.Text, @"^[0-9]{10,12}$"))
{
MessageBox.Show("Invalid contact ,should be 11 digits");
textBox3.BackColor = Color.DodgerBlue;
textBox3.Clear();
conn.Close();
return;
}
textBox3.BackColor = Color.White;
if (string.IsNullOrEmpty(email) || !Regex.IsMatch(textBox4.Text, @"^([\w]+)@([\w]+)\.([\w]+)$"))
{
MessageBox.Show("Invalid email ");
textBox4.BackColor = Color.DodgerBlue;
textBox4.Clear();
conn.Close();
return;
}
textBox4.BackColor = Color.White;
if (string.IsNullOrEmpty(salary) || !Regex.IsMatch(textBox6.Text, @"^[0-9]*$"))
{
MessageBox.Show("Invalid salary,should be digits ");
textBox6.BackColor = Color.DodgerBlue;
textBox6.Clear();
conn.Close();
return;
}
textBox6.BackColor = Color.White;
DateTime dt = dateTimePicker1.Value;
string design = comboBox2.SelectedItem.ToString();
try
{
//getting designation
string designn = "SELECT Id FROM Lookup WHERE Category= 'DESIGNATION' AND Value ='" + design + "' ";
SqlCommand InvAmountcom = new SqlCommand(designn, conn);
int desigination = Convert.ToInt32(InvAmountcom.ExecuteScalar().ToString());
string gen = comboBox1.SelectedItem.ToString();
//getting gender value from lookup
string genvalue = "SELECT Id FROM Lookup WHERE Category= 'GENDER' AND Value ='" + gen + "' ";
SqlCommand genint = new SqlCommand(genvalue, conn);
genint.ExecuteNonQuery();
int gender = Convert.ToInt32(genint.ExecuteScalar());
//inserting value in person
String insert1 = "update Person set FirstName = '" + fn + "',LastName='" + ln + "',Contact='" + con + "',Email='" + email + "',DateOfBirth='" + dt + "',Gender='" + gender + "'where Id='" + IDD + "'";
SqlCommand sql1 = new SqlCommand(insert1, conn);
int i1 = sql1.ExecuteNonQuery();
//getting id from person for advisor
string sid = String.Format("SELECT Id FROM Person Where Email ='{0}'", email);
SqlCommand stuid = new SqlCommand(sid, conn);
int id = Convert.ToInt32(stuid.ExecuteScalar());
String insert2 = "update Advisor set Designation = '" + desigination + "',Salary='" + salary + "'where Id='" + IDD + "'";
SqlCommand sql2 = new SqlCommand(insert2, conn);
int i2 = sql2.ExecuteNonQuery();
// to check if the student is registered or not
if (i1 >= 1 && i2 >= 1)
{
MessageBox.Show("Edited Succesfully");
this.Refresh();
}
else
{
{ MessageBox.Show("Edition failed"); }
}
this.Refresh();
Refresh();
this.Hide();
advisor ss = new advisor();
ss.Show();
conn.Close();
}
catch (Exception a)
{
MessageBox.Show(a.Message);
conn.Close();
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
conn.Open();
if (e.RowIndex == dataGridView1.NewRowIndex || e.RowIndex < 0)
{
return;
}
//Check if click is on specific column
if (e.ColumnIndex == dataGridView1.Columns["DeleteButton"].Index)
{
int index = e.RowIndex;
int ID = Convert.ToInt32(dataGridView1.Rows[index].Cells[3].Value.ToString());
try
{
string delete3 = String.Format("DELETE FROM ProjectAdvisor WHERE AdvisorId = '{0}'", ID);
SqlCommand de13 = new SqlCommand(delete3, conn);
int k = de13.ExecuteNonQuery();
string delete1 = String.Format("DELETE FROM Advisor WHERE Id = '{0}'", ID);
SqlCommand de11 = new SqlCommand(delete1, conn);
int i = de11.ExecuteNonQuery();
string delete2 = string.Format("DELETE FROM Person WHERE Id = '{0}'", ID);
SqlCommand del2 = new SqlCommand(delete2, conn);
int j = del2.ExecuteNonQuery();
// to check if the student is registered or not
if (i >= 1 && j >= 1)
{
MessageBox.Show(" Advisor Deleted ");
this.Refresh();
}
else
{
MessageBox.Show(" Advisor not Deleted");
}
this.Refresh();
Refresh();
this.Hide();
advisor ss = new advisor();
ss.Show();
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
}
else if (e.ColumnIndex == dataGridView1.Columns["EditButton"].Index)
{
int indexx = e.RowIndex;
IDD = Convert.ToInt32(dataGridView1.Rows[indexx].Cells[3].Value.ToString());
panel5.Show();
button5.Hide();
button6.Show();
try
{
string fn = "SELECT FirstName FROM Person WHERE Id = '" + IDD + "' ";
SqlCommand fnn = new SqlCommand(fn, conn);
fnn.ExecuteNonQuery();
string first = Convert.ToString(fnn.ExecuteScalar());
textBox1.Text = first;
string ln = "SELECT LastName FROM Person WHERE Id = '" + IDD + "' ";
SqlCommand lnn = new SqlCommand(ln, conn);
lnn.ExecuteNonQuery();
string last = Convert.ToString(lnn.ExecuteScalar());
textBox2.Text = last;
string con = "SELECT Contact FROM Person WHERE Id = '" + IDD + "' ";
SqlCommand cont = new SqlCommand(con, conn);
cont.ExecuteNonQuery();
string contact = Convert.ToString(cont.ExecuteScalar());
textBox3.Text = contact;
string em = "SELECT Email FROM Person WHERE Id = '" + IDD + "' ";
SqlCommand emi = new SqlCommand(em, conn);
emi.ExecuteNonQuery();
string email = Convert.ToString(emi.ExecuteScalar());
textBox4.Text = email;
string sal = "SELECT Salary FROM Advisor WHERE Id = '" + IDD + "' ";
SqlCommand sall = new SqlCommand(sal, conn);
sall.ExecuteNonQuery();
string salary = Convert.ToString(sall.ExecuteScalar());
textBox6.Text = salary;
string gen = "SELECT Gender FROM Person WHERE Id = '" + IDD + "' ";
SqlCommand gender = new SqlCommand(gen, conn);
gender.ExecuteNonQuery();
int genn = Convert.ToInt32(gender.ExecuteScalar());
string designn = "SELECT Value FROM Lookup WHERE Category= 'GENDER' AND Id ='" + genn + "' ";
SqlCommand InvAmountcom = new SqlCommand(designn, conn);
string mf = Convert.ToString(InvAmountcom.ExecuteScalar().ToString());
comboBox1.Text = mf;
string de = "SELECT Designation FROM Advisor WHERE Id = '" + IDD + "' ";
SqlCommand des = new SqlCommand(de, conn);
des.ExecuteNonQuery();
int desg = Convert.ToInt32(des.ExecuteScalar());
string design = "SELECT Value FROM Lookup WHERE Category= 'DESIGNATION' AND Id ='" + desg + "' ";
SqlCommand InvAmount = new SqlCommand(design, conn);
string mff = Convert.ToString(InvAmount.ExecuteScalar().ToString());
comboBox2.Text = mff;
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
}
else if (e.ColumnIndex == dataGridView1.Columns["DetailsButton"].Index)
{
this.Hide();
advidetails s1 = new advidetails();
s1.Show();
}
conn.Close();
}
private void button5_Click(object sender, EventArgs e)
{
conn.Open();
string fn = textBox1.Text;
string ln = textBox2.Text;
string con = textBox3.Text;
string email = textBox4.Text;
string salary = textBox6.Text;
if (string.IsNullOrEmpty(fn) || !Regex.IsMatch(textBox1.Text, @"[A-Za-z]+$"))
{
MessageBox.Show("Invalid First name. Should be letters ");
textBox1.BackColor = Color.DodgerBlue;
textBox1.Clear();
conn.Close();
return;
}
textBox1.BackColor = Color.White;
if (string.IsNullOrEmpty(ln) || !Regex.IsMatch(textBox2.Text, @"[A-Za-z]+$"))
{
MessageBox.Show("Invalid Last name. Should be letters");
textBox2.BackColor = Color.DodgerBlue;
textBox2.Clear();
conn.Close();
return;;
}
textBox2.BackColor = Color.White;
if (string.IsNullOrEmpty(con) || !Regex.IsMatch(textBox3.Text, @"^[0-9]{11}$"))
{
MessageBox.Show("Invalid contact number.Should be digits");
textBox3.BackColor = Color.DodgerBlue;
textBox3.Clear();
conn.Close();
return;
}
textBox3.BackColor = Color.White;
SqlCommand cmd = new SqlCommand("Select count(*) from Person where Email= @email", conn);
cmd.Parameters.AddWithValue("@email", this.textBox4.Text);
int result = Convert.ToInt32(cmd.ExecuteScalar());
if (result != 0)
{
MessageBox.Show("Email Exists");
conn.Close();
return;
}
if (string.IsNullOrEmpty(email) || !Regex.IsMatch(textBox4.Text, @"^([\w]+)@([\w]+)\.([\w]+)$"))
{
MessageBox.Show("Invalid email ");
textBox4.BackColor = Color.DodgerBlue;
textBox4.Clear();
conn.Close();
return;
}
textBox4.BackColor = Color.White;
if (string.IsNullOrEmpty(salary) || !Regex.IsMatch(textBox6.Text, @"^[0-9]{0,18}$"))
{
MessageBox.Show("Invalid salary.Should be digits ");
textBox6.BackColor = Color.DodgerBlue;
textBox6.Clear();
conn.Close();
return;
}
textBox6.BackColor = Color.White;
DateTime dt = dateTimePicker1.Value;
string design = comboBox2.SelectedItem.ToString();
//getting designation
try
{
string designn = "SELECT Id FROM Lookup WHERE Category= 'DESIGNATION' AND Value ='" + design + "' ";
SqlCommand InvAmountcom = new SqlCommand(designn, conn);
int desigination = Convert.ToInt32(InvAmountcom.ExecuteScalar().ToString());
string gen = comboBox1.SelectedItem.ToString();
//getting gender value from lookup
string genvalue = "SELECT Id FROM Lookup WHERE Category= 'GENDER' AND Value ='" + gen + "' ";
SqlCommand genint = new SqlCommand(genvalue, conn);
genint.ExecuteNonQuery();
int gender = Convert.ToInt32(genint.ExecuteScalar());
String insert1 = "insert into Person values('" + fn + "','" + ln + "','" + con + "','" + email + "','" + dt + "','" + gender + "' ) ";
SqlCommand sql1 = new SqlCommand(insert1, conn);
int i1 = sql1.ExecuteNonQuery();
//getting id from person for advisor
string sid = String.Format("SELECT Id FROM Person Where Email ='{0}'", email);
SqlCommand stuid = new SqlCommand(sid, conn);
int id = Convert.ToInt32(stuid.ExecuteScalar());
String insert2 = "insert into Advisor values('" + id + "','" + desigination + "','" + salary + "')";
SqlCommand sql2 = new SqlCommand(insert2, conn);
int i2 = sql2.ExecuteNonQuery();
// to check if the student is registered or not
if (i1 >= 1 && i2 >= 1)
{
MessageBox.Show("Advisor Registered ");
this.Refresh();
}
else
{
{ MessageBox.Show(" Advisor not Registered :"); }
}
this.Refresh();
Refresh();
this.Hide();
advisor ss = new advisor();
ss.Show();
}
catch (Exception a)
{
MessageBox.Show(a.Message);
}
conn.Close();
}