Example #1
0
        public void TestRowFilter()
        {
            string     tableName      = "DimCustomer";
            string     dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            PFDatabase db             = null;
            string     connStr        = string.Empty;
            string     nmSpace        = string.Empty;
            string     clsName        = string.Empty;
            string     dllPath        = string.Empty;
            DataTable  dt             = null;
            string     configValue    = string.Empty;
            string     configKey      = string.Empty;

            try
            {
                _msg.Length = 0;
                _msg.Append("TestRowFilter started ...\r\n");
                WriteToMessageLog(_msg.ToString());

                //
                configValue = AppConfig.GetStringValueFromConfigFile("DefaultConnection_SQLServerCE35", string.Empty);
                if (configValue == string.Empty)
                {
                    _msg.Length = 0;
                    _msg.Append("Unable to find config.sys entry for DefaultConnection_SQLServerCE35");
                    throw new System.Exception(_msg.ToString());
                }
                dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
                connStr        = configValue;

                dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
                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();

                configKey   = dbPlatformDesc + "_" + tableName;
                configValue = AppConfig.GetStringValueFromConfigFile(configKey, string.Empty);
                if (configValue == string.Empty)
                {
                    _msg.Length = 0;
                    _msg.Append("Unable to find ");
                    _msg.Append(configKey);
                    _msg.Append(" in config.sys.");
                    AppMessages.DisplayErrorMessage(_msg.ToString());
                    return;
                }
                //db.SQLQuery = "select  CustomerKey ,GeographyKey ,CustomerAlternateKey ,Title ,FirstName ,MiddleName ,LastName ,NameStyle ,BirthDate ,MaritalStatus ,Suffix ,Gender ,EmailAddress ,YearlyIncome ,TotalChildren ,NumberChildrenAtHome ,EnglishEducation ,SpanishEducation ,FrenchEducation ,EnglishOccupation ,SpanishOccupation ,FrenchOccupation ,HouseOwnerFlag ,NumberCarsOwned ,AddressLine1 ,AddressLine2 ,Phone ,DateFirstPurchase ,CommuteDistance from DimCustomer where 1=0";
                db.SQLQuery    = configValue;
                db.CommandType = CommandType.Text;

                //dt = db.GetQueryDataSchema();
                dt = db.RunQueryDataTable();

                DataView dv = new DataView(dt);
                dv.RowFilter = "CustomerKey NOT < 11200 and CustomerKey NOT > 11600";

                _msg.Length = 0;
                _msg.Append("Num rows before view: ");
                _msg.Append(dt.Rows.Count.ToString("#,##0"));
                _msg.Append(Environment.NewLine);
                _msg.Append("Num rows after view:  ");
                _msg.Append(dv.Count.ToString("#,##0"));
                _msg.Append(Environment.NewLine);
                WriteToMessageLog(_msg.ToString());

                DataTable dt2 = dt.AsEnumerable().Skip(0).Take(50).CopyToDataTable();
                _msg.Length = 0;
                _msg.Append("Num rows after AsEnumerable.Take:  ");
                _msg.Append(dt2.Rows.Count.ToString("#,##0"));
                _msg.Append(Environment.NewLine);
                WriteToMessageLog(_msg.ToString());
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                WriteToMessageLog(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db.IsConnected)
                {
                    db.CloseConnection();
                }
                db = null;

                _msg.Length = 0;
                _msg.Append("\r\n... TestRowFilter finished.");
                WriteToMessageLog(_msg.ToString());
            }
        }
        /// <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());
            }
        }
Example #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);
        }
Example #4
0
        public static void CreateTableTest(MainForm frm)
        {
            string           dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            DatabasePlatform dbPlatform     = DatabasePlatform.Unknown;
            PFDatabase       db             = null;
            string           connStr        = string.Empty;
            string           nmSpace        = string.Empty;
            string           clsName        = string.Empty;
            string           dllPath        = string.Empty;
            Stopwatch        sw             = new Stopwatch();

            string              createScript = string.Empty;
            string              tableName    = string.Empty;
            StringBuilder       sql          = new StringBuilder();
            PFUnitTestDataTable unitTestDt01 = null;
            PFUnitTestDataTable unitTestDt02 = null;

            try
            {
                _msg.Length = 0;
                _msg.Append("CreateTableTest started ...\r\n");
                Program._messageLog.WriteLine(_msg.ToString());

                string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|');
                dbPlatformDesc = parsedConnectionInfo[0];
                connStr        = parsedConnectionInfo[1];

                string   configValue  = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
                string[] parsedConfig = configValue.Split('|');
                nmSpace = parsedConfig[0];
                clsName = parsedConfig[1];
                dllPath = parsedConfig[2];

                if (frm.txtTableName.Text.Length == 0)
                {
                    throw new System.Exception("You must specify a table name.");
                }

                _msg.Length = 0;
                _msg.Append("Connecting to ");
                _msg.Append(dbPlatformDesc);
                Program._messageLog.WriteLine(_msg.ToString());


                sw.Start();

                db                  = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
                dbPlatform          = db.DbPlatform;
                db.ConnectionString = connStr;
                db.OpenConnection();

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Open connection time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());


                tableName = frm.txtTableName.Text;

                string   catalogName     = string.Empty;
                string   schemaName      = string.Empty;
                string   tabName         = string.Empty;
                string[] parsedTableName = tableName.Split('.');
                if (parsedTableName.Length == 2)
                {
                    schemaName = parsedTableName[0];
                    tabName    = parsedTableName[1];
                }
                else if (parsedTableName.Length == 1)
                {
                    tabName = parsedTableName[0];
                }
                else if (parsedTableName.Length == 3)
                {
                    catalogName = parsedTableName[0];
                    schemaName  = parsedTableName[1];
                    tabName     = parsedTableName[2];
                }
                else
                {
                    tabName = string.Empty;
                }


                if (db.TableExists(catalogName, schemaName, tabName))
                {
                    bool dropped = db.DropTable(catalogName, schemaName, tabName);
                    if (dropped == false)
                    {
                        _msg.Length = 0;
                        _msg.Append("Unable to drop table ");
                        if (catalogName != string.Empty)
                        {
                            _msg.Append(catalogName);
                            _msg.Append(".");
                        }
                        if (schemaName != string.Empty)
                        {
                            _msg.Append(schemaName);
                            _msg.Append(".");
                        }
                        _msg.Append(tabName);
                        throw new DataException(_msg.ToString());
                    }
                }

                if (db.TableExists(catalogName, schemaName, tabName + "_02"))
                {
                    bool dropped = db.DropTable(catalogName, schemaName, tabName + "_02");
                    if (dropped == false)
                    {
                        _msg.Length = 0;
                        _msg.Append("Unable to drop table ");
                        if (catalogName != string.Empty)
                        {
                            _msg.Append(catalogName);
                            _msg.Append(".");
                        }
                        if (schemaName != string.Empty)
                        {
                            _msg.Append(schemaName);
                            _msg.Append(".");
                        }
                        _msg.Append(tabName + "_02");
                        throw new DataException(_msg.ToString());
                    }
                }


                unitTestDt01 = new PFUnitTestDataTable(db, schemaName, tabName, true);
                unitTestDt02 = new PFUnitTestDataTable(db, schemaName, tabName + "_02", true);


                //---
                _msg.Length = 0;
                _msg.Append("Initializing TableColumns");
                Program._messageLog.WriteLine(_msg.ToString());

                //select which data types to include
                List <KeyValuePair <string, string> > dataTypesToInclude = new List <KeyValuePair <string, string> >();

                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int32", "1"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.String", "this is a string value"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int32", "1123456789"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt32", "3123456789"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int64", "23123456789"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt64", "8881234567889"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Int16", "11123"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.UInt16", "52432"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Double", "123456.7654"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Single", "321.234"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Decimal", "2123456789.22"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Char", "A"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Char[]", "ABCDEFGH"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Byte", "254"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.SByte", "125"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Byte[]", "UVWZYZ));"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Boolean", "true"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Object", "This is an object: be careful!"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.DateTime", "5/31/2013 13:54:25"));
                dataTypesToInclude.Add(new KeyValuePair <string, string>("System.Guid", "58a4a08d-6101-4393-86dc-b2a8db46ec0f"));

                unitTestDt01.SetDataTypesToInclude(dataTypesToInclude);
                unitTestDt01.SetDataTypeOptions("System.String", false, true, 75);


                unitTestDt02.SetDataTypesToInclude(dataTypesToInclude);
                unitTestDt02.SetDataTypeOptions("System.String", false, true, 75000);

                //create the table

                _msg.Length = 0;
                _msg.Append("Creating tables");
                Program._messageLog.WriteLine(_msg.ToString());

                unitTestDt01.CreateTableFromTableColumns();
                unitTestDt02.CreateTableFromTableColumns();
                createScript = unitTestDt01.TableCreateScript;


                //import data to database
                _msg.Length = 0;
                _msg.Append("Importing data to TestTable01");
                Program._messageLog.WriteLine(_msg.ToString());

                unitTestDt01.ImportTableToDatabase();

                _msg.Length = 0;
                _msg.Append("Importing data to TestTable02");
                Program._messageLog.WriteLine(_msg.ToString());

                unitTestDt02.ImportTableToDatabase();


                //retrieve just created table and see what data types get assigned to data table columns

                Program._messageLog.WriteLine("\r\nRead row just created for " + tableName + "\r\n");

                sql.Length = 0;
                sql.Append("select * from ");
                sql.Append(tableName);

                DataTable testTab = db.RunQueryDataTable(sql.ToString(), CommandType.Text);

                for (int c = 0; c < testTab.Columns.Count; c++)
                {
                    _msg.Length = 0;
                    _msg.Append(testTab.Columns[c].ColumnName);
                    _msg.Append(", ");
                    _msg.Append(testTab.Columns[c].DataType.FullName);
                    _msg.Append(", ");
                    _msg.Append(testTab.Columns[c].MaxLength.ToString());
                    Program._messageLog.WriteLine(_msg.ToString());
                }
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... CreateTableTest finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Example #5
0
        public static void DataTableTest(MainForm frm)
        {
            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("DataTableTest started ...\r\n");
                Program._messageLog.WriteLine(_msg.ToString());

                string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|');
                dbPlatformDesc = parsedConnectionInfo[0];
                connStr        = parsedConnectionInfo[1];

                string   configValue  = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
                string[] parsedConfig = configValue.Split('|');
                nmSpace = parsedConfig[0];
                clsName = parsedConfig[1];
                dllPath = parsedConfig[2];

                if (frm.txtSQLQuery.Text.Length == 0)
                {
                    throw new System.Exception("You must specify a SQL query to run.");
                }

                _msg.Length = 0;
                _msg.Append("Connecting to ");
                _msg.Append(dbPlatformDesc);
                Program._messageLog.WriteLine(_msg.ToString());


                sw.Start();

                db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
                db.ConnectionString = connStr;
                db.OpenConnection();

                db.SQLQuery    = frm.txtSQLQuery.Text;
                db.CommandType = CommandType.Text;

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Open connection time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

                sw.Start();

                DataTable tab1 = db.RunQueryDataTable();
                db.returnResult += new PFDatabase.ResultDelegate(OutputResults);
                db.ProcessDataTable(tab1);

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Process Table time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());


                if (_textFile.FileIsOpen)
                {
                    _textFile.CloseFile();
                }
                _textFile.OpenFile(@"c:\temp\TableTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);

                //sw.Start();
                DataTable tab = db.RunQueryDataTable();
                //db.returnResultAsString += new PFdb.ResultAsStringDelegate(OutputExtractFormattedData);
                db.returnResultAsString += new PFDatabase.ResultAsStringDelegate(OutputResultsToFile);
                db.ExtractDelimitedDataFromTable(tab, ",", "\r\n", true);
                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Extract Delimiated Table time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());
                tab = null;
                sw.Start();

                if (_textFile.FileIsOpen)
                {
                    _textFile.CloseFile();
                }
                _textFile.OpenFile(@"c:\temp\TableTestExtractFXL.txt", PFFileOpenOperation.OpenFileForWrite);
                tab = db.RunQueryDataTable();
                db.ExtractFixedLengthDataFromTable(tab, true, true, false);

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Extract Fixed Length Table time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());



                db.SaveDataTableToXmlSchemaFile(tab, @"c:\temp\Testtab.xsd");
                db.SaveDataTableToXmlFile(tab, @"c:\temp\Testtab.xml");
                db.SaveDataTableWithSchemaToXmlFile(tab, @"c:\temp\Testtabplus.xml");
                DataTable tab2    = db.LoadXmlFileToDataTable(@"c:\temp\Testtabplus.xml");;
                int       numRows = tab2.Rows.Count;

                PFDataProcessor dataProcessor = new PFDataProcessor();
                XmlDocument     xmlDoc        = dataProcessor.CopyDataTableToXmlDocument(tab);
                Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... DataTableTest finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Example #6
0
        public static void GetQueryDataSchema(MainForm frm)
        {
            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("GetQueryDataSchema started ...\r\n");
                Program._messageLog.WriteLine(_msg.ToString());

                string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|');
                dbPlatformDesc = parsedConnectionInfo[0];
                connStr        = parsedConnectionInfo[1];

                string   configValue  = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
                string[] parsedConfig = configValue.Split('|');
                nmSpace = parsedConfig[0];
                clsName = parsedConfig[1];
                dllPath = parsedConfig[2];

                if (frm.txtSQLQuery.Text.Length == 0)
                {
                    throw new System.Exception("You must specify a SQL query to run.");
                }

                _msg.Length = 0;
                _msg.Append("Connecting to ");
                _msg.Append(dbPlatformDesc);
                Program._messageLog.WriteLine(_msg.ToString());


                sw.Start();

                db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
                db.ConnectionString = connStr;
                db.OpenConnection();

                db.SQLQuery    = frm.txtSQLQuery.Text;
                db.CommandType = CommandType.Text;

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Open connection time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

                sw.Start();

                DataTable tab = db.RunQueryDataTable();

                foreach (DataColumn col in tab.Columns)
                {
                    _msg.Length = 0;
                    _msg.Append(col.ColumnName);
                    _msg.Append(", ");
                    _msg.Append(col.DataType.ToString());
                    Program._messageLog.WriteLine(_msg.ToString());
                }

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Get Query Data Schema time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... GetQueryDataSchema finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
        private void CopyTables(PFDatabase sourceDb, PFDatabase destinationDb, bool replaceExistingTables, int batchSizeForDataWrites, string outputTablesSchema)
        {
            string    sourceQuery          = string.Empty;
            string    destinationTableName = string.Empty;
            int       numTableCreateErrors = 0;
            int       numImportErrors      = 0;
            Stopwatch sw = new Stopwatch();

            try
            {
                if (sourceDb.DbPlatform == DatabasePlatform.SQLServerCE35)
                {
                    sourceQuery = _sourceQuery.Replace("dbo.", string.Empty);
                }
                else
                {
                    sourceQuery = _sourceQuery;
                }

                _msg.Length = 0;
                _msg.Append("Output Database Platform: ");
                _msg.Append(destinationDb.DbPlatform.ToString());
                _msg.Append(Environment.NewLine);
                Program._messageLog.WriteLine(_msg.ToString());


                sw.Start();
                foreach (stTableInfo tabInfo in _tableInfo)
                {
                    //load data from source database into DataTable
                    string qry         = sourceQuery.Replace(@"<tablename>", tabInfo.tableName);
                    string configValue = AppConfig.GetStringValueFromConfigFile(destinationDb.DbPlatform.ToString() + "_" + tabInfo.tableName, string.Empty);
                    if (configValue.Length > 0)
                    {
                        qry = configValue;
                    }
                    sourceDb.SQLQuery    = qry;
                    sourceDb.CommandType = CommandType.Text;
                    DataTable dt = sourceDb.RunQueryDataTable();
                    _msg.Length = 0;
                    _msg.Append("Query text: ");
                    _msg.Append(sourceDb.SQLQuery);
                    _msg.Append(Environment.NewLine);
                    _msg.Append("Expected number of rows: ");
                    _msg.Append(tabInfo.numRows.ToString("#,##0"));
                    _msg.Append(Environment.NewLine);
                    _msg.Append("Number of rows returned: ");
                    _msg.Append(dt.Rows.Count.ToString("#,##0"));
                    Program._messageLog.WriteLine(_msg.ToString());

                    //import data from DataTable into destination database
                    if (outputTablesSchema.Trim().Length > 0)
                    {
                        destinationTableName = outputTablesSchema + "." + tabInfo.tableName;
                    }
                    else
                    {
                        destinationTableName = tabInfo.tableName;
                    }

                    //workaround to fix issue with Oracle limit on identifier lengths
                    if (destinationDb.DbPlatform == DatabasePlatform.OracleNative || destinationDb.DbPlatform == DatabasePlatform.MSOracle)
                    {
                        if (tabInfo.tableName == "FactAdditionalInternationalProductDescription")
                        {
                            if (outputTablesSchema.Trim().Length > 0)
                            {
                                destinationTableName = outputTablesSchema + "." + "FactProductDescriptionExt";
                            }
                            else
                            {
                                destinationTableName = "FactProductDescriptionExt";
                            }
                        }
                    }

                    dt.TableName = destinationTableName;

                    _msg.Length = 0;
                    _msg.Append(destinationTableName);
                    if (replaceExistingTables)
                    {
                        if (destinationDb.TableExists(destinationTableName))
                        {
                            destinationDb.DropTable(destinationTableName);
                            if (destinationDb.TableExists(destinationTableName) == false)
                            {
                                _msg.Append(" dropped.");
                            }
                            else
                            {
                                _msg.Append(" drop failed.");
                            }
                        }
                        else
                        {
                            _msg.Append(" does not exist.");
                        }
                    }
                    else
                    {
                        _msg.Append(": No check was made to determine if table already existed.");
                    }

                    Program._messageLog.WriteLine(_msg.ToString());

                    Program._messageLog.WriteLine("Creating table in the database ...");

                    //create the table
                    string createScript    = string.Empty;
                    string errorMessages   = string.Empty;
                    bool   createSucceeded = true;
                    createSucceeded = destinationDb.CreateTable(dt, out createScript, out errorMessages);

                    _msg.Length = 0;
                    _msg.Append("Create table result: ");
                    _msg.Append(createSucceeded.ToString());
                    _msg.Append(Environment.NewLine);
                    if (errorMessages.Trim().Length > 0)
                    {
                        _msg.Append("Error Messages: ");
                        _msg.Append(Environment.NewLine);
                        _msg.Append(errorMessages);
                        _msg.Append(Environment.NewLine);
                    }
                    _msg.Append("Create table statement: ");
                    _msg.Append(Environment.NewLine);
                    _msg.Append(createScript);
                    _msg.Append(Environment.NewLine);
                    Program._messageLog.WriteLine(_msg.ToString());

                    if (createSucceeded == false)
                    {
                        numTableCreateErrors++;
                    }
                    else
                    {
                        Program._messageLog.WriteLine("Importing data table to the database ...");
                        _msg.Length = 0;
                        _msg.Append("Data Write batch size: ");
                        _msg.Append(batchSizeForDataWrites.ToString());
                        Program._messageLog.WriteLine(_msg.ToString());

                        try
                        {
                            if (batchSizeForDataWrites == 1)
                            {
                                destinationDb.ImportDataFromDataTable(dt);
                            }
                            else
                            {
                                destinationDb.ImportDataFromDataTable(dt, batchSizeForDataWrites);
                            }

                            _msg.Length = 0;
                            _msg.Append("Table imported: ");
                            _msg.Append(dt.TableName);
                            _msg.Append(Environment.NewLine);
                            Program._messageLog.WriteLine(_msg.ToString());
                        }
                        catch (System.Exception ex)
                        {
                            numImportErrors++;
                            _msg.Length = 0;
                            _msg.Append("ERROR: Data import failed:");
                            _msg.Append(Environment.NewLine);
                            _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                            Program._messageLog.WriteLine(_msg.ToString());
                        }
                        finally
                        {
                            ;
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                _msg.Length = 0;
                if (numTableCreateErrors > 0)
                {
                    _msg.Append("One or more table create errors were reported: ");
                    _msg.Append(numTableCreateErrors.ToString());
                    _msg.Append(Environment.NewLine);
                }
                if (numImportErrors > 0)
                {
                    _msg.Append("One or more data import errors were reported: ");
                    _msg.Append(numImportErrors.ToString());
                    _msg.Append(Environment.NewLine);
                }
                if (_msg.Length > 0)
                {
                    Program._messageLog.WriteLine(_msg.ToString());
                    AppMessages.DisplayErrorMessage(_msg.ToString());
                }
            }
        }