}//public static void Main /// <summary>Database Query.</summary> /// <param name="databaseConnectionString">The database connection string, for example, "Provider=SQLOLEDB;Data Source=localhost;user id=WordEngineering;password=WordEngineering;Initial Catalog=Bible;"</param> /// <param name="exceptionMessage">The exception message.</param> /// <param name="iDataReader">The IDataReader.</param> /// <param name="word">The word to find.</param> /// <param name="findVersesContaining">All Words = 0, Any Word = 1, Phrase = 2</param> /// <param name="bibleBookIdMinimum">The search should begin from this particular bible book.</param> /// <param name="bibleBookIdMaximum">The search should end in this particular bible book.</param> /// <param name="scriptureReference">The scripture reference.</param> /// <param name="arrayListBibleBookClassificationId">The bible book classification Ids.</param> public static void DatabaseQuery ( string databaseConnectionString, ref string exceptionMessage, ref IDataReader iDataReader, string word, FindVersesContaining findVersesContaining, int bibleBookIdMinimum, int bibleBookIdMaximum, ScriptureReference[] scriptureReference, ArrayList arrayListBibleBookClassificationId ) { int bibleBookClassificationCount; int bibleBookClassificationId; int bibleBookClassificationTotal = -1; BibleBookClassification[] bibleBookClassification = null; if ( arrayListBibleBookClassificationId != null ) { bibleBookClassificationTotal = arrayListBibleBookClassificationId.Count; if ( bibleBookClassificationTotal > 0 ) { bibleBookClassification = new BibleBookClassification[ bibleBookClassificationTotal ]; for ( bibleBookClassificationCount = 0; bibleBookClassificationCount < bibleBookClassificationTotal; ++bibleBookClassificationCount) { bibleBookClassificationId = (int) arrayListBibleBookClassificationId[ bibleBookClassificationCount ]; bibleBookClassification[bibleBookClassificationCount] = ( BibleBookClassification ) BibleBookClassification.arrayListCollection[ bibleBookClassificationId ]; #if (DEBUG) System.Console.WriteLine ( "bibleBookClassificationCount[{0}]: {1} ", bibleBookClassificationCount, bibleBookClassification[bibleBookClassificationCount] ); #endif }//for ( bibleBookClassificationCount = 0; bibleBookClassificationCount < bibleBookClassificationTotal; ++bibleBookClassificationCount) }//if ( bibleBookClassificationTotal > 0 ) }//if ( bibleBookClassificationTotal > 0 ) DatabaseQuery ( databaseConnectionString, ref exceptionMessage, ref iDataReader, word, findVersesContaining, bibleBookIdMinimum, bibleBookIdMaximum, scriptureReference, bibleBookClassification ); }//DatabaseQuery
}//DatabaseQuery /// <summary>Database Query.</summary> /// <param name="databaseConnectionString">The database connection string, for example, "Provider=SQLOLEDB;Data Source=localhost;user id=WordEngineering;password=WordEngineering;Initial Catalog=Bible;"</param> /// <param name="exceptionMessage">The exception message.</param> /// <param name="iDataReader">The IDataReader.</param> /// <param name="word">The word to find.</param> /// <param name="findVersesContaining">All Words = 0, Any Word = 1, Phrase = 2</param> /// <param name="bibleBookIdMinimum">The search should begin from this particular bible book.</param> /// <param name="bibleBookIdMaximum">The search should end in this particular bible book.</param> /// <param name="scriptureReference">The scripture reference.</param> /// <param name="bibleBookClassification">The bible book classification Ids.</param> public static void DatabaseQuery ( string databaseConnectionString, ref string exceptionMessage, ref IDataReader iDataReader, string word, FindVersesContaining findVersesContaining, int bibleBookIdMinimum, int bibleBookIdMaximum, ScriptureReference[] scriptureReference, BibleBookClassification[] bibleBookClassification ) { int wordCount = 0; int wordTotal = 0; string wordDelimiter = null; string[] wordSplit = null; ArrayList arrayListClassificationBibleBookId = null; StringBuilder SQLStatement = null; StringBuilder WhereWord = null; StringBuilder WhereBookId = null; StringBuilder WhereBookClassification = null; SQLStatement = new StringBuilder( SQLStatementSelect ); SQLStatement.Append( DatabaseColumnNameScriptureReference ); SQLStatement.Append( "," ); SQLStatement.Append( DatabaseColumnNameVerseText ); SQLStatement.Append( SQLStatementFrom ); SQLStatement.Append( " " ); SQLStatement.Append( DatabaseTableName ); SQLStatement.Append( " " ); SQLStatement.Append( SQLStatementSelectLock ); if ( word != null ) { switch(findVersesContaining) { case FindVersesContaining.allWords: wordDelimiter = " AND "; break; case FindVersesContaining.anyWords: wordDelimiter = " OR "; break; case FindVersesContaining.phrase: wordDelimiter = null; break; }//switch(findVersesContaining) word = word.Trim(); if ( findVersesContaining == FindVersesContaining.allWords || findVersesContaining == FindVersesContaining.anyWords ) { wordSplit = word.Split( StringDelimiterCharacterArray ); wordTotal = wordSplit.Length; WhereWord = new StringBuilder(); WhereWord.Append( " ( " ); for ( wordCount = 0; wordCount < wordTotal; ++wordCount ) { WhereWord.Append( DatabaseColumnNameVerseText ); WhereWord.Append( " LIKE '%" ); WhereWord.Append( wordSplit[wordCount] ); WhereWord.Append( "%'" ); if ( wordCount + 1 < wordTotal ) { WhereWord.Append( wordDelimiter ); }//if ( wordCount + 1 < wordTotal ) }//for ( wordCount = 0; wordCount < wordTotal; ++wordCount ) WhereWord.Append( " ) " ); }//if ( findVersesContaining == FindVersesContainingAllWords or findVersesContaining == FindVersesContainingAnyWords ) if ( findVersesContaining == FindVersesContaining.phrase ) { WhereWord = new StringBuilder(); WhereWord.Append( " ( " ); WhereWord.Append( DatabaseColumnNameVerseText ); WhereWord.Append( " LIKE '%" ); WhereWord.Append( word ); WhereWord.Append( "%' ) " ); }//if ( findVersesContaining == FindVersesContainingPhrase ) }//if ( word != null ) if ( bibleBookIdMinimum > 1 || bibleBookIdMaximum < 66 ) { WhereBookId = new StringBuilder(); WhereBookId.Append(" ( bookId BETWEEN "); WhereBookId.Append(bibleBookIdMinimum); WhereBookId.Append(" AND "); WhereBookId.Append(bibleBookIdMaximum); WhereBookId.Append(" ) "); } if ( bibleBookClassification != null ) { int bibleBookClassificationTotal = 0; WhereBookClassification = new StringBuilder(); arrayListClassificationBibleBookId = BibleBookClassification.BibleBookIds( bibleBookClassification ); bibleBookClassificationTotal = arrayListClassificationBibleBookId.Count; WhereBookClassification.Append(" bookId IN ( "); for ( int i = 0; i < bibleBookClassificationTotal; ++i ) { WhereBookClassification.Append( arrayListClassificationBibleBookId[i] ); if ( i + 1 < bibleBookClassificationTotal ) { WhereBookClassification.Append( "," ); }//if ( i + 1 < bibleBookClassificationTotal ) }//for ( int i = 0; i < bibleBookClassificationTotal; ++i ) WhereBookClassification.Append(" ) "); }//if ( bibleBookClassification != null ) if ( WhereWord != null || WhereBookId != null || WhereBookClassification != null ) { bool appendAnd = false; SQLStatement.Append( SQLStatementSelectWhere ); if ( WhereWord != null ) { SQLStatement.Append( WhereWord ); appendAnd = true; }//if ( WhereWord != null ) if ( WhereBookId != null ) { if ( appendAnd ) { SQLStatement.Append( " AND " ); } SQLStatement.Append( WhereBookId ); appendAnd = true; }//if ( WhereBookId != null ) if ( WhereBookClassification != null ) { if ( appendAnd ) { SQLStatement.Append( " AND " ); } SQLStatement.Append( WhereBookClassification ); appendAnd = true; }//if ( WhereBookClassification != null ) }//if ( WhereWord != null or WhereBookId != null or WhereBookClassification != null ) SQLStatement.Append(SQLStatementSelectOrderBy); UtilityDatabase.DatabaseQuery ( databaseConnectionString, ref exceptionMessage, ref iDataReader, SQLStatement.ToString(), CommandType.Text ); System.Console.WriteLine("SQLStatement: {0}", SQLStatement); }//public static DatabaseQuery()