/// <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; } } } }