public async Task InsertWordMappings(IEnumerable <UserWordMapping> mappings) { try { using (var db = new PostgresDBProvider()) { var transaction = db.Connection.BeginTransaction(); var stringCollection = new List <string>(); using (var cmd = db.Connection.CreateCommand()) { foreach (var mapping in mappings) { stringCollection.Add($"({mapping.UserId}, {mapping.WordId}, {mapping.Value})"); } cmd.CommandText = "INSERT into user_word_mapping(user_id, word_id, value) VALUES " + string.Join(",", stringCollection) + ";"; cmd.Transaction = transaction; await cmd.ExecuteNonQueryAsync(); } await transaction.CommitAsync(); } } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); } }
public async Task UpdateUserWordOwnership(ulong userId, IEnumerable <UserWordMapping> mappings) { using (var db = new PostgresDBProvider()) { var transaction = db.Connection.BeginTransaction(); var sb = new StringBuilder(); if (mappings.Any()) { using (var cmd = db.Connection.CreateCommand()) { cmd.Transaction = transaction; // Dynamically building sql string to prevent multiple trips to database. foreach (var mapping in mappings) { sb.Append($"UPDATE user_word_mapping SET value={mapping.Value}, user_id={Convert.ToInt64(userId)}, word_id={mapping.WordId} WHERE id={mapping.Id};"); } cmd.CommandText = sb.ToString(); await cmd.ExecuteNonQueryAsync(); } } await transaction.CommitAsync(); } }
public async Task Delete(UserWordMapping entity) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("DELETE FROM user_word_mapping WHERE id='@id';", db.Connection)) { cmd.Parameters.AddWithValue("@id", entity.Id); using (var reader = cmd.ExecuteReader()) { await cmd.ExecuteNonQueryAsync(); } } }
/// <summary> /// Gets all words from the word table. /// </summary> /// <returns>A collection of words.</returns> public async Task <IEnumerable <Config> > GetAll() { var configCollection = new List <Config>(); using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT * from config;", db.Connection)) using (var reader = cmd.ExecuteReader()) { if (await reader.ReadAsync()) { configCollection.Add(GetConfigFromDataReader(reader)); } } return(configCollection); }
public async Task <IEnumerable <UserWordMapping> > GetAll() { var wordCollection = new List <UserWordMapping>(); using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT * from user_word_mapping;", db.Connection)) using (var reader = cmd.ExecuteReader()) { if (await reader.ReadAsync()) { wordCollection.Add(GetUserWordMappingFromDataReader(reader)); } } return(wordCollection); }
public async Task <User> Update(User entity) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("UPDATE public.user SET username=@username WHERE id='@id';", db.Connection)) { cmd.Parameters.AddWithValue("@id", Convert.ToInt64(entity.Id)); cmd.Parameters.AddWithValue("@username", entity.Username); using (var reader = cmd.ExecuteReader()) { await cmd.ExecuteNonQueryAsync(); return(entity); } } }
public async Task <User> Insert(User entity) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("INSERT INTO public.user (id, username) VALUES (@id, @username);", db.Connection)) { cmd.Parameters.AddWithValue("@id", Convert.ToInt64(entity.Id)); cmd.Parameters.AddWithValue("@username", entity.Username); using (var reader = cmd.ExecuteReader()) { await cmd.ExecuteNonQueryAsync(); return(entity); } } }
public async Task <Word> Insert(Word entity) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("INSERT INTO word (name, value) VALUES (@name, @value);", db.Connection)) { cmd.Parameters.AddWithValue("@name", entity.Name); cmd.Parameters.AddWithValue("@value", entity.Value); using (var reader = cmd.ExecuteReader()) { await cmd.ExecuteNonQueryAsync(); return(await GetByWord(entity.Name)); } } }
public async Task <UserWordMapping> Upsert(UserWordMapping entity) { var word = await GetById(entity.Id); using (var db = new PostgresDBProvider()) { if (word == null) { return(await Insert(entity)); } else { return(await Update(entity)); } } }
/// <summary> /// Give a string, returns a word that matches the value. /// </summary> /// <param name="word"></param> /// <returns>A word, or null if not found.</returns> public async Task <Word> GetByWord(string word) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT * from word WHERE name = @name", db.Connection)) { cmd.Parameters.AddWithValue("name", word); using (var reader = cmd.ExecuteReader()) { if (await reader.ReadAsync()) { return(GetWordFromDataReader(reader)); } } } return(null); }
public async Task <Word> Update(Word entity) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("UPDATE word SET name=@name, value=@value WHERE id='@id';", db.Connection)) { cmd.Parameters.AddWithValue("@id", entity.Id); cmd.Parameters.AddWithValue("@name", entity.Name); cmd.Parameters.AddWithValue("@value", entity.Value); using (var reader = cmd.ExecuteReader()) { await cmd.ExecuteNonQueryAsync(); return(entity); } } }
public async Task <User> Upsert(User entity) { var user = await GetById(entity.Id); using (var db = new PostgresDBProvider()) { if (user == null) { return(await Insert(entity)); } else { return(await Update(entity)); } } }
public async Task <UserWordMapping> Insert(UserWordMapping entity) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("INSERT INTO user_word_mapping (user_id, word_id, value) VALUES (@user_id, @word_id, @value);", db.Connection)) { cmd.Parameters.AddWithValue("user_id", entity.UserId); cmd.Parameters.AddWithValue("word_id", entity.WordId); cmd.Parameters.AddWithValue("value", entity.Value); using (var reader = cmd.ExecuteReader()) { await cmd.ExecuteNonQueryAsync(); return(await GetByWordId(entity.WordId)); } } }
public async Task <User> GetById(ulong id) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT * from public.user WHERE id=@id;", db.Connection)) { cmd.Parameters.AddWithValue("id", NpgsqlTypes.NpgsqlDbType.Bigint, Convert.ToInt64(id)); using (var reader = cmd.ExecuteReader()) { if (await reader.ReadAsync()) { return(GetUserFromDataReader(reader)); } } } return(null); }
public async Task <Config> GetById(int id) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT * from config WHERE id='@id';", db.Connection)) { cmd.Parameters.AddWithValue("@id", id); using (var reader = cmd.ExecuteReader()) { if (await reader.ReadAsync()) { return(GetConfigFromDataReader(reader)); } } } return(null); }
public async Task <UserWordMapping> Update(UserWordMapping entity) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("UPDATE user_word_mapping SET value=@value, user_id=@userId, word_id=@wordId WHERE id=@id;", db.Connection)) { cmd.Parameters.AddWithValue("id", entity.Id); cmd.Parameters.AddWithValue("userId", entity.UserId); cmd.Parameters.AddWithValue("wordId", entity.WordId); cmd.Parameters.AddWithValue("value", entity.Value); using (var reader = cmd.ExecuteReader()) { await cmd.ExecuteNonQueryAsync(); return(entity); } } }
public async Task <UserWordMapping> GetByWordName(string wordName) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT wm.* from user_word_mapping wm INNER JOIN word w ON (w.Id = wm.word_id) WHERE w.name ='@wordName';", db.Connection)) { cmd.Parameters.AddWithValue("wordName", wordName); using (var reader = cmd.ExecuteReader()) { if (await reader.ReadAsync()) { return(GetUserWordMappingFromDataReader(reader)); } } } return(null); }
public async Task <UserWordMapping> GetByWordId(int wordId) { using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT * from user_word_mapping WHERE word_id='@wordId';", db.Connection)) { cmd.Parameters.AddWithValue("wordId", wordId); using (var reader = cmd.ExecuteReader()) { if (await reader.ReadAsync()) { return(GetUserWordMappingFromDataReader(reader)); } } } return(null); }
public async Task <IEnumerable <UserWordMapping> > GetWordsOwnedByOtherUsers(ulong userId, IEnumerable <string> words) { var collection = new List <UserWordMapping>(); using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand("SELECT wm.* FROM user_word_mapping wm INNER JOIN word w ON (w.Id = wm.word_id) WHERE wm.user_id != @userId and w.name = ANY(:words);", db.Connection)) { cmd.Parameters.AddWithValue("userId", Convert.ToInt64(userId)); cmd.Parameters.Add("words", NpgsqlDbType.Array | NpgsqlDbType.Varchar).Value = words.ToArray(); using (var reader = cmd.ExecuteReader()) { while (await reader.ReadAsync()) { collection.Add(GetUserWordMappingFromDataReader(reader)); } } } return(collection); }
public async Task PingAsync() { var x = (Context.Message.Author); long count = 0; using (var db = new PostgresDBProvider()) { using (var cmd = new NpgsqlCommand("SELECT count(*) from word", db.Connection)) using (var reader = cmd.ExecuteReader()) while (reader.Read()) { count = reader.GetInt64(0); } } await ReplyAsync($"I currently know about {count} total words!"); //await ReplyAsync($"You are user {x.Username} with id {x.Id}"); }
/// <summary> /// Given a collection of strings, returns a group of word entities /// </summary> /// <param name="words"></param> /// <returns>A collection of words. Will be empty if no matches.</returns> public async Task <IEnumerable <Word> > GetByWords(IEnumerable <string> words) { var wordCollection = new List <Word>(); using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand($"SELECT * from word WHERE name = ANY(:words);", db.Connection)) { cmd.Parameters.Add("words", NpgsqlDbType.Array | NpgsqlDbType.Varchar).Value = words; var sql = cmd.CommandText; using (var reader = cmd.ExecuteReader()) { while (await reader.ReadAsync()) { wordCollection.Add(GetWordFromDataReader(reader)); } } } return(wordCollection); }
public async Task <IEnumerable <UserWordMapping> > GetUserOwnedWords(ulong userId) { var collection = new List <UserWordMapping>(); using (var db = new PostgresDBProvider()) using (var cmd = new NpgsqlCommand($"SELECT * from user_word_mapping WHERE user_id = @userId;", db.Connection)) { cmd.Parameters.AddWithValue("userId", Convert.ToInt64(userId)); var sql = cmd.CommandText; using (var reader = cmd.ExecuteReader()) { while (await reader.ReadAsync()) { collection.Add(GetUserWordMappingFromDataReader(reader)); } } } return(collection); }