question

WelchPKG avatar image
0 Votes"
WelchPKG asked karenpayneoregon answered

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

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



dotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered WelchPKG commented

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.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My example was a simple one. The one that I really need brings many tables together.
I want to do these merges from my C# program at the same time.
Can you give me an example of how to do this?
I have tried tasks and Parallel.tasks but what I am finding is once the first task is done it stops all of them.
I was wondering if the issue is the shared code in the DLL.
Does each task bring in a separate instance of the shared code?

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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

129312-output.png



output.png (16.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.