//private void btnCancel_Click(object sender, EventArgs e)
//{
// lsvCompareResult.Items.Add("Cancelling...");
// _CompareBgWorker.CancelAsync();
//}
private void btnCompare3_Click(object sender, EventArgs e)
{
Debug.WriteLine("ON : btnCompare3_Click()...");
// resource
OracleConnection connOra = null; // new OracleConnection(connInfoOra.ConnString);
SqlConnection connSql = null; // new SqlConnection(connInfoSql.ConnString);
try
{
//# prefix
if (txtDbName.Text.Trim() == string.Empty)
{
if (sender == btnBatchCmp)
toolTip1.Show("need [DB.Table] to use to compare.", this);
else
MessageBox.Show("need [DB.Table] to use to compare.");
return;
}
if (txtOrcRowCnt.Text.Trim() == string.Empty || txtSqlRowCnt.Text.Trim() == string.Empty)
{
if (sender == btnBatchCmp)
toolTip1.Show("the [Oracle Row Count], [Sql Row Count] should be taken before", this);
else
MessageBox.Show("the [Oracle Row Count], [Sql Row Count] should be taken before");
return;
}
//# UI control
//this.Cursor = Cursors.WaitCursor;
this.lsvCompareResult.Cursor = Cursors.WaitCursor;
this.trvDbTables.Cursor = Cursors.WaitCursor;
this.btnCompare3.Enabled = false;
this.btnCancel3.Enabled = true;
this.btnShowSchema.Enabled = false;
//# UI control - reste columns
lsvCompareResult.Clear();
lsvCompareResult.Columns.Add(colMsgTag);
lsvCompareResult.Columns.Add(colKeyValue);
lsvCompareResult.Columns.Add(colDescription);
// Initialize [UserState]
CompareBgWorker_UserState userState = new CompareBgWorker_UserState();
// arguments
userState.DbName = txtDbName.Text;
userState.TableName = txtTableName.Text;
userState.OraQryCmd = BuildQracleQueryCmd(txtDbName.Text, txtTableName.Text, txtWhereCond.Text, txtOrderBy.Text, numTopRow.Value);
userState.SqlQryCmd = BuildSqlQueryCmd(txtDbName.Text, txtTableName.Text, txtWhereCond.Text, txtOrderBy.Text, numTopRow.Value);
// user state
userState.TotalRowCnt = numTopRow.Value == 0 ? int.Parse(txtOrcRowCnt.Text.Replace(",", "")) : (int)numTopRow.Value;
userState.OraReadCnt = 0;
userState.SqlReadCnt = 0;
userState.NotEqualCnt = 0;
userState.ManualStop = false;
//userState.Message = string.Empty;
btnCancel3.Tag = null; // "CancellationPending";
// resource : for EvaluteFinishTime
double interval_time = 2.0;
EvaluteFinishTime evaluteFinishTime = new EvaluteFinishTime(userState.TotalRowCnt, (int)interval_time);
// show arguments
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Argument" // message tag
, "Oracle Query Command" // key value
, userState.OraQryCmd // description
}));
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Argument" // message tag
, "Sql Query Command" // key value
, userState.SqlQryCmd // description
}));
// -- to show progress
this.StatusProgressBar1.Minimum = 0;
this.StatusProgressBar1.Maximum = userState.TotalRowCnt;
this.StatusProgressBar1.Value = 0; // userState.OraReadCnt; // to show progress - 0% - BEGIN
Application.DoEvents(); // show me some stuff
//// parse & take argument.
//CompareBgWorker_UserState userState = (CompareBgWorker_UserState)e.Argument;
// 1.Get connection information.
Database1DataSet.ConnInfoRow connInfoOra = this.connInfoTableAdapter.GetDataByPk("Ora", userState.DbName)[0];
Database1DataSet.ConnInfoRow connInfoSql = this.connInfoTableAdapter.GetDataByPk("Sql", userState.DbName)[0];
//OracleConnection
connOra = new OracleConnection(connInfoOra.ConnString);
//SqlConnection
connSql = new SqlConnection(connInfoSql.ConnString);
// 2.Open connection
connOra.Open();
connSql.Open();
// 3.Build command
string cmdTextOra = userState.OraQryCmd; // string.Format("SELECT * FROM \"{0}\".\"{1}\"", userState.DbName, userState.TableName);
OracleCommand dbCmdOra = new OracleCommand(cmdTextOra, connOra);
dbCmdOra.CommandTimeout = (int)numTimeout.Value;
string cmdTextSql = userState.SqlQryCmd; // string.Format("SELECT * FROM [{0}].dbo.[{1}]", userState.DbName, userState.TableName);
SqlCommand dbCmdSql = new SqlCommand(cmdTextSql, connSql);
dbCmdSql.CommandTimeout = (int)numTimeout.Value;
#region 4.Execute cmmand and get ResultSet
var readerOra = dbCmdOra.ExecuteReader();
var readerSql = dbCmdSql.ExecuteReader();
try
{
// resource
string first_not_equal_field = string.Empty;
// get schema
DataTable dtSchema = readerOra.GetSchemaTable();
userState.OraReadCnt = 0;
userState.SqlReadCnt = 0;
DateTime checkPoint = DateTime.Now;
// compare field by field
while (readerOra.Read())
{
userState.OraReadCnt++;
if (readerSql.Read())
{
userState.SqlReadCnt++;
for (int i = 0; i < dtSchema.Rows.Count; i++) // schema-column
{
Type oraType = readerOra.GetFieldType(i);
Type sqlType = readerSql.GetFieldType(i);
object oraValue; // = readerOra.GetValue(i);
object sqlValue; // = readerSql.GetValue(i);
if (oraType == typeof(decimal) && sqlType == typeof(double))
{
if (readerOra.IsDBNull(i))
oraValue = DBNull.Value;
else
oraValue = readerOra.GetDecimal(i);
if (readerSql.IsDBNull(i))
sqlValue = DBNull.Value;
else
sqlValue = new Decimal(readerSql.GetDouble(i));
}
else // default
{
oraValue = readerOra.GetValue(i);
sqlValue = readerSql.GetValue(i);
}
// compare
if (!(oraValue.Equals(sqlValue)))
{
userState.NotEqualCnt++;
//
if (first_not_equal_field == string.Empty)
first_not_equal_field = Convert.ToString(dtSchema.Rows[i]["ColumnName"]);
}
}
//# Not equal & stopping comparing
if (userState.NotEqualCnt > 0)
{
StringBuilder oraFiledValues = new StringBuilder("{"); // json format
StringBuilder sqlFiledValues = new StringBuilder("{");
// the first
object oraValue = readerOra.GetValue(0);
object sqlValue = readerSql.GetValue(0);
oraFiledValues.AppendFormat("{0}: {1}", dtSchema.Rows[0]["ColumnName"], Convert.ToString(oraValue));
sqlFiledValues.AppendFormat("{0}: {1}", dtSchema.Rows[0]["ColumnName"], Convert.ToString(sqlValue));
// as following
for (int i = 1; i < dtSchema.Rows.Count; i++) // schema-column
{
oraValue = readerOra.GetValue(i);
sqlValue = readerSql.GetValue(i);
oraFiledValues.AppendFormat(",{0}: {1}", dtSchema.Rows[i]["ColumnName"], Convert.ToString(oraValue));
sqlFiledValues.AppendFormat(",{0}: {1}", dtSchema.Rows[i]["ColumnName"], Convert.ToString(sqlValue));
}
oraFiledValues.Append("}");
sqlFiledValues.Append("}");
#region show progress
// show "step" message
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Progress" // Message Tag
, string.Format("{0:N0}%", userState.PercentProgress) // key value
, string.Format("{0:N0} \\ {1:N0} \\ {2:N0} \\ {3:N0}", userState.TotalRowCnt, userState.OraReadCnt, userState.SqlReadCnt, userState.NotEqualCnt) // description
}));
// show "Not equal"
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Not equal!!!" // Message Tag
, "first_not_equal_field" // key value
, first_not_equal_field // description
}));
//
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Not equal first" // Message Tag
, "Ora value" // key value
, oraFiledValues.ToString() // description
}));
//
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Not equal first" // Message Tag
, "Sql value" // key value
, sqlFiledValues.ToString() // description
})).EnsureVisible();
Application.DoEvents(); // show me some stuff
#endregion
//## not equal & leave
break;
}
//-------------------------
}
// repore progrss in one second.
//double interval_time = 1.0;
if (DateTime.Now.Subtract(checkPoint).TotalSeconds >= interval_time)
{
//_CompareBgWorker.ReportProgress(userState.PercentProgress, userState);
#region show progress
// evaluate the finish time
StatusLabel1.Text = evaluteFinishTime.Evalute(userState.OraReadCnt);
// show "step" message
ListViewItem addedItem = lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Progress" // Message Tag
, string.Format("{0:N0}%", userState.PercentProgress) // key value
, string.Format("{0:N0} \\ {1:N0} \\ {2:N0} \\ {3:N0}", userState.TotalRowCnt, userState.OraReadCnt, userState.SqlReadCnt, userState.NotEqualCnt) // description
}));
addedItem.EnsureVisible(); // force to show
// to show progress
this.StatusProgressBar1.Value = userState.OraReadCnt;
this.StatusProgressBar1.Invalidate();
// don't let application fall into "not response"!
Application.DoEvents();
#endregion
// next check point
checkPoint = DateTime.Now;
}
//if (_CompareBgWorker.CancellationPending)
if(btnCancel3.Tag != null && (string)btnCancel3.Tag == "CancellationPending")
{
//## cancel & leave
// e.Cancel = true;
// remark "e.Cancel", beecust it makes "e.Result" un-available. use "userState.ManualStop" to instead.
userState.ManualStop = true;
break;
}
}
//// success & set up result to return
//e.Result = userState;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//e.Result = ex;
// show statistics & message
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Abort"
, ex.GetType().ToString()
, ex.Message
}));
return; // leave
}
finally
{
readerOra.Close();
readerSql.Close();
}
#endregion
//## 5.close
connOra.Close();
connSql.Close();
//## 6 success & show result
// show statistics & message
if (userState.ManualStop)
lsvCompareResult.Items.Add("Compare cancelled");
else
lsvCompareResult.Items.Add("Compare finished");
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Ora read count"
, string.Format("{0:N0}", userState.OraReadCnt)
}));
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Sql read count"
, string.Format("{0:N0}", userState.SqlReadCnt)
}));
lsvCompareResult.Items.Add(new ListViewItem(new string[] {
"Not equal fields"
, string.Format("{0:N0}", userState.NotEqualCnt)
})).EnsureVisible(); // show me
// to show progress - 0% - END
this.StatusProgressBar1.Value = this.StatusProgressBar1.Minimum;
StatusLabel1.Text = "00:00:00";
//this.StatusProgressBar1.Invalidate();
// to show result.
txtOraQryCmd.Text = userState.OraQryCmd;
txtSqlQryCmd.Text = userState.SqlQryCmd;
txtCmpResult.Text = userState.ManualStop ? "cancel"
: userState.NotEqualCnt == 0 ? "equal"
: "may not equal";
txtLogDtm.Text = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
//# save the result to DB
// update status
Database1DataSet.TabCompareRecordRow lastCmpRec = this.DataStore.TabCompareRecord[0];
//lastCmpRec.DbName = userState.DbName; // PK
//lastCmpRec.TableName = userState.TableName; // PK
lastCmpRec.WhereCond = txtWhereCond.Text.Trim();
lastCmpRec.OrderBy = txtOrderBy.Text.Trim();
lastCmpRec.TopRow = (int)numTopRow.Value;
lastCmpRec.OraQryCmd = userState.OraQryCmd;
lastCmpRec.SqlQryCmd = userState.SqlQryCmd;
lastCmpRec.LastCmpResult = txtCmpResult.Text.Trim();
lastCmpRec.LogDtm = DateTime.Now;
//
int ret = this.tabCompareRecordTableAdapter.Update(lastCmpRec);
}
catch (Exception ex)
{
// release resource
connOra.Close();
connSql.Close();
MessageBox.Show(ex.Message);
//e.Result = ex;
}
finally
{
// UI control - mouse
//this.Cursor = Cursors.Default;
this.lsvCompareResult.Cursor = Cursors.Default;
this.trvDbTables.Cursor = Cursors.Default;
this.btnCompare3.Enabled = true;
this.btnCancel3.Enabled = false;
this.btnShowSchema.Enabled = true;
}
}
private void btnHardCompare_Click(object sender, EventArgs e)
{
try
{
//# prefix
if (txtDbName.Text.Trim() == string.Empty)
{
MessageBox.Show("need [DB.Table] to use to compare.");
return;
}
if (txtOrcRowCnt.Text.Trim() == string.Empty || txtSqlRowCnt.Text.Trim() == string.Empty)
{
MessageBox.Show("the [Oracle Row Count], [Sql Row Count] should be taken before");
return;
}
//# UI Control
this.Cursor = Cursors.WaitCursor;
this.btnHardCompare.Enabled = false;
this.lsvMessage.Items.Clear();
//# take parameters
string dbName = txtDbName.Text.Trim();
string tableName = txtTableName.Text.Trim();
//#
// 1.Get connection information.
Database1DataSet.ConnInfoRow connInfoOra = this.connInfoTableAdapter.GetDataByPk("Ora", dbName)[0];
Database1DataSet.ConnInfoRow connInfoSql = this.connInfoTableAdapter.GetDataByPk("Sql", dbName)[0];
OracleConnection connOra = new OracleConnection(connInfoOra.ConnString);
SqlConnection connSql = new SqlConnection(connInfoSql.ConnString);
// 2.Open connection
connOra.Open();
connSql.Open();
// 3.Build command - take schema
// select from
string cmdTextOra = string.Format("SELECT * FROM \"{0}\".\"{1}\"", dbName, tableName);
// where
if (txtWhereCond.Text != string.Empty)
{
cmdTextOra += " WHERE " + txtWhereCond.Text;
}
OracleCommand dbCmdOra = new OracleCommand(cmdTextOra, connOra);
dbCmdOra.CommandTimeout = 0;
// 4.Execute cmmand - take schema
DataTable dtSchema;
using (OracleDataReader readerOra = dbCmdOra.ExecuteReader(CommandBehavior.SchemaOnly))
{
dtSchema = readerOra.GetSchemaTable();
}
// 5.Compare Fields
int oraReadCnt = 0;
int sqlCheckCnt = 0;
int notEqualCnt = 0;
//
int totRowCnt = int.MaxValue; // = int.Parse(txtOrcRowCnt.Text);
int.TryParse(txtOrcRowCnt.Text.Replace(",", ""), out totRowCnt);
// resource : for EvaluteFinishTime
double interval_time = 2.0;
EvaluteFinishTime evaluteFinishTime = new EvaluteFinishTime(totRowCnt, (int)interval_time);
StringBuilder cmdTextSql = new StringBuilder();
// show arguments
lsvMessage.Items.Add(new ListViewItem(new string[] {
"Argument" // message tag
, "Oracle Query Command" // key value
, cmdTextOra // description
}));
// -- to show progress
this.StatusProgressBar1.Minimum = 0;
this.StatusProgressBar1.Maximum = totRowCnt;
this.StatusProgressBar1.Value = 0;
Application.DoEvents(); // show me
DateTime checkPoint = DateTime.Now;
using (OracleDataReader readerOra = dbCmdOra.ExecuteReader(CommandBehavior.Default))
{
while (readerOra.Read())
{
oraReadCnt++;
//# build command to compare.
//StringBuilder
cmdTextSql = new StringBuilder(string.Format(@"SELECT COUNT(*) FROM [dbo].[{0}]", tableName));
for (int i = 0; i < dtSchema.Rows.Count; i++)
{
Type oraType = readerOra.GetFieldType(i);
object oraValue = readerOra.GetValue(i);
// arguments
string concat = (i == 0) ? "WHERE" : "AND";
//if (dtSchema.Rows[i]["ColumnName"].Equals("RATING"))
// Debugger.Break();
//if (dtSchema.Rows[i]["ColumnName"].Equals("REPORT_NAV"))
// Debugger.Break();
// concat field comparing...
if (readerOra.IsDBNull(i))
{
cmdTextSql.AppendFormat(" {0} [{1}] IS NULL", concat, dtSchema.Rows[i]["ColumnName"]);
}
else if (oraValue is string)
{
cmdTextSql.AppendFormat(" {0} [{1}]=N'{2}'", concat, dtSchema.Rows[i]["ColumnName"], oraValue);
}
else if (oraValue is DateTime)
{
cmdTextSql.AppendFormat(" {0} [{1}]='{2:yyyy-MM-dd HH:mm:ss}'", concat, dtSchema.Rows[i]["ColumnName"], oraValue);
}
//cmdTextSql.AppendFormat(" {0} CONVERT(VARCHAR,[{1}],121)='{2:yyyy-MM-dd HH:mm:ss}'", concat, columns[i], values[i]);
else if (oraType == typeof(Decimal) && dtSchema.Rows[i]["NumericPrecision"].Equals((Int16)126) && dtSchema.Rows[i]["NumericScale"].Equals((Int16)129)) // float
{
// ex: AND ABS([REPORT_NAV]-14.29) < 0.00001
cmdTextSql.AppendFormat(" {0} ABS([{1}]-({2}))<0.00001", concat, dtSchema.Rows[i]["ColumnName"], oraValue);
}
else
{
cmdTextSql.AppendFormat(" {0} [{1}]={2}", concat, dtSchema.Rows[i]["ColumnName"], oraValue);
}
}
////# TRACE
//listView1.Items.Add(new ListViewItem(new string[]{
// "TRACE"
// ,"CmdText"
// , cmdText.ToString()}));
//# do compare
//string cmdTextOra = string.Format("SELECT * FROM \"{0}\".\"{1}\"", dbName, tableName);
SqlCommand dbCmdSql = new SqlCommand(cmdTextSql.ToString(), connSql);
dbCmdSql.CommandTimeout = 0;
int IsExists = (int)dbCmdSql.ExecuteScalar();
if (IsExists > 0)
{
sqlCheckCnt++;
}
else
{
notEqualCnt++;
// show "not equal" message
lsvMessage.Items.Add(new ListViewItem(new string[] {
"Message" // Message Tag
, "Not equal" //string.Format("{0:N0}%", ) // key value
, cmdTextSql.ToString() // description
})).EnsureVisible(); // force to show
break; // leave
}
//# repore progrss each 2 second.
//double interval_time = 2.0;
if (DateTime.Now.Subtract(checkPoint).TotalSeconds >= interval_time)
{
#region ReportProgress
// show what doing
txtSqlLastChkCmd.Text = cmdTextSql.ToString();
// evaluate the finish time
StatusLabel1.Text = evaluteFinishTime.Evalute(oraReadCnt);
// show "step" message
ListViewItem addedItem = lsvMessage.Items.Add(new ListViewItem(new string[] {
"Progress" // Message Tag
, CalculatePercent(oraReadCnt, totRowCnt) // key value
, string.Format("{0:N0} \\ {1:N0} \\ {2:N0} \\ {3:N0}", totRowCnt, oraReadCnt, sqlCheckCnt, notEqualCnt) // description
}));
addedItem.EnsureVisible(); // force to show
// show progress
this.StatusProgressBar1.Value = oraReadCnt;
// don't let application fall into "not response"!
Application.DoEvents();
#endregion
// next check point
checkPoint = DateTime.Now;
}
} // end of: while (readerOra.Read())
}
//## 6 success & show result
// show what doing
txtSqlLastChkCmd.Text = cmdTextSql.ToString();
// show statistics & message
lsvMessage.Items.Add("Compare Finished");
lsvMessage.Items.Add(new ListViewItem(new string[] {
"Ora Read Count"
, string.Format("{0:N0}", oraReadCnt)
}));
lsvMessage.Items.Add(new ListViewItem(new string[] {
"Sql Check Count"
, string.Format("{0:N0}", sqlCheckCnt)
}));
lsvMessage.Items.Add(new ListViewItem(new string[] {
"Not Equal Rows"
, string.Format("{0:N0}", notEqualCnt)
})).EnsureVisible(); // show me
// to show progress - 0% - END
this.StatusProgressBar1.Value = this.StatusProgressBar1.Minimum;
StatusLabel1.Text = "00:00:00";
// to show result.
txtOraQryCmd.Text = cmdTextOra;
//txtSqlQryCmd.Text = userState.SqlQryCmd;
txtCmpResult.Text = (notEqualCnt == 0) ? "equal" : "may not equal";
txtLogDtm.Text = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
//# save the result to DB
// update status
Database1DataSet.TabHardCmpRecordRow lastCmpRec = this.DataStore.TabHardCmpRecord[0];
//lastCmpRec.DbName = userState.DbName; // PK
//lastCmpRec.TableName = userState.TableName; // PK
lastCmpRec.WhereCond = txtWhereCond.Text.Trim();
lastCmpRec.OraQryCmd = cmdTextOra;
lastCmpRec.SqlLastChkCmd = cmdTextSql.ToString();
lastCmpRec.LastCmpResult = txtCmpResult.Text.Trim();
lastCmpRec.LogDtm = DateTime.Now;
//
int ret = this.tabHardCmpRecordTableAdapter.Update(lastCmpRec);
// 7.close
connOra.Close();
connSql.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//# UI Control
this.Cursor = Cursors.Default;
this.btnHardCompare.Enabled = true;
}
}
