public IList <LoanAllocation> GetLoanAllocations(int customerID)
{
IList <LoanAllocation> allocations = new List <LoanAllocation>();
SqlConnection connection = GetDBConnection();
connection.Open();
SqlCommand command = new SqlCommand("SELECT LoanAllocationID, DatePrepared, EmployeeID, CustomerID, AccountNumber, LoanTypeID, LoanAmount, InterestRate, Periods FROM LoanAllocations WHERE CustomerID=" + customerID, connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
LoanAllocation allocation = new LoanAllocation
{
LoanAllocationID = int.Parse(reader.GetValue(0).ToString()),
DatePrepared = DateTime.Parse(reader.GetValue(1).ToString()),
EmployeeID = int.Parse(reader.GetValue(2).ToString()),
CustomerID = int.Parse(reader.GetValue(3).ToString()),
AccountNumber = reader.GetValue(4).ToString(),
LoanTypeID = int.Parse(reader.GetValue(5).ToString()),
LoanAmount = double.Parse(reader.GetValue(6).ToString()),
InterestRate = double.Parse(reader.GetValue(7).ToString()),
Periods = double.Parse(reader.GetValue(8).ToString())
};
allocations.Add(allocation);
}
command.Dispose();
connection.Close();
return(allocations);
}
public bool InsertLoanAllocation(LoanAllocation loanAllocation)
{
SqlConnection connection = GetDBConnection();
SqlCommand command = GetSqlCommand(connection, "InsertLoanAllocation");
SqlParameter datePrepared = new SqlParameter("@DatePrepared", SqlDbType.DateTime2);
SetInputSqlParameter(command, datePrepared, loanAllocation.DatePrepared);
SqlParameter employeeID = new SqlParameter("@EmployeeID", SqlDbType.Int);
SetInputSqlParameter(command, employeeID, loanAllocation.EmployeeID);
SqlParameter customerID = new SqlParameter("@CustomerID", SqlDbType.Int);
SetInputSqlParameter(command, customerID, loanAllocation.CustomerID);
SqlParameter accountNumber = new SqlParameter("@AccountNumber", SqlDbType.NChar, 10);
SetInputSqlParameter(command, accountNumber, loanAllocation.AccountNumber);
SqlParameter loanTypeID = new SqlParameter("@LoanTypeID", SqlDbType.Int);
SetInputSqlParameter(command, loanTypeID, loanAllocation.LoanTypeID);
SqlParameter loanAmount = new SqlParameter("@LoanAmount", SqlDbType.Money);
SetInputSqlParameter(command, loanAmount, loanAllocation.LoanAmount);
SqlParameter interestRate = new SqlParameter("@InterestRate", SqlDbType.Decimal);
SetInputSqlParameter(command, interestRate, loanAllocation.InterestRate);
SqlParameter periods = new SqlParameter("@Periods", SqlDbType.Decimal);
SetInputSqlParameter(command, periods, loanAllocation.Periods);
SqlParameter response = new SqlParameter("@Response", SqlDbType.Bit);
SetOutputSqlParameter(command, response);
connection.Open();
command.ExecuteNonQuery();
Boolean retValue = (Boolean)command.Parameters["@Response"].Value;
command.Dispose();
connection.Close();
if (retValue)
{
return(true);
}
else
{
return(false);
}
}