//Make a method to ensure password1 and password2 are equivalent
protected Boolean checkPassword(BusinessEmp bus)
{
if (Password1.Value.ToString() != Password2.Value.ToString())
{
//PassDontMatch.Text = "The passwords entered aren't the same. Please correct and try again.";
return(false);
}
else
{
return(true);
}
}
//Insert into the Database
protected void insert_Click(object sender, EventArgs e)
{
//Create new Employer object
BusinessEmp bus = new BusinessEmp(FirstName.Value.ToString(), LastName.Value.ToString(), CompanyName.Value.ToString(), JobTitle.Value.ToString(), Summary.Value.ToString(), EmailAdd.Value.ToString(), Password1.Value.ToString(),
PhoneNumber.Value.ToString(), CompHouseNumber.Value.ToString(), CompStreet.Value.ToString(), City.Value.ToString(),
CompCountry.Value.ToString(), State.Value.ToString(), CompZip.Value.ToString());
//Doesn't add to the DB if the email address is taken
checkEmail(bus);
if (checkEmail(bus) == false)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "ShowAlert", "ShowDangerAlert();", true);
return;
}
else
{
EmailTaken.Visible = false;
}
checkPassword(bus);
if (checkPassword(bus) == false)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "ShowPassAlert", "ShowDangerPassAlert();", true);
return;
}
else
{
PassDontMatch.Visible = false;
}
if (EmailTaken.Visible == false || PassDontMatch.Visible == false)
{
//Insert values into database when user clicks "Insert"
//Insert into address table
sc.Open();
System.Data.SqlClient.SqlCommand insertAddress = new System.Data.SqlClient.SqlCommand();
insertAddress.Connection = sc;
insertAddress.CommandText = "insert into[Address](HouseNumber, Street, City, State, Country, ZipCode) " +
"values(@HouseNumber,@CompStreet,@City,@CompState,@Country,@ZipCode)";
insertAddress.Parameters.Add(new SqlParameter("@HouseNumber", bus.getCompHouseNumber()));
insertAddress.Parameters.Add(new SqlParameter("@CompStreet", bus.getCompStreet()));
insertAddress.Parameters.Add(new SqlParameter("@City", bus.getCity()));
insertAddress.Parameters.Add(new SqlParameter("@CompState", bus.getState()));
insertAddress.Parameters.Add(new SqlParameter("@Country", bus.getCountry()));
insertAddress.Parameters.Add(new SqlParameter("@ZipCode", bus.getZipCode()));
insertAddress.ExecuteNonQuery();
sc.Close();
//Insert into person table
sc.Open();
System.Data.SqlClient.SqlCommand insertPerson = new System.Data.SqlClient.SqlCommand();
insertPerson.Connection = sc;
System.Data.SqlClient.SqlCommand getdbAddressID = new System.Data.SqlClient.SqlCommand();
getdbAddressID.Connection = sc;
getdbAddressID.CommandText = "SELECT Max(AddressID) from ADDRESS";
getdbAddressID.ExecuteNonQuery();
int holdAddID = (Int32)getdbAddressID.ExecuteScalar();
insertPerson.CommandText = "insert into [Person](FirstName,LastName,Email,personType,AddressID,PhoneNumber) values(@FirstName,@LastName,@Email,@PersonType,@AddressID,@PhoneNumber)";
insertPerson.Parameters.Add(new SqlParameter("@FirstName", bus.getFirstName()));
insertPerson.Parameters.Add(new SqlParameter("@LastName", bus.getLastName()));
insertPerson.Parameters.Add(new SqlParameter("@Email", bus.getEmail()));
insertPerson.Parameters.Add(new SqlParameter("@PhoneNumber", bus.getPhone()));
insertPerson.Parameters.Add(new SqlParameter("@PersonType", "Employer"));
insertPerson.Parameters.Add(new SqlParameter("@AddressID", holdAddID));
insertPerson.ExecuteNonQuery();
sc.Close();
//Insert into employer table
sc.Open();
System.Data.SqlClient.SqlCommand insertEmployer = new System.Data.SqlClient.SqlCommand();
insertEmployer.Connection = sc;
System.Data.SqlClient.SqlCommand getdbPersonID = new System.Data.SqlClient.SqlCommand();
getdbPersonID.Connection = sc;
getdbPersonID.CommandText = "SELECT MAX(PERSONID) from PERSON";
getdbPersonID.ExecuteNonQuery();
int holdPersonID = (Int32)getdbPersonID.ExecuteScalar();
insertEmployer.CommandText = "insert into [Employer](EmployerName,JobTitle,PersonID,isApproved,EmployerSummary) values(@EmployerName,@JobTitle,@PersonID,@isApproved,@EmployerSummary)";
insertEmployer.Parameters.Add(new SqlParameter("@EmployerName", bus.getCompany()));
insertEmployer.Parameters.Add(new SqlParameter("@JobTitle", bus.getJobTitle()));
insertEmployer.Parameters.Add(new SqlParameter("@PersonID", holdPersonID));
insertEmployer.Parameters.Add(new SqlParameter("@isApproved", bus.getApproval()));
insertEmployer.Parameters.Add(new SqlParameter("@EmployerSummary", bus.getEmpSummary()));
insertEmployer.ExecuteNonQuery();
sc.Close();
//Insert into activity table
sc.Open();
System.Data.SqlClient.SqlCommand insertAct = new System.Data.SqlClient.SqlCommand();
insertAct.Connection = sc;
insertAct.CommandText = "insert into [Account](PersonID, Username,PasswordHash,PasswordSalt,ModifiedDate) values(@PersonID, @Username,@PasswordHash,@PasswordSalt,@ModifiedDate)";
insertAct.Parameters.Add(new SqlParameter("@PersonID", holdPersonID));
insertAct.Parameters.Add(new SqlParameter("@Username", bus.getEmail()));
insertAct.Parameters.Add(new SqlParameter("@PasswordHash", PasswordHash.HashPassword(bus.getPassword())));
insertAct.Parameters.Add(new SqlParameter("@PasswordSalt", "Salt"));
insertAct.Parameters.Add(new SqlParameter("@ModifiedDate", DateTime.Now));
insertAct.ExecuteNonQuery();
//Make a success alert appear when the account is created successfully
ScriptManager.RegisterStartupScript(this, this.GetType(), "ShowAlert", "ShowSuccessAlert();", true);
//sql.Close();
sc.Close();
}
}
//Can't have same registered email create two accounts
protected Boolean checkEmail(BusinessEmp bus)
{
//Checking to see if email in created object is equivalent to an email already in the DB
//If so, don't add object to DB, and make message pop-up saying that specific email is already taken
sc.Open();
System.Data.SqlClient.SqlCommand countCommand = new System.Data.SqlClient.SqlCommand();
countCommand.Connection = sc;
countCommand.CommandText = "SELECT COUNT(Email) from PERSON";
countCommand.ExecuteNonQuery();
//Variable that is the amount of rows inserted in the email column
int countEmails = (Int32)countCommand.ExecuteScalar();
System.Data.SqlClient.SqlCommand selectEmails = new System.Data.SqlClient.SqlCommand();
selectEmails.Connection = sc;
selectEmails.CommandText = "SELECT Email from PERSON";
selectEmails.ExecuteNonQuery();
SqlDataReader reader = selectEmails.ExecuteReader();
String[] emailAddresses = new String[countEmails];
//Populate String array with emailAddresses
for (int i = 0; i < emailAddresses.Length; i++)
{
while (reader.Read())
{
emailAddresses[i] = reader.GetValue(0).ToString();
}
}
reader.Close();
sc.Close();
int counter;
counter = 0;
//Loop through all email addresses and check if they're equal to one another
for (int i = 0; i < countEmails; i++)
{
if (EmailAdd.Value.Equals(emailAddresses[i]))
{
//EmailTaken.Text = "Email already registered, please use a different address.";
counter++;
}
else
{
//EmailTaken.Text = "";
//return true;
}
}
if (counter > 0)
{
return(false);
}
else
{
return(true);
}
}
//Insert into the Database
protected void insert_Click(object sender, EventArgs e)
{
//Create new Employer object
BusinessEmp bus = new BusinessEmp(FirstName.Value.ToString(), LastName.Value.ToString(), CompanyName.Value.ToString(), JobTitle.Value.ToString(), Summary.Value.ToString(), EmailAdd.Value.ToString(), Password1.Value.ToString(),
PhoneNumber.Value.ToString(), CompHouseNumber.Value.ToString(), CompStreet.Value.ToString(), City.Value.ToString(),
CompCountry.Value.ToString(), State.Value.ToString(), CompZip.Value.ToString());
//Doesn't add to the DB if the email address is taken
checkEmail(bus);
if (checkEmail(bus) == false)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "ShowAlert", "ShowDangerAlert();", true);
return;
}
else
{
EmailTaken.Visible = false;
}
checkPassword(bus);
if (checkPassword(bus) == false)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "ShowPassAlert", "ShowDangerPassAlert();", true);
return;
}
else
{
PassDontMatch.Visible = false;
}
if (EmailTaken.Visible == false || PassDontMatch.Visible == false)
{
//Insert values into database when user clicks "Insert"
//Insert into address table
sc.Open();
System.Data.SqlClient.SqlCommand insertAddress = new System.Data.SqlClient.SqlCommand();
insertAddress.Connection = sc;
insertAddress.CommandText = "insert into[Address](HouseNumber, Street, City, State, Country, ZipCode) " +
"values(@HouseNumber,@CompStreet,@City,@CompState,@Country,@ZipCode)";
insertAddress.Parameters.Add(new SqlParameter("@HouseNumber", bus.getCompHouseNumber()));
insertAddress.Parameters.Add(new SqlParameter("@CompStreet", bus.getCompStreet()));
insertAddress.Parameters.Add(new SqlParameter("@City", bus.getCity()));
insertAddress.Parameters.Add(new SqlParameter("@CompState", bus.getState()));
insertAddress.Parameters.Add(new SqlParameter("@Country", bus.getCountry()));
insertAddress.Parameters.Add(new SqlParameter("@ZipCode", bus.getZipCode()));
insertAddress.ExecuteNonQuery();
sc.Close();
//Insert intp Employer table
//Insert into employer table
sc.Open();
System.Data.SqlClient.SqlCommand insertEmployer = new System.Data.SqlClient.SqlCommand();
insertEmployer.Connection = sc;
SqlCommand selectCompany = new SqlCommand();
selectCompany.Connection = sc;
selectCompany.CommandText = "SELECT EmployerName from Employer where EmployerName = " + "'" + bus.getCompany() + "'";
selectCompany.ExecuteNonQuery();
SqlDataReader companyReader = selectCompany.ExecuteReader();
for (int i = 0; i < 1; i++)
{
if (companyReader.HasRows)
{
break;
}
else
{
companyReader.Close();
insertEmployer.CommandText = "insert into [Employer](EmployerName,isApproved) values(@EmployerName,@isApproved)";
insertEmployer.Parameters.Add(new SqlParameter("@EmployerName", bus.getCompany()));
insertEmployer.Parameters.Add(new SqlParameter("@isApproved", bus.getApproval()));
insertEmployer.ExecuteNonQuery();
}
}
companyReader.Close();
sc.Close();
//Insert into Person table
sc.Open();
System.Data.SqlClient.SqlCommand insertPerson = new System.Data.SqlClient.SqlCommand();
insertPerson.Connection = sc;
System.Data.SqlClient.SqlCommand getdbAddressID = new System.Data.SqlClient.SqlCommand();
getdbAddressID.Connection = sc;
getdbAddressID.CommandText = "SELECT Max(AddressID) from ADDRESS";
getdbAddressID.ExecuteNonQuery();
int holdAddID = (Int32)getdbAddressID.ExecuteScalar();
SqlCommand EmpIDforPerson = new SqlCommand();
EmpIDforPerson.Connection = sc;
EmpIDforPerson.CommandText = "Select EmployerID from Employer where Employername = " + "'" + bus.getCompany() + "'";
EmpIDforPerson.ExecuteNonQuery();
int holdEmpID = (Int32)EmpIDforPerson.ExecuteScalar();
int length = ProfilePic.PostedFile.ContentLength;
byte[] pic = new byte[length];
ProfilePic.PostedFile.InputStream.Read(pic, 0, length);
Session["pic"] = ProfilePic.PostedFile.InputStream.Read(pic, 0, length);
insertPerson.CommandText = "insert into [Person](FirstName,LastName,Email,personType,AddressID,PhoneNumber,JobTitle,ProfilePhoto,PersonalSummary,EmployerID)" +
" values(@FirstName,@LastName,@Email,@PersonType,@AddressID,@PhoneNumber,@JobTitle,@ProfilePhoto,@PersonalSummary,@EmployerID)";
insertPerson.Parameters.Add(new SqlParameter("@FirstName", bus.getFirstName()));
insertPerson.Parameters.Add(new SqlParameter("@LastName", bus.getLastName()));
insertPerson.Parameters.Add(new SqlParameter("@Email", bus.getEmail()));
insertPerson.Parameters.Add(new SqlParameter("@PhoneNumber", bus.getPhone()));
insertPerson.Parameters.Add(new SqlParameter("@JobTitle", bus.getJobTitle()));
insertPerson.Parameters.Add(new SqlParameter("ProfilePhoto", pic));
insertPerson.Parameters.Add(new SqlParameter("@PersonalSummary", bus.getEmpSummary()));
insertPerson.Parameters.Add(new SqlParameter("@PersonType", "Employer"));
insertPerson.Parameters.Add(new SqlParameter("@AddressID", holdAddID));
insertPerson.Parameters.Add(new SqlParameter("@EmployerID", holdEmpID));
insertPerson.ExecuteNonQuery();
sc.Close();
//Insert into account table
sc.Open();
System.Data.SqlClient.SqlCommand insertAct = new System.Data.SqlClient.SqlCommand();
insertAct.Connection = sc;
System.Data.SqlClient.SqlCommand getdbPersonID = new System.Data.SqlClient.SqlCommand();
getdbPersonID.Connection = sc;
getdbPersonID.CommandText = "SELECT MAX(PERSONID) from PERSON";
getdbPersonID.ExecuteNonQuery();
Int32 holdPersonID = (Int32)getdbPersonID.ExecuteScalar();
insertAct.CommandText = "insert into [Account](Username,PersonID,PasswordHash,PasswordSalt,ModifiedDate) values(@Username,@PersonID, @PasswordHash,@PasswordSalt,@ModifiedDate)";
insertAct.Parameters.Add(new SqlParameter("@Username", bus.getEmail()));
insertAct.Parameters.Add(new SqlParameter("@PasswordHash", PasswordHash.HashPassword(bus.getPassword())));
insertAct.Parameters.Add(new SqlParameter("@PasswordSalt", "Salt"));
insertAct.Parameters.Add(new SqlParameter("@ModifiedDate", DateTime.Now));
insertAct.Parameters.Add(new SqlParameter("@PersonID", holdPersonID));
insertAct.ExecuteNonQuery();
//Make a success alert appear when the account is created successfully
ScriptManager.RegisterStartupScript(this, this.GetType(), "ShowAlert", "ShowSuccessAlert();", true);
//sql.Close();
sc.Close();
clearSubmit();
}
}
