public List <Transaction> GetTransactionsForAccount(int accNo, int pageSize,
int page)
{
List <Transaction> history = new List <Transaction>();
int offset = (page - 1) * pageSize;
using (var conn = GetConnection())
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = $@"select * from {TableName}
where AccountNumber = @AccNo
order by TransactionTimeUtc desc
offset @Offset rows
fetch next @PageSize rows only";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
{ "AccNo", accNo },
{ "Offset", offset },
{ "PageSize", pageSize }
});
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Transaction t = GetTransactionFromReader(reader);
history.Add(t);
}
reader.Close();
}
return(history);
}
public List <int> GetAccountNumbersForCustomer(int custId)
{
List <int> accNos = new List <int>();
using (var conn = GetConnection())
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = $@"select AccountNumber
from {TableName} where CustomerID = @CustId";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object> {
{ "CustId", custId }
});
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
accNos.Add((int)reader["AccountNumber"]);
}
reader.Close();
}
return(accNos);
}
public void AddCustomer(int id, string name, string address,
string city, string postcode)
{
using (var conn = GetConnection())
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = $@"insert into {TableName}
(CustomerID, Name, Address, City, PostCode)
values (@Id, @Name, @Address, @City, @Postcode)";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
["Id"] = id,
["Name"] = name,
["Address"] = (object)address ?? DBNull.Value,
["City"] = (object)city ?? DBNull.Value,
["Postcode"] = (object)postcode ?? DBNull.Value
});
command.ExecuteNonQuery();
}
}
private void WithDraw(int accNo, decimal amount, SqlCommand command)
{
command.CommandText = $@"update {TableName}
set Balance -= @Amount
where AccountNumber = @AccNo";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
["Amount"] = amount,
["AccNo"] = accNo
});
command.ExecuteNonQuery();
}
public void AddAccount(int custId, int accNo, char type, decimal balance)
{
using (var conn = GetConnection())
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = $@"insert into {TableName}
(AccountNumber, AccountType, CustomerID, Balance)
values (@AccNo, @Type, @CustId, @Balance)";
DBUtil.AddSqlParam(command.Parameters, new Dictionary <string, object>
{
["AccNo"] = accNo, ["Type"] = type,
["Balance"] = balance, ["CustId"] = custId
});
command.ExecuteNonQuery();
}
}
public void AddTransaction(char type, int accNo, int?destAccNo,
decimal amount, string comment, DateTime time, SqlCommand command)
{
command.CommandText = $@"insert into {TableName}
(TransactionType, AccountNumber, DestinationAccountNumber, Amount, Comment, TransactionTimeUtc)
values (@Type, @AccNo, @DestAccNo, @Amount, @Comment, @Time)";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
{ "Type", type },
{ "AccNo", accNo },
{ "DestAccNo", (object)destAccNo ?? DBNull.Value },
{ "Amount", amount },
{ "Comment", (object)comment ?? DBNull.Value },
{ "Time", time }
});
command.ExecuteNonQuery();
}
public void AddLogin(int custId, string loginId, string pwdHash)
{
using (var conn = GetConnection())
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = $@"insert into {TableName}
(LoginID, CustomerID, PasswordHash)
values (@LoginId, @CustId, @PwdHash)";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
{ "LoginId", loginId },
{ "CustId", custId },
{ "PwdHash", pwdHash }
});
command.ExecuteNonQuery();
}
}
private void Transfer(int srcNo, int destNo, decimal amount,
SqlCommand command)
{
command.CommandText = $@"update {TableName}
set Balance -= @Amount where AccountNumber = @SrcNo";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
["Amount"] = amount,
["SrcNo"] = srcNo
});
command.ExecuteNonQuery();
command.CommandText = $@"update {TableName}
set Balance += @Amount where AccountNumber = @DestNo";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
["DestNo"] = destNo
});
command.ExecuteNonQuery();
}
public Account GetAccountByAccountNumber(int accNo)
{
using (var conn = GetConnection())
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = $@"select * from {TableName}
where AccountNumber = @AccNo";
DBUtil.AddSqlParam(command.Parameters, new Dictionary <string, object>
{
["AccNo"] = accNo
});
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
return(GetAccountFromReader(reader));
}
reader.Close();
}
return(null);
}
public Customer GetCustomerByCustomerID(int id)
{
using (var conn = GetConnection())
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = $@"select * from {TableName}
where CustomerID = @Id";
DBUtil.AddSqlParam(command.Parameters,
new Dictionary <string, object>
{
["Id"] = id
});
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
return(GetCustomerFromReader(reader));
}
reader.Close();
}
return(null);
}
