private async Task <DalResult> ExecuteCommand(string sql)
{
using var conn = new SqlConnection(ConnectionString);
try
{
await conn.OpenAsync();
await conn.ExecuteAsync(sql);
return(DalResult.Success);
}
catch (Exception ex)
{
return(DalResult.FromException(ex));
}
finally
{
await conn.CloseAsync();
}
}
public virtual async Task <DalResult> DeleteAsync(int id)
{
var sql = $"delete from {_tableName} where Id={id};";
using var conn = new SqlConnection(ConnectionString);
try
{
await conn.OpenAsync();
await conn.ExecuteAsync(sql);
return(DalResult.Success);
}
catch (Exception ex)
{
return(DalResult.FromException(ex));
}
finally
{
await conn.CloseAsync();
}
}
public async Task <DalResult> InsertAsync(MovementEntity movement, BeanEntity bean)
{
if (movement is null)
{
throw new ArgumentNullException(nameof(movement));
}
if (bean is null)
{
throw new ArgumentNullException(nameof(bean));
}
using var conn = new SqlConnection(ConnectionString);
await conn.OpenAsync();
using var transaction = await conn.BeginTransactionAsync();
try
{
var result = await conn.InsertAsync(movement, transaction : transaction);
movement.Id = result;
var beanresult = await conn.UpdateAsync(bean, transaction : transaction);
await transaction.CommitAsync();
return(beanresult ? DalResult.Success : DalResult.NotFound);
}
catch (Exception ex)
{
await transaction.RollbackAsync();
return(DalResult.FromException(ex));
}
finally
{
await conn.CloseAsync();
}
}
public async Task <DalResult> BuyFromExchangeAsync(int userid, int beanid, long quantity)
{
if (userid <= 0)
{
return(new(DalErrorCode.Invalid, new("User id is invalid")));
}
if (beanid <= 0)
{
return(new(DalErrorCode.Invalid, new("Bean id is invalid")));
}
using var conn = new SqlConnection(ConnectionString);
await conn.OpenAsync();
using var transaction = await conn.BeginTransactionAsync();
try
{
var sql = $"select * from Users where Id={userid};";
var user = await conn.QueryFirstOrDefaultAsync <UserEntity>(sql, transaction : transaction);
if (user is null)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NotFound, new($"No user with the id '{userid}' was found")));
}
sql = $"select * from Beans where Id={beanid};";
var bean = await conn.QueryFirstOrDefaultAsync <BeanEntity>(sql, transaction : transaction);
if (bean is null)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NotFound, new($"No bean with the id '{beanid}' was found")));
}
if (quantity > bean.Outstanding)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NSF, new("Insufficient outstanding beans for that quantity")));
}
if (quantity * bean.Price > user.Balance)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NSF, new("User has insufficient funds to buy that many beans")));
}
user.Balance -= quantity * bean.Price;
bean.Outstanding -= quantity;
var holding = new HoldingEntity
{
Id = 0,
UserId = userid,
BeanId = beanid,
PurchaseDate = DateTime.UtcNow,
Price = bean.Price,
Quantity = quantity,
Bean = null
};
await conn.InsertAsync(holding, transaction : transaction);
await conn.UpdateAsync(user, transaction : transaction);
await conn.UpdateAsync(bean, transaction : transaction);
await transaction.CommitAsync();
return(DalResult.Success);
}
catch (Exception ex)
{
await transaction.RollbackAsync();
return(DalResult.FromException(ex));
}
finally
{
await conn.CloseAsync();
}
}
public async Task <DalResult> SellToExchangeAsync(int holdingid, long quantity)
{
if (holdingid <= 0)
{
return(new(DalErrorCode.Invalid, new("Holding id is invalid")));
}
if (quantity <= 0)
{
return(new(DalErrorCode.Invalid, new("Quantity is invalid")));
}
using var conn = new SqlConnection(ConnectionString);
await conn.OpenAsync();
using var transaction = await conn.BeginTransactionAsync();
try
{
var sql = $"select * from holdings where id={holdingid};";
var holding = await conn.QueryFirstOrDefaultAsync <HoldingEntity>(sql, transaction : transaction);
if (holding is null)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NotFound, new($"No holding with the id '{holdingid}' was found")));
}
if (holding.Quantity < quantity)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NSF, new("Insufficient beans in that holding")));
}
sql = $"select * from Users where Id={holding.UserId};";
var user = await conn.QueryFirstOrDefaultAsync <UserEntity>(sql, transaction : transaction);
if (user is null)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NotFound, new($"No user with the email id '{holding.UserId}' was found")));
}
sql = $"select * from Beans where Id={holding.BeanId};";
var bean = await conn.QueryFirstOrDefaultAsync <BeanEntity>(sql, transaction : transaction);
if (bean is null)
{
await transaction.RollbackAsync();
return(new(DalErrorCode.NotFound, new($"No bean with the id '{holding.BeanId}' was found")));
}
var sale = new SaleEntity
{
Id = 0,
UserId = user.Id,
BeanId = bean.Id,
Quantity = quantity,
PurchaseDate = holding.PurchaseDate,
CostBasis = holding.Price,
SaleDate = DateTime.UtcNow,
SalePrice = bean.Price,
Bean = null,
};
bean.Outstanding += quantity;
if (quantity == holding.Quantity)
{
sql = $"delete from holdings where Id={holdingid};";
await conn.ExecuteAsync(sql, transaction : transaction);
}
else
{
holding.Quantity -= quantity;
await conn.UpdateAsync(holding, transaction : transaction);
}
user.Balance += quantity * bean.Price;
await conn.InsertAsync(sale, transaction : transaction);
await conn.UpdateAsync(user, transaction : transaction);
await conn.UpdateAsync(bean, transaction : transaction);
await transaction.CommitAsync();
return(DalResult.Success);
}
catch (Exception ex)
{
await transaction.RollbackAsync();
return(DalResult.FromException(ex));
}
finally
{
await conn.CloseAsync();
}
}
