Esempio n. 1
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);
        }
Esempio n. 2
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);
        }
Esempio n. 3
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;
        }
Esempio n. 4
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);
        }
        private void VerifyConnectionString()
        {
            if (this.txtConnectionString.Text.Trim() == string.Empty)
            {
                this.ConnectionString = BuildConnectionString();
                if (this.txtConnectionString.Text.Trim() == string.Empty)
                {
                    AppMessages.DisplayWarningMessage("You must specify a connection string. Fill in input fields and then use Build button to transform input fields into a connection string.");
                    return;
                }
            }

            try
            {
                DisableFormControls();
                this.Cursor = Cursors.WaitCursor;

                _db.ConnectionString = this.ConnectionString;
                _db.OpenConnection();
                _msg.Length = 0;
                if (_db.IsConnected)
                {
                    this._connectionAccessStatus = enConnectionAccessStatus.IsAccessible;
                    _msg.Append("Connection successful!");
                    AppMessages.DisplayInfoMessage(_msg.ToString());
                }
                else
                {
                    this._connectionAccessStatus = enConnectionAccessStatus.NotAccessible;
                    _msg.Append("Connection failed.");
                    AppMessages.DisplayErrorMessage(_msg.ToString());
                }
            }
            catch (System.Exception ex)
            {
                this._connectionAccessStatus = enConnectionAccessStatus.Unknown;
                _msg.Length = 0;
                _msg.Append("Attempt to connect to database failed.");
                _msg.Append(Environment.NewLine);
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                AppMessages.DisplayErrorMessage(_msg.ToString());
            }
            finally
            {
                if (_db.IsConnected)
                {
                    _db.CloseConnection();
                }

                EnableFormControls();
                this.Cursor = Cursors.Default;
                this.Focus();
            }
        }
        /// <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();
        }
Esempio n. 7
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);
        }
Esempio n. 8
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);
        }
Esempio n. 9
0
        //test routines
        public static void ConnectionTest(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;

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

                _msg.Length = 0;
                _msg.Append("Platform: ");
                _msg.Append(dbPlatformDesc);
                _msg.Append("\r\nConnectionString: ");
                _msg.Append(connStr);
                _msg.Append("\r\nNamespace: ");
                _msg.Append(nmSpace);
                _msg.Append("\r\nClassName: ");
                _msg.Append(clsName);
                _msg.Append("\r\nDLL Path: ");
                _msg.Append(dllPath);
                Program._messageLog.WriteLine(_msg.ToString());


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

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

                _msg.Length = 0;
                if (db.IsConnected)
                {
                    _msg.Append("Connection successful.");
                }
                else
                {
                    _msg.Append("**Connection failed.");
                }
                Program._messageLog.WriteLine(_msg.ToString());

                Program._messageLog.WriteLine(Environment.NewLine);
                foreach (stKeyValuePair <string, string> kv in db.ConnectionStringKeyVals)
                {
                    _msg.Length = 0;
                    _msg.Append(kv.Key + "=" + kv.Value);
                    Program._messageLog.WriteLine(_msg.ToString());
                }
                Program._messageLog.WriteLine(Environment.NewLine);
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... ConnectionTest finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Esempio n. 10
0
        public static void DataSetTest(MainForm frm)
        {
            string     dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            PFDatabase db             = null;
            string     connStr        = string.Empty;
            string     nmSpace        = string.Empty;
            string     clsName        = string.Empty;
            string     dllPath        = string.Empty;
            Stopwatch  sw             = new Stopwatch();

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

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

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

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

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


                sw.Start();

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

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

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

                sw.Start();
                DataSet ds1 = db.RunQueryDataSet();
                db.returnResult += new PFDatabase.ResultDelegate(OutputResults);
                db.ProcessDataSet(ds1);
                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Process Dataset time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

                //Run data extract test
                Program._messageLog.WriteLine("\r\nRunning data extract tests ...\r\n");
                db.returnResultAsString += new PFDatabase.ResultAsStringDelegate(OutputResultsToFile);
                if (_textFile.FileIsOpen)
                {
                    _textFile.CloseFile();
                }
                _textFile.OpenFile(@"c:\temp\DatasetDelimitedTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
                sw.Start();
                DataSet ds = db.RunQueryDataSet();
                db.ExtractDelimitedDataFromDataSet(ds, "~", "\r\n", true);
                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Extract Delimiated Dataset time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

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

                sw.Start();
                ds = db.RunQueryDataSet();
                db.ExtractFixedLengthDataFromDataSet(ds, true, true, false);
                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Extract Fixed Length Dataset time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

                db.SaveDataSetToXmlSchemaFile(ds, @"c:\temp\Testds.xsd");
                db.SaveDataSetToXmlFile(ds, @"c:\temp\Testds.xml");
                db.SaveDataSetWithSchemaToXmlFile(ds, @"c:\temp\Testdsplus.xml");
                DataSet ds2     = db.LoadXmlFileToDataSet(@"c:\temp\Testds.xml");;
                int     numRows = ds2.Tables[0].Rows.Count;


                PFDataProcessor dataProcessor = new PFDataProcessor();
                XmlDocument     xmlDoc        = dataProcessor.CopyDataSetToXmlDocument(ds);
                Program._messageLog.WriteLine(xmlDoc.OuterXml);

                if (_textFile.FileIsOpen)
                {
                    _textFile.CloseFile();
                }
                ds = null;
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... DataSetTest finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Esempio n. 11
0
        public static void DataReaderToDataTableTest(MainForm frm)
        {
            string     dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            PFDatabase db             = null;
            string     connStr        = string.Empty;
            string     nmSpace        = string.Empty;
            string     clsName        = string.Empty;
            string     dllPath        = string.Empty;
            Stopwatch  sw             = new Stopwatch();

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

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

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

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

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


                sw.Start();

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

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

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

                sw.Start();

                DbDataReader rdr = (DbDataReader)db.RunQueryDataReader();
                DataTable    tab = db.ConvertDataReaderToDataTable(rdr);
                Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString());
                rdr.Close();

                for (int i = 0; i < tab.Rows.Count; i++)
                {
                    DataRow r = tab.Rows[i];
                    _msg.Length = 0;
                    int maxColInx = tab.Columns.Count - 1;
                    for (int ci = 0; ci <= maxColInx; ci++)
                    {
                        _msg.Append(tab.Columns[ci].ColumnName);
                        _msg.Append(": ");
                        _msg.Append(r[ci].ToString());
                        if (ci < maxColInx)
                        {
                            _msg.Append(", ");
                        }
                    }
                    Program._messageLog.WriteLine(_msg.ToString());
                }

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Table read time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... DataReaderToDataTableTest finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
Esempio n. 12
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);
        }
Esempio n. 13
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());
            }
        }
Esempio n. 14
0
        public static void CreateTableTest(MainForm frm)
        {
            string           dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            DatabasePlatform dbPlatform     = DatabasePlatform.Unknown;
            PFDatabase       db             = null;
            string           connStr        = string.Empty;
            string           nmSpace        = string.Empty;
            string           clsName        = string.Empty;
            string           dllPath        = string.Empty;
            Stopwatch        sw             = new Stopwatch();

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

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

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

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

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

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


                sw.Start();

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

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


                tableName = frm.txtTableName.Text;

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


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

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


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


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

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

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

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


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

                //create the table

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

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


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

                unitTestDt01.ImportTableToDatabase();

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

                unitTestDt02.ImportTableToDatabase();


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

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

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

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

                for (int c = 0; c < testTab.Columns.Count; c++)
                {
                    _msg.Length = 0;
                    _msg.Append(testTab.Columns[c].ColumnName);
                    _msg.Append(", ");
                    _msg.Append(testTab.Columns[c].DataType.FullName);
                    _msg.Append(", ");
                    _msg.Append(testTab.Columns[c].MaxLength.ToString());
                    Program._messageLog.WriteLine(_msg.ToString());
                }
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... CreateTableTest finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
        /// <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);
        }
Esempio n. 16
0
        public static void GetQueryDataSchema(MainForm frm)
        {
            string     dbPlatformDesc = DatabasePlatform.Unknown.ToString();
            PFDatabase db             = null;
            string     connStr        = string.Empty;
            string     nmSpace        = string.Empty;
            string     clsName        = string.Empty;
            string     dllPath        = string.Empty;
            Stopwatch  sw             = new Stopwatch();

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

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

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

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

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


                sw.Start();

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

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

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

                sw.Start();

                DataTable tab = db.RunQueryDataTable();

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

                sw.Stop();
                _msg.Length = 0;
                _msg.Append("Get Query Data Schema time: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());
            }
            catch (System.Exception ex)
            {
                _msg.Length = 0;
                _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
                Program._messageLog.WriteLine(_msg.ToString());
                AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
            }
            finally
            {
                if (db != null)
                {
                    if (db.IsConnected)
                    {
                        db.CloseConnection();
                    }
                    db = null;
                }
                _msg.Length = 0;
                _msg.Append("\r\n... GetQueryDataSchema finished.");
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }
        /// <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);
        }
        /// <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);
        }
Esempio n. 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);
        }
Esempio n. 21
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 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());
            }
        }
Esempio n. 23
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());
            }
        }
Esempio n. 24
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());
            }
        }
Esempio n. 25
0
        public void CopyAdventureWorksDW(DatabasePlatform sourceDbPlatform, string sourceConnection,
                                         DatabasePlatform destinationDbPlatorm, string destinationConnection,
                                         bool replaceExistingTables, int batchSizeForDataWrites, string outputTablesSchema)
        {
            PFDatabase sourceDb      = null;
            PFDatabase destinationDb = null;
            Stopwatch  sw            = new Stopwatch();

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

                sw.Start();

                sourceDb = GetDbObject(sourceDbPlatform, sourceConnection);
                if (sourceDb.IsConnected == false)
                {
                    _msg.Length = 0;
                    _msg.Append("Unable to connect to source datatabase ");
                    _msg.Append(sourceDbPlatform.ToString());
                    _msg.Append(Environment.NewLine);
                    _msg.Append("Connection String: ");
                    _msg.Append(Environment.NewLine);
                    _msg.Append(sourceConnection);
                    throw new System.Exception(_msg.ToString());
                }

                destinationDb = GetDbObject(destinationDbPlatorm, destinationConnection);
                if (destinationDb.IsConnected == false)
                {
                    _msg.Length = 0;
                    _msg.Append("Unable to connect to destination datatabase ");
                    _msg.Append(destinationDbPlatorm.ToString());
                    _msg.Append(Environment.NewLine);
                    _msg.Append("Connection String: ");
                    _msg.Append(Environment.NewLine);
                    _msg.Append(destinationConnection);
                    throw new System.Exception(_msg.ToString());
                }

                _msg.Length = 0;
                _msg.Append("Time to connect to databases: ");
                _msg.Append(sw.FormattedElapsedTime);
                Program._messageLog.WriteLine(_msg.ToString());

                CopyTables(sourceDb, destinationDb, replaceExistingTables, batchSizeForDataWrites, outputTablesSchema);
            }
            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();
                }
                sourceDb = null;

                if (destinationDb.IsConnected)
                {
                    destinationDb.CloseConnection();
                }
                destinationDb = null;

                sw.Stop();

                _msg.Length = 0;
                _msg.Append("\r\n... CopyAdventureWorksDW finished.");
                Program._messageLog.WriteLine(_msg.ToString());
                _msg.Length = 0;
                _msg.Append(Environment.NewLine);
                _msg.Append("Elapsed copy time: ");
                _msg.Append(sw.FormattedElapsedTime);
                _msg.Append(Environment.NewLine);
                Program._messageLog.WriteLine(_msg.ToString());
            }
        }