private void ReconcileData(DataSet ds)
{
ClassDestinationDb OBJ = new ClassDestinationDb();
if (ClassSqlLit.SourceMasterColum != string.Empty && ClassSqlLit.DestinationMsterColum != string.Empty)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
string dui_id = "0";
if (row[ClassSqlLit.SourceMasterColum] != DBNull.Value)
{
dui_id = row[ClassSqlLit.SourceMasterColum].ToString();
lblStatus.Text = "Fetching... Please wait.";
Application.DoEvents();
string Cmd = "select * from " + varDesinationTableName + " where " + ClassSqlLit.DestinationMsterColum + " ='" + dui_id + "'";
DataSet dsRecon = new DataSet();
if (ClassDBInfo.DBType.ToUpper() == "SQL")
{
dsRecon = OBJ.GeneralSearchSql(Cmd);
}
else if (ClassDBInfo.DBType.ToUpper() == "ORACLE")
{
dsRecon = OBJ.GeneralSearchOracle(Cmd);
}
else if (ClassDBInfo.DBType.ToUpper() == "MYSQL")
{
dsRecon = OBJ.GeneralSearchMySql(Cmd);
}
if (OBJ.CheckDs(dsRecon) == false)
{
//row.Delete();
//ds.Tables[0].AcceptChanges();
dsRecords.Tables[0].Rows.Add(row.ItemArray);
dsRecords.Tables[0].AcceptChanges();
}
dsRecon.Clear();
dsRecon.Dispose();
dsRecon = null;
}
Application.DoEvents();
}
}
else
{
MessageBox.Show("Master column mappings should be defined", "ReconcileData", MessageBoxButtons.OK);
}
OBJ = null;
}
private void ProcessInsertSql()
{
ClassDestinationDb OBJ = new ClassDestinationDb();
if (OBJ.CheckDs(dsRecords) == true)
{
int index = 0;
int reccnt = 1;
foreach (DataRow RowRec in dsRecords.Tables[0].Rows)
{
SqlCommand Cmd = new SqlCommand();
string varCols = "";
string varColVal = "";
foreach (DataRow row in varTable.Rows)
{
varCols = varCols + row["DestinationColumn"].ToString() + ",";
varColVal = varColVal + "@" + row["DestinationColumn"].ToString() + ",";
Cmd.Parameters.AddWithValue("@" + row["DestinationColumn"].ToString(), RowRec[row["SourceColumn"].ToString()]);
}
varCols = varCols.Remove(varCols.Length - 1, 1);
varColVal = varColVal.Remove(varColVal.Length - 1, 1);
string varCmd = "insert into " + varDesinationTableName + "(" + varCols + ")values(" + varColVal + ")";
Cmd.CommandText = varCmd;
try
{
OBJ.GeneralInsertMethodSql(Cmd, spath);
DG1.Rows[index].Selected = true;
index += 1;
lblStatus.Text = "Start Data Transfer " + reccnt + " out of " + dsRecords.Tables[0].Rows.Count.ToString();
reccnt = reccnt + 1;
}
catch (Exception ex)
{
WriteErrorLog(spath, ex.Message + " " + ex.ToString());
// MessageBox.Show(ex.Message.ToString(), "ProcessInsertSql", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Application.DoEvents();
}
}
}
private void FillDestinationTables()
{
CboDestinationTables.Items.Clear();
ClassDestinationDb obj = new ClassDestinationDb();
DataSet dsdata = new DataSet();
string TableName = CboSourceTables.Text;
Application.DoEvents();
if (CboDbType.SelectedIndex == 1)
{
dsdata = obj.GeneralSearchSql("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
}
if (CboDbType.SelectedIndex == 2)
{
dsdata = obj.GeneralSearchOracle("SELECT table_name FROM user_tables order by table_name");
}
if (CboDbType.SelectedIndex == 3)
{
dsdata = obj.GeneralSearchMySql("SELECT table_name FROM information_schema.tables where table_schema='" + ClassDBInfo.DbName + "' order by table_name");
}
if (dsdata != null)
{
if (dsdata.Tables.Count > 0)
{
if (dsdata.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dsdata.Tables[0].Rows)
{
//DataRow[] varRow = dsForLoggTable.Tables[0].Select("TableName = '" + row[0].ToString() + "'") ;
//if (varRow.Length > 0)
//{
CboDestinationTables.Items.Add(row[0].ToString());
// }
}
CboDestinationTables.SelectedIndex = 0;
}
}
}
}
private void FillDestinationFields()
{
LvDestination.Items.Clear();
ClassDestinationDb obj = new ClassDestinationDb();
DataSet dsdata = new DataSet();
string TableName = CboDestinationTables.Text;
Application.DoEvents();
if (CboDbType.SelectedIndex == 1)
{
dsdata = obj.GeneralSearchSql("select Column_Name,Data_Type from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + TableName + "' order by Ordinal_Position");
}
if (CboDbType.SelectedIndex == 2)
{
dsdata = obj.GeneralSearchOracle("select column_name,data_type from user_tab_columns where TABLE_NAME = upper('" + TableName + "') order by column_id");
}
if (CboDbType.SelectedIndex == 3)
{
dsdata = obj.GeneralSearchMySql("select Column_Name,Data_Type from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + TableName + "'");
}
if (dsdata != null)
{
if (dsdata.Tables.Count > 0)
{
if (dsdata.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dsdata.Tables[0].Rows)
{
LvDestination.Items.Add(row[0].ToString());
LvDestination.Items[LvDestination.Items.Count - 1].SubItems.Add(row[1].ToString());
}
}
}
}
}
private void ProcessInsertOracle()
{
ClassDestinationDb OBJ = new ClassDestinationDb();
if (OBJ.CheckDs(dsRecords) == true)
{
int index = 0;
int reccnt = 1;
foreach (DataRow RowRec in dsRecords.Tables[0].Rows)
{
OleDbCommand Cmd = new OleDbCommand();
string varCols = "";
string varColVal = "";
string varValues = "";
foreach (DataRow row in varTable.Rows)
{
varCols = varCols + row["DestinationColumn"].ToString() + ",";
varColVal = varColVal + "?,";
//string varTempVal = RowRec[row["SourceColumn"].ToString()].ToString();
if (CheckNullValue(row["SourceColumn"].ToString(), RowRec))
{
Cmd.Parameters.AddWithValue("@" + row["DestinationColumn"].ToString(), DBNull.Value);
}
else
{
if (row["ColumnDataType"].ToString().ToUpper() == "BIT")
{
string varBitVal = "0";
if (RowRec[row["SourceColumn"].ToString()].ToString().ToUpper() == "TRUE")
{
varBitVal = "1";
}
Cmd.Parameters.AddWithValue("@" + row["DestinationColumn"].ToString(), varBitVal);
}
else
{
Cmd.Parameters.AddWithValue("@" + row["DestinationColumn"].ToString(), RowRec[row["SourceColumn"].ToString()]);
}
}
varValues += RowRec[row["SourceColumn"].ToString()] + " , ";
}
varCols = varCols.Remove(varCols.Length - 1, 1);
varColVal = varColVal.Remove(varColVal.Length - 1, 1);
string varCmd = "insert into " + varDesinationTableName + "(" + varCols + ")values(" + varColVal + ")";
Cmd.CommandText = varCmd;
try
{
OBJ.GeneralInsertMethodOracle(Cmd, spath);
DG1.Rows[index].Selected = true;
index += 1;
lblStatus.Text = "Start Data Transfer " + reccnt + " out of " + dsRecords.Tables[0].Rows.Count.ToString();
reccnt = reccnt + 1;
}
catch (Exception ex)
{
WriteErrorLog(spath, ex.Message + " " + ex.ToString());
//MessageBox.Show(ex.Message.ToString(), "ProcessInsertOracle", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Application.DoEvents();
}
}
}
