public static List <BibleSearchResult> 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 \"VERSION\", \"REFBOOK\", \"REFCHAPTER\", \"REFVERSE\", \"DATA\", \"BOOK\", \"CHAPTER\", \"VERSE\" FROM \"BIBLEVERSES\" WHERE "; foreach (string s in searchTerms) { query += "(\"DATA\" LIKE '%" + s + "%') AND "; } query = query.Remove(query.Length - 4); // Query the database List <BibleSearchResult> lResults = new List <BibleSearchResult>(); using (FBirdTask t = new FBirdTask()) { t.CommandText = query; t.ExecuteReader(); int cutoff = 250; while (t.DR.Read() && cutoff > 0) { cutoff--; BibleSearchResult r = new BibleSearchResult(); BibleVerse bv = new BibleVerse(); bv.RefVersion = t.GetString(0); bv.RefBook = t.GetString(1); bv.RefChapter = t.GetInt32(2); bv.RefVerse = t.GetInt32(3); bv.Text = t.GetString(4); bv.SecondaryBook = t.GetString(5); bv.SecondaryChapter = t.GetInt32(6); bv.SecondaryVerse = t.GetInt32(7); r.bibVerse = bv; r.searchResult = bv.Text; lResults.Add(r); } } return(lResults); }
public static void LoadVerses(List <SongVerse> listToLoad, int autoNumber, bool removeFormatting) { listToLoad.Clear(); using (FBirdTask t = new FBirdTask()) { t.CommandText = "SELECT [AutoNumber], [IsChorus], [Verse], [OrderNum] " + "FROM [SongVerses] " + "WHERE [AutoNumber] = @AutoNumber " + "ORDER BY [OrderNum]"; t.Parameters.Add("@AutoNumber", FbDbType.Integer); t.Parameters["@AutoNumber"].Value = autoNumber; t.ExecuteReader(); int verseNumber = 1; while (t.DR.Read()) { bool isChorus = t.GetInt32(1) == 1; int vnum = isChorus ? -1 : verseNumber; string verseData = t.GetString(2); if (removeFormatting) { verseData = RemoveVerseFormatting(verseData); } SongVerse sv = new SongVerse(isChorus, verseData, vnum); listToLoad.Add(sv); if (!isChorus) { verseNumber++; } } } }
private void UpdateVerseList() { if (lbBibleBook == null || lbBibleBook.SelectedIndex == -1 || lbBibleChapter.SelectedIndex == -1) { return; } // Get the data from db & load into the list lbBibleVerse.SuspendLayout(); lbBibleVerse.Items.Clear(); using (FBirdTask t = new FBirdTask()) { t.CommandText = "SELECT \"DATA\" FROM \"BIBLEVERSES\" WHERE " + "\"VERSION\" = '" + Program.ConfigHelper.BiblePrimaryTranslation + "' AND " + "\"REFBOOK\" = '" + ((BookData)lbBibleBook.SelectedItem).mappingName + "' AND " + "\"REFCHAPTER\" = " + lbBibleChapter.SelectedItem.ToString() + " " + "ORDER BY \"REFBOOK\", \"REFCHAPTER\", \"REFVERSE\""; t.ExecuteReader(); int i = 0; while (t.DR.Read()) { i++; VerseData vd = new VerseData(t.GetString(0), i); lbBibleVerse.Items.Add(vd); } } lbBibleVerse.ResumeLayout(); }
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); }
private void LoadSongNote(int songNumber) { dt = new PresenterDataset.SongNotesDataTable(); SNdataView = new DataView(); this.Controls.Add(SndataGrid); BindingSource source = new BindingSource(); source.DataSource = SNdataView; SndataGrid.DataSource = SNdataView; int SongId = 5; using (FBirdTask t = new FBirdTask()) { t.CommandText = "SELECT [SongNotes].[Number], [SongNotes].[Note], [SongNotes].[IdSong] FROM [SongNotes]" + " where [SongNotes].[IdSong]=" + songNumber; t.ExecuteReader(); if (t.DR != null) { int rowNumber = 1; while (t.DR.Read()) { sRow = dt.NewSongNotesRow(); sRow.Number = t.GetInt32(0); // rowNumber; sRow.Note = t.GetString(1); // sRow.SongNumber= t.GetInt32(2); dt.AddSongNotesRow(sRow); rowNumber++; } t.DR.Close(); SNdataView.BeginInit(); SNdataView.Table = dt; SNdataView.EndInit(); } else { sRow = dt.NewSongNotesRow(); sRow.Note = ""; dt.AddSongNotesRow(sRow); } rowCount = dt.Rows.Count; } }
private void InternalLoadSongFromDB(PresenterDataset.SongsRow row, bool isNew) { dt = new PresenterDataset.SongVersesDataTable(); dt.Columns["AutoNumber"].DefaultValue = row.AutoNumber; if (!isNew) { using (FBirdTask t = new FBirdTask()) { t.CommandText = "SELECT [AutoNumber], [IsChorus], [Verse], [OrderNum] " + "FROM [SongVerses] " + "WHERE [AutoNumber] = @AutoNumber " + "ORDER BY [OrderNum]"; t.Parameters.Add("@AutoNumber", FbDbType.Integer); t.Parameters["@AutoNumber"].Value = row.AutoNumber; PresenterDataset.SongVersesRow r; t.ExecuteReader(); while (t.DR.Read()) { r = dt.NewSongVersesRow(); r.AutoNumber = t.GetInt32(0); r.IsChorus = t.GetInt32(1) == 1 ? true : false; r.Verse = t.GetString(2).Replace("\r\n", "\n").Replace("\n", "\r\n"); r.OrderNum = t.GetInt32(3); dt.AddSongVersesRow(r); } } } dt.AcceptChanges(); if (dt.Count == 0) { btnSave.Enabled = false; } dataView1.BeginInit(); dataView1.Table = dt; dataView1.EndInit(); dataGrid1.TableStyles[0].MappingName = dt.TableName; }
// 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); } }
private void LoadBibDat(BibleVerse verse) { // Understanding the databse // SELECT FIRST 10 * FROM BIBLEVERSES // SELECT DISTINCT VERSION FROM BIBLEVERSES // SELECT DISTINCT BOOK FROM BIBLEVERSES // Verses are queried in the number scheme of the primary translation // KJV Psalms 23 == RST Psalms 22 // SELECT FIRST 10 * FROM BIBLEVERSES AS PRI WHERE PRI.VERSION = 'RST' AND PRI.BOOK = 'Psalms' // Old implementation of the Bibleverse table // KJV | Ref book = original KJV numbering | Book = RST equivalent numbering // RST | Ref book = original RST numbering | Book = KJV equivalent numbering // One advantage of the old method is that ability to preload all the version of a chapter relative to the primary translation // PENDING: This code needs to be refactored to use a universal numbering scheme // https://www.biblegateway.com/passage/?search=Psalm+23&version=RUSV // KJV | Ref book = KJV numbering | Book = KJV numbering // RST | Ref book = KJV numbering | Book = RST numbering List <string> translations = TranslationList(); List <BibleVerse> verses = new List <BibleVerse>(); bibVerses.Clear(); currentVerseNum = verse.RefVerse; using (FBirdTask t = new FBirdTask()) { string select = "SELECT "; for (int tnum = 1; tnum <= translations.Count; tnum++) { if (tnum > 1) { select += ", "; } select += string.Format("T{0}.REFBOOK, T{0}.REFCHAPTER, T{0}.REFVERSE, T{0}.DATA", tnum); } string from = " FROM BIBLEVERSES AS T1"; for (int transIx = 1; transIx < translations.Count; transIx++) { bool doMaping = translations[0] == "KJV" || translations[transIx] == "KJV"; if (doMaping) { from += string.Format(" LEFT JOIN BIBLEVERSES AS T{0} ON T1.BOOK = T{0}.REFBOOK AND T1.CHAPTER = T{0}.REFCHAPTER AND T1.VERSE = T{0}.REFVERSE", (transIx + 1)); } else { from += string.Format(" LEFT JOIN BIBLEVERSES AS T{0} ON T1.REFBOOK = T{0}.REFBOOK AND T1.REFCHAPTER = T{0}.REFCHAPTER AND T1.REFVERSE = T{0}.REFVERSE", (transIx + 1)); } } string where = " WHERE T1.VERSION = @Version1 AND T1.REFBOOK = @Book AND T1.REFCHAPTER = @Chapter"; for (int transIx = 1; transIx < translations.Count; transIx++) { where += string.Format(" AND T{0}.VERSION = @Version{0}", (transIx + 1)); } string order = " ORDER BY T1.REFBOOK, T1.REFCHAPTER, T1.REFVERSE, T1.VERSE"; t.CommandText = select + from + where + order; t.Command.Parameters.Add("@Book", FbDbType.VarChar, 50).Value = verse.RefBook; t.Command.Parameters.Add("@Chapter", FbDbType.Integer).Value = verse.RefChapter; for (int tnum = 1; tnum <= translations.Count; tnum++) { t.Command.Parameters.Add("@Version" + tnum, FbDbType.VarChar, 10).Value = translations[tnum - 1]; } t.ExecuteReader(); while (t.DR.Read()) { int ix = 0; BibleVerse bVerse = new BibleVerse(); bVerse.RefVersion = translations[0]; bVerse.RefBook = t.GetString(ix++); bVerse.RefChapter = t.GetInt32(ix++); bVerse.RefVerse = t.GetInt32(ix++); bVerse.Text = t.GetString(ix++); if (translations.Count > 1) { bVerse.SecondaryVersion = translations[1]; bVerse.SecondaryBook = t.GetString(ix++); bVerse.SecondaryChapter = t.GetInt32(ix++); bVerse.SecondaryVerse = t.GetInt32(ix++); bVerse.SecondaryText = t.GetString(ix++); } if (translations.Count > 2) { bVerse.TertiaryVersion = translations[2]; bVerse.TertiaryBook = t.GetString(ix++); bVerse.TertiaryChapter = t.GetInt32(ix++); bVerse.TertiaryVerse = t.GetInt32(ix++); bVerse.TertiaryText = t.GetString(ix++); } verses.Add(bVerse); } } verses.ForEach(v => bibVerses.Add(v.RefVerse, v)); }