I have a situation where my program performs these two tasks

在我的程序执行这两个任务的情况下

1. Fetch data via ODBC for 96 tables one after the other.
   
   a. Create insert commands sql file.
   
   b. Execute the inserts-script via SqlCmd command line utility.

I need to enhance this process so as I can utilize the full potential of the CPU via multi-threading

我需要增强这个过程，以便通过多线程充分利用CPU的潜力

The ideal solution I am trying to implement is as such. The main thread will keep fetching the data via ODBC where as upon each table's data is fetched and stored in a file on disk the main thread then initiates a new Process of SqlCMD utility which inserts the data into SQL server and the main thread goes on to fetching the next table via ODBC.

我正在尝试实施的理想解决方案就是这样。主线程将继续通过ODBC获取数据，当每个表的数据被获取并存储在磁盘上的文件中时，主线程然后启动SqlCMD实用程序的新进程，该进程将数据插入到SQL服务器中，并且主线程继续通过ODBC获取下一个表。

The problem or the goal that I want to achieve is that I want to make sure that the new Process of SqlCmd start in a queue/sequence only when the prior processese of SqlCMD are finished

我想要实现的问题或目标是，我想确保只有当SqlCMD的先前进程完成时，SqlCmd的新进程才会以队列/序列的形式启动

This method of exportOdbcToSQLDB is called in an outer loop 96 times.

此exportOdbcToSQLDB方法在外部循环中被调用96次。

private void exportOdbcToSQLDB(string tableName, SqlConnection sqlCon, OdbcConnection odbcCon)
 {
     InsertAndValidate insertValidateObj = new InsertAndValidate();
     insertValidateObj.sqlCon = sqlCon;
     insertValidateObj.odcConnection = odbcCon;
     insertValidateObj.logTableName = "_Log";

     if (string.IsNullOrEmpty(tableName))
         return;
     String columns = "";
     string tmpSelect = "SELECT * FROM [" + tableName + "]";
     using (var adapter = new SqlDataAdapter(tmpSelect, sqlCon))
     using (var builder = new SqlCommandBuilder(adapter))
     {
         columns = builder.GetInsertCommand().CommandText;
         columns = columns.Split(new String[] { ")" }, StringSplitOptions.None)[0];
         columns = columns.Split(new String[] { "(" }, StringSplitOptions.None)[1];
         columns = columns.Trim();
         Application.DoEvents();
     }

     int totalCount = 0;
     DateTime startTime = DateTime.Now;
     string strInsert = "";
     System.IO.File.WriteAllText("sql_" + tableName + ".sql", "");

     string tableFilePath = Path.Combine(Config.qbTablesDir, tableName + ".sql");
     using (StreamWriter writer = new StreamWriter(tableFilePath, true))
     {
         writer.WriteLine("SET NOCOUNT ON" + Helper1.Go());
         writer.Write("Update _Log set Remarks  = Concat(Remarks, '| Insert Start time ',dbo.dNow())" + Helper1.Go());
         string insertTemp = "Insert into [" + tableName + "] (" + columns + ") Values ";
         string queryString = @"select " + columns + " from [" + tableName + "]";

         insertValidateObj.addTableState(tableName);
         insertValidateObj.ResetStats(tableName);
         Application.DoEvents();

         OdbcCommand command = new OdbcCommand(queryString, insertValidateObj.odcConnection);
         using (OdbcDataReader reader = command.ExecuteReader())
         {
             int colCount = reader.FieldCount;
             if (reader.HasRows)
             {
                 try
                 {
                     while (reader.Read())
                     {
                         totalCount++;
                         writer.Write(insertTemp + this.GenerateInsert(reader, tableName, columns) + Helper1.Go());
                     }
                 }
                 catch (Exception ex)
                 {
                     string exception1 = (String.Concat("Row ", totalCount, ex.ToString()));
                 }
                 Application.DoEvents();
             }
             insertTemp = "Update _Log set Remarks  = Concat(Remarks, '| Insert End time ',dbo.dNow())" + Helper1.Go();
             insertTemp = "Update _log set SuccessRecords = (Select count(1) from [" + tableName + "]) where TableName = '" + tableName + "'" + Helper1.Go();
             writer.Write(insertTemp);
         }
     }
     DateTime endTime = DateTime.Now;
     insertValidateObj.SetStats(tableName, totalCount, 0, 0, startTime, endTime);

     if (totalCount > 0)
     {
         tablesToInsert.Enqueue(tableName);
         ExecuteQbInserts(tableName);
     }
 }

This method is called in the above method

此方法在上面的方法中调用

public static void ExecuteQbInserts(string tableName)
 {
     string tmpPathInput = Path.Combine(Config.qbTablesDir, tableName + ".sql");
     string tmpPathOutput = Path.Combine(Config.qbSqlInsertsLogsDir, tableName + "_log.txt");

     string command = string.Concat("sqlcmd -S . -d qb", Config.ClientName, " -U sa -P atiqf -i \"", tmpPathInput, "\" -o \"", tmpPathOutput, "\"");
     ProcessStartInfo processInfo;
     processInfo = new ProcessStartInfo("cmd.exe", "/c " + command);
     processInfo.CreateNoWindow = false;
     processInfo.UseShellExecute = false;
     Process.Start(processInfo);
 }

Result:

结果：

Multiple SqlCMD processes runs when the rate of sqlCmd inserts is less than the rate at which the main thread fetches the ODBC data.

当sqlCmd插入的速率低于主线程获取ODBC数据的速率时，会运行多个SqlCMD进程。

Solution required:
Have the the main thread keeps getting data of 96 tables at its own rate and have sqlCMD processes run one after the other.

所需的解决方案：让主线程保持以自己的速度获取96个表的数据，并让SQLCMD进程一个接一个地运行。

I think that a sufficient solution to your problem would be to parallelize the ExportOdbcToSQLDB and ExecuteQbInserts in this way: The ExportOdbcToSQLDB for the table i will be executed in parallel with the ExecuteQbInserts for the table i - 1. Assuming that one of the two operations is consistently slower than the other, the total amount of time for processing all tables will be equal with the sum of the slower operation. The faster operation will travel by hitchhiking, so to say, without affecting the total execution time.

我认为您的问题的充分解决方案将是以这种方式并行化ExportOdbcToSQLDB和ExecuteQbInserts：表I的ExportOdbcToSQLDB将与表I-1的ExecuteQbInserts并行执行。假设两个操作中的一个始终比另一个慢，则处理所有表的总时间将等于较慢的操作的总和。可以说，更快的操作将通过搭便车进行，而不会影响总执行时间。

The ParallelizeTwoActions method below implements this scheme in an abstract generic way:

下面的ParallizeTwoActions方法以抽象的通用方式实现此方案：

/// <remarks>
/// Invokes two actions for each element in the source sequence, sequentially.
/// The action1 is invoked sequentially for one element at a time.
/// The action2 is also invoked sequentially for one element at a time.
/// The action1 for an element is invoked in parallel with the action2 for its
/// previous element.
/// </remarks>
public static async Task ParallelizeTwoActions<TSource>(IEnumerable<TSource> source,
    Action<TSource> action1, Action<TSource> action2)
{
    ArgumentNullException.ThrowIfNull(source);
    ArgumentNullException.ThrowIfNull(action1);
    ArgumentNullException.ThrowIfNull(action2);

    Task task1 = Task.CompletedTask;
    Task task2 = Task.CompletedTask;
    try
    {
        foreach (TSource item in source)
        {
            task1 = Task.Run(() => action1(item));
            await Task.WhenAll(task1, task2).ConfigureAwait(false);
            task2 = Task.Run(() => action2(item));
        }
    }
    finally
    {
        await Task.WhenAll(task1, task2).ConfigureAwait(false);
    }
}

The marble diagram below illustrates how the action1 and action2 are invoked for six elements A, B, C, D, E and F.

下面的大理石图说明了如何为六个元素A、B、C、D、E和F调用动作1和动作2。

action1: A------| B----------| C-----|    D---------| E-------| F-------|
action2:          A----|       B--------| C---|       D----|    E----|    F----|

Notice how the action1(B) overlaps with the action2(A).

注意动作1(B)与动作2(A)是如何重叠的。

You could use the ParallelizeTwoActions like this:

您可以按如下方式使用ParallizeTwoActions：

private async void btnExecute_Click(object sender, EventArgs e)
{
    try
    {
        Cursor = Cursors.WaitCursor;
        btnExecute.Enabled = false;

        await ParallelizeTwoActions(tableNames, tableName =>
        {
            ExportOdbcToSQLDB(tableName, sqlCon, odbcCon);
        }, tableName =>
        {
            ExecuteQbInserts(tableName);
        });
    }
    finally
    {
        btnExecute.Enabled = true;
        Cursor = Cursors.Default;
    }
}

For this to work, you must do a change at the bottom of the ExecuteQbInserts:

为此，您必须在ExecuteQbInserts的底部进行更改：

Process process = Process.Start(processInfo);
process.WaitForExit(); /* Block the current thread until the process terminates */

You must also remove all the Application.DoEvents(); lines, because both the ExportOdbcToSQLDB and the ExecuteQbInserts will be invoked on ThreadPool threads. You should avoid any interaction with UI controls inside these methods. It is strictly forbidden to interact with UI controls from any thread other than the UI thread.

您还必须删除所有Application.DoEvents()；行，因为ExportOdbcToSQLDB和ExecuteQbInserts都将在ThreadPool线程上调用。您应该避免与这些方法中的UI控件进行任何交互。严格禁止从UI线程以外的任何线程与UI控件交互。

In case you are not familiar with async/await, you might want to read some tutorials, like this or this. It's a really great way to keep your UI responsive by offloading work to background threads, without complicating your code with manual Thread management, or with awkward BackgroundWorkers etc.

如果您不熟悉异步/等待，您可能想要阅读一些教程，像这样或这样。这是一种非常好的方式来保持你的用户界面的响应性，通过将工作转移到后台线程，而不会使你的代码变得复杂，手动的线程管理，或者笨拙的BackekWorker等等。