With the following code, only the first set of database commands are actually carried out to fruition (one table is dropped, others have a record deleted from them) IF the MessageBox.Show() call at the beginning of DropTablesAndDeleteFromTables() is commented out.
If I uncomment it, so that the user has to dismiss after each set of database manipulations (obviously not what I want in the version to be used by customers), all is well - all the tables are dropped, and references to them deleted, as desired. Why does interrupting the process in this way (computus interruptus?) make the difference between success and failure, and how can I have my pie and eat it, too (get all the database commands to succeed without bothering the user with N MessageBox.Show() dialogs to dismiss?
private void DropTablesAndDeleteFromTables(string recordType, string fileName)
{
MessageBox.Show(String.Format("In DropTablesAndDeleteFromTables(), recordType is {0}, fileName is {1}", recordType, fileName)); //TODO: Remove
try
{
WorkFiles wrkFile = new WorkFiles();
int tableOK = 0;
DataSet workfiles;
tableOK = wrkFile.isValidWorkTable();
if (tableOK > 0) //Table has at least one record
{
workfiles = wrkFile.getAllRecords();
//Go thru dataset and find filename to clean up after
foreach (DataRow row in workfiles.Tables[0].Rows)
{
string tmpType = row["fileType"].ToString();
if (tmpType.EndsWith("0") || tmpType.EndsWith("1"))
{
tmpType = tmpType.Substring(0, 3);
}
string tmpStr = row["Name"].ToString();
int intSite = (int) row["siteNo"];
string tmpName = tmpType + "_" + intSite.ToString() + "_" + tmpStr;
if (tmpType != recordType) continue;
if (tmpName != fileName) continue;
//Drop workTables table from site-specific DB [ such as from HHSDB003.SDF ]
String dropTable = "DROP TABLE " + tmpType + tmpStr;
String delWorkTableSimple = string.Format("DELETE FROM workTables WHERE filetype = '{0}' and Name = '{1}'", tmpType, tmpStr);
String delWorkTable0 = "DELETE FROM workTables WHERE filetype = '" + tmpType + "0' and Name = '" + tmpStr + "'";
String delWorkTable1 = "DELETE FROM workTables WHERE filetype = '" + tmpType + "1' and Name = '" + tmpStr + "'";
// Do site-specific database first
// 0) Drop the table whose contents have been sent
SendCommandToDB(dropTable, intSite, true);
PauseThatRefreshes();
// 1) Delete record from site-specific [ HHSDB[siteNum].SDF workTables, such as HHSDB003.SDF ]
SendCommandToDB(delWorkTableSimple, intSite, true);
PauseThatRefreshes();
// Bypassing the "0" and "1" tables did nothing - still only drops one table and deletes
// 2) Same as 1, but for table named [DSD,INV}0_Bla
SendCommandToDB(delWorkTable0, intSite, true);
PauseThatRefreshes();
// 3) Same as 2, but for table named [DSD,INV}1_Bla instead of [DSD,INV}0_Bla
SendCommandToDB(delWorkTable1, intSite, true);
PauseThatRefreshes();
// Four calls to site-specific above; Three-four calls to NON-site-specific below
// 4) Delete record from NON-site-specific [ HHSDB[siteNum].SDF workTables, such as HHSDB003.SDF ]
SendCommandToDB(delWorkTableSimple, intSite, false);
PauseThatRefreshes();
// 5) Same as 1, but for table named [DSD,INV}0_Bla
SendCommandToDB(delWorkTable0, intSite, false);
PauseThatRefreshes();
// 6) Same as 2, but for table named [DSD,INV}1_Bla instead of [DSD,INV}0_Bla
SendCommandToDB(delWorkTable1, intSite, false);
PauseThatRefreshes();
// 7) Conditionally delete a record (if a DSD record, from DSDHeader, which is in the base (NON-site-specific) database
if (tmpType == "DSD")
{
String dml = string.Format("DELETE FROM {0}Header WHERE Name = '{1}'", tmpType, tmpStr);
SendCommandToDB(dml, intSite, false);
}
populateTransactionListBoxWithWorkTables();
return;
} // foreach (DataRow row in workfiles.Tables[0].Rows)
} // if ( tableOK > 0) //Table exist
//} // lock TFS#4054
} // try
catch (Exception ex)
{
SSCS.ExceptionHandler(ex, "frmCentral.DropTablesAndDeleteFromTables");
}
} // DropTablesAndDeleteFromTables
private void PauseThatRefreshes()
{
int j = 0;
while (j < 100000)
{
j++;
}
}
private void SendCommandToDB(String sql, int siteNum, bool SiteSpecificDB)
{
try
{
if (SiteSpecificDB)
{
if (dbconn.InBaseDatabase())
{
dbconn = DBConnection.GetInstance(siteNum.ToString());
}
}
else
{
if (!(dbconn.InBaseDatabase()))
{
dbconn = DBConnection.GetInstance();
}
}
dbconn.DBCommand(sql, true);
}
catch (SqlCeException ee)
{
. . .
}
}
What is a workaround to let the process come up for air without forcing the user to play a role in the charade?
It seems to be a matter of how much time elapses between each set of database manipulations. When I changed this:
while (i < 100000)
... in PauseThatRefreshes() to this:
while (i < 10000000)
(with the MessageBox.Show() commented out) it worked! But that still makes me nervous. Is there a more "scientific" (elegant?) way to accomplish this?
Your code example is both too complicated, and incomplete. So I can't say for sure what's wrong.
But the symptom is a classic indication that you are a) running the code in question on the main GUI thread, and b) that code at some point winds up blocked waiting for the main GUI thread to do something else (i.e. deadlock).
The right way to fix it is to perform those operations on a different thread than the main GUI thread. This will most likely introduce new problems where you are accessing GUI elements from the operation, which you'll have to address by using your GUI API's "Invoke" mechanism.