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); }