public async Task <AccountDetails> Evaluate(string accountName) { using (var cn = _sqlServerDatabaseConnection.New()) { await cn.OpenAsync(); using (var cmd = new SqlCommand()) { cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.usp_GetAccountByName"; cmd.Parameters.Add("@AccountName", SqlDbType.NVarChar, 100).Value = accountName; using (var dr = await cmd.ExecuteReaderAsync()) { if (!await dr.ReadAsync()) { return(null); } var result = new AccountDetails() { AccountName = (string)dr["AccountName"], OpeningCash = (int)dr["OpeningCash"], AccountNumber = (Guid)dr["AccountNumber"] }; return(result); } } } }
async Task <List <Transaction> > ITransactionsForAccountQuery.Evaluate(Guid accountNumber) { using (var cn = _sqlServerDatabaseConnection.New()) { await cn.OpenAsync(); using (var cmd = new SqlCommand()) { cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.usp_GetTransactionsForAccount"; cmd.Parameters.Add("@AccountNumber", SqlDbType.UniqueIdentifier).Value = accountNumber; using (var dr = await cmd.ExecuteReaderAsync()) { var result = new List <Transaction>(); while (await dr.ReadAsync()) { result.Add(new Transaction() { AccountNumber = accountNumber, ID = (Guid)dr["ID"], Quantity = (int)dr["Quantity"], Time = (DateTime)dr["DateAndTime"], TotalValue = (int)dr["TotalValue"], UnitPrice = (int)dr["UnitPrice"], ProductCode = (string)dr["ProductCode"], }); } return(result); } } } }
public async Task <AccountDetails> Evaluate(Guid accountNumber) { using (var cn = _sqlServerDatabaseConnection.New()) { await cn.OpenAsync(); using (var cmd = new SqlCommand()) { cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.usp_GetAccount"; cmd.Parameters.Add("@AccountNumber", SqlDbType.UniqueIdentifier).Value = accountNumber; using (var dr = await cmd.ExecuteReaderAsync()) { if (!await dr.ReadAsync()) { return(null); } var result = new AccountDetails() { AccountName = (string)dr["AccountName"], OpeningCash = (int)dr["OpeningCash"], AccountNumber = accountNumber, Portfolio = new List <Investment>() }; if (!await dr.NextResultAsync()) { throw new Exception("Call to usp_GetAccount did not return a result set contains the portfolio"); } while (await dr.ReadAsync()) { result.Portfolio.Add(new Investment() { ProductCode = (string)dr["ProductCode"], Quantity = (int)dr["CurrentQuantity"] }); } if (!await dr.NextResultAsync()) { throw new Exception("Call to usp_GetAccount did not return a result set contains the total money"); } if (await dr.ReadAsync()) { result.TotalFromTransactions = (int)dr["TotalFromTransactions"]; } return(result); } } } }
public async Task Execute(AccountDetails account) { using (var cn = _sqlServerDatabaseConnection.New()) { await cn.OpenAsync(); using (var cmd = new SqlCommand()) { cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.usp_CreateAccount"; cmd.Parameters.Add("@AccountNumber", SqlDbType.UniqueIdentifier).Value = account.AccountNumber; cmd.Parameters.Add("@AccountName", SqlDbType.NVarChar, 100).Value = account.AccountName; cmd.Parameters.Add("@OpeningCash", SqlDbType.Int).Value = account.OpeningCash; await cmd.ExecuteNonQueryAsync(); } } }
public async Task Execute(Transaction transaction) { using (var cn = _sqlServerDatabaseConnection.New()) { await cn.OpenAsync(); using (var cmd = new SqlCommand()) { cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.usp_CreateTransaction"; cmd.Parameters.Add("@AccountNumber", SqlDbType.UniqueIdentifier).Value = transaction.AccountNumber; cmd.Parameters.Add("@DateAndTime", SqlDbType.DateTime2).Value = transaction.Time; cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = transaction.ID; cmd.Parameters.Add("@ProductCode", SqlDbType.NVarChar, 100).Value = transaction.ProductCode; cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = transaction.Quantity; cmd.Parameters.Add("@TotalValue", SqlDbType.Int).Value = transaction.TotalValue; cmd.Parameters.Add("@UnitPrice", SqlDbType.Int).Value = transaction.UnitPrice; await cmd.ExecuteNonQueryAsync(); } } }