Example #1
0
 ///<summary>NOT Oracle compatible.  This will take a set of rows from the specified table from the current database and insert them into a
 ///different database.  It does NOT create tables nor databases if they don't exist, such things need to already exist.  Enter text for the
 ///"whereClause" parameter to filter what rows to retrieve.  Needs to include the "WHERE" statement, if it's being used. (Ex. "WHERE PatNum != 2")
 ///IMPORTANT NOTE --------------  MAKE SURE YOU SET CLASS-WIDE VARIABLES PRIOR TO RUNNING TABLE COMMANDS. - TableName, TablePriKey at the very least.</summary>
 public static void InsertIntoLargeTable(string whereClause)
 {
                 #if DEBUG
     Stopwatch s = new Stopwatch();
     s.Start();
                 #endif
     SetListPriKeyMaxPerBatch(whereClause);
     _tempTableName = _tableName;          //Set these to be equal for the Insert function - We aren't renaming tables, we are copying from one to another.
     ODThread odThreadQueueData = new ODThread(QueueBatches);
     try {
         lock (_lockObjQueueBatchQueries) {
             _queueBatchQueries = new Queue <BatchQueries>();
         }
         _areQueueBatchThreadsDone = false;
         odThreadQueueData.Name    = "QueueDataThread";
         odThreadQueueData.AddExceptionHandler(new ODThread.ExceptionDelegate((Exception ex) => { _areQueueBatchThreadsDone = true; }));
         odThreadQueueData.Start(true);
         InsertBatches();
     }
     catch (Exception ex) {
         ex.DoNothing();
         throw;
     }
     finally {
         odThreadQueueData?.QuitAsync();
         _areQueueBatchThreadsDone = true;
                         #if DEBUG
         s.Stop();
         Console.WriteLine("Total time to insert into table " + _tableName + " with " + string.Format("{0:#,##0.##}", _totalRowCount) + " rows: "
                           + (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"")
                           + (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec");
                         #endif
     }
 }
Example #2
0
 private void QuitThread()
 {
     if (_threadCheckKeyboard != null)
     {
         _threadCheckKeyboard.QuitAsync();
         _threadCheckKeyboard = null;
     }
 }
 private void butCancel_Click(object sender, EventArgs e)
 {
     if (_odThread != null)
     {
         _odThread.QuitAsync();
     }
     DialogResult = DialogResult.Cancel;
     Close();
 }
Example #4
0
 private void ODForm_FormClosed(object sender, FormClosedEventArgs e)
 {
     if (_threadFilter != null)
     {
         _threadFilter.QuitAsync();                //It's fine if our thread loop finishes, it protects against unhandled exceptions.
         _threadFilter = null;
         //We don't want an enumeration exception here so don't clear _listFilterControls. It will get garbage collected anyways.
     }
     DisposeHelpButton();
     _hasClosed = true;
 }
        private void FormConnectionLost_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (_threadShouldWindowClose != null && !_threadShouldWindowClose.HasQuit)
            {
                _threadShouldWindowClose.QuitAsync();
            }
            //Unregister the "Fired" event from this form.  The constructor will throw an exception if _eventInfoFired is not valid.
            Delegate   delegateFired       = GetDelegateFired(_eventInfoFired.EventHandlerType);
            MethodInfo methodRemoveHandler = _eventInfoFired.GetRemoveMethod();

            methodRemoveHandler.Invoke(this, new object[] { delegateFired });
        }
 private void WorkerParse834(ODThread odThread)
 {
     Load834_Safe();
     odThread.QuitAsync();
 }
Example #7
0
 ///<summary>NOT Oracle compatible.  Do NOT try-catch this method, even when only adding an index.
 ///This will rename the table specified to be preceded by "convTmp_"+tableName+"random number", creates a copy of
 ///the table structure and indexes, adds the column(s) in colNamesAndDefs (Tuples with Item1 the column name and Item2 the column def), and if
 ///indexColsAndNames is not null adds the index on the comma-delimited list of columns (Item1) with index name (Item2) if included, otherwise
 ///letting MySQL name the index.  Once the empty table clone is made, keys will be disabled (for MyISAM only), all rows will be inserted from the
 ///renamed convTmp_ table, the keys (indexes) will be enabled (MyISAM only), and the convTmp_ table will be dropped.
 ///A null or empty colName will cause this method to not add a new column to the table.  This is useful for adding an index only.</summary>
 public static void AlterLargeTable(string tableName, string priKeyField, List <Tuple <string, string> > colNamesAndDefs = null,
                                    List <Tuple <string, string> > indexColsAndNames = null)
 {
     if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
     {
         throw new ODException("AlterLargeTable should only be invoked from convert script classes.  Middle Tier not supported.");
     }
                 #if DEBUG
     Stopwatch s = new Stopwatch();
     s.Start();
                 #endif
     _tableName        = tableName;
     _tablePriKeyField = priKeyField;
     string        dbName      = GetCurrentDatabase();
     string        command     = "";
     List <string> listChanges = new List <string>();
     colNamesAndDefs = colNamesAndDefs?.FindAll(x => !string.IsNullOrEmpty(x.Item1) && !string.IsNullOrEmpty(x.Item2)) ?? new List <Tuple <string, string> >();
     if (colNamesAndDefs.Count > 0)
     {
         listChanges.AddRange(colNamesAndDefs.Select(x => (ColumnExists(dbName, x.Item1)?"MODIFY":"ADD")
                                                     + " `" + x.Item1 + "` " + x.Item2));//modify does nothing if data type isn't changed
     }
     indexColsAndNames = indexColsAndNames?.FindAll(x => !string.IsNullOrEmpty(x.Item1) && !IndexExists(_tableName, x.Item1)) ?? new List <Tuple <string, string> >();
     if (indexColsAndNames.Count > 0)
     {
         //drop any index with different columns but the same name as the index we're adding.  Useful if we are modifying the cols of a composite index
         listChanges.AddRange(indexColsAndNames.FindAll(x => !string.IsNullOrEmpty(x.Item2) && IndexNameExists(_tableName, x.Item2))
                              .Select(x => "DROP INDEX `" + x.Item2 + "`"));
         listChanges.AddRange(indexColsAndNames.Select(x => "ADD INDEX " + (!string.IsNullOrEmpty(x.Item2)?("`" + x.Item2 + "` "):"") + "(`" + x.Item1.Replace(",", "`,`") + "`)"));
     }
     if (listChanges.Count < 1)
     {
         return;                //nothing to do, just return
     }
     string alterTableStmt = "ALTER TABLE `" + _tableName + "` " + string.Join(",", listChanges);
     command = "SELECT ValueString FROM preference WHERE PrefName='UpdateAlterLargeTablesDirectly'";
     if (Db.GetScalar(command) == "1")
     {
         Db.NonQ(alterTableStmt);                //execute the alter table statement on the original table
         return;
     }
     try {
         SetListPriKeyMaxPerBatch();
     }
     catch (Exception ex) {
         //MySQL 5.0 cannot run the command in the above method.
         Db.NonQ(alterTableStmt);                //execute the alter table statement on the original table
         ex.DoNothing();
         return;
     }
     if (_listPriKeyMaxPerBatch.Count <= 1)      //if no rows, list will be empty.  One row means the table isn't very large.  Either way, small enough to run normal script
     {
         Db.NonQ(alterTableStmt);                //execute the alter table statement on the original table
         return;
     }
     //The table is large enough to merit multiple batches.
     string rndStr = new Random().Next(1000000).ToString();
     _tempTableName = "tempConv_" + _tableName + rndStr;
     command        = "DROP TABLE IF EXISTS `" + _tempTableName + "`";
     Db.NonQ(command);
     command = "RENAME TABLE `" + _tableName + "` TO `" + _tempTableName + "`";
     Db.NonQ(command);
     command = "CREATE TABLE `" + _tableName + "` LIKE `" + _tempTableName + "`";
     Db.NonQ(command);
     Db.NonQ(alterTableStmt);            //execute the alter table statement on the new empty table
     command = "SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + POut.String(dbName) + "' AND TABLE_NAME='" + POut.String(_tableName) + "'";
     string tableEngine = Db.GetScalar(command)?.ToLower();
     if (tableEngine == "myisam")
     {
         command = "ALTER TABLE `" + _tableName + "` DISABLE KEYS";
         Db.NonQ(command);
     }
     ODThread odThreadQueueData = new ODThread(QueueBatches);
     try {
         lock (_lockObjQueueBatchQueries) {
             _queueBatchQueries = new Queue <BatchQueries>();
         }
         _areQueueBatchThreadsDone = false;
         odThreadQueueData.Name    = "QueueDataThread";
         odThreadQueueData.AddExceptionHandler(new ODThread.ExceptionDelegate((Exception ex) => { _areQueueBatchThreadsDone = true; }));
         odThreadQueueData.Start(true);
         InsertBatches();
         if (tableEngine == "myisam")
         {
             command = "ALTER TABLE `" + _tableName + "` ENABLE KEYS";
             Db.NonQ(command);
         }
         if (_insertBatchCount == _listPriKeyMaxPerBatch.Count)
         {
             command = "DROP TABLE IF EXISTS `" + _tempTableName + "`";
             Db.NonQ(command);
         }
         else                  //if a batch(es) failed to run, keep the original table in case we want to drop the new and rename the original to undo this action
         {
             try {
                 command = "DROP TABLE IF EXISTS `" + _tableName + "`";
                 Db.NonQ(command);
                 command = "RENAME TABLE `" + _tempTableName + "` TO `" + _tableName + "`";
                 Db.NonQ(command);
                 Db.NonQ(alterTableStmt);                        //execute the alter table statement on the original table
             }
             catch (Exception ex) {
                 //Both the batch method of altering the table and the simple query failed. We will display an error message showing the number of rows that
                 //were copied in the batch method, and we will also include the exception from the simple method as the inner exception.
                 string colText = "";
                 if (colNamesAndDefs.Count > 0)
                 {
                     colText += "column" + (colNamesAndDefs.Count > 1?"s ":" ")
                                + string.Join((colNamesAndDefs.Count == 2?" and ":", "), colNamesAndDefs.Select(x => x.Item1)) + " ";
                     if (colNamesAndDefs.Count > 2)
                     {
                         colText = colText.Insert(colText.LastIndexOf(", ") + 2, "and ");
                     }
                 }
                 string indexText = "";
                 if (indexColsAndNames.Count > 0)
                 {
                     indexText = "index" + (indexColsAndNames.Count > 1?"es ":" ")
                                 + string.Join((indexColsAndNames.Count == 2?" and ":", ")
                                               //use the index name if provided, otherwise use the column name
                                               , indexColsAndNames.Select(x => !string.IsNullOrEmpty(x.Item2)?x.Item2:("on column " + x.Item1))) + " ";
                     if (indexColsAndNames.Count > 2)
                     {
                         indexText = indexText.Insert(indexText.LastIndexOf(", ") + 2, "and ");
                     }
                 }
                 throw new ApplicationException("Adding the " + string.Join("and the ", (new[] { colText, indexText }).Where(x => !string.IsNullOrEmpty(x)))
                                                + " to the " + _tableName + " table failed due to incorrect number of rows being copied.", ex);
             }
         }
     }
     catch (Exception ex) {
         ex.DoNothing();
         throw;
     }
     finally {
         odThreadQueueData?.QuitAsync();
         _areQueueBatchThreadsDone = true;
                         #if DEBUG
         s.Stop();
         Console.WriteLine("Total time to alter table " + _tableName + " with " + string.Format("{0:#,##0.##}", _totalRowCount) + " rows: "
                           + (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"")
                           + (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec");
                         #endif
     }
 }