Importing Data from Multiple Databases into 1 table as same time using C#

Question

I have a table that list all of my ERP database that I read to get the connection string.
I need to collect all customer info into 1 table.
To do this I would create a dataset with the list of databases,
then do a foreach to read thru the dataset,
create the insert statement,
call a routine in my DLL to run the statement
Below is a copy of some of my code.
Can someone help me?

I want to run all of these imports at 1 time and not lock up my UI.
I would also like to have a progress bar show the status of the downloads, so how do I keep the UI from locking up while the downloads are running?

I know my programming style is different, it is more old school.
if it starts with a sub then it is a subroutine (a routine that runs and does not return anything)
if it start with a fct then it is a function (a rountine that runs and returns something)
if it start with a L then it is local to the routine
if it starts with a P the it is private to the form
if it starts with a G then it is Global and definded in the DLL
the 2nd and 3rd characters defind what it is definded as
S = string
I = Integer
L = Long
DT = Datetime
...
The rest will describe what it is used for

Here is the code in my DLL that I add to my program

public static Int32 GIWelchCustApps = 0;
public static bool[] GBCon = new bool[200];
public static string[] GSConnection = new string[200];
public static string[] GSDBName = new string[200];
public static Boolean GBConLoaded = false;

public static void subSQLStmt2(string LSSqlstmt, int LIDataBase)
{
if (LSSqlstmt != null && LSSqlstmt != "")
{
subDBOpenDB(LIDataBase);
SqlCommand LSCmd = new SqlCommand();
object LOReturnValue;

            LSCmd.CommandText = LSSqlstmt;
            LSCmd.CommandType = CommandType.Text;
            LSCmd.Connection = GODBConn[LIDataBase];
            LSCmd.CommandTimeout = 0;
            LOReturnValue = LSCmd.ExecuteNonQuery();
        }

    }

public static void subDBOpenDB(int LIDBNumber)
{
string[] LSRD;
string LSData = "";

        if (GSMasterConStr == "")
        {
            subMasterConnString();
        }

        //==== Load Connection Strings from MSTR_DBInfo table ====
        if (GBConLoaded == false)
        {

            string LSSQL = "Select * " +
                "from MSTR_DBInfo";

            //A
            SqlConnection LSConn = new SqlConnection(GSMasterConStr);
            //A

            SqlDataAdapter LOAdapter = new SqlDataAdapter();
            DataSet LDSet = new DataSet();
            SqlCommand LOCommand = new SqlCommand(LSSQL, LSConn);
            LOCommand.CommandType = CommandType.Text;
            LOCommand.CommandTimeout = 0;
            try
            {
                LSConn.Open();
                LOAdapter.SelectCommand = LOCommand;
                LOAdapter.Fill(LDSet, "DBSLoad");
                LSConn.Close();


                foreach (DataRow LRSet in LDSet.Tables["DBSLoad"].Rows)
                {
                    if (LRSet["DB"].ToString().ToUpper() == GSCatalog.ToUpper())
                    {
                        GIWelchCustApps = Convert.ToInt32(LRSet["CDBNUM"].ToString());
                    }

                    Int32 LIDB = Convert.ToInt32(LRSet["CDBNum"].ToString());
                    GBCon[LIDB] = false;
                    if (GBTest == false)
                    {
                        GSConnection[LIDB] = LRSet["IntStr"].ToString();
                        GSDBName[LIDB] = LRSet["DB"].ToString();
                    }
                    else
                    {
                        if (LRSet["IntStrTest"].ToString() != "")
                        {
                            GSConnection[LIDB] = LRSet["IntStrTest"].ToString();
                        }
                        else
                        {
                            GSConnection[LIDB] = LRSet["IntStr"].ToString() + ";MultipleActiveResultSets=true";
                        }

                        if (LRSet["DBTest"].ToString() != "")
                        {
                            GSDBName[LIDB] = LRSet["DBTest"].ToString();
                        }
                        else
                        {
                            GSDBName[LIDB] = LRSet["DB"].ToString();
                        }

                    }
                }

                GBConLoaded = true;
            }
            catch (Exception e)
            {
                GSDBOpenError = e.Message.ToString();
                GBConLoaded = false;
                return;
            }
        }

        //==== Open Connection to Database ====
        if (GBCon[LIDBNumber] == false)
        {
            GODBConn[LIDBNumber] = new SqlConnection(GSConnection[LIDBNumber]);
            GODBConn[LIDBNumber].Open();
            GBCon[LIDBNumber] = true;
        }

    }

public static void fctGetDataSet(DataSet LDSet, string LSSQLStmt, string LSTableName, string LSDBName)
{
subDBOpenDB(LSDBName);
SqlDataAdapter LOAdapter = new SqlDataAdapter();

        SqlCommand LOCommand = new SqlCommand(LSSQLStmt, GODBConn[PIDatabase]);
        LOCommand.CommandType = CommandType.Text;
        LOCommand.CommandTimeout = 0;
        LOAdapter.SelectCommand = LOCommand;
        LOAdapter.Fill(LDSet, LSTableName);
    }

Here is the code I am running is series that I want to run in parallel.

//==== Get List of Databases ====
PSSQLStmt = "Select SVR + '.' + DB + '.dbo.' as DBInfo, DB " +
"From MSTR_DBInfo with (NoLock) " +
"Where AmtechDB = 'Y' " +
"And Active = 'Y'";
//==== Close Dataset ====
clsWSS.subCloseTable(PDSet, "CapUpDBs");

//==== Open Dataset ====
clsWSS.fctGetDataSet(PDSet, PSSQLStmt, "CapUpDBs", PIDBWelchCustApps);

foreach (DataRow LRSCapUpDBs in PDSet.Tables["CapUpDBs"].Rows)
{
string LSDBInfo = LRSCapUpDBs["DBInfo"].ToString();

            string LSSQLStmt = "Insert Into CustomerMaster " +
                "(Plt_No, CustomerNumber, CustomerName) " +
                "Select Plt_No,CustomerNumber,CustomerName " +
                "From " + LSDBInfo + "ERPCustomerMaster ";
            clsWSS.subSQLStmt2(LSSqlstmt, PIDBWelchCustApps);


        }
        clsWSS.subCloseTable(PDSet, "CapUpDBs");

Answer 1

Look at Task-based asynchronous pattern model. which has everything you need.

SQL-Server data provider has async methods for all operations coupled with an optional CancellationToken for premature cancelling operations.

Answer 2

Here is a mocked up for sequentially running several task taken from this Stackoverflow post and tweaked to be realistic.

public static Task FirstAsync()  
{  
    Debug.WriteLine($"Started {nameof(FirstAsync)}");  
    return Task.Delay(1000).ContinueWith(t =>   
        Debug.WriteLine($"Finished  {nameof(FirstAsync)}"));  
}  
  
public static Task SecondAsync()  
{  
    Debug.WriteLine($"Started {nameof(SecondAsync)}");  
    return Task.Delay(1000).ContinueWith(t =>   
        Debug.WriteLine($"Finished {nameof(SecondAsync)}"));  
}  
public static Task ThirdAsync()  
{  
    Debug.WriteLine($"Started {nameof(ThirdAsync)}");  
    return Task.Delay(1000).ContinueWith(t =>   
        Debug.WriteLine($"Finished {nameof(ThirdAsync)}"));  
}  
public static Task ForEachAsync(IEnumerable<Func<Task>> tasks)  
{  
    var tcs = new TaskCompletionSource<bool>();  
  
    Task currentTask = Task.FromResult(false);  
  
    foreach (Func<Task> function in tasks)  
    {  
        currentTask.ContinueWith(t =>   
            tcs.TrySetException(t.Exception.InnerExceptions), TaskContinuationOptions.OnlyOnFaulted);  
        currentTask.ContinueWith(t =>   
            tcs.TrySetCanceled(), TaskContinuationOptions.OnlyOnCanceled);  
          
        Task<Task> continuation = currentTask.ContinueWith(t =>   
            function(), TaskContinuationOptions.OnlyOnRanToCompletion);  
          
        currentTask = continuation.Unwrap();  
    }  
  
    currentTask.ContinueWith(t =>   
        tcs.TrySetException(t.Exception.InnerExceptions), TaskContinuationOptions.OnlyOnFaulted);  
      
    currentTask.ContinueWith(t =>   
        tcs.TrySetCanceled(), TaskContinuationOptions.OnlyOnCanceled);  
      
    currentTask.ContinueWith(t =>   
        tcs.TrySetResult(true), TaskContinuationOptions.OnlyOnRanToCompletion);  
  
    return tcs.Task;  
}  
  
private void RunSomeTaskButton_Click(object sender, EventArgs e)  
{  
    List<Func<Task>> list = new List<Func<Task>>  
    {  
        FirstAsync,  
        SecondAsync,  
        ThirdAsync  
    };  
  
  
    Task task = ForEachAsync(list);  
  
    task.ContinueWith(t =>   
        Debug.WriteLine(t.Exception.ToString()),   
        TaskContinuationOptions.OnlyOnFaulted);  
      
    task.ContinueWith(t =>   
        Debug.WriteLine("Done!"),   
        TaskContinuationOptions.OnlyOnRanToCompletion);  
}  

Output

Answer 3

Continuing from my last reply, we can separate ForEachAsync into it's own class

public class TaskHelpers
{
    public static Task ForEachAsync(IEnumerable<Func<Task>> tasks)
    {
        var tcs = new TaskCompletionSource<bool>();

        Task currentTask = Task.FromResult(false);

        foreach (Func<Task> function in tasks)
        {
            currentTask.ContinueWith(t =>
                tcs.TrySetException(t.Exception.InnerExceptions), TaskContinuationOptions.OnlyOnFaulted);
            currentTask.ContinueWith(t =>
                tcs.TrySetCanceled(), TaskContinuationOptions.OnlyOnCanceled);

            Task<Task> continuation = currentTask.ContinueWith(t =>
                function(), TaskContinuationOptions.OnlyOnRanToCompletion);

            currentTask = continuation.Unwrap();
        }

        currentTask.ContinueWith(t =>
            tcs.TrySetException(t.Exception.InnerExceptions), TaskContinuationOptions.OnlyOnFaulted);

        currentTask.ContinueWith(t =>
            tcs.TrySetCanceled(), TaskContinuationOptions.OnlyOnCanceled);

        currentTask.ContinueWith(t =>
            tcs.TrySetResult(true), TaskContinuationOptions.OnlyOnRanToCompletion);

        return tcs.Task;
    }

}

Task into a separate class

public class ExampleTask
{
    public static Task FirstAsync()
    {
        Debug.WriteLine($"Started {nameof(FirstAsync)}");
        return Task.Delay(1000).ContinueWith(t =>
            Debug.WriteLine($"Finished  {nameof(FirstAsync)}"));
    }

    public static Task SecondAsync()
    {
        Debug.WriteLine($"Started {nameof(SecondAsync)}");
        return Task.Delay(1000).ContinueWith(t =>
            Debug.WriteLine($"Finished {nameof(SecondAsync)}"));
    }
    public static Task ThirdAsync()
    {
        Debug.WriteLine($"Started {nameof(ThirdAsync)}");
        return Task.Delay(1000).ContinueWith(t =>
            Debug.WriteLine($"Finished {nameof(ThirdAsync)}"));
    }

}

Now the form code (in this case is much cleaner)

private void RunSomeTaskButton_Click(object sender, EventArgs e)
{
    List<Func<Task>> list = new List<Func<Task>>
    {
        ExampleTask.FirstAsync,
        ExampleTask.SecondAsync,
        ExampleTask.ThirdAsync
    };


    Task taskingAsync = TaskHelpers.ForEachAsync(list);

    taskingAsync.ContinueWith(task => 
        Debug.WriteLine(task.Exception.ToString()), 
        TaskContinuationOptions.OnlyOnFaulted);

    taskingAsync.ContinueWith(task => 
        Debug.WriteLine("Done!"), 
        TaskContinuationOptions.OnlyOnRanToCompletion);
}