public int InsertApplicant(ApplicantDto applicant, out string message) { int ret = 0; using (SqlConnection conn = Repository.GetDBConnection()) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { ret = UserProvider.InsertApplicant(conn, trans, applicant); trans.Commit(); message = string.Format("Applicant created successfully. ApplicantId = {0}", ret); } catch { trans.Rollback(); message = "Fail to create user. Please try again."; throw; } } } return(ret); }
internal static List <ApplicantDto> GetApplicantList(SqlConnection connection, int pageSize, int pageIndex) { SqlCommand cmdApplicant = Repository.GetSQLCommand(StoredProcedure.SP_p_tbl_Applicant_retrieve_paging, connection); cmdApplicant.Parameters.Add("@xPageSize", SqlDbType.Int); cmdApplicant.Parameters[0].Value = pageSize; cmdApplicant.Parameters.Add("@xPageIndex", SqlDbType.Int); cmdApplicant.Parameters[1].Value = pageIndex; cmdApplicant.ExecuteNonQuery(); List <ApplicantDto> applicants = null; using (SqlDataReader tempDataReader = cmdApplicant.ExecuteReader()) { if (tempDataReader.HasRows) { applicants = new List <ApplicantDto>(); while (tempDataReader.Read()) { var applicant = new ApplicantDto(); applicant.ApplicantId = Convert.ToInt32(tempDataReader["ApplicantId"]); applicant.FirstName = tempDataReader["FirstName"].ToString(); applicant.LastName = tempDataReader["LastName"].ToString(); applicant.CompanyName = tempDataReader["CompanyName"].ToString(); applicant.Phone = tempDataReader["Phone"].ToString(); applicant.Email = tempDataReader["Email"].ToString(); applicant.CorrespondenceAddress = tempDataReader["CorrespondenceAddress"].ToString(); applicant.JobTitle = tempDataReader["JobTitle"].ToString(); applicant.JobSpecification = tempDataReader["JobSpecification"].ToString(); applicant.IndustryName = tempDataReader["IndustryName"].ToString(); applicant.LocationName = tempDataReader["LocationName"].ToString(); applicant.SubLocationName = tempDataReader["SubLocationName"].ToString(); applicant.SalaryRange = tempDataReader["SalaryRange"].ToString(); applicants.Add(applicant); } } } return(applicants); }
internal static int InsertApplicant(SqlConnection connection, SqlTransaction txnApplicant, ApplicantDto applicant) { SqlCommand cmdApplicant = Repository.GetSQLCommand(StoredProcedure.SP_p_tbl_Applicant_insert, connection); cmdApplicant.Transaction = txnApplicant; cmdApplicant.Parameters.Add("@xFirstName", SqlDbType.NVarChar); cmdApplicant.Parameters[0].Value = applicant.FirstName; cmdApplicant.Parameters.Add("@xLastName", SqlDbType.NVarChar); cmdApplicant.Parameters[1].Value = applicant.LastName; cmdApplicant.Parameters.Add("@xCompanyName", SqlDbType.NVarChar); cmdApplicant.Parameters[2].Value = applicant.CompanyName; cmdApplicant.Parameters.Add("@xPhone", SqlDbType.NVarChar); cmdApplicant.Parameters[3].Value = applicant.Phone; cmdApplicant.Parameters.Add("@xEmail", SqlDbType.NVarChar); cmdApplicant.Parameters[4].Value = applicant.Email; cmdApplicant.Parameters.Add("@xCorrespondenceAddress", SqlDbType.NVarChar); cmdApplicant.Parameters[5].Value = applicant.CorrespondenceAddress; cmdApplicant.Parameters.Add("@xJobTitle", SqlDbType.NVarChar); cmdApplicant.Parameters[6].Value = applicant.JobTitle; cmdApplicant.Parameters.Add("@xJobSpecification", SqlDbType.NVarChar); cmdApplicant.Parameters[7].Value = applicant.JobSpecification; cmdApplicant.Parameters.Add("@xLocationId", SqlDbType.Int); cmdApplicant.Parameters[8].Value = applicant.LocationId; cmdApplicant.Parameters.Add("@xSubLocationId", SqlDbType.Int); cmdApplicant.Parameters[9].Value = applicant.SubLocationId; cmdApplicant.Parameters.Add("@xIndustryId", SqlDbType.Int); cmdApplicant.Parameters[10].Value = applicant.IndustryId; cmdApplicant.Parameters.Add("@xSalaryRangeId", SqlDbType.Int); cmdApplicant.Parameters[11].Value = applicant.SalaryRangeId; cmdApplicant.Parameters.Add("@ReturnValue", SqlDbType.Int); cmdApplicant.Parameters["@ReturnValue"].Direction = ParameterDirection.Output; cmdApplicant.ExecuteNonQuery(); // In stored proc standards, zero equals success int retVal = 0; retVal = (int)cmdApplicant.Parameters["@ReturnValue"].Value; cmdApplicant.Dispose(); if (retVal < 0) { // throw exception throw new DataException(String.Format("Fail to insert new applicant \"{0}\".", applicant.FirstName + " " + applicant.LastName)); } return(retVal); }