}//ConfigurationXml
/// <summary>Scripture Reference URI.</summary>
/// <param name="databaseConnectionString">The database connection string.</param>
/// <param name="filenameConfigurationXml">The filename configuration Xml.</param>
/// <param name="exceptionMessage">The exception message.</param>
/// <param name="dataSet">The dataset.</param>
public static void ScriptureReferenceURI
(
string databaseConnectionString,
string filenameConfigurationXml,
ref string exceptionMessage,
ref DataSet dataSet
)
{
bool boolView = false;
int columnIndexScriptureReference = -1;
string dataColumnName = null;
string scriptureReferenceColumnValue = null;
string scriptureReferenceColumnName = null;
string scriptureReferenceColumnNameURI = null;
string scriptureReferenceURI = null;
string[][] scriptureReferenceArray = null;
string columnNamePrimary = null;
string columnValuePrimary = null;
string tableName = null;
ArrayList scriptureReferenceSet = null;
ArrayList scriptureReferenceURISet = null;
ScriptureReferenceBookChapterVersePrePost[] scriptureReferenceBookChapterVersePrePost = null;
StringBuilder columnSet = null;
StringBuilder uriCrosswalkXml = null;
StringBuilder uriCrosswalkHtml = null;
StringBuilder SQLStatement = null;
OleDbConnection oleDbConnection = null;
XmlNodeList scriptureReferenceColumnSet = null;
scriptureReferenceColumnSet = UtilityXml.SelectNodes
(
filenameConfigurationXml,
ref exceptionMessage,
XPathColumnScriptureReference
);
try
{
oleDbConnection = UtilityDatabase.DatabaseConnectionInitialize
(
databaseConnectionString,
ref exceptionMessage
);
foreach ( DataTable dataTable in dataSet.Tables )
{
columnIndexScriptureReference = UtilityDatabase.DataTableColumnIndex
(
dataTable,
"ScriptureReference"
);
if ( columnIndexScriptureReference < 0 )
{
continue;
}
tableName = dataTable.TableName;
scriptureReferenceSet = new ArrayList();
scriptureReferenceURISet = new ArrayList();
SQLStatement = new StringBuilder();
if ( tableName.IndexOf("View" ) < 0 )
{
boolView = false;
SQLStatement.AppendFormat
(
InformationSchemaColumnNamePrimaryColumnSQL,
tableName
);
}
else
{
boolView = true;
SQLStatement.AppendFormat
(
InformationSchemaColumnNamePrimaryColumnSQL,
tableName.Substring(4)
);
}
#if (DEBUG)
System.Console.WriteLine("SQLStatement: {0}", SQLStatement);
#endif
columnNamePrimary = (string) UtilityDatabase.DatabaseQuery
(
databaseConnectionString,
ref exceptionMessage,
SQLStatement.ToString(),
CommandType.Text
);
if ( boolView == true )
{
columnNamePrimary = String.Concat( tableName.Substring(4), columnNamePrimary );
}
#if (DEBUG)
System.Console.WriteLine("ColumnNamePrimary: {0}", columnNamePrimary);
#endif
foreach ( DataColumn dataColumn in dataTable.Columns )
{
dataColumnName = dataColumn.ColumnName.Trim();
/*
#if (DEBUG)
System.Console.WriteLine("Table Name: {0} | ColumnName: {1}", tableName, dataColumnName);
#endif
*/
if ( dataColumnName.IndexOf( ScriptureReferenceURIColumnNamePostfix ) >= 0 )
{
continue;
}
if ( dataColumnName.IndexOf( ScriptureReferenceColumnNamePostfix ) >= 0 )
{
scriptureReferenceSet.Add( dataColumnName );
scriptureReferenceColumnName = dataColumnName;
scriptureReferenceColumnNameURI = scriptureReferenceColumnName + "URI";
if ( dataTable.Columns.Contains( scriptureReferenceColumnNameURI ) == false )
{
/*
#if (DEBUG)
System.Console.WriteLine("Table Name: {0} | ColumnName: {1}", tableName, scriptureReferenceColumnNameURI);
#endif
*/
scriptureReferenceURISet.Add( scriptureReferenceColumnNameURI );
}//if dataTable.Columns.Contains( scriptureReferenceColumnNameURI ) == false )
}//if ( dataColumnName.IndexOf( ScriptureReferenceColumnNamePostfix ) >= 0 )
}//foreach ( DataColumn dataColumn in dataTable.Columns )
foreach ( object columnName in scriptureReferenceURISet )
{
scriptureReferenceColumnNameURI = columnName.ToString().Trim();
dataTable.Columns.Add( scriptureReferenceColumnNameURI );
}
foreach ( DataColumn dataColumn in dataTable.Columns )
{
dataColumnName = dataColumn.ColumnName.Trim();
/*
#if (DEBUG)
System.Console.WriteLine("Table Name: {0} | ColumnName: {1}", tableName, dataColumnName);
#endif
*/
}//foreach ( DataColumn dataColumn in dataTable.Columns )
foreach ( DataRow dataRow in dataTable.Rows )
{
columnValuePrimary = dataRow[columnNamePrimary].ToString().Trim();
columnSet = new StringBuilder();
/*
#if (DEBUG)
System.Console.WriteLine("ColumnValuePrimary: {0}", columnValuePrimary);
#endif
*/
foreach ( object columnName in scriptureReferenceSet )
{
#if (DEBUG)
System.Console.WriteLine("ColumnName: {0}", columnName);
#endif
scriptureReferenceColumnName = columnName.ToString().Trim();
scriptureReferenceColumnValue = dataRow[scriptureReferenceColumnName].ToString().Trim();
#if (DEBUG)
System.Console.WriteLine
(
"Scripture Reference Column Name: {0} | Value: {1}",
scriptureReferenceColumnName,
scriptureReferenceColumnValue
);
#endif
scriptureReferenceColumnNameURI = scriptureReferenceColumnName + "URI";
if ( scriptureReferenceColumnValue != null && scriptureReferenceColumnValue != string.Empty )
{
ScriptureReference.ScriptureReferenceParser
(
new string[] { scriptureReferenceColumnValue },
databaseConnectionString,
ref exceptionMessage,
ref scriptureReferenceBookChapterVersePrePost,
ref scriptureReferenceArray,
ref uriCrosswalkHtml,
ref uriCrosswalkXml
);//ScriptureReference.ScriptureReferenceParser()
scriptureReferenceURI = uriCrosswalkHtml.ToString();
if ( UtilityDatabase.DataSetTableColumnContains( dataSet, tableName, scriptureReferenceColumnNameURI ) == false )
{
UtilityDatabase.DataSetTableColumnAdd( dataSet, tableName, scriptureReferenceColumnNameURI );
dataRow[scriptureReferenceColumnNameURI] = scriptureReferenceURI;
continue;
}//if ( DataSetTableColumnContains( dataSet, dataTable, scriptureReferenceColumnNameURI ) )
dataRow[scriptureReferenceColumnNameURI] = scriptureReferenceURI;
if ( scriptureReferenceURISet.IndexOf( scriptureReferenceColumnNameURI ) >= 0 )
{
continue;
}
if ( columnSet.Length != 0 )
{
columnSet.Append(',');
}
columnSet.AppendFormat
(
UtilityDatabase.DatabaseTemplateSQLSet,
scriptureReferenceColumnNameURI,
scriptureReferenceURI
);
/*
#if (DEBUG)
System.Console.WriteLine("columnSet: {0}", columnSet);
#endif
*/
SQLStatement = new StringBuilder();
SQLStatement.AppendFormat
(
UtilityDatabase.DatabaseTemplateSQLUpdateScriptureReferenceURISet,
tableName,
columnSet,
columnNamePrimary,
columnValuePrimary
);
#if (DEBUG)
System.Console.WriteLine("SQL Statement: {0}", SQLStatement.ToString() );
#endif
UtilityDatabase.DatabaseNonQuery
(
oleDbConnection,
ref exceptionMessage,
SQLStatement.ToString()
);
}//if ( scriptureReferenceColumnValue != null && scriptureReferenceColumnValue != string.Empty )
}//foreach ( object columnName in dataTableColumnName )
}//foreach ( DataRow dataRow in dataTable )
}//foreach ( DataTable dataTable in dataSet )
UtilityDatabase.DatabaseConnectionHouseKeeping
(
oleDbConnection,
ref exceptionMessage
);
}//try
catch (OleDbException exception)
{
//exceptionMessage = exception.Message;
exceptionMessage = UtilityEventLog.WriteEntryOleDbErrorCollection( exception );
System.Console.WriteLine("OleDbException: {0}", exceptionMessage);
}//catch (OleDbException exception)
catch (SecurityException exception)
{
exceptionMessage = exception.Message;
System.Console.WriteLine( "SecurityException: {0}", exception.Message );
}//catch (SecurityException exception)
catch (SystemException exception)
{
exceptionMessage = exception.Message;
System.Console.WriteLine( "SystemException: {0}", exception.Message );
}//catch (SystemException exception)
catch (Exception exception)
{
exceptionMessage = exception.Message;
System.Console.WriteLine( "Exception: {0}", exception.Message );
}//catch (Exception exception)
}//public static void ScriptureReferenceURI()