/// <summary>
/// Read single entity and lock
/// </summary>
/// <param name="id">Id of record</param>
/// <param name="lockBy">lock for username</param>
/// <returns></returns>
public async Task <EntityY> ReadAsync(int id, string lockBy = "")
{
using (var cmd = MsSql.NewCommand(_cn, "procReadY")) {
cmd.Parameters["@id"].Value = id;
cmd.Parameters["@lockby"].Value = lockBy;
try {
using (var rs = await cmd.ExecuteReaderAsync()) {
if (await rs.ReadAsync())
{
return(new EntityY {
Id = (int)rs[0],
Name = (string)rs[1],
Price = (decimal)rs[2],
LockedBy = GetValueOrEmpty(rs[3])
});
}
}
} catch (SqlException ex) {
if (ex.State == 11)
{
throw new ConcurrencyException(ex.Message, ex);
}
else
{
throw;
}
}
throw new Exception("Record not exists");
}
}
public async Task CreateAsync(EntityY item)
{
using (var cmd = MsSql.NewCommand(_cn, "procAddY")) {
cmd.Parameters["@name"].Value = item.Name;
cmd.Parameters["@price"].Value = item.Price;
await cmd.ExecuteNonQueryAsync();
item.Id = (int)cmd.Parameters[0].Value;
}
}
public async Task DeleteAsync(int id, string lockBy = "")
{
//Delete do not check for concurrency here,it just deletes the record.
using (var cmd = MsSql.NewCommand(_cn, "procDeleteY")) {
cmd.Parameters["@id"].Value = id;
cmd.Parameters["@lockedby"].Value = lockBy;
var result = await cmd.ExecuteNonQueryAsync();
if (result == 0)
{
throw new Exception("Record with this primary key cannot be deleted");
}
}
}
public async Task <IEnumerable <EntityY> > GetListAsync()
{
var result = new List <EntityY>();
using (var cmd = MsSql.NewCommand(_cn, "procListY")) {
using (var rs = await cmd.ExecuteReaderAsync()) {
while (await rs.ReadAsync())
{
result.Add(new EntityY {
Id = (int)rs[0],
Name = (string)rs[1],
Price = (decimal)rs[2],
LockedBy = GetValueOrEmpty(rs[3])
});
}
}
return(result);
}
}
public async Task <EntityX> ReadAsync(int id, string lockBy = "")
{
using (var cmd = MsSql.NewCommand(_cn, "procReadX")) {
cmd.Parameters["@id"].Value = id;
using (var rs = await cmd.ExecuteReaderAsync()) {
if (await rs.ReadAsync())
{
return(new EntityX {
Id = (int)rs[0],
Name = (string)rs[1],
Price = (decimal)rs[2],
RowVersion = (Guid)rs[3]
});
}
}
throw new Exception("Record not exists");
}
}
public async Task <IEnumerable <EntityX> > GetListAsync()
{
var result = new List <EntityX>();
using (var cmd = MsSql.NewCommand(_cn, "procListX")) {
using (var rs = await cmd.ExecuteReaderAsync()) {
while (await rs.ReadAsync())
{
result.Add(new EntityX {
Id = (int)rs[0],
Name = (string)rs[1],
Price = (decimal)rs[2],
RowVersion = (Guid)rs[3]
});
}
}
return(result);
}
}
public async Task UpdateAsync(EntityY item)
{
//Update record in database. Record is actually updated only if lock is set by matching user
//If not, SQL SP throws an error.
using (var cmd = MsSql.NewCommand(_cn, "procUpdateY")) {
cmd.Parameters["@id"].Value = item.Id;
cmd.Parameters["@name"].Value = item.Name;
cmd.Parameters["@price"].Value = item.Price;
cmd.Parameters["@lockedby"].Value = item.LockedBy;
try {
await cmd.ExecuteNonQueryAsync();
} catch (SqlException ex) {
if (ex.State == 10)
{
throw new ConcurrencyException(ex.Message, ex);
}
else
{
throw;
}
}
}
}