private async Task InsertOrUpdateUserSetting(string userSettingName, string paramValue)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
var getCmd = new SQLiteCommand(SQLQueries.GetUserSetting(userSettingName), conn);
var response = (string)(await getCmd.ExecuteScalarAsync());
if (String.IsNullOrWhiteSpace(response))
{
var cmd = new SQLiteCommand(SQLQueries.InsertUserSetting(userSettingName), conn);
cmd.Parameters.Add(new SQLiteParameter("@KeyValue", paramValue));
await cmd.ExecuteNonQueryAsync();
}
else
{
var cmd = new SQLiteCommand(SQLQueries.UpdateUserSetting(userSettingName), conn);
cmd.Parameters.Add(new SQLiteParameter("@KeyValue", paramValue));
await cmd.ExecuteNonQueryAsync();
}
}
}
public async Task <long> AddNote(Note note)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
var group = await GetGroupWithConnection(conn, note.Group);
long groupID = 0;
if (group == null)
{
groupID = await AddGroupWithConnection(conn, note.Group);
}
else
{
groupID = group.ID;
}
using (var cmd = new SQLiteCommand(SQLQueries.AddNote(), conn))
{
cmd.Parameters.Add(new SQLiteParameter("@CloudKey", note.CloudKey));
cmd.Parameters.Add(new SQLiteParameter("@LastMOdified", note.LastModified.ToString()));
cmd.Parameters.Add(new SQLiteParameter("@Content", note.Content));
cmd.Parameters.Add(new SQLiteParameter("@GroupID", groupID));
await cmd.ExecuteNonQueryAsync();
}
using (var cmdGetID = new SQLiteCommand(SQLQueries.GetLastInsertedRowID(), conn))
return((long)cmdGetID.ExecuteScalar());
}
}
private async Task CreateTables()
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
await new SQLiteCommand(SQLQueries.CreateTables(), conn).ExecuteNonQueryAsync();
}
}
private async Task <string> GetUserSetting(string userSettingName)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
var cmd = new SQLiteCommand(SQLQueries.GetUserSetting(userSettingName), conn);
return((string)(await cmd.ExecuteScalarAsync()));
}
}
private async Task <long> AddGroupWithConnection(SQLiteConnection conn, string groupName)
{
using (var cmd = new SQLiteCommand(SQLQueries.AddGroup(), conn))
{
cmd.Parameters.Add(new SQLiteParameter("@Group", groupName.ToUpper()));
await cmd.ExecuteNonQueryAsync();
}
using (var cmdGetID = new SQLiteCommand(SQLQueries.GetLastInsertedRowID(), conn))
return((long)await cmdGetID.ExecuteScalarAsync());
}
public async Task DeleteUserSetting(string userSettingName, string paramValue)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
var cmd = new SQLiteCommand(SQLQueries.DeleteUserSetting(userSettingName), conn);
cmd.Parameters.Add(new SQLiteParameter("@KeyValue", paramValue));
await cmd.ExecuteNonQueryAsync();
}
}
public async Task DeleteGroup(string groupName)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
var cmdDelete = new SQLiteCommand(SQLQueries.DeleteGroup(), conn);
cmdDelete.Parameters.Add(new SQLiteParameter("@Group", groupName.ToUpper()));
await cmdDelete.ExecuteNonQueryAsync();
}
}
public Task DeleteNote(Note note)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
using (var cmd = new SQLiteCommand(SQLQueries.DeleteNote(), conn))
{
cmd.Parameters.Add(new SQLiteParameter("@ID", note.ID));
return(cmd.ExecuteNonQueryAsync());
}
}
}
private async Task <List <Group> > GetAllGroupsWithConnection(SQLiteConnection conn)
{
using (var reader = await new SQLiteCommand(SQLQueries.GetAllGroups(), conn)
.ExecuteReaderAsync())
{
var groups = new List <Group>();
while (reader.Read())
{
groups.Add(ReadGroupFromSQL(reader));
}
return(groups);
}
}
public async Task UpdateGroup(string oldGroupName, string newGroupName)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
var oldGroup = await GetGroupWithConnection(conn, oldGroupName.ToUpper());
var cmdUpdate = new SQLiteCommand(SQLQueries.UpdateGroup(), conn);
cmdUpdate.Parameters.Add(new SQLiteParameter("@Group", newGroupName.ToUpper()));
cmdUpdate.Parameters.Add(new SQLiteParameter("@GroupID", oldGroup.ID));
await cmdUpdate.ExecuteNonQueryAsync();
}
}
public Task UpdateNote(Note note)
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
using (var cmd = new SQLiteCommand(SQLQueries.UpdateNote(), conn))
{
cmd.Parameters.Add(new SQLiteParameter("@CloudKey", note.CloudKey));
cmd.Parameters.Add(new SQLiteParameter("@LastModified", note.LastModified));
cmd.Parameters.Add(new SQLiteParameter("@Content", note.Content));
cmd.Parameters.Add(new SQLiteParameter("@Group", note.Group));
return(cmd.ExecuteNonQueryAsync());
}
}
}
private async Task <Group> GetGroupWithConnection(SQLiteConnection conn, string groupName)
{
using (var cmd = new SQLiteCommand(SQLQueries.GetGroup(), conn))
{
cmd.Parameters.Add(new SQLiteParameter("@Group", groupName));
using (var reader = await cmd.ExecuteReaderAsync())
{
if (reader.Read())
{
return(ReadGroupFromSQL(reader));
}
else
{
return(null);
}
}
}
}
public async Task <List <Note> > GetAllNotes()
{
using (var conn = new SQLiteConnection(_connectionString))
{
conn.Open();
using (var reader = await new SQLiteCommand(SQLQueries.GetAllNotes(), conn)
.ExecuteReaderAsync())
{
var notes = new List <Note>();
while (reader.Read())
{
notes.Add(ReadNoteFromSQL(reader));
}
return(notes);
}
}
}