steve4king
IS-IT--Management
I know I could handle this with a large sql command or SQL procedure, but I want to make a more robust process that can accurately report progress and support cancellation.
Unless I close the 2nd sqlbase connection after each row read from the first connection, it will assign a new cursor for the query within while(reader.read()) loop.
I've handled this in the code below, but disconnecting doesn't seem the right way to handle this.
Any tips?
Unless I close the 2nd sqlbase connection after each row read from the first connection, it will assign a new cursor for the query within while(reader.read()) loop.
I've handled this in the code below, but disconnecting doesn't seem the right way to handle this.
Any tips?
Code:
private void dostuff()
{
bool bError = false;
string sErrorMessages = "";
int iCount = 0;
int iTotal = 18000; //test value
string sID = "";
string sDts = "";
string sGetID = "select id, DTS from tableA";
string sGetMinDTS = "select min(DTS) from TableB where id = ";
OleDbConnection conn1 = new OleDbConnection("Provider=SQLBaseOLEDB;Data Source=DBNAME;User ID=admin;Password=myPass;");
OleDbConnection conn2 = new OleDbConnection("Provider=SQLBaseOLEDB;Data Source=DBNAME;User ID=admin;Password=myPass;");
conn1.Open();
try
{
OleDbCommand commit = new OleDbCommand("commit", conn2);
OleDbCommand comGetID = new OleDbCommand(sGetID, conn1);
OleDbDataReader reader = comGetID.ExecuteReader();
while (reader.Read())
{
progressBar1.Value = Convert.ToInt32(iCount * (100.0 / iTotal));
iCount++;
sID = reader[0].ToString();
sDts = reader[1].ToString();
OleDbCommand comGetMinID = new OleDbCommand(sGetMinDTS + sID, conn2);
OleDbDataReader reader2 = comGetMinID.ExecuteReader();
conn2.Open();
while (reader2.Read())
{
if (reader2[0].ToString().Length > 0)
{
if (Convert.ToDateTime(reader2[0].ToString()) < Convert.ToDateTime(sDts))
{
OleDbCommand upd = new OleDbCommand("update tableB set dts = " + reader2[0] + "where id = '" + sID + "'", conn2);
upd.ExecuteNonQuery();
}
}
}
conn2.Close();
}
}
catch (OleDbException e)
{
sErrorMessages = "";
for (int i = 0; i < e.Errors.Count; i++)
{
sErrorMessages +=
"ErrCount# " + (i + 1) + "\n" +
"Message: " + e.Errors[i].Message + "\n" +
"NativeError: " + e.Errors[i].NativeError + "\n" +
"Source: " + e.Errors[i].Source + "\n" +
"SQLState: " + e.Errors[i].SQLState + "\n\n" + sGetID
;
bError = true;
}
}
}