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