public BankAccountCreateResponse CreateBankAccount(BankAccountCreateRequest bankAccountCreateRequest)
{
BankAccountCreateResponse bankAccountCreateResponse = new BankAccountCreateResponse();
BankAccount bankAccount = new BankAccount();
bankAccount.CustomerRef = bankAccountCreateRequest.CustomerName;
_bankRepository.Add(bankAccount);
return bankAccountCreateResponse;
}
public void Add(BankAccount bankAccount)
{
string insertSql = "INSERT INTO BankAccounts " +
"(BankAccountID, Balance, CustomerRef) VALUES " +
"(@BankAccountID, @Balance, @CustomerRef)";
using (SqlConnection connection =
new SqlConnection(_connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = insertSql;
SetCommandParametersForInsertUpdateTo(bankAccount, command);
connection.Open();
command.ExecuteNonQuery();
}
UpdateTransactionsFor(bankAccount);
}
public void Save(BankAccount bankAccount)
{
string bankAccoutnUpdateSql = "UPDATE BankAccounts " +
"SET Balance = @Balance, CustomerRef= @CustomerRef " +
"WHERE BankAccountID = @BankAccountID;";
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = bankAccoutnUpdateSql;
SetCommandParametersForInsertUpdateTo(bankAccount, command);
connection.Open();
command.ExecuteNonQuery();
}
UpdateTransactionsFor(bankAccount);
}
private void UpdateTransactionsFor(BankAccount bankAccount)
{
string deleteTransactionSQl = "DELETE Transactions WHERE BankAccountId = @BankAccountId;";
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = deleteTransactionSQl;
command.Parameters.Add(
new SqlParameter("@BankAccountID", bankAccount.AccountNo));
connection.Open();
command.ExecuteNonQuery();
}
string insertTransactionSql = "INSERT INTO Transactions " +
"(BankAccountID, Deposit, Withdraw, Reference, [Date]) VALUES " +
"(@BankAccountID, @Deposit, @Withdraw, @Reference, @Date)";
foreach (Transaction tran in bankAccount.GetTransactions())
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = insertTransactionSql;
command.Parameters.Add(
new SqlParameter("@BankAccountID",
bankAccount.AccountNo));
command.Parameters.Add(new SqlParameter("@Deposit", tran.Deposit));
command.Parameters.Add(
new SqlParameter("@Withdraw", tran.Withdrawal));
command.Parameters.Add(
new SqlParameter("@Reference", tran.Reference));
command.Parameters.Add(new SqlParameter("@Date", tran.Date));
connection.Open();
command.ExecuteNonQuery();
}
}
}
private IList<BankAccount> CreateListOfAccountsFrom(IDataReader datareader)
{
IList<BankAccount> accounts = new List<BankAccount>();
BankAccount bankAccount;
string id = "";
IList<Transaction> transactions = new List<Transaction>();
while (datareader.Read())
{
if (id != datareader["BankAccountId"].ToString())
{
id = datareader["BankAccountId"].ToString();
transactions = new List<Transaction>();
bankAccount = new BankAccount(new Guid(id), Decimal.Parse(datareader["Balance"].ToString()),
transactions, datareader["CustomerRef"].ToString());
accounts.Add(bankAccount);
}
transactions.Add(CreateTransactionFrom(datareader));
}
return accounts;
}
private static void SetCommandParametersForInsertUpdateTo(BankAccount bankAccount, SqlCommand command)
{
command.Parameters.Add(new SqlParameter("@BankAccountID", bankAccount.AccountNo));
command.Parameters.Add(new SqlParameter("@Balance", bankAccount.Balance));
command.Parameters.Add(new SqlParameter("@CustomerRef", bankAccount.CustomerRef));
}