public async Task <StoreResult> InsertRecord(DepartmentVentilation model)
{
StoreResult result = StoreResult.Success;
using (var txn = Db.BeginTransaction(IsolationLevel.RepeatableRead))
{
try
{
var param = new DynamicParameters(model)
.Output(model, m => m.Uid)
.Output(model, m => m.RowStamp)
.Output(model, m => m.Created);
var sql = $@"
Select @DepartmentUid = Uid from Department where EntityKey = @DepartmentKey;
INSERT INTO [dbo].[VentilationCapacity]
([EntityKey],[UnitName],[DepartmentUid],[Capacity],[Created])
VALUES
(@EntityKey, @UnitName, @DepartmentUid, @Capacity, SYSUTCDATETIME());
SELECT @Uid = [Uid], @Created = Created, @RowStamp = RowStamp FROM [dbo].[VentilationCapacity] WHERE [Uid] = SCOPE_IDENTITY(); ";
var results = await Db.ExecuteAsync(sql, param, txn);
if (results != -1)
{
txn.Commit();
}
else
{
txn.Rollback();
result = StoreResult.Failure();
}
}
catch (Exception ex)
{
txn.Rollback();
result = StoreResult.DbException(ex);
}
}
return(result);
}
public static async Task <StoreResult> DeleteAsync(this IDbConnection db, string table, string where, object param, byte[] rowStamp = null, IDbTransaction txn = null, string rowStampFieldName = "RowStamp")
{
var builder = new SqlBuilder();
// enable NOCOUNT and take care of returning the rowcount ourselves; this means
// that we know that the result of this query will be the number of rows affected;
// we can then assume that >=1 means that our deletion was successful, and 0 means
// that it wasn't (might end up with >1 if there are cascading FKs or triggers)
var template = builder.AddTemplate($@"
SET NOCOUNT ON;
DELETE FROM [{DbUtil.CleanseFieldName(table)}] /**where**/;
SET @ResultCount = @@ROWCOUNT");
// append the core where clause
builder.Where(where, param);
var dynamicParams = new DynamicParameters(param);
dynamicParams.Add("ResultCount", 0, DbType.Int32, ParameterDirection.Output);
if (rowStamp != null)
{
// if we've provided a concurrency token/rowstamp, then use it; otherwise we
// apparently don't care and just want to nuke the data from orbit
builder.Where($"[{DbUtil.CleanseFieldName(rowStampFieldName)}] = @rowStamp", rowStamp);
dynamicParams.Add("rowStamp", rowStamp);
}
try
{
await db.ExecuteAsync(template.RawSql, dynamicParams, txn);
var result = dynamicParams.Get <int>("ResultCount");
if (result == 1)
{
// woohoo!
return(StoreResult.Success);
}
else if (rowStamp != null)
{
// if we supplied a rowstamp, then returning 0 rows probably means that someone
// else changed the data, and we need to either return an error or re-run the
// txn - either way, this problem is above our pay-grade
return(StoreResult.ConcurrencyError());
}
else
{
// generic failure of genericness
return(StoreResult.Failure(StoreResultErrorCodes.NotFound));
}
}
catch (DbException ex)
{
return(StoreResult.DbException(ex));
}
catch (Exception ex)
{
return(StoreResult.UnhandledException(ex));
}
}