public static Applicant GetApplication(string primaryKey) { //establish new connection by //accessing connection string in LoansDB SqlConnection c = LoansDB.GetConnection(); //Build SQL statement string selectStatement = "SELECT CustID, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip " + "FROM LoanApplicant " + "WHERE CustID = @CustID "; //Put statment and connection string in to SQLCommand obj SqlCommand selectCommand = new SqlCommand(selectStatement, c); //replaces parameter in statement with passed PK look-up value selectCommand.Parameters.AddWithValue("@CustID", primaryKey); try { //Open connection and instantiate SQL reader //Describe query results as SingleRow c.Open(); SqlDataReader applicationReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); //.Read() advances reader to the next record. if (applicationReader.Read()) { //instantiate new app obj and fill properties from DB Applicant application = new Applicant(); application.CustID = applicationReader["CustID"].ToString(); application.FirstName = applicationReader["CustFirstName"].ToString(); application.LastName = applicationReader["CustLastName"].ToString(); application.Street = applicationReader["CustStreet"].ToString(); application.City = applicationReader["CustCity"].ToString(); application.State = applicationReader["CustState"].ToString(); application.Zip = Convert.ToInt32(applicationReader["CustZip"]); return(application); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { c.Close(); } }
public static bool UpdateApplicant(Applicant oldApplicant, Applicant newApplicant) { SqlConnection c = LoansDB.GetConnection(); string updateStatement = "UPDATE LoanApplicant SET " + "CustFirstName = @NewFName, " + "CustLastName = @NewLName, " + "CustStreet = @NewStreet, " + "CustCity = @NewCity, " + "CustState = @NewState, " + "CustZip = @NewZip " + "WHERE CustID = @OldCustID"; SqlCommand updateCommand = new SqlCommand(updateStatement, c); updateCommand.Parameters.AddWithValue( "@NewFName", newApplicant.FirstName); updateCommand.Parameters.AddWithValue( "@NewLName", newApplicant.LastName); updateCommand.Parameters.AddWithValue( "@NewStreet", newApplicant.Street); updateCommand.Parameters.AddWithValue( "@NewCity", newApplicant.City); updateCommand.Parameters.AddWithValue( "@NewState", newApplicant.State); updateCommand.Parameters.AddWithValue( "@NewZip", newApplicant.Zip); updateCommand.Parameters.AddWithValue( "@OldCustID", oldApplicant.CustID); try { c.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { c.Close(); } }
public static bool InsertNewJob(Job job) { { SqlConnection c = LoansDB.GetConnection(); string insertStatement = "INSERT INTO JobHistory " + "(JobID, CustID, JobTitle, StartDate, EndDate, Salary) " + "VALUES (@NewJobID, @NewCustID, @NewJobTitle, @NewStartDate, @NewEndDate, @NewSalary);"; SqlCommand insertCommand = new SqlCommand(insertStatement, c); insertCommand.Parameters.AddWithValue( "@NewJobID", job.JobID); insertCommand.Parameters.AddWithValue( "@NewCustID", job.CustID); insertCommand.Parameters.AddWithValue( "@NewJobTitle", job.Job_Title); insertCommand.Parameters.AddWithValue( "@NewStartDate", job.Start_Date); insertCommand.Parameters.AddWithValue( "@NewEndDate", job.End_Date); insertCommand.Parameters.AddWithValue( "@NewSalary", job.Salary); try { c.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { c.Close(); } } }
public static bool UpdateJob(Job oldJob, Job newJob) { SqlConnection c = LoansDB.GetConnection(); string updateStatement = "UPDATE JobHistory SET " + "JobTitle = @NewJobTitle, " + "StartDate = @NewStartDate, " + "EndDate = @NewEndDate, " + "Salary = @NewSalary " + "WHERE JobID = @oldJobID"; SqlCommand updateCommand = new SqlCommand(updateStatement, c); updateCommand.Parameters.AddWithValue( "@NewJobTitle", newJob.Job_Title); updateCommand.Parameters.AddWithValue( "@NewStartDate", newJob.Start_Date); updateCommand.Parameters.AddWithValue( "@NewEndDate", newJob.End_Date); updateCommand.Parameters.AddWithValue( "@newSalary", newJob.Salary); updateCommand.Parameters.AddWithValue( "@oldJobID", oldJob.JobID); try { c.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { c.Close(); } }
public static bool DeleteJob(Job job) { SqlConnection c = LoansDB.GetConnection(); string deleteStatement = "DELETE FROM JobHistory " + "WHERE JobID = @JobID;"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, c); deleteCommand.Parameters.AddWithValue( "@JobID", job.JobID); try { c.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { c.Close(); } }
public static DataTable GetApplicantJobHistory(string primaryKey) { SqlConnection c = LoansDB.GetConnection(); string selectStatement = "SELECT CustID, JobID, JobTitle, StartDate, EndDate, Salary " + "FROM JobHistory " + "WHERE CustID = @CustID"; SqlCommand selectCommand = new SqlCommand(selectStatement, c); selectCommand.Parameters.AddWithValue("@CustID", primaryKey); try { c.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); if (reader.HasRows) { DataTable dt = new DataTable(); dt.Load(reader); return(dt); } else { return(null); } } catch (Exception ex) { throw ex; } finally { c.Close(); } }