/// <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()); } }