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