private void ManageAdvisor_Click(object sender, EventArgs e)
{
ManageAdvisor E3 = new ManageAdvisor();
E3.Show();
this.Hide();
}
private void dataGridView1_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
{
SqlConnection con = new SqlConnection(conURL);
con.Open();
int currentRow = int.Parse(e.RowIndex.ToString());
int currentColumnIndex = int.Parse(e.ColumnIndex.ToString());
string email = string.Format("SELECT Id FROM Person WHERE Email = '{0}'", dataGridView1.Rows[currentRow].Cells[6].Value.ToString());
SqlCommand cmd3 = new SqlCommand(email, con);
int id = (Int32)cmd3.ExecuteScalar();
if (currentColumnIndex == 0)
{
AddAdvisor f2 = new AddAdvisor(id);
f2.Show();
this.Hide();
}
if (currentColumnIndex == 1)
{
var confirmResult = MessageBox.Show("Are you sure to delete this item ??",
"Confirm Delete!!",
MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
SqlCommand cmd1;
SqlCommand cmd2;
SqlCommand cmd4;
string deleteperson = "DELETE FROM Person Where Id = @num";
string deleteadvisor = "DELETE FROM Advisor Where Id = @num";
string deletepradvisor = "DELETE FROM ProjectAdvisor Where AdvisorId = @num";
cmd1 = new SqlCommand(deleteadvisor, con);
cmd2 = new SqlCommand(deleteperson, con);
cmd4 = new SqlCommand(deletepradvisor, con);
cmd1.Parameters.AddWithValue("@num", id);
cmd2.Parameters.AddWithValue("@num", id);
cmd4.Parameters.AddWithValue("@num", id);
cmd4.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record deleted succesfully");
ManageAdvisor f3 = new ManageAdvisor();
this.Close();
f3.Show();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(conURL);
con.Open();
if (flag > 0)
{
bool j = myvalidations();
if (j == true)
{
SqlCommand cmd1;
SqlCommand cmd3;
string updateperson = "Update Person set FirstName=@FirstName, LastName=@LastName, Contact=@Contact, Email=@Email, DateOfBirth=@DateOfBirth, Gender=@Gender Where Person.Id=@flag1";
string updateadvisor = "Update Advisor set Designation = @Designation, Salary = @Salary Where Advisor.Id = @flag2";
cmd1 = new SqlCommand(updateperson, con);
cmd3 = new SqlCommand(updateadvisor, con);
cmd1.Parameters.AddWithValue("@flag1", flag);
cmd3.Parameters.AddWithValue("@flag2", flag);
string des = string.Format("SELECT Id FROM Lookup WHERE Value = '{0}'", Designation.Text);
SqlCommand cmd4 = new SqlCommand(des, con);
int desid = (Int32)cmd4.ExecuteScalar();
cmd3.Parameters.AddWithValue("@Designation", desid);
if (String.IsNullOrEmpty(Salary.Text))
{
cmd3.Parameters.AddWithValue("@Salary", DBNull.Value);
}
else
{
cmd3.Parameters.AddWithValue("@Salary", Salary.Text);
}
if (String.IsNullOrEmpty(FirstName.Text))
{
cmd1.Parameters.AddWithValue("@FirstName", DBNull.Value);
}
else
{
cmd1.Parameters.AddWithValue("@FirstName", FirstName.Text);
}
if (String.IsNullOrEmpty(LastName.Text))
{
cmd1.Parameters.AddWithValue("@LastName", DBNull.Value);
}
else
{
cmd1.Parameters.AddWithValue("@LastName", LastName.Text);
}
if (String.IsNullOrEmpty(Contact.Text))
{
cmd1.Parameters.AddWithValue("@Contact", DBNull.Value);
}
else
{
cmd1.Parameters.AddWithValue("@Contact", Contact.Text);
}
cmd1.Parameters.AddWithValue("@Email", Email.Text);
if (String.IsNullOrEmpty(DOB.Text))
{
cmd1.Parameters.AddWithValue("@DateOfBirth", DBNull.Value);
}
else
{
cmd1.Parameters.AddWithValue("@DateOfBirth", Convert.ToDateTime(DOB.Text));
}
if (String.IsNullOrEmpty(Gender.Text))
{
cmd1.Parameters.AddWithValue("@Gender", DBNull.Value);
}
else
{
string gen = string.Format("SELECT Id FROM Lookup WHERE Value = '{0}'", Gender.Text);
SqlCommand cmd2 = new SqlCommand(gen, con);
int gid = (Int32)cmd2.ExecuteScalar();
cmd1.Parameters.AddWithValue("@Gender", gid);
}
cmd1.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
con.Close();
MessageBox.Show("Advisor Details Successfully Updated ");
ManageAdvisor f3 = new ManageAdvisor();
f3.Show();
this.Hide();
}
}
else
{
bool j = myvalidations();
if (j == true)
{
SqlCommand cmd;
SqlCommand cmd1;
string s = "INSERT INTO Person(FirstName,LastName,Contact,Email,DateOfBirth,Gender) values(@FirstName,@LastName,@Contact,@Email,@DateOfBirth,@Gender)";
string advisor = "INSERT INTO Advisor(Id,Designation, Salary) values(@Id,@Designation, @Salary)";
cmd = new SqlCommand(s, con);
cmd1 = new SqlCommand(advisor, con);
cmd.Parameters.AddWithValue("@FirstName", FirstName.Text);
if (String.IsNullOrEmpty(LastName.Text))
{
cmd.Parameters.AddWithValue("@LastName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@LastName", LastName.Text);
}
if (String.IsNullOrEmpty(Contact.Text))
{
cmd.Parameters.AddWithValue("@Contact", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Contact", Contact.Text);
}
if (String.IsNullOrEmpty(Email.Text))
{
cmd.Parameters.AddWithValue("@Email", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Email", Email.Text);
}
if (String.IsNullOrEmpty(DOB.Text))
{
cmd.Parameters.AddWithValue("@DateOfBirth", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@DateOfBirth", Convert.ToDateTime(DOB.Text));
}
if (String.IsNullOrEmpty(Gender.Text))
{
cmd.Parameters.AddWithValue("@Gender", DBNull.Value);
}
else
{
string gen = string.Format("SELECT Id FROM Lookup WHERE Value = '{0}'", Gender.Text);
SqlCommand cmd2 = new SqlCommand(gen, con);
int gid = (Int32)cmd2.ExecuteScalar();
cmd.Parameters.AddWithValue("@Gender", gid);
}
int i = cmd.ExecuteNonQuery();
string personid = string.Format("SELECT max(Id) FROM Person");
SqlCommand cmd3 = new SqlCommand(personid, con);
int pid = (Int32)cmd3.ExecuteScalar();
cmd1.Parameters.AddWithValue("@Id", pid);
if (String.IsNullOrEmpty(Salary.Text))
{
cmd1.Parameters.AddWithValue("@Salary", DBNull.Value);
}
else
{
cmd1.Parameters.AddWithValue("@Salary", Convert.ToInt64(Salary.Text));
}
if (String.IsNullOrEmpty(Designation.Text))
{
cmd1.Parameters.AddWithValue("@Designation", DBNull.Value);
}
else
{
string des = string.Format("SELECT Id FROM Lookup WHERE Value = '{0}'", Designation.Text);
SqlCommand cmd4 = new SqlCommand(des, con);
int desid = (Int32)cmd4.ExecuteScalar();
cmd1.Parameters.AddWithValue("@Designation", desid);
}
cmd1.ExecuteNonQuery();
con.Close();
clearfields();
MessageBox.Show(i + " Row(s) Inserted ");
}
}
}