public async Task Clear() { using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); await conn.ExecuteAsync("delete from [User]").ConfigureAwait(false); } }
public async Task<UserMessage> AddMessage(string userLogin, string text) { var res = new UserMessage() {Text = text, UserLogin = userLogin, Created = DateTime.UtcNow}; using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); int count = await conn.ExecuteAsync( "insert or ignore into [Message] (Text, Created, UserId) select @text as Text, @created as Created, UserId from [User] where [Login] = @userLogin", new {text, created = res.Created, userLogin}).ConfigureAwait(false); if (count == 0) { int userId = await conn.ExecuteScalarAsync<int>( "insert into [User] (Login, Created) values(@userLogin, @created); SELECT last_insert_rowid() FROM [User]", new {userLogin, created = res.Created}).ConfigureAwait(false); await conn.ExecuteAsync("insert into [Message] (Text, Created, UserId) values(@text, @created, @userId)", new { text, created = res.Created, userId}).ConfigureAwait(false); } } return res; }
public async Task<UserInfo> AddUser(string userLogin, string displayName) { var res = new UserInfo() {UserLogin = userLogin, DisplayName = displayName, Created = DateTime.UtcNow}; using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); int count = await conn.ExecuteAsync("insert or ignore into [User] (Login, Display, Created) values(@userLogin, @displayName, @created)", new {userLogin, displayName, created = res.Created }).ConfigureAwait(false); if (count == 0) throw new Exception($"The user '{userLogin}' already exists"); } return res; }
public async Task<int> RemoveUser(string userLogin) { using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); return await conn.ExecuteAsync("delete from [User] where [Login] = @userLogin", new {userLogin}).ConfigureAwait(false); } }