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;
}
}
}
