public IEnumerable <WordSearchResult> SearchAllEntries(string query) { var sb = new StringBuilder(); if (string.IsNullOrEmpty(query)) { sb.Append("%"); } else { sb.Append("%"); foreach (char c in query) { switch (c) { //']' on its own doesn't need to be escaped. case '%': case '_': case '[': sb.Append('[').Append(c).Append(']'); break; default: sb.Append(c); break; } } sb.Append("%"); } using (var reader = SelectReader(@" TYPES Integer, Text, Text, Text, Integer; SELECT SetID, Name, Phrase, Translation, ListPosition FROM VocabItems vi JOIN Sets s ON vi.SetID = s.ID WHERE Phrase LIKE @1 OR Translation LIKE @1 ORDER BY SetID ASC, Phrase ASC" , sb.ToString())) { while (reader.Read()) { var wsr = new WordSearchResult { SetID = reader.GetInt64(0), SetName = reader.GetString(1), Phrase = reader.GetString(2), Translation = reader.GetString(3), ListPosition = reader.GetInt32(4) }; yield return(wsr); } } }
public List <WordSearchResult> getWords(String searchedWord) { List <WordSearchResult> results = new List <WordSearchResult>(); using (NpgsqlCommand cmd = new NpgsqlCommand(String.Format("SELECT word, referencenum, booknum, book, chapter, versenum, verse FROM getWords('{0}')", searchedWord), connection)) { using (NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { WordSearchResult result = new WordSearchResult(reader.GetString(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetInt32(4), reader.GetInt32(5), reader.GetString(6)); results.Add(result); } return(results); } } }
public IEnumerable<WordSearchResult> SearchAllEntries(string query) { var sb = new StringBuilder(); if (string.IsNullOrEmpty(query)) { sb.Append("%"); } else { sb.Append("%"); foreach (char c in query) { switch (c) { //']' on its own doesn't need to be escaped. case '%': case '_': case '[': sb.Append('[').Append(c).Append(']'); break; default: sb.Append(c); break; } } sb.Append("%"); } using (var reader = SelectReader(@" TYPES Integer, Text, Text, Text, Integer; SELECT SetID, Name, Phrase, Translation, ListPosition FROM VocabItems vi JOIN Sets s ON vi.SetID = s.ID WHERE Phrase LIKE @1 OR Translation LIKE @1 ORDER BY SetID ASC, Phrase ASC", sb.ToString())) { while (reader.Read()) { var wsr = new WordSearchResult { SetID = reader.GetInt64(0), SetName = reader.GetString(1), Phrase = reader.GetString(2), Translation = reader.GetString(3), ListPosition = reader.GetInt32(4) }; yield return wsr; } } }