/// <summary>
/// Synchronizes the saved randomizer specifications in a query definition file with the current column specifications.
/// </summary>
/// <param name="queryDef">File containing the query definition.</param>
/// <param name="colSpecs">List containing the column specifications for the query.</param>
public void SyncColSpecsWithSavedValues(pfQueryDef queryDef, PFList <DataTableRandomizerColumnSpec> colSpecs)
{
if (colSpecs == null || queryDef.RandomizerColSpecs == null)
{
return;
}
for (int cs = 0; cs < colSpecs.Count; cs++)
{
for (int qd = 0; qd < queryDef.RandomizerColSpecs.Count; qd++)
{
if (queryDef.RandomizerColSpecs[qd].DataTableColumnName == colSpecs[cs].DataTableColumnName)
{
colSpecs[cs].RandomDataType = queryDef.RandomizerColSpecs[qd].RandomDataType;
colSpecs[cs].RandomDataSource = queryDef.RandomizerColSpecs[qd].RandomDataSource;
colSpecs[cs].RandomNamesAndLocationsNumber = queryDef.RandomizerColSpecs[qd].RandomNamesAndLocationsNumber;
colSpecs[cs].RandomDataFieldName = queryDef.RandomizerColSpecs[qd].RandomDataFieldName;
break;
}
}
}
}
/// <summary>
/// Synchronizes the list of column specifications with the current query definition.
/// </summary>
/// <param name="queryDef">File containing the current query definitiion.</param>
/// <param name="colSpecs">List containing the existing column specifications.</param>
/// <remarks>This routine is used to catch any changes made to a query after a set of column specifications were generated.</remarks>
public void SyncColSpecsWithDataSchema(pfQueryDef queryDef, ref PFList <DataTableRandomizerColumnSpec> colSpecs)
{
PFList <DataTableRandomizerColumnSpec> newColSpecs = new PFList <DataTableRandomizerColumnSpec>();
DataTable dt = null;
if (colSpecs == null || queryDef.RandomizerColSpecs == null)
{
return;
}
try
{
dt = GetQueryDefSchema(queryDef);
if (dt == null)
{
_msg.Length = 0;
_msg.Append("Unable to retrieve query schema in SyncColSpecsWithDataSchema routine for ");
_msg.Append(queryDef.QueryName);
_msg.Append(".");
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString());
return;
}
else if (dt.Columns == null)
{
_msg.Length = 0;
_msg.Append("Unable to retrieve query schema column list in SyncColSpecsWithDataSchema routine for ");
_msg.Append(queryDef.QueryName);
_msg.Append(".");
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString());
return;
}
else if (dt.Columns.Count == 0)
{
_msg.Length = 0;
_msg.Append("Query text must specify one or more data columns for SyncColSpecsWithDataSchema routine.");
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString());
return;
}
else
{
_msg.Length = 0;
_msg.Append("SyncColSpecsWithDataSchema will process queryDef ");
_msg.Append(queryDef.QueryName);
WriteMessageToLog(_msg.ToString());
}
for (int dtColInx = 0; dtColInx < dt.Columns.Count; dtColInx++)
{
DataTableRandomizerColumnSpec newColSpec = new DataTableRandomizerColumnSpec();
for (int colSpecsInx = 0; colSpecsInx < colSpecs.Count; colSpecsInx++)
{
if (dt.Columns[dtColInx].ColumnName.ToLower() == colSpecs[colSpecsInx].DataTableColumnName.ToLower())
{
newColSpec.DataTableColumnName = colSpecs[colSpecsInx].DataTableColumnName;
newColSpec.DataTableColumnIndex = colSpecs[colSpecsInx].DataTableColumnIndex;
newColSpec.DataTableColumnDataType = colSpecs[colSpecsInx].DataTableColumnDataType;
newColSpec.CurrentValueIndex = colSpecs[colSpecsInx].CurrentValueIndex;
newColSpec.RandomDataFieldColumnIndex = colSpecs[colSpecsInx].RandomDataFieldColumnIndex;
newColSpec.RandomDataFieldName = colSpecs[colSpecsInx].RandomDataFieldName;
newColSpec.RandomDataFileName = colSpecs[colSpecsInx].RandomDataFileName;
newColSpec.RandomDataListIndex = colSpecs[colSpecsInx].RandomDataListIndex;
newColSpec.RandomDataSource = colSpecs[colSpecsInx].RandomDataSource;
newColSpec.RandomDataType = colSpecs[colSpecsInx].RandomDataType;
newColSpec.RandomDataTypeProcessorIndex = colSpecs[colSpecsInx].RandomDataTypeProcessorIndex;
newColSpec.RandomNamesAndLocationsNumber = colSpecs[colSpecsInx].RandomNamesAndLocationsNumber;
break;
}
} //end for loop colSpecInx
newColSpecs.Add(newColSpec);
} //end for loop dtColInx
if (dt.Columns.Count != newColSpecs.Count)
{
_msg.Length = 0;
_msg.Append("SyncColSpecsWithDataSchema has failed: DataTable column count does not match count for new column specs list.");
_msg.Append(Environment.NewLine);
_msg.Append("Data Table Column count: ");
_msg.Append(dt.Columns.Count.ToString());
_msg.Append(Environment.NewLine);
_msg.Append("New Column Specs Column count: ");
_msg.Append(newColSpecs.Count.ToString());
_msg.Append(Environment.NewLine);
_msg.Append("Query Definition Name: ");
_msg.Append(queryDef.QueryName);
WriteMessageToLog(_msg.ToString());
throw new System.Exception(_msg.ToString());
}
colSpecs = newColSpecs;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
;
}
} //end method
/// <summary>
/// Displays the form on which randomizing definitions can be attached to query data columns.
/// </summary>
/// <param name="queryDef">File containing the query definition.</param>
/// <returns>List containing the column reandomization specifications.</returns>
public PFList <DataTableRandomizerColumnSpec> ShowRandomizerDefinitionForm(pfQueryDef queryDef)
{
PFList <DataTableRandomizerColumnSpec> colSpecs = null;
DataTable dt = null;
DataTableRandomizer dtr = null;
try
{
_msg.Length = 0;
_msg.Append("ShowRandomizerDefinitionForm started ... ");
WriteMessageToLog(_msg.ToString());
dt = GetQueryDefSchema(queryDef);
if (dt == null)
{
_msg.Length = 0;
_msg.Append("Unable to determine schema for query definition ");
_msg.Append(queryDef.QueryName);
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString());
return(colSpecs);
}
dtr = new DataTableRandomizer();
colSpecs = dtr.GetInitColSpecListFromDataTable(dt);
SyncColSpecsWithSavedValues(queryDef, colSpecs);
SyncColSpecsWithDataSchema(queryDef, ref colSpecs);
PFRandomDataForms.DataTableRandomizerColumnSpecForm frm = new PFRandomDataForms.DataTableRandomizerColumnSpecForm(colSpecs);
frm.ColSpecs = colSpecs;
DialogResult res = frm.ShowDialog();
if (res == DialogResult.OK)
{
colSpecs = frm.ColSpecs;
_msg.Length = 0;
_msg.Append(frm.ColSpecs.ToXmlString());
WriteMessageToLog(_msg.ToString());
}
else
{
_msg.Length = 0;
_msg.Append("Column spec edit cancelled.");
WriteMessageToLog(_msg.ToString());
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("... ShowRandomizerDefinitionForm finished.");
WriteMessageToLog(_msg.ToString());
}
return(colSpecs);
}
/// <summary>
/// Retrieves a DataTable object containing the query schema for the specified query.
/// </summary>
/// <param name="queryDef">File containing query definition.</param>
/// <returns>DataTable object containing the schema information.</returns>
public DataTable GetQueryDefSchema(pfQueryDef queryDef)
{
DataTable dt = null;
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
DatabasePlatform dbPlat = DatabasePlatform.Unknown;
string dbConnStr = string.Empty;
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
try
{
dbPlat = queryDef.DatabaseType;
if (queryDef.DatabaseType == DatabasePlatform.Unknown ||
queryDef.ConnectionString.Trim().Length == 0)
{
_msg.Length = 0;
_msg.Append("You must specify both a data source and a connection string for the query.");
throw new System.Exception(_msg.ToString());
}
if (queryDef.Query.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
dbPlatformDesc = queryDef.DatabaseType.ToString();
connStr = queryDef.ConnectionString;
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
string[] parsedConfig = configValue.Split('|');
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
db.SQLQuery = queryDef.Query;
db.CommandType = CommandType.Text;
dt = db.GetQueryDataSchema(queryDef.Query, CommandType.Text);
dt.TableName = queryDef.QueryName;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
}
return(dt);
}
/// <summary>
/// Routine to execute the specified query.
/// </summary>
/// <param name="queryDef">File containing the query definition.</param>
/// <param name="randomizeOutput">Set to true to enable randomizing of output.</param>
/// <param name="showRowNumber">Set to true to add a row number to the output generated by the query.</param>
public void RunQuery(pfQueryDef queryDef, bool randomizeOutput, bool showRowNumber)
{
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
_msg.Length = 0;
_msg.Append("RunQuery started at ");
_msg.Append(DateTime.Now.ToString());
WriteMessageToLog(_msg.ToString());
sw.Start();
if (queryDef.DatabaseType == DatabasePlatform.Unknown ||
queryDef.ConnectionString.Trim().Length == 0)
{
_msg.Length = 0;
_msg.Append("You must specify both a data source and a connection string for the query.");
throw new System.Exception(_msg.ToString());
}
if (queryDef.Query.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
dbPlatformDesc = queryDef.DatabaseType.ToString();
connStr = queryDef.ConnectionString;
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
string[] parsedConfig = configValue.Split('|');
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
db.SQLQuery = queryDef.Query;
db.CommandType = CommandType.Text;
DataTable tab = db.RunQueryDataTable();
tab.TableName = queryDef.QueryName;
if (randomizeOutput)
{
PFList <DataTableRandomizerColumnSpec> colSpecs = queryDef.RandomizerColSpecs;
PFList <DataTableRandomizerColumnSpec> saveOrigColSpecs = queryDef.RandomizerColSpecs;
SyncColSpecsWithDataSchema(queryDef, ref colSpecs);
queryDef.RandomizerColSpecs = colSpecs;
DataTableRandomizer dtr = new DataTableRandomizer();
dtr.MessageLogUI = this.MessageLogUI;
dtr.RandomizeDataTableValues(tab, queryDef.RandomizerColSpecs, this.BatchSizeForRandomDataGeneration);
queryDef.RandomizerColSpecs = saveOrigColSpecs; //get rid of any changes made during runtime: some of the colspecs fields are used as work fields by the randomizer routines.
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Total time to execute the query: ");
_msg.Append(sw.FormattedElapsedTime);
WriteMessageToLog(_msg.ToString());
OutputResultToGrid(tab, showRowNumber);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteMessageToLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (sw.StopwatchIsRunning)
{
sw.Stop();
}
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
_msg.Length = 0;
_msg.Append("RunQuery ended at ");
_msg.Append(DateTime.Now.ToString());
WriteMessageToLog(_msg.ToString());
}
}