// load all words that are in database
public BindingList <WordModel> LoadWords()
{
BindingList <WordModel> list = new BindingList <WordModel>();
string query = "SELECT * FROM Words";
SQLiteCommand command = new SQLiteCommand(query, myConnection);
using (SQLiteDataReader data = command.ExecuteReader())
{
WordModel Word;
while (data.Read())
{
int id = data.GetInt32(0);
string word = data.GetString(1);
PartSpeech part;
Enum.TryParse <PartSpeech>(data.GetString(2), out part);
string definition = data.GetString(3);
Word = new WordModel(id, word, definition, part);
list.Add(Word);
}
}
return(list);
}
public void RemoveWord(WordModel word)
{
string query = @"DELETE FROM Words WHERE Id=@id";
SQLiteCommand command = new SQLiteCommand(query, myConnection);
command.Parameters.AddWithValue("@id", word.Id);
command.ExecuteNonQuery();
}
public void SaveWord(WordModel word)
{
string query = @"INSERT INTO Words ('Id', 'Word', 'Part', 'Definition')
VALUES (@id, @word, @part, @definition);";
SQLiteCommand command = new SQLiteCommand(query, myConnection);
command.Parameters.AddWithValue("@id", word.Id);
command.Parameters.AddWithValue("@word", word.Word);
command.Parameters.AddWithValue("@part", word.Part.ToString());
command.Parameters.AddWithValue("@definition", word.Definition);
command.ExecuteNonQuery();
}
// update value in the database when it is changed
public void UpdateValue(WordModel updatedWord)
{
string query = @"UPDATE Words
SET Word = @word,
Definition = @definition,
Part = @part
WHERE
Id = @id;";
SQLiteCommand command = new SQLiteCommand(query, myConnection);
command.Parameters.AddWithValue("@id", updatedWord.Id);
command.Parameters.AddWithValue("@word", updatedWord.Word);
command.Parameters.AddWithValue("@part", updatedWord.Part);
command.Parameters.AddWithValue("definition", updatedWord.Definition);
command.ExecuteNonQuery();
}
// search for words that contains substring
public BindingList <WordModel> SearchRequest(string filterWord, FilterPartSpeech filterPart)
{
BindingList <WordModel> list = new BindingList <WordModel>();
string query;
if (filterPart == FilterPartSpeech.All) // if filter part is not choosen
{
query = string.Format("SELECT * FROM Words WHERE Word LIKE '%{0}%' ORDER BY Word;", filterWord);
}
else
{
// query for both part and word
query = string.Format("SELECT * FROM Words WHERE Word LIKE '%{0}%' AND Part = '{1}' ORDER BY Word;", filterWord, filterPart.ToString());
if (String.IsNullOrEmpty(filterWord)) // extra query if word search is empty
{
query = string.Format("SELECT * FROM Words WHERE Part = '{0}' ORDER BY Word;", filterPart.ToString());
}
}
SQLiteCommand command = new SQLiteCommand(query, myConnection);
using (SQLiteDataReader data = command.ExecuteReader())
{
WordModel Word;
while (data.Read())
{
int id = data.GetInt32(0);
string word = data.GetString(1);
PartSpeech part;
Enum.TryParse <PartSpeech>(data.GetString(2), out part);
string definition = data.GetString(3);
Word = new WordModel(id, word, definition, part);
list.Add(Word);
}
}
return(list);
}