| public Prepare ( string sql ) : ISQLiteStatement | ||
| sql | string | |
| return | ISQLiteStatement | |
private void DeleteSublistEntryInternal(ISublistEntry entry, SQLiteTransaction transaction)
{
const string deleteMainSql = @"DELETE FROM Entry WHERE Id = @id";
const string deleteRelationSql = @"DELETE FROM EntryRelation WHERE ChildId = @id OR ParentId = @id";
using (var statement = transaction.Prepare(deleteRelationSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
using (var statement = transaction.Prepare(deleteMainSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
foreach (var sublistEntry in entry.SubEntries)
{
using (var statement = transaction.Prepare(deleteRelationSql))
{
statement.Binding("@id", sublistEntry.Id);
transaction.Execute(statement);
}
using (var statement = transaction.Prepare(deleteMainSql))
{
statement.Binding("@id", sublistEntry.Id);
transaction.Execute(statement);
}
DeleteSublistEntryInternal(sublistEntry, transaction);
}
}
public void UpdateSublistEntry(ISublistEntry entry)
{
const string updateMainSql = @"UPDATE Entry " +
"SET Title = @title, Completed = @completed " +
"WHERE Id = @id";
const string updateRelationsSql = @"INSERT OR REPLACE INTO EntryRelation " +
"(ParentId, ChildId) " +
"VALUES (@parentId, @id)";
const string deleteRelationsSql = @"DELETE FROM EntryRelation " +
"WHERE ChildId = @id";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(updateMainSql))
{
statement.Binding("@title", entry.Title);
statement.Binding("@completed", entry.Completed);
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
if (entry.ParentId.HasValue && entry.ParentId.Value == 0)
{
using (var statement = transaction.Prepare(deleteRelationsSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
}
else if (entry.ParentId.HasValue && entry.ParentId.Value != 0)
{
using (var statement = transaction.Prepare(updateRelationsSql))
{
statement.Binding("@parentId", entry.ParentId);
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
}
transaction.Commit();
}
}
public long AddSublistEntry(ISublistEntry entry)
{
const string mainSql = @"INSERT INTO Entry " +
"(Title, Completed, CreatedAtUtc) " +
"VALUES " +
"(@title, @completed, @createdAtUtc)";
const string relationSql = @"INSERT INTO EntryRelation " +
"(ParentId, ChildId) " +
"VALUES " +
"(@parentId, @childId)";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(mainSql))
{
statement.Binding("@title", entry.Title);
statement.Binding("@completed", entry.Completed);
statement.Binding("@createdAtUtc", entry.CreatedAtUtc);
transaction.Execute(statement);
}
entry.Id = transaction.LastInsertRowId();
if (entry.ParentId.HasValue)
{
using (var statement = transaction.Prepare(relationSql))
{
statement.Binding("@parentId", entry.ParentId.Value);
statement.Binding("@childId", entry.Id);
transaction.Execute(statement);
}
}
transaction.Commit();
}
return(entry.Id);
}
public long AddSublistEntry(ISublistEntry entry)
{
const string mainSql = @"INSERT INTO Entry " +
"(Title, Completed, CreatedAtUtc) " +
"VALUES " +
"(@title, @completed, @createdAtUtc)";
const string relationSql = @"INSERT INTO EntryRelation " +
"(ParentId, ChildId) " +
"VALUES " +
"(@parentId, @childId)";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(mainSql))
{
statement.Binding("@title", entry.Title);
statement.Binding("@completed", entry.Completed);
statement.Binding("@createdAtUtc", entry.CreatedAtUtc);
transaction.Execute(statement);
}
entry.Id = transaction.LastInsertRowId();
if (entry.ParentId.HasValue)
{
using (var statement = transaction.Prepare(relationSql))
{
statement.Binding("@parentId", entry.ParentId.Value);
statement.Binding("@childId", entry.Id);
transaction.Execute(statement);
}
}
transaction.Commit();
}
return entry.Id;
}
public void UpdateAppData(IAppData appData)
{
const string mainSql = @"UPDATE AppData SET ShowCompleted = @showCompleted WHERE Id = 1";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(mainSql))
{
statement.Binding("@showCompleted", appData.ShowCompleted);
transaction.Execute(statement);
}
transaction.Commit();
}
}
private void DeleteSublistEntryInternal(ISublistEntry entry, SQLiteTransaction transaction)
{
const string deleteMainSql = @"DELETE FROM Entry WHERE Id = @id";
const string deleteRelationSql = @"DELETE FROM EntryRelation WHERE ChildId = @id OR ParentId = @id";
using (var statement = transaction.Prepare(deleteRelationSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
using (var statement = transaction.Prepare(deleteMainSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
foreach (var sublistEntry in entry.SubEntries)
{
using (var statement = transaction.Prepare(deleteRelationSql))
{
statement.Binding("@id", sublistEntry.Id);
transaction.Execute(statement);
}
using (var statement = transaction.Prepare(deleteMainSql))
{
statement.Binding("@id", sublistEntry.Id);
transaction.Execute(statement);
}
DeleteSublistEntryInternal(sublistEntry, transaction);
}
}
public void UpdateSublistEntry(ISublistEntry entry)
{
const string updateMainSql = @"UPDATE Entry " +
"SET Title = @title, Completed = @completed " +
"WHERE Id = @id";
const string updateRelationsSql = @"INSERT OR REPLACE INTO EntryRelation " +
"(ParentId, ChildId) " +
"VALUES (@parentId, @id)";
const string deleteRelationsSql = @"DELETE FROM EntryRelation " +
"WHERE ChildId = @id";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(updateMainSql))
{
statement.Binding("@title", entry.Title);
statement.Binding("@completed", entry.Completed);
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
if (entry.ParentId.HasValue && entry.ParentId.Value == 0)
{
using (var statement = transaction.Prepare(deleteRelationsSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
}
else if (entry.ParentId.HasValue && entry.ParentId.Value != 0)
{
using (var statement = transaction.Prepare(updateRelationsSql))
{
statement.Binding("@parentId", entry.ParentId);
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
}
transaction.Commit();
}
}
public void UpdateAppData(IAppData appData)
{
const string mainSql = @"UPDATE AppData SET ShowCompleted = @showCompleted WHERE Id = 1";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(mainSql))
{
statement.Binding("@showCompleted", appData.ShowCompleted);
transaction.Execute(statement);
}
transaction.Commit();
}
}