public override LoanModel.SaveLoanDetailResponse HandleRequest(LoanModel.SaveLoanDetailsRequest request) { var response = new LoanModel.SaveLoanDetailResponse(); var query = "Insert Into LoanEMITransaction " + "(LoanId, InstallmentNumber, OpeningAmount" + ",PrincipalAmount, InterestAmount" + ",EMI, ClosingAmount" + ",CumulativeInterestAmount) " + "Values " + "(@LoanId, @InstallmentNumber, @OpeningAmount" + ",@PrincipalAmount, @InterestAmount" + ",@EMI, @ClosingAmount" + ",@CumulativeInterestAmount);"; var con = new SqlConnection(); try { foreach (var emi in request.InterestData) { using (con = new SqlConnection(Constants.ConnectionString)) { con.Open(); var cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@LoanId", request.LoanId); cmd.Parameters.AddWithValue("@InstallmentNumber", emi.Installment); cmd.Parameters.AddWithValue("@OpeningAmount", emi.Opening); cmd.Parameters.AddWithValue("@PrincipalAmount", emi.Principal); cmd.Parameters.AddWithValue("@InterestAmount", emi.Interest); cmd.Parameters.AddWithValue("@EMI", emi.Emi); cmd.Parameters.AddWithValue("@ClosingAmount", emi.Closing); cmd.Parameters.AddWithValue("@CumulativeInterestAmount", emi.CumulativeInterest); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); con.Close(); } } response.IsAlreadyExists = false; } finally { if (con.State != ConnectionState.Closed) { con.Close(); } } return(response); }
public override LoanModel.SaveLoanDetailResponse HandleRequest(LoanModel.SaveLoanDetailsRequest request) { var query = "select Id from Loan where Amount = @Amount and" + " InterestRate = @InterestRate and " + "Tenure = @Tenure"; var isExists = false; var response = new LoanModel.SaveLoanDetailResponse(); var con = new SqlConnection(); try { using (con = new SqlConnection(Constants.ConnectionString)) { con.Open(); var command = new SqlCommand(query, con); command.Parameters.AddWithValue("@Amount", request.LoanAmount); command.Parameters.AddWithValue("@InterestRate", request.InterestRate); command.Parameters.AddWithValue("@Tenure", request.NumberOfYears); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { isExists = reader["Id"] != null && Convert.ToInt32(reader["Id"]) > 0; } } } if (isExists) { response.IsAlreadyExists = true; return(response); } else { return(Successor?.HandleRequest(request)); } } finally { con.Close(); } }