コード例 #1
0
    //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();
        }
    }
コード例 #2
0
    //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();
        }
    }