Exemplo n.º 1
0
        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();
                }
            }
        }
Exemplo n.º 2
0
        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());
            }
        }
Exemplo n.º 3
0
        private async Task CreateTables()
        {
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();

                await new SQLiteCommand(SQLQueries.CreateTables(), conn).ExecuteNonQueryAsync();
            }
        }
Exemplo n.º 4
0
        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()));
            }
        }
Exemplo n.º 5
0
        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());
        }
Exemplo n.º 6
0
        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();
            }
        }
Exemplo n.º 7
0
        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();
            }
        }
Exemplo n.º 8
0
        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());
                }
            }
        }
Exemplo n.º 9
0
        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);
            }
        }
Exemplo n.º 10
0
        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();
            }
        }
Exemplo n.º 11
0
        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());
                }
            }
        }
Exemplo n.º 12
0
        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);
                    }
                }
            }
        }
Exemplo n.º 13
0
        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);
                }
            }
        }