public static List <SongSearchResult> Search(List <string> searchTerms)
{
if (searchTerms.Count < 1)
{
throw (new ApplicationException("No search terms"));
}
// Double check sql
string p = ",./<>?[]\\{}|!@#$%^&*()-=_+:;'\"";
foreach (string str in searchTerms)
{
if (str.IndexOfAny(p.ToCharArray()) != -1)
{
throw (new ApplicationException("Malformed sql passed to Search"));
}
}
// Build the query
string query = "SELECT \"SongVerses\".\"AutoNumber\", \"Songs\".\"Number\", \"SongVerses\".\"Verse\", \"SongVerses\".\"OrderNum\", \"SongVerses\".\"IsChorus\" " +
"FROM \"SongVerses\" INNER JOIN \"Songs\" ON \"SongVerses\".\"AutoNumber\" = \"Songs\".\"AutoNumber\" WHERE ";
foreach (string s in searchTerms)
{
query += "(\"Verse\" LIKE '%" + s + "%') AND ";
}
query = query.Remove(query.Length - 4);
query += "ORDER BY \"Number\"";
// Query the database
List <SongSearchResult> lResult = new List <SongSearchResult>();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = query;
t.ExecuteReader();
int cutoff = 100;
while (t.DR.Read() && cutoff > 0)
{
cutoff--;
SongSearchResult ssr = new SongSearchResult();
ssr.autoNumber = t.GetInt32(0);
ssr.songNumber = t.GetInt32(1);
ssr.verseData = t.GetString(2);
ssr.isAtStart = t.GetInt32(3) == 0;
ssr.isChorus = t.GetBoolean(4);
ssr.verseData = SongProject.RemoveVerseFormatting(ssr.verseData);
ssr.searchResult = ssr.verseData;
lResult.Add(ssr);
}
}
return(lResult);
}
// Database support
public static PresenterFont GetFontFromDatabase(int id)
{
PresenterFont font = new PresenterFont();
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT [FONTNAME], [SIZEINPOINTS], [COLOR], [VERTICALALIGNMENT], [HORIZONTALALIGNMENT], [OUTLINE], [SHADOW], [OUTLINECOLOR], [SHADOWCOLOR], [ITALIC], [BOLD], [DOUBLESPACE] " +
"FROM [PPTFONT] " +
"WHERE [AUTONUMBER] = @AUTONUMBER";
t.AddParameter("@AUTONUMBER", id);
t.ExecuteReader();
if (t.DR.Read())
{
font.FontName = t.GetString(0);
font.SizeInPoints = t.GetInt32(1);
font.Color = t.GetColor(2, Color.Black);
switch (t.GetInt32(3))
{
case 0:
font.VerticalAlignment = VerticalAlignment.Top;
break;
case 1:
font.VerticalAlignment = VerticalAlignment.Middle;
break;
case 2:
font.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
break;
}
switch (t.GetInt32(4))
{
case 0:
font.HorizontalAlignment = HorizontalAlignment.Left;
break;
case 2:
font.HorizontalAlignment = HorizontalAlignment.Center;
break;
case 1:
font.HorizontalAlignment = HorizontalAlignment.Right;
break;
default:
break;
}
font.Outline = t.GetBoolean(5);
font.Shadow = t.GetBoolean(6);
font.OutlineColor = t.GetColor(7, Color.White);
font.ShadowColor = t.GetColor(8, Color.Gray);
font.Italic = t.GetBoolean(9);
font.Bold = t.GetBoolean(10);
font.DoubleSpace = t.GetBoolean(11);
}
return(font);
}
}
