private void OutputRandomDataFile(RandomCustomValuesDataRequest dataRequest, ref DataTable dt, string sqlStatement, int valueInx, int frequencyInx, int adjustedFrequencyInx, int adjustmentNumberInx) { string randomDataFileName = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".xml"); string randomDataSummaryFile = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".clistsum"); string randomDataDefinition = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".clistdef"); string randomDataSQLStatementFile = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".sql"); PFList <string> randomDataList = new PFList <string>(); int frequency = 0; dt.WriteXml(randomDataSummaryFile, XmlWriteMode.WriteSchema); dataRequest.SaveToXmlFile(randomDataDefinition); System.IO.File.WriteAllText(randomDataSQLStatementFile, sqlStatement); for (int i = 0; i < dt.Rows.Count; i++) { frequency = Convert.ToInt32(dt.Rows[i][adjustedFrequencyInx].ToString()); for (int k = 0; k < frequency; k++) { randomDataList.Add(dt.Rows[i][valueInx].ToString()); } } randomDataList.SaveToXmlFile(randomDataFileName); }
private string BuildSQLStatement(RandomCustomValuesDataRequest dataRequest) { StringBuilder sqlStatement = new StringBuilder(); string condition = string.Empty; string criteria = string.Empty; sqlStatement.Length = 0; sqlStatement.Append("select "); sqlStatement.Append(dataRequest.DbFieldName); sqlStatement.Append(", "); sqlStatement.Append("count(*) as Frequency, count(*) as AdjustedFrequency, 1 as AdjustmentNumber "); sqlStatement.Append(Environment.NewLine); sqlStatement.Append(" from "); sqlStatement.Append(dataRequest.DbTableName); sqlStatement.Append(" "); sqlStatement.Append(Environment.NewLine); if (dataRequest.SelectionField.Trim() != string.Empty && dataRequest.SelectionField != "<value frequency>") { if (dataRequest.SelectionCondition.Trim() != string.Empty && dataRequest.SelectionCriteria.Trim() != string.Empty) { sqlStatement.Append(" where "); sqlStatement.Append(dataRequest.SelectionField.Trim()); sqlStatement.Append(" "); switch (dataRequest.SelectionCondition.Trim()) { case "Equal To": condition = "="; break; case "Greater Than": condition = ">"; break; case "Less Than": condition = "<"; break; case "In": condition = "in"; break; case "Like": condition = "like"; break; case "Not Equal To": condition = "!="; break; case "Not Greater Than": condition = "<="; break; case "Not Less Than": condition = ">="; break; case "Not In": condition = "not in"; break; case "Not Like": condition = "not like"; break; default: condition = "="; break; } sqlStatement.Append(condition); sqlStatement.Append(" "); criteria = dataRequest.SelectionCriteria.Replace("\"", "'"); if (dataRequest.SelectionCondition == "In" || dataRequest.SelectionCondition == "Not In") { if (dataRequest.SelectionCriteria.StartsWith("(") == false) { sqlStatement.Append("("); } else { ; //let it be. User has already supplied the required parentheses. } sqlStatement.Append(criteria); if (dataRequest.SelectionCriteria.StartsWith("(") == false) { sqlStatement.Append(")"); } else { ; //let it be. User has already supplied the required parentheses. } } else { sqlStatement.Append(criteria); } sqlStatement.Append(" "); sqlStatement.Append(Environment.NewLine); } } sqlStatement.Append(" group by "); sqlStatement.Append(dataRequest.DbFieldName); sqlStatement.Append(" "); sqlStatement.Append(Environment.NewLine); if (dataRequest.MinimumValueFrequency > 1) { sqlStatement.Append(" having count(*) >= "); sqlStatement.Append(dataRequest.MinimumValueFrequency.ToString()); sqlStatement.Append(" "); sqlStatement.Append(Environment.NewLine); } if (dataRequest.DbPlatform == DatabasePlatform.MSAccess) { sqlStatement.Append(" order by count(*) desc "); } else { sqlStatement.Append(" order by AdjustedFrequency desc "); } return(sqlStatement.ToString()); }
/// <summary> /// Generates a custom random value list based on the criteria supplied in the dataRequest object. /// </summary> /// <param name="dataRequest">Object containing criteria for generating random data values.</param> /// <param name="generateXmlFile">If true, an Xml file will be created containing the generated data values. If false, only the generateSQL and customListDataTableValues will be generated.</param> /// <param name="generatedSQL">Will contain the SQL statement used to generrate the random data values.</param> /// <param name="customListDataTableValues">DataTable containing the custom data values and their frequencies.</param> /// <returns>True if successful.</returns> public bool GenerateCustomRandomDataList(RandomCustomValuesDataRequest dataRequest, bool generateXmlFile, out string generatedSQL, out DataTable customListDataTableValues) { bool success = false; PFDatabase db = null; string sqlStatement = string.Empty; DataTable dt = null; int valueInx = 0; int frequencyInx = 1; int adjustedFrequencyInx = 2; int adjustmentNumberInx = 3; //_msg.Length = 0; //_msg.Append("GenerateCustomRandomDataList not yet implemented."); //_msg.Append(Environment.NewLine); //_msg.Append("+ " + dataRequest.ListName + Environment.NewLine); //_msg.Append("+ " + dataRequest.CustomDataListFolder + Environment.NewLine); //_msg.Append("+ " + dataRequest.DbPlatform.ToString() + Environment.NewLine); //_msg.Append("+ " + dataRequest.DbConnectionString + Environment.NewLine); //_msg.Append("+ " + dataRequest.DbTableName + Environment.NewLine); //_msg.Append("+ " + dataRequest.DbFieldName + Environment.NewLine); //AppMessages.DisplayAlertMessage(_msg.ToString()); //connect to database //build sql statement (include adjustedfrequency and adjustmentnumber columns) //exec sql statement //get total from data table for adjustedfrequency //start with divide by 10 and continue adding 10 until sum of adjustedfrequency is <=1500 // if divide results in zero, set to 1 //Update adjustedfrequency and adjustmentnumber on each row after sum <= 1500 found //write out .xml file with each value repeated ajustedfrequency times //save summary table to an access .rdatasum file (xml format) (or an Access .mdb or an text file (delimited or fixedlength)) //save request definition to a .rdatadef file (xml format) this.DbPlatform = dataRequest.DbPlatform; this.ConnectionString = dataRequest.DbConnectionString; generatedSQL = string.Empty; customListDataTableValues = null; if (this.DbPlatform == DatabasePlatform.Unknown) { return(false); } if (this.ConnectionString.Length == 0) { return(false); } try { db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName); db.ConnectionString = this.ConnectionString; db.OpenConnection(); sqlStatement = BuildSQLStatement(dataRequest); generatedSQL = sqlStatement; dt = db.RunQueryDataTable(sqlStatement, CommandType.Text); dt.TableName = dataRequest.ListName; if (dt.Rows.Count < 1) { _msg.Length = 0; _msg.Append("No rows returned from query for random data: "); _msg.Append(Environment.NewLine); _msg.Append(sqlStatement); _msg.Append(Environment.NewLine); //AppMessages.DisplayWarningMessage(_msg.ToString()); throw new System.Exception(_msg.ToString()); } AdjustFrequencies(ref dt, valueInx, frequencyInx, adjustedFrequencyInx, adjustmentNumberInx); if (generateXmlFile) { OutputRandomDataFile(dataRequest, ref dt, sqlStatement, valueInx, frequencyInx, adjustedFrequencyInx, adjustmentNumberInx); } customListDataTableValues = dt; db.CloseConnection(); success = true; } catch (System.Exception ex) { _msg.Length = 0; _msg.Append("Error occurred while attempting to create custom random data file: "); _msg.Append(Environment.NewLine); _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); throw new System.Exception(_msg.ToString()); } finally { if (db != null) { if (db.IsConnected) { db.CloseConnection(); } db = null; } } return(success); }