private Task <string> RunTaskInTransaction(Func <Task <string> > action) { return(Task.Run(async() => { _sqliteConnection.BeginTransaction(); try { var result = await Task.Run(action); if (!string.IsNullOrEmpty(result)) { _sqliteConnection.Rollback(); } else { _sqliteConnection.Commit(); } return result; } catch (Exception ex) { _sqliteConnection.Rollback(); throw; } })); }
public void RemoveMediaItemAtIndex(int index) { if (PlaylistId == 0) { return; } ISQLiteConnection conn = null; try { conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection(); conn.BeginTransaction(); conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index); conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition > ?", PlaylistId, index); conn.Commit(); } catch (Exception e) { if (!ReferenceEquals(conn, null)) { conn.Rollback(); } logger.Error(e); } finally { Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn); } }
public void MoveMediaItem(int fromIndex, int toIndex) { // make sure the input is within bounds and is not null if (fromIndex >= PlaylistCount || fromIndex < 0 || toIndex < 0 || toIndex == fromIndex) { return; } logger.IfInfo("Moving media item"); ISQLiteConnection conn = null; try { // to do - better way of knowing whether or not a query has been successfully completed. conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection(); conn.BeginTransaction(); // Get the item out of the way to prevent constraint violations conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = " + Int32.MaxValue + " WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, fromIndex); if (fromIndex > toIndex) { // Do this as a reversed loop because a single update statement can have a constraint violation for (int position = fromIndex - 1; position >= toIndex; position--) { logger.IfInfo("Updating position " + position + " to " + (position + 1)); conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position); } // conditional rollback here } else { // Do this as a reversed loop because a single update statement can have a constraint violation for (int position = fromIndex + 1; position <= toIndex; position++) { logger.IfInfo("Updating position " + position + " to " + (position + 1)); conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position); } // conditional rollback here } conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistId = ? AND ItemPosition = ?", toIndex, PlaylistId, Int32.MaxValue); // conditional rollback here conn.Commit(); } catch (Exception e) { if (!ReferenceEquals(conn, null)) { conn.Rollback(); } logger.Error(e); } finally { Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn); } }
public void RemoveMediaItemAtIndexes(IList <int> indices) { ISQLiteConnection conn = null; try { conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection(); conn.BeginTransaction(); // delete the items at the indicated indices foreach (int index in indices) { logger.IfInfo("Deleting row at ItemPosition: " + index); conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index); } // select the id of all members of the playlist var result = conn.Query <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? ORDER BY ItemPosition", PlaylistId); // update the values of each index in the array to be the new index for (int i = 0; i < result.Count; i++) { var item = result[i]; conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistItemId = ? AND PlaylistId = ?", i, item.PlaylistItemId, PlaylistId); } conn.Commit(); } catch (Exception e) { if (!ReferenceEquals(conn, null)) { conn.Rollback(); } logger.Error(e); } finally { Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn); } }
public void InsertMediaItem(IMediaItem item, int index) { // make sure the input is within bounds and is not null if (ReferenceEquals(item, null) || index > PlaylistCount || index < 0 || ReferenceEquals(PlaylistId, null)) { return; } ISQLiteConnection conn = null; try { int?id = Injection.Kernel.Get <IItemRepository>().GenerateItemId(ItemType.PlaylistItem); if (!ReferenceEquals(id, null)) { // to do - better way of knowing whether or not a query has been successfully completed. conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection(); conn.BeginTransaction(); for (int position = (int)PlaylistCount - 1; position >= index; position--) { logger.IfInfo("Updating position " + position + " to " + (position + 1)); conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position); } // conditional rollback here // Insert the new item var playlistItem = new PlaylistItem(); playlistItem.PlaylistItemId = id; playlistItem.PlaylistId = PlaylistId; playlistItem.ItemType = item.ItemType; playlistItem.ItemId = item.ItemId; playlistItem.ItemPosition = index; int affected = conn.Insert(playlistItem); // conditional rollback here if (affected > 0) { PlaylistCount++; PlaylistDuration += (int)item.Duration; LastUpdateTime = DateTime.UtcNow.ToUnixTime(); Md5Hash = CalculateHash(); conn.ExecuteLogged("UPDATE Playlist SET PlaylistName = ?, PlaylistCount = ?, PlaylistDuration = ?, Md5Hash = ?, LastUpdateTime = ? " + "WHERE PlaylistId = ?", PlaylistName == null ? "" : PlaylistName, PlaylistCount, PlaylistDuration, Md5Hash, LastUpdateTime, PlaylistId); conn.Commit(); } else { conn.Rollback(); } } } catch (Exception e) { if (!ReferenceEquals(conn, null)) { conn.Rollback(); } logger.Error(e); } finally { Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn); } }