Beispiel #1
0
        private bool WriteMessageToDatabaseRetryQueue(PFLogMessage logMessage)
        {
            PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
            bool       logWriteSucceeded = false;

            try
            {
                logWriteSucceeded = false;
            }
            catch
            {
                logWriteSucceeded = false;
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                }
                db = null;
            }

            if (!logWriteSucceeded)
            {
                SaveLogMessageToRetryQueue(logMessage);
            }

            return(logWriteSucceeded);
        }
        /// <summary>
        /// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
        /// </summary>
        /// <param name="connectionString">Connection parameters for the database.</param>
        /// <param name="taskName">Name of the the to retrieve.</param>
        /// <param name="actualStartTime">Actual start time for the history entry to be retrieved.</param>
        /// <returns>TaskHistoryEntry object.</returns>
        public static PFTaskHistoryEntry LoadFromDatabase(string connectionString, string taskName, DateTime actualStartTime)
        {
            string             sqlStmt            = string.Empty;
            PFTaskHistoryEntry objectInstance     = null;
            PFTaskHistoryEntry tempObjectInstance = new PFTaskHistoryEntry();
            PFDatabase         db  = new PFDatabase(DatabasePlatform.SQLServerCE35);
            DbDataReader       rdr = null;
            string             taskHistoryDefXml = string.Empty;

            db.ConnectionString = connectionString;
            db.OpenConnection();

            sqlStmt = tempObjectInstance._taskHistoryDefinitionsSelectTaskEntrySQL.Replace("<taskname>", taskName);
            sqlStmt = sqlStmt.Replace("<rundate>", actualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
            rdr     = db.RunQueryDataReader(sqlStmt, CommandType.Text);
            while (rdr.Read())
            {
                taskHistoryDefXml = rdr.GetString(0);
                objectInstance    = PFTaskHistoryEntry.LoadFromXmlString(taskHistoryDefXml);
                break;  //should be only one record
            }


            return(objectInstance);
        }
Beispiel #3
0
        /// <summary>
        /// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
        /// </summary>
        /// <param name="connectionString">Connection parameters for the database.</param>
        /// <param name="listName">Name of the list in the database.</param>
        /// <returns>PFListEx object.</returns>
        public PFKeyValueList <K, V> LoadFromDatabase(string connectionString, string listName)
        {
            string sqlStmt = string.Empty;
            PFKeyValueList <K, V> objectInstance = null;
            PFDatabase            db             = new PFDatabase(DatabasePlatform.SQLServerCE35);
            DbDataReader          rdr            = null;
            string pfKeyValueListExXml           = string.Empty;

            db.ConnectionString = connectionString;
            db.OpenConnection();

            sqlStmt = _listsSelectSQL.Replace("<listname>", listName);
            rdr     = db.RunQueryDataReader(sqlStmt, CommandType.Text);
            while (rdr.Read())
            {
                pfKeyValueListExXml = rdr.GetString(0);
                objectInstance      = PFKeyValueList <K, V> .LoadFromXmlString(pfKeyValueListExXml);

                break;  //should be only one record
            }

            db.CloseConnection();
            db = null;

            if (objectInstance == null)
            {
                objectInstance = new PFKeyValueList <K, V>();
            }

            return(objectInstance);
        }
Beispiel #4
0
        //properties

        //methods

        /// <summary>
        /// Saves the public property values contained in the current instance to the database specified by the connection string.
        /// </summary>
        /// <param name="kvlist">Key/Value list object to be saved to a database.</param>
        /// <param name="connectionString">Contains information needed to open the database.</param>
        /// <param name="listName">Name of the list in the database.</param>
        public void SaveToDatabase(PFKeyValueList <K, V> kvlist, string connectionString, string listName)
        {
            string     sqlStmt         = string.Empty;
            PFDatabase db              = new PFDatabase(DatabasePlatform.SQLServerCE35);
            int        numRecsAffected = 0;
            DateTime   currdate        = DateTime.Now;
            string     currBatchId     = string.Empty;
            string     listObject      = string.Empty;

            db.ConnectionString = connectionString;
            db.OpenConnection();

            //create batch id for this list
            currBatchId = "'" + Guid.NewGuid().ToString().Trim() + "'";

            listObject = kvlist.ToXmlString().Replace("'", "");      //get rid of any single quotes in the object. they will mess up the sql syntax e.g. values(1, 'two' ,'this is the 'object'')

            //insert current list to the database
            sqlStmt         = _listsInsertSQL.Replace("<listname>", listName).Replace("<id>", currBatchId).Replace("<listobject>", listObject);
            numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);

            //get rid of any previous PFListEx objects in the database
            sqlStmt         = _listsDeleteOldSQL.Replace("<listname>", listName).Replace("<id>", currBatchId);
            numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);


            db.CloseConnection();
            db = null;
        }
Beispiel #5
0
        private bool CreateSdfFile(string filepath)
        {
            bool          fileCreated      = false;
            PFDatabase    db               = null;
            StringBuilder connectionString = new StringBuilder();

            try
            {
                db = new PFDatabase(DatabasePlatform.SQLServerCE40);
                //"data source='C:\Testfiles\Randomizer\TestTables.sdf';"
                connectionString.Length = 0;
                connectionString.Append("data source='");
                connectionString.Append(filepath);
                connectionString.Append("';");
                fileCreated = db.CreateDatabase(connectionString.ToString());
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                AppMessages.DisplayErrorMessage(_msg.ToString());
            }
            finally
            {
                ;
            }

            return(fileCreated);
        }
        private void InitDatabaseObject()
        {
            string connStr = string.Empty;
            string nmSpace = string.Empty;
            string clsName = string.Empty;
            string dllPath = string.Empty;

            string configValue = AppConfig.GetStringValueFromConfigFile(_dbPlatform.ToString(), string.Empty);

            if (configValue.Length > 0)
            {
                string[] parsedConfig = configValue.Split('|');
                if (parsedConfig.Length != 3)
                {
                    _msg.Length = 0;
                    _msg.Append("Invalid config entry items for ");
                    _msg.Append(_dbPlatform.ToString());
                    _msg.Append(". Number of items after parse: ");
                    _msg.Append(parsedConfig.Length.ToString());
                    _msg.Append(".");
                    throw new System.Exception(_msg.ToString());
                }

                nmSpace = parsedConfig[0];
                clsName = parsedConfig[1];
                dllPath = parsedConfig[2];

                _db = new PFDatabase(_dbPlatform.ToString(), dllPath, nmSpace + "." + clsName);
            }
            else
            {
                _db = new PFDatabase(_dbPlatform);
            }
        }
Beispiel #7
0
        private void GetPlatformPropertiesAndKeys()
        {
            DatabasePlatform dbPlat = _frm.DbPlatform;
            PFDatabase       db     = InitDatabaseObject(dbPlat);

            db.ConnectionString = _frm.ConnectionString;
            _connectionDefinition.DbPlatformConnectionStringProperties = db.GetPropertiesForPlatform();
            _connectionDefinition.ConnectionKeyElements = db.ConnectionStringKeyVals;
        }
Beispiel #8
0
        //methods

        /// <summary>
        /// Routine to return a list of table names for the database pointed to by the current connection string.
        /// </summary>
        /// <returns>Object containing list of table definitions.</returns>
        public PFList <PFTableDef> GetTableList(string tablesToIncludePattern, string tablesToExcludePattern)
        {
            PFList <PFTableDef> tableList = null;
            PFDatabase          db        = null;

            string[] includePatterns = { string.Empty };
            string[] excludePatterns = { string.Empty };

            if (this.DbPlatform == DatabasePlatform.Unknown)
            {
                return(null);
            }
            if (this.ConnectionString.Length == 0)
            {
                return(null);
            }


            try
            {
                includePatterns[0] = tablesToIncludePattern.Trim();
                excludePatterns[0] = tablesToExcludePattern.Trim();
                db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
                db.ConnectionString = this.ConnectionString;
                db.OpenConnection();
                tableList = db.GetTableList(includePatterns, excludePatterns);
                db.CloseConnection();
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append("Attempt to retrieve list of table names failed: ");
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                throw new System.Exception(_msg.ToString());
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
            }



            return(tableList);
        }
        /// <summary>
        /// Saves the public property values contained in the current instance to the database specified by the connection string.
        /// </summary>
        /// <param name="connectionString">Contains information needed to open the database.</param>
        /// <remarks>Task name must be unique in the database. SQL Server CE 3.5 local file used for database storage.</remarks>
        public void SaveToDatabase(string connectionString)
        {
            string       sqlStmt         = string.Empty;
            PFDatabase   db              = new PFDatabase(DatabasePlatform.SQLServerCE35);
            DbDataReader rdr             = null;
            int          numRecsFound    = 0;
            int          numRecsAffected = 0;

            db.ConnectionString = connectionString;
            db.OpenConnection();


            //check if already exists
            sqlStmt      = _taskHistoryDefinitionsIfTaskHistoryExistsSQL.Replace("<taskname>", this.TaskName);
            sqlStmt      = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
            rdr          = db.RunQueryDataReader(sqlStmt, CommandType.Text);
            numRecsFound = 0;
            while (rdr.Read())
            {
                numRecsFound = rdr.GetInt32(0);
                break;  //should be only one record
            }

            // if exists update it
            if (numRecsFound > 0)
            {
                //update the record
                sqlStmt         = _taskHistoryDefinitionsUpdateSQL.Replace("<taskname>", this.TaskName);
                sqlStmt         = sqlStmt.Replace("<taskobject>", this.ToXmlString());
                sqlStmt         = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
                numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
            }
            else
            {
                //insert the new record
                sqlStmt         = _taskHistoryDefinitionsInsertSQL.Replace("<taskname>", this.TaskName);
                sqlStmt         = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
                sqlStmt         = sqlStmt.Replace("<taskobject>", this.ToXmlString());
                numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
            }


            db.CloseConnection();
        }
Beispiel #10
0
        private PFList <PFTaskHistoryEntry> GetTaskListDatabase(string taskName)
        {
            PFList <PFTaskHistoryEntry> taskHistoryEntryList = new PFList <PFTaskHistoryEntry>();
            PFDatabase db      = null;
            string     sqlStmt = string.Empty;

            try
            {
                db = new PFDatabase(DatabasePlatform.SQLServerCE35);
                db.ConnectionString = this.ConnectionString;
                db.OpenConnection();

                sqlStmt = _taskHistoryDefinitionsSelectTaskSQL.Replace("<taskname>", taskName);
                DbDataReader rdr = db.RunQueryDataReader(sqlStmt, System.Data.CommandType.Text);

                while (rdr.Read())
                {
                    string             str = rdr["TaskHistoryObject"].ToString();
                    PFTaskHistoryEntry the = PFTaskHistoryEntry.LoadFromXmlString(str);
                    taskHistoryEntryList.Add(the);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                }
            }



            return(taskHistoryEntryList);
        }
        }//end method

        private PFDatabase GetPFDatabaseObject(DatabasePlatform dbPlat)
        {
            string     dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            PFDatabase db             = null;
            string     connStr        = string.Empty;
            string     nmSpace        = string.Empty;
            string     clsName        = string.Empty;
            string     dllPath        = string.Empty;

            dbPlatformDesc = dbPlat.ToString();

            string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);

            if (configValue.Trim() == string.Empty)
            {
                _msg.Length = 0;
                _msg.Append("Unable to find config entry for ");
                _msg.Append(dbPlatformDesc);
                throw new System.Exception(_msg.ToString());
            }
            string[] parsedConfig = configValue.Split('|');
            if (parsedConfig.Length != 3)
            {
                _msg.Length = 0;
                _msg.Append("Invalid config entry items for ");
                _msg.Append(dbPlatformDesc);
                _msg.Append(". Number of items after parse: ");
                _msg.Append(parsedConfig.Length.ToString());
                _msg.Append(".");
                throw new System.Exception(_msg.ToString());
            }

            nmSpace = parsedConfig[0];
            clsName = parsedConfig[1];
            dllPath = parsedConfig[2];

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


            return(db);
        }
Beispiel #12
0
        public static void GetSupportedDatabasesList(MainForm frm)
        {
            PFList <string> dblist = null;

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

                dblist = PFDatabase.GetListOfSupportedDatabases();

                string tab = null;

                dblist.SetToBOF();

                while ((tab = dblist.NextItem) != null)
                {
                    if (tab.ToUpper() != "UNKNOWN")
                    {
                        _msg.Length = 0;
                        _msg.Append(tab);
                        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
            {
                _msg.Length = 0;
                _msg.Append("\r\n... GetSupportedDatabasesList finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Beispiel #13
0
        private PFList <PFSchedule> GetScheduleListDatabase()
        {
            PFList <PFSchedule> scheduleList = new PFList <PFSchedule>();
            PFDatabase          db           = null;

            try
            {
                db = new PFDatabase(DatabasePlatform.SQLServerCE35);
                db.ConnectionString = this.ConnectionString;
                db.OpenConnection();

                DbDataReader rdr = db.RunQueryDataReader(_scheduleDefinitionsSelectAllSQL, System.Data.CommandType.Text);

                while (rdr.Read())
                {
                    string     str      = rdr["ScheduleObject"].ToString();
                    PFSchedule schedule = PFSchedule.LoadFromXmlString(str);
                    scheduleList.Add(schedule);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                }
            }



            return(scheduleList);
        }
        private PFDatabase GetDbObject(DatabasePlatform dbPlatform, string dbConnectionString)
        {
            string     dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            PFDatabase db             = null;
            string     connStr        = string.Empty;
            string     nmSpace        = string.Empty;
            string     clsName        = string.Empty;
            string     dllPath        = string.Empty;

            try
            {
                dbPlatformDesc = dbPlatform.ToString();
                connStr        = dbConnectionString;

                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();
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                ;
            }

            return(db);
        }
Beispiel #15
0
        /// <summary>
        /// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
        /// </summary>
        /// <param name="connectionString">Connection parameters for the database.</param>
        /// <param name="taskName">Name of the task to retrieve.</param>
        /// <returns>Task object.</returns>
        public static PFTask LoadFromDatabase(string connectionString, string taskName)
        {
            string       sqlStmt            = string.Empty;
            PFTask       objectInstance     = null;
            PFTask       tempObjectInstance = new PFTask();
            PFDatabase   db         = new PFDatabase(DatabasePlatform.SQLServerCE35);
            DbDataReader rdr        = null;
            string       taskDefXml = string.Empty;

            db.ConnectionString = connectionString;
            db.OpenConnection();

            sqlStmt = tempObjectInstance._taskDefinitionsSelectTaskSQL.Replace("<taskname>", taskName);
            rdr     = db.RunQueryDataReader(sqlStmt, CommandType.Text);
            while (rdr.Read())
            {
                taskDefXml     = rdr.GetString(0);
                objectInstance = PFTask.LoadFromXmlString(taskDefXml);
                break;  //should be only one record
            }


            return(objectInstance);
        }
Beispiel #16
0
        //tests
        public static void GetTabDefList(MainForm frm)
        {
            //PFTableDefinitions tabDefs = new PFTableDefinitions();
            PFList <PFTableDef> tabDefList = null;
            PFDatabase          db         = null;
            string dbAssemblyPath          = string.Empty;

            string[] includes        = null;
            string[] excludes        = null;
            string[] lineTerminators = { "\r\n", Environment.NewLine };
            string   dbPlatformDesc  = frm.cboSourceDbPlatform.Text;
            string   nmSpace         = string.Empty;
            string   clsName         = string.Empty;
            string   dllPath         = string.Empty;

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

                string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
                if (configValue.Trim() == string.Empty)
                {
                    _msg.Length = 0;
                    _msg.Append("Unable to find config entry for ");
                    _msg.Append(dbPlatformDesc);
                    throw new System.Exception(_msg.ToString());
                }
                string[] parsedConfig = configValue.Split('|');
                if (parsedConfig.Length != 3)
                {
                    _msg.Length = 0;
                    _msg.Append("Invalid config entry items for ");
                    _msg.Append(dbPlatformDesc);
                    _msg.Append(". Number of items after parse: ");
                    _msg.Append(parsedConfig.Length.ToString());
                    _msg.Append(".");
                    throw new System.Exception(_msg.ToString());
                }

                nmSpace = parsedConfig[0];
                clsName = parsedConfig[1];
                dllPath = parsedConfig[2];


                if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString())
                {
                    dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty);
                    db             = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath, nmSpace + "." + clsName);
                }
                else if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString())
                {
                    dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty);
                    db             = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath, nmSpace + "." + clsName);
                }
                else
                {
                    db = new PFDatabase(frm.cboSourceDbPlatform.Text, dllPath, nmSpace + "." + clsName);
                }

                db.ConnectionString = frm.cboSourceDbConnectionString.Text;
                db.OpenConnection();

                if (frm.txtIncludePatterns.Text.Trim().Length > 0)
                {
                    includes = frm.txtIncludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
                    if (includes.Length > 0)
                    {
                        for (int i = 0; i < includes.Length; i++)
                        {
                            if (includes[i].Length == 0)
                            {
                                includes[i] = "ignore this include";
                            }
                        }
                    }
                }
                if (frm.txtExcludePatterns.Text.Trim().Length > 0)
                {
                    excludes = frm.txtExcludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
                    if (excludes.Length > 1)
                    {
                        for (int i = 0; i < excludes.Length; i++)
                        {
                            if (excludes[i].Length == 0)
                            {
                                excludes[i] = "ignore this exclude";
                            }
                        }
                    }
                }

                tabDefList = db.GetTableList(includes, excludes);

                PFTableDef td = null;

                tabDefList.SetToBOF();

                while ((td = tabDefList.NextItem) != null)
                {
                    _msg.Length = 0;
                    _msg.Append(td.TableFullName);
                    if (frm.chkShowTableCreateStatements.Checked)
                    {
                        _msg.Append(":\r\n");
                        _msg.Append(td.TableCreateStatement);
                        _msg.Append("\r\n");
                    }
                    Program._messageLog.WriteLine(_msg.ToString());
                }


                db.CloseConnection();
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                //tabDefs = null;
                tabDefList = null;
                if (db.IsConnected)
                {
                    db.CloseConnection();
                }
                db          = null;
                _msg.Length = 0;
                _msg.Append("\r\n... GetTabDefList finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
        /// <summary>
        /// Retrieves a DataTable object containing the query schema for the specified query.
        /// </summary>
        /// <param name="queryDef">File containing query definition.</param>
        /// <returns>DataTable object containing the schema information.</returns>
        public DataTable GetQueryDefSchema(pfQueryDef queryDef)
        {
            DataTable        dt             = null;
            string           dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            DatabasePlatform dbPlat         = DatabasePlatform.Unknown;
            string           dbConnStr      = string.Empty;
            PFDatabase       db             = null;
            string           connStr        = string.Empty;
            string           nmSpace        = string.Empty;
            string           clsName        = string.Empty;
            string           dllPath        = string.Empty;

            try
            {
                dbPlat = queryDef.DatabaseType;

                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;

                dt           = db.GetQueryDataSchema(queryDef.Query, CommandType.Text);
                dt.TableName = queryDef.QueryName;
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                WriteMessageToLog(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                }
                db = null;
            }

            return(dt);
        }
        /// <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());
            }
        }
Beispiel #19
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);
        }
        /// <summary>
        /// Writes data contained in ADO.NET DataTable object to path stored in OutputFileName property.
        /// </summary>
        /// <param name="dt">DataTable object containing data to be output.</param>
        /// <returns>True if output operation is successful. False if write fails.</returns>
        public bool WriteDataToOutput(DataTable dt)
        {
            bool success = true;
            IDesktopDatabaseProvider db = null;

            string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            string connStr        = string.Empty;
            string nmSpace        = string.Empty;
            string clsName        = string.Empty;
            string dllPath        = string.Empty;

            string templateFile = string.Empty;

            try
            {
                if (File.Exists(_outputFileName))
                {
                    if (_replaceExistingFile)
                    {
                        try
                        {
                            File.SetAttributes(_outputFileName, FileAttributes.Normal);
                            File.Delete(_outputFileName);
                        }
                        catch (System.Exception ex)
                        {
                            _msg.Length = 0;
                            _msg.Append("Unable to delete old file. It may be locked by SQLAnywhere local server. Exit this application and try again. This should remove the lock.");
                            _msg.Append(Environment.NewLine);
                            _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                            throw new System.Exception(_msg.ToString());
                        }
                    }
                    else
                    {
                        _msg.Length = 0;
                        _msg.Append("File exists and ReplaceExistingFile set to False. Write to Output has failed.");
                        throw new System.Exception(_msg.ToString());
                    }
                }

                string oldLogFile = Path.Combine(Path.GetDirectoryName(_outputFileName), Path.GetFileNameWithoutExtension(_outputFileName) + ".Log");
                if (File.Exists(oldLogFile))
                {
                    File.SetAttributes(oldLogFile, FileAttributes.Normal);
                    File.Delete(oldLogFile);
                }

                if (this._desktopDbVersion == enDesktopDbVersion.SQLCE_Version40)
                {
                    templateFile   = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLCE40TemplateFile);
                    dbPlatformDesc = DatabasePlatform.SQLServerCE40.ToString();
                }
                else if (this._desktopDbVersion == enDesktopDbVersion.SQLAnywhere)
                {
                    templateFile   = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLAnywhereTemplateFile);
                    dbPlatformDesc = DatabasePlatform.SQLAnywhere.ToString();
                }
                else if (this._desktopDbVersion == enDesktopDbVersion.SQLAnywhere_UltraLite)
                {
                    templateFile   = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLAnywhereUltraLiteTemplateFile);
                    dbPlatformDesc = DatabasePlatform.SQLAnywhereUltraLite.ToString();
                }
                else
                {
                    templateFile   = Path.Combine(_defaultDbTemplatesFolder, _defaultSQLCE35TemplateFile);
                    dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
                }

                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);

                if (_desktopDbVersion == enDesktopDbVersion.SQLCE_Version35 ||
                    _desktopDbVersion == enDesktopDbVersion.SQLCE_Version40)
                {
                    db.DatabasePath = _outputFileName;
                    connStr         = db.ConnectionString;
                    db.CreateDatabase(connStr);
                }
                else if (_desktopDbVersion == enDesktopDbVersion.SQLAnywhere_UltraLite)
                {
                    connStr = OutputFileName;
                    db.CreateDatabase(connStr);
                    connStr             = _defaultSQLAnywhereUltraLiteConnectionString.Replace("<filename>", OutputFileName);
                    db.ConnectionString = connStr;
                }
                else if (_desktopDbVersion == enDesktopDbVersion.SQLAnywhere)
                {
                    if (File.Exists(templateFile))
                    {
                        connStr             = _defaultSQLAnywhereConnectionString.Replace("<filename>", templateFile);
                        db.ConnectionString = connStr;
                        db.OpenConnection();
                        db.CreateDatabase(OutputFileName);
                        db.CloseConnection();
                        //db.CreateDatabase(OutputFileName, templateFile);   //file copy create not working properly: transaction log lock outs.
                        connStr             = _defaultSQLAnywhereConnectionString.Replace("<filename>", OutputFileName);
                        db.ConnectionString = connStr;
                    }
                    else
                    {
                        _msg.Length = 0;
                        _msg.Append("Unable to find template file for SQLAnywhere databases: ");
                        _msg.Append(templateFile);
                        _msg.Append(".");
                        throw new System.Exception(_msg.ToString());
                    }
                }
                else
                {
                    _msg.Length = 0;
                    _msg.Append("Invalid or unexpected desktop database platform: ");
                    _msg.Append(_desktopDbVersion);
                    _msg.Append(".");
                    throw new System.Exception(_msg.ToString());
                }
                db.OpenConnection();
                db.CreateTable(dt);
                db.CloseConnection();
                db.OpenConnection();
                db.ImportDataFromDataTable(dt);   //this is very slow for SQLAnywhere UltraLite
                db.CloseConnection();
            }
            catch (System.Exception ex)
            {
                success     = false;
                _msg.Length = 0;
                _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);
        }
Beispiel #21
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>
        /// Writes data contained in DataTable to table stored in TableName property.
        /// </summary>
        /// <param name="dt">DataTable object containing data to be output.</param>
        /// <returns>True if output operation is successful. False if write fails.</returns>
        public bool WriteDataToOutput(DataTable dt)
        {
            bool       success       = true;
            PFDatabase db            = null;
            bool       createTable   = false;
            DataTable  saveDtSchema  = null;
            string     tableName     = string.Empty;
            bool       isOracleOdbc  = false;
            bool       isOracleOledb = false;

            try
            {
                //save column names that may be changed during export processing so that they can be restored in finally block below
                saveDtSchema = dt.Clone();

                db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
                db.ConnectionString = this.ConnectionString;

                tableName = this.TableName;

                //workaround for oracle odbc and oledb driver problems with inserts
                if (this.DbPlatform == DatabasePlatform.ODBC)
                {
                    PFOdbc odbcDb = new PFOdbc();
                    odbcDb.ConnectionString = this.ConnectionString;
                    DatabasePlatform odbcDbPlat = odbcDb.GetDatabasePlatform();
                    if (odbcDbPlat == DatabasePlatform.OracleNative || odbcDbPlat == DatabasePlatform.MSOracle)
                    {
                        isOracleOdbc = true;
                    }
                    odbcDb = null;
                }

                if (this.DbPlatform == DatabasePlatform.OLEDB)
                {
                    PFOleDb oledbDb = new PFOleDb();
                    oledbDb.ConnectionString = this.ConnectionString;
                    DatabasePlatform oledbDbPlat = oledbDb.GetDatabasePlatform();
                    if (oledbDbPlat == DatabasePlatform.OracleNative || oledbDbPlat == DatabasePlatform.MSOracle)
                    {
                        isOracleOledb = true;
                    }
                    oledbDb = null;
                }

                //set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
                if (isOracleOdbc || isOracleOledb)
                {
                    tableName = this.TableName.ToUpper();
                    for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
                    {
                        dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
                    }
                }

                //end workaround for oracle odbc and oledb

                dt.TableName = tableName;

                if (db.TableExists(tableName))
                {
                    if (this.ReplaceExistingTable)
                    {
                        db.OpenConnection();
                        db.DropTable(tableName);
                        db.CloseConnection();
                        createTable = true;
                    }
                    else
                    {
                        createTable = false;  //existing table will be imported into
                    }
                }
                else
                {
                    createTable = true;
                }

                if (createTable)
                {
                    db.OpenConnection();
                    string createScript  = string.Empty;
                    string errorMessages = string.Empty;
                    bool   tabCreated    = db.CreateTable(dt, out createScript, out errorMessages);
                    if (tabCreated == false)
                    {
                        _msg.Length = 0;
                        _msg.Append("Unable to create table: ");
                        _msg.Append(tableName);
                        _msg.Append(Environment.NewLine);
                        _msg.Append("Error Messages: ");
                        _msg.Append(errorMessages);
                        _msg.Append(Environment.NewLine);
                        _msg.Append("Create Script: ");
                        _msg.Append(createScript);
                        throw new System.Exception(_msg.ToString());
                    }
                    db.CloseConnection();
                }

                db.OpenConnection();
                db.ImportDataFromDataTable(dt, this.OutputBatchSize);
                db.CloseConnection();
            }
            catch (System.Exception ex)
            {
                success     = false;
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                throw new System.Exception(_msg.ToString());
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                //resstore any column names that may have been changed during export processing
                for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
                {
                    dt.Columns[colInx].ColumnName = saveDtSchema.Columns[colInx].ColumnName;
                }
            }

            return(success);
        }
        /// <summary>
        /// Writes data contained in DataTable to table stored in TableName property.
        /// </summary>
        /// <param name="dtList">List of temp file names containing data tables with grid rows to be output.</param>
        /// <returns>True if output operation is successful. False if write fails.</returns>
        public bool WriteDataToOutput(PFList <string> dtList)
        {
            bool       success       = true;
            PFDatabase db            = null;
            bool       createTable   = false;
            DataTable  dt            = new DataTable();
            string     tableName     = string.Empty;
            bool       isOracleOdbc  = false;
            bool       isOracleOledb = false;

            try
            {
                if (dtList.Count == 0)
                {
                    _msg.Length = 0;
                    _msg.Append("WriteDataToOutput for list of temp files has failed.");
                    _msg.Append(Environment.NewLine);
                    _msg.Append("Temp file name list is empty.");
                    throw new System.Exception(_msg.ToString());
                }

                //Load first temp file in list to get data table schema
                dt.Rows.Clear();
                dt.ReadXml(dtList[0]);

                db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
                db.ConnectionString = this.ConnectionString;

                tableName = this.TableName;

                //workaround for oracle odbc and oledb driver problems with inserts
                if (this.DbPlatform == DatabasePlatform.ODBC)
                {
                    PFOdbc odbcDb = new PFOdbc();
                    odbcDb.ConnectionString = this.ConnectionString;
                    DatabasePlatform odbcDbPlat = odbcDb.GetDatabasePlatform();
                    if (odbcDbPlat == DatabasePlatform.OracleNative || odbcDbPlat == DatabasePlatform.MSOracle)
                    {
                        isOracleOdbc = true;
                    }
                    odbcDb = null;
                }

                if (this.DbPlatform == DatabasePlatform.OLEDB)
                {
                    PFOleDb oledbDb = new PFOleDb();
                    oledbDb.ConnectionString = this.ConnectionString;
                    DatabasePlatform oledbDbPlat = oledbDb.GetDatabasePlatform();
                    if (oledbDbPlat == DatabasePlatform.OracleNative || oledbDbPlat == DatabasePlatform.MSOracle)
                    {
                        isOracleOledb = true;
                    }
                    oledbDb = null;
                }

                //set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
                if (isOracleOdbc || isOracleOledb)
                {
                    tableName = this.TableName.ToUpper();
                    for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
                    {
                        dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
                    }
                }
                else
                {
                    tableName = this.TableName;
                }

                //end workaround for oracle odbc and oledb



                dt.TableName = tableName;

                if (db.TableExists(tableName))
                {
                    if (this.ReplaceExistingTable)
                    {
                        db.OpenConnection();
                        db.DropTable(tableName);
                        db.CloseConnection();
                        createTable = true;
                    }
                    else
                    {
                        createTable = false;  //existing table will be imported into
                    }
                }
                else
                {
                    createTable = true;
                }

                if (createTable)
                {
                    db.OpenConnection();
                    string createScript  = string.Empty;
                    string errorMessages = string.Empty;
                    bool   tabCreated    = db.CreateTable(dt, out createScript, out errorMessages);
                    if (tabCreated == false)
                    {
                        _msg.Length = 0;
                        _msg.Append("Unable to create table: ");
                        _msg.Append(tableName);
                        _msg.Append(Environment.NewLine);
                        _msg.Append("Error Messages: ");
                        _msg.Append(errorMessages);
                        _msg.Append(Environment.NewLine);
                        _msg.Append("Create Script: ");
                        _msg.Append(createScript);
                        throw new System.Exception(_msg.ToString());
                    }
                    db.CloseConnection();
                }

                for (int dtInx = 0; dtInx < dtList.Count; dtInx++)
                {
                    _msg.Length = 0;
                    _msg.Append("List # ");
                    _msg.Append(dtInx.ToString());
                    Console.WriteLine(_msg.ToString());
                    dt           = new DataTable();
                    dt.TableName = this.TableName;  //original table name should be in xml file definition
                    dt.Rows.Clear();
                    dt.ReadXml(dtList[dtInx]);
                    db.OpenConnection();
                    //set table and column names to upper case if this is an Oracle ODBC driver or OLEDB provider
                    if (isOracleOdbc || isOracleOledb)
                    {
                        dt.TableName = this.TableName.ToUpper();
                        for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
                        {
                            dt.Columns[colInx].ColumnName = dt.Columns[colInx].ColumnName.ToUpper();
                        }
                    }
                    db.ImportDataFromDataTable(dt, this.OutputBatchSize);
                    db.CloseConnection();
                    dt = null;
                }
            }
            catch (System.Exception ex)
            {
                success     = false;
                _msg.Length = 0;
                _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);
        }
Beispiel #24
0
        public static void ConvertTableDefs(MainForm frm)
        {
            Stopwatch           sw                          = new Stopwatch();
            string              dbAssemblyPath              = string.Empty;
            PFDatabase          sourceDb                    = null;
            string              sourceConnectionString      = frm.cboSourceDbConnectionString.Text;
            PFDatabase          destDb                      = null;
            string              destinationConnectionString = frm.cboDestinationDbConnectionString.Text;
            PFList <PFTableDef> tabDefList                  = null;

            string[] includes        = null;
            string[] excludes        = null;
            string[] lineTerminators = { "\r\n", Environment.NewLine };

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

                if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString())
                {
                    dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty);
                    sourceDb       = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath);
                }
                else if (frm.cboSourceDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString())
                {
                    dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty);
                    sourceDb       = new PFDatabase(frm.cboSourceDbPlatform.Text, dbAssemblyPath);
                }
                else
                {
                    sourceDb = new PFDatabase(frm.cboSourceDbPlatform.Text);
                }

                if (frm.cboDestinationDbPlatform.Text == DatabasePlatform.SQLServerCE35.ToString())
                {
                    dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V35_AssemblyPath", string.Empty);
                    destDb         = new PFDatabase(frm.cboDestinationDbPlatform.Text, dbAssemblyPath);
                }
                else if (frm.cboDestinationDbPlatform.Text == DatabasePlatform.SQLServerCE40.ToString())
                {
                    dbAssemblyPath = AppConfig.GetStringValueFromConfigFile("SQLServerCE_V40_AssemblyPath", string.Empty);
                    destDb         = new PFDatabase(frm.cboDestinationDbPlatform.Text, dbAssemblyPath);
                }
                else
                {
                    destDb = new PFDatabase(frm.cboDestinationDbPlatform.Text);
                }



                sourceDb.ConnectionString = sourceConnectionString;
                sourceDb.OpenConnection();

                if (frm.txtIncludePatterns.Text.Trim().Length > 0)
                {
                    includes = frm.txtIncludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
                }
                if (frm.txtExcludePatterns.Text.Trim().Length > 0)
                {
                    excludes = frm.txtExcludePatterns.Text.Split(lineTerminators, StringSplitOptions.None);
                }

                tabDefList = sourceDb.GetTableList(includes, excludes);

                destDb.ConnectionString = destinationConnectionString;
                destDb.OpenConnection();

                PFList <PFTableDef> newTableDefs = destDb.ConvertTableDefs(tabDefList, frm.txtNewSchema.Text.Trim());
                PFTableDef          newtd        = null;

                newTableDefs.SetToBOF();

                while ((newtd = newTableDefs.NextItem) != null)
                {
                    _msg.Length = 0;
                    _msg.Append(newtd.TableFullName);
                    if (frm.chkShowTableCreateStatements.Checked)
                    {
                        _msg.Append(":\r\n");
                        _msg.Append(newtd.TableCreateStatement);
                        _msg.Append("\r\n");
                    }
                    Program._messageLog.WriteLine(_msg.ToString());
                }

                if (frm.chkRunConvertedTableCreateStatements.Checked)
                {
                    sw.Start();

                    newTableDefs.SetToBOF();
                    int numTabsCreated = destDb.CreateTablesFromTableDefs(newTableDefs, true);

                    sw.Stop();

                    _msg.Length = 0;
                    _msg.Append("\r\nNumber of tables created: \r\n");
                    _msg.Append(numTabsCreated.ToString());
                    _msg.Append("\r\n");
                    _msg.Append("Elapsed time: ");
                    _msg.Append(sw.FormattedElapsedTime);
                    Program._messageLog.WriteLine(_msg.ToString());


                    sw.Stop();


                    if (frm.chkImportDataFromSourceToDestination.Checked)
                    {
                        sw.Start();

                        PFList <TableCopyDetails> tableCopyLog = destDb.CopyTableDataFromTableDefs(sourceDb, includes, null, frm.txtNewSchema.Text.Trim(), true);

                        sw.Stop();

                        tableCopyLog.SetToBOF();
                        TableCopyDetails tcdetails = null;

                        while ((tcdetails = tableCopyLog.NextItem) != null)
                        {
                            _msg.Length = 0;
                            _msg.Append("Table: ");
                            _msg.Append(tcdetails.destinationTableName);
                            _msg.Append(", NumRowsCopied: ");
                            _msg.Append(tcdetails.numRowsCopied.ToString("#,##0"));
                            if (tcdetails.result != TableCopyResult.Success)
                            {
                                _msg.Append("\r\n    ");
                                _msg.Append("Result: ");
                                _msg.Append(tcdetails.result.ToString());
                                _msg.Append("  Messages: ");
                                _msg.Append(tcdetails.messages);
                            }
                            Program._messageLog.WriteLine(_msg.ToString());
                        }
                        _msg.Length = 0;
                        _msg.Append("Elapsed time: ");
                        _msg.Append(sw.FormattedElapsedTime);
                        Program._messageLog.WriteLine(_msg.ToString());
                    } //end import data routine
                }     //end table create routine
            }
            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 (sourceDb != null)
                {
                    if (sourceDb.IsConnected)
                    {
                        sourceDb.CloseConnection();
                    }
                }
                if (destDb != null)
                {
                    if (destDb.IsConnected)
                    {
                        destDb.CloseConnection();
                    }
                }
                sourceDb    = null;
                destDb      = null;
                _msg.Length = 0;
                _msg.Append("\r\n... ConvertTableDefs finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Beispiel #25
0
        public static void GetSQLServerTables(MainForm frm)
        {
            Stopwatch sw = new Stopwatch();

            string      sourceConnectionString = @"Data Source=PROFASTWS3; Initial Catalog=AdventureWorksDW2008R2; Integrated Security=True; Application Name=TestprogGetSchemas; Workstation ID=PROFASTWS5;";
            PFSQLServer sourceDb = new PFSQLServer();

            string      destinationConnectionString = @"Data Source=PROFASTSV2; Initial Catalog=AWTest; Integrated Security=True; Application Name=TestprogGetSchemas; Workstation ID=PROFASTWS5;";
            PFSQLServer destDb = new PFSQLServer();

            PFTableDefinitions tabdefs = new PFTableDefinitions();

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

                sourceDb.ConnectionString = sourceConnectionString;
                sourceDb.OpenConnection();


                Program._messageLog.WriteLine("Get all table names:\r\n");

                string[] includes = new string[4];
                includes[0] = "dbo.DimDate";
                includes[1] = "dbo.DimC*";
                includes[2] = "dbo.DimGeography";
                includes[3] = "dbo.FactCurrencyRate";

                //PFList<PFTableDef> tableDefs = sourceDb.GetTableList();
                PFList <PFTableDef> tableDefs = tabdefs.GetTableList(sourceDb);
                //PFList<PFTableDef> tableDefs = tabdefs.GetTableList(sourceDb);
                PFTableDef td = null;

                tableDefs.SetToBOF();

                while ((td = tableDefs.NextItem) != null)
                {
                    _msg.Length = 0;
                    _msg.Append(td.TableFullName);
                    _msg.Append(":\r\n");
                    _msg.Append(td.TableCreateStatement);
                    _msg.Append("\r\n");
                    Program._messageLog.WriteLine(_msg.ToString());
                }



                _msg.Length = 0;
                _msg.Append("\r\nConverted Table Defs follow: \r\n");
                Program._messageLog.WriteLine(_msg.ToString());

                destDb.ConnectionString = destinationConnectionString;
                destDb.OpenConnection();

                PFList <PFTableDef> newTableDefs = destDb.ConvertTableDefs(tableDefs, "xyz");
                PFTableDef          newtd        = null;

                newTableDefs.SetToBOF();

                while ((newtd = newTableDefs.NextItem) != null)
                {
                    _msg.Length = 0;
                    _msg.Append(newtd.TableFullName);
                    _msg.Append(":\r\n");
                    _msg.Append(newtd.TableCreateStatement);
                    _msg.Append("\r\n");
                    Program._messageLog.WriteLine(_msg.ToString());
                }

                _msg.Length = 0;
                _msg.Append("\r\nTesting table creates: \r\n");
                Program._messageLog.WriteLine(_msg.ToString());

                sw.Start();

                newTableDefs.SetToBOF();
                int numTabsCreated = destDb.CreateTablesFromTableDefs(newTableDefs, true);

                sw.Stop();

                _msg.Length = 0;
                _msg.Append("\r\nNumber of tables created: \r\n");
                _msg.Append(numTabsCreated.ToString());
                _msg.Append("\r\n");
                _msg.Append("Elapsed time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

                _msg.Length = 0;
                _msg.Append("\r\nTesting table copies: \r\n");
                Program._messageLog.WriteLine(_msg.ToString());

                sw.Start();

                newTableDefs.SetToBOF();
                PFDatabase dbtemp = new PFDatabase(DatabasePlatform.MSSQLServer);
                dbtemp.ConnectionString = sourceDb.ConnectionString;
                dbtemp.OpenConnection();
                //PFList<TableCopyDetails> tableCopyLog = destDb.CopyTableDataFromTableDefs(dbtemp, includes, null, "xyz", true);
                PFList <TableCopyDetails> tableCopyLog = destDb.CopyTableDataFromTableDefs(dbtemp, null, null, "xyz", true);

                dbtemp.CloseConnection();

                sw.Stop();


                _msg.Length = 0;
                _msg.Append("\r\nTable copies finished: \r\n");
                _msg.Append("Elapsed time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

                tableCopyLog.SetToBOF();
                TableCopyDetails tcdetails = null;

                while ((tcdetails = tableCopyLog.NextItem) != null)
                {
                    _msg.Length = 0;
                    _msg.Append("Table: ");
                    _msg.Append(tcdetails.destinationTableName);
                    _msg.Append(", NumRowsCopied: ");
                    _msg.Append(tcdetails.numRowsCopied.ToString("#,##0"));
                    if (tcdetails.result != TableCopyResult.Success)
                    {
                        _msg.Append("\r\n    ");
                        _msg.Append("Result: ");
                        _msg.Append(tcdetails.result.ToString());
                        _msg.Append("  Messages: ");
                        _msg.Append(tcdetails.messages);
                    }
                    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 (sourceDb.IsConnected)
                {
                    sourceDb.CloseConnection();
                }
                if (destDb.IsConnected)
                {
                    destDb.CloseConnection();
                }
                sourceDb    = null;
                destDb      = null;
                _msg.Length = 0;
                _msg.Append("\r\n... GetSQLServerTables finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Beispiel #26
0
        private bool WriteMessageToDatabaseLog(PFLogMessage logMessage, bool saveFailedWriteToRetryQueue)
        {
            PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
            bool       logWriteSucceeded = false;
            string     sqlStmt           = string.Empty;
            string     messageText       = string.Empty;
            string     logObject         = string.Empty;

            try
            {
                db.ConnectionString = _logFileConnectionString;
                db.OpenConnection();

                if (logMessage.LogMessageDateTime == DateTime.MinValue)
                {
                    logMessage.LogMessageDateTime = DateTime.Now;
                }
                messageText = logMessage.MessageText.Replace("'", "");
                logObject   = logMessage.ToXmlString().Replace("'", "");

                sqlStmt = _dbLogInsertStatement.Replace("<LogEntryDateTime>", "'" + logMessage.LogMessageDateTime.ToString("MM/dd/yyyy HH:mm:ss") + "'")
                          .Replace("<ApplicationName>", "'" + logMessage.ApplicationName + "'")
                          .Replace("<MachineName>", "'" + logMessage.MachineName + "'")
                          .Replace("<Username>", "'" + logMessage.Username + "'")
                          .Replace("<MessageLevel>", "'" + logMessage.LogMessageType.ToString() + "'")
                          .Replace("<MessageText>", "'" + messageText + "'")
                          .Replace("<LogMessageObject>", "'" + logObject + "'")
                ;

                int numRecsAffected = db.RunNonQuery(sqlStmt, System.Data.CommandType.Text);

                if (numRecsAffected > 0)
                {
                    logWriteSucceeded = true;
                }
                else
                {
                    logWriteSucceeded = false;
                }
            }
            catch
            {
                logWriteSucceeded = false;
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                }
                db = null;
            }

            if (!logWriteSucceeded && saveFailedWriteToRetryQueue)
            {
                SaveLogMessageToRetryQueue(logMessage);
            }

            return(logWriteSucceeded);
        }
        public void ColSpecForm()
        {
            DataTable dt = null;
            TEST_DataTableRandomizer dtr = null;
            string           dbConnStr   = string.Empty;
            DatabasePlatform dbPlat      = DatabasePlatform.Unknown;
            PFDatabase       db          = null;
            PFList <DataTableRandomizerColumnSpec> colSpecs            = null;
            RandomNamesAndLocationsDataRequest     randomizerNameSpecs = null;

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

                dbPlat = DatabasePlatform.SQLServerCE35;

                db = GetPFDatabaseObject(dbPlat);
                db.ConnectionString = GetConnectionString(dbPlat);

                randomizerNameSpecs = RandomNamesAndLocationsDataRequest.LoadFromXmlFile(@"C:\Testfiles\Randomizer\CountryRequestPersonsOnly.xml");

                db.OpenConnection();

                string sqlQuery = "select * from RandomNameData";
                //listTable = db.RunQueryDataTable(sqlQuery, CommandType.Text);
                dt = db.GetQueryDataSchema(sqlQuery, CommandType.Text);

                dtr      = new TEST_DataTableRandomizer();
                colSpecs = dtr.GetInitColSpecListFromDataTable(dt);

                PFRandomDataForms.DataTableRandomizerColumnSpecForm frm = new PFRandomDataForms.DataTableRandomizerColumnSpecForm(colSpecs);
                DialogResult res = frm.ShowDialog();
                if (res == DialogResult.OK)
                {
                    //time to do something
                    _msg.Length = 0;
                    _msg.Append(frm.ColSpecs.ToXmlString());
                    _messageLog.WriteLine(_msg.ToString());
                }
                else
                {
                    _msg.Length = 0;
                    _msg.Append("Column spec edit cancelled.");
                    _messageLog.WriteLine(_msg.ToString());
                }
            }
            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... ColSpecForm finished.");
                _messageLog.WriteLine(_msg.ToString());
            }
        }
Beispiel #28
0
        //application routines

        public void ShowFixedLenInputColSpecForm(string tableName)
        {
            PFFixedLenColDefsInputForm frm        = new PFFixedLenColDefsInputForm();
            DialogResult           res            = DialogResult.None;
            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;
            PFColumnDefinitionsExt colDefsExt     = null;
            string                 configValue    = string.Empty;
            string                 configKey      = string.Empty;

            try
            {
                _msg.Length = 0;
                _msg.Append("ShowFixedLenInputColSpecForm 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();

                //colDefsExt = PFColumnDefinitionsExt.GetColumnDefinitionsExt(dt);

                colDefsExt = new PFColumnDefinitionsExt(1);

                frm.ColDefs = colDefsExt;

                frm.MessageLogUI = _messageLog;
                res = frm.ShowDialog();

                colDefsExt = frm.ColDefs;

                _msg.Length = 0;
                _msg.Append("Form closed with DialogResult = ");
                _msg.Append(res.ToString());
                WriteToMessageLog(_msg.ToString());

                if (res == DialogResult.OK)
                {
                    _msg.Length = 0;
                    _msg.Append(Environment.NewLine);
                    _msg.Append("COLUMN DEFINITIONS:\r\n\r\n");
                    _msg.Append(colDefsExt.ToXmlString());
                    _msg.Append(Environment.NewLine);
                    _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;

                if (frm != null)
                {
                    if (FormIsOpen(frm.Name))
                    {
                        frm.Close();
                    }
                }
                frm = null;

                _msg.Length = 0;
                _msg.Append("\r\n... ShowFixedLenInputColSpecForm finished.");
                WriteToMessageLog(_msg.ToString());
            }
        }
        /// <summary>
        /// Routine to copy data from a fixed length data text file to a database table.
        /// </summary>
        /// <param name="dataFileName">Path to file containing the data.</param>
        /// <param name="lineDefFileName">Path to file containing the PFFixedLengthDataLine object that encapsulates the line formatting information for the specified data file.</param>
        /// <param name="db">Database object for the target database.</param>
        /// <param name="tableName">Name of table in database to which data is to be copied.</param>
        /// <param name="updateBatchSize">Number of individual SQL modification statements to include in a table modification operation.</param>
        /// <returns>Number of rows uploaded to the database.</returns>
        public int ImportFixedLengthTextFileToDatabase(string dataFileName, string lineDefFileName, PFDatabase db, string tableName, int updateBatchSize)
        {
            DataTable dt = ImportFixedLengthTextFileToDataTable(dataFileName, lineDefFileName);

            dt.TableName = tableName;

            db.ImportDataFromDataTable(dt, updateBatchSize);

            return(dt.Rows.Count);
        }
Beispiel #30
0
        public static void ImportTableTest(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
            {
                sw.Start();
                _msg.Length = 0;
                _msg.Append("ImportTableTest 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];

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

                string tableName = frm.txtKeyValsTableName.Text;

                DataTable tab = frm.KeyValTable;
                tab.TableName = tableName;

                _msg.Length = 0;
                _msg.Append(tableName);
                if (db.TableExists(tableName))
                {
                    db.DropTable(tableName);
                    if (db.TableExists(tableName) == false)
                    {
                        _msg.Append(" dropped.");
                    }
                    else
                    {
                        _msg.Append(" drop failed.");
                    }
                }
                else
                {
                    _msg.Append(" does not exist.");
                }

                Program._messageLog.WriteLine("\r\nCreating a table in the database ...");

                //create the table
                db.CreateTable(tab);

                Program._messageLog.WriteLine("\r\nImporting data table to the database ...");

                int batchSize = Convert.ToInt32(frm.txtUpdateBatchSize.Text);
                if (batchSize == 1)
                {
                    db.ImportDataFromDataTable(tab);
                }
                else
                {
                    db.ImportDataFromDataTable(tab, batchSize);
                }

                _msg.Length = 0;
                _msg.Append("Table imported: ");
                _msg.Append(tab.TableName);
                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 (sw.StopwatchIsRunning)
                {
                    sw.Stop();
                    sw.ShowMilliseconds = false;
                    _msg.Length         = 0;
                    _msg.Append("Elapsed Time: ");
                    _msg.Append(sw.FormattedElapsedTime);
                    Program._messageLog.WriteLine(_msg.ToString());
                    sw = null;
                }
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... ImportTableTest finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }