Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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());
        }
Esempio n. 3
0
        /// <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);
        }