/// <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());
}
}
/// <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);
}
public void DatesAndTimesTest()
{
DataTable dt = new DataTable();
DataTableRandomizer dtr = new DataTableRandomizer();
PFList <DataTableRandomizerColumnSpec> colSpecs = new PFList <DataTableRandomizerColumnSpec>();
int maxOutputRows = 100;
try
{
_msg.Length = 0;
_msg.Append("DatesAndTimesTest started ...\r\n");
_messageLog.WriteLine(_msg.ToString());
DataColumn dc0 = new DataColumn("OrigDateTime", Type.GetType("System.DateTime"));
DataColumn dc1 = new DataColumn("RandomDateUsingRange", Type.GetType("System.DateTime"));
DataColumn dc2 = new DataColumn("RandomDateFromRange", Type.GetType("System.DateTime"));
DataColumn dc3 = new DataColumn("RandomDateToRange", Type.GetType("System.DateTime"));
DataColumn dc4 = new DataColumn("RandomTodayDate", Type.GetType("System.DateTime"));
DataColumn dc5 = new DataColumn("RandomTodayDateOffset", Type.GetType("System.Int32"));
DataColumn dc6 = new DataColumn("RandomDate", Type.GetType("System.DateTime"));
DataColumn dc7 = new DataColumn("RandomDateOffset", Type.GetType("System.Int32"));
dt.Columns.Add(dc0);
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Columns.Add(dc4);
dt.Columns.Add(dc5);
dt.Columns.Add(dc6);
dt.Columns.Add(dc7);
colSpecs = dtr.GetInitColSpecListFromDataTable(dt);
colSpecs[1].RandomDataType = enRandomDataType.RandomDatesAndTimes;
colSpecs[1].RandomDataSource = "Years1930-1939.xml";
colSpecs[4].RandomDataType = enRandomDataType.RandomDatesAndTimes;
colSpecs[4].RandomDataSource = "CurrentDateOffsetPlusMinus10Years.xml";
colSpecs[6].RandomDataType = enRandomDataType.RandomDatesAndTimes;
colSpecs[6].RandomDataSource = "DatesOffsetPlusMinus10Years.xml";
string outputPath = Path.Combine(_defaultDataGridExportFolder, "DateTimeTestColSpecs.xml");
colSpecs.SaveToXmlFile(outputPath);
for (int r = 0; r < maxOutputRows; r++)
{
DataRow dr = dt.NewRow();
dr[0] = Convert.ToDateTime("04/13/1948 00:00:01");
dr[1] = Convert.ToDateTime("04/13/1948 00:00:01");
dr[2] = Convert.ToDateTime("01/01/1930 00:00:00");
dr[3] = Convert.ToDateTime("12/31/1939 23:59:59");
dr[4] = DateTime.Now;
dr[5] = (int)0;
dr[6] = Convert.ToDateTime("04/13/1948 00:00:01");
dr[7] = (int)0;
dt.Rows.Add(dr);
}
dtr.RandomizeDataTableValues(dt, colSpecs, 100);
for (int r = 0; r < maxOutputRows; r++)
{
DataRow dr = dt.Rows[r];
DateTime newDateTime = (DateTime)dr[4];
TimeSpan ts = newDateTime.Subtract(DateTime.Now);
dr[5] = (int)ts.TotalDays;
newDateTime = (DateTime)dr[6];
ts = newDateTime.Subtract(Convert.ToDateTime("04/13/1948 00:00:01"));
dr[7] = (int)ts.TotalDays;
}
dt.AcceptChanges();
OutputDataTableToGrid(dt);
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
_messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
_msg.Length = 0;
_msg.Append("\r\n... DatesAndTimesTest finished.");
_messageLog.WriteLine(_msg.ToString());
}
}