20230113

static void Main(string[] args)
{
Console.WriteLine("--- STRAT ---\r\n" + DateTime.Now + "\r\n");

Program pg = new Program();
DataTable dt = new DataTable();

//★実行内容★
pg.B_TableMarge();
//pg.OraTableDrop(); //テーブル削除
//pg.OraCopyCreate(); //テーブル複製

//pg.Count();
//pg.MINUS();
//pg.TableDistinct();
//dt = pg.OraMarge();

Console.WriteLine(DateTime.Now + "\r\n\"--- END ---\r\n");
Console.ReadKey();
}

public void B_TableMarge()
{
DataTable dt = new DataTable();

string repTN = "DEPT_BK R";
string xbbTN = "DEPT X";

col1 = "DEPTNO";
col2 = "KOUSHINBI";

whereON =
"R." + col2 + "< X." + col2 +
" and R." + col1 + " = X." + col1;

int colNum = 2;

//全件数カウント
CNT(repTN);

//marge実行
MRG(repTN, xbbTN, colNum);

}

public void CNT(string repTN)
{
DataTable dt = new DataTable();

//全件数
strSQL = "select count(*) from " + repTN; ;
dt = DBA(strSQL); //SQL実行
Console.WriteLine(repTN + " 全" + dt.Rows[0][0] + "行\r\n");
}

public void MRG(string repTN, string xbbTN, int colNum)
{
DataTable dt = new DataTable();
int i = 1;

//差分抽出
dt = TableMinus(repTN, xbbTN);

switch (colNum)
{
case 0:

break;

case 1:

if (dt.Rows.Count != 0)
{
foreach (DataColumn dc in dt.Columns)
{
Console.WriteLine("marge開始...\r\n");

if (dc.ColumnName == col1)
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新不可\r\n");
}

else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...\r\n");

strSQL_MRG =
"merge into " + repTN +//strign dbkTN
" using " + xbbTN +//dTN
" on (" + whereON + ")" + //sql_ON
" when matched then update set " +
"R." + dc.ColumnName + " = X." + dc.ColumnName;// //sql_UPSET = DBK.dc.ColumnName + D.dc.ColumnName

strSQL = strSQL_MRG;
dt = DBA(strSQL);
}

i++;
}
}

break;

case 2:

if (dt.Rows.Count != 0)
{
foreach (DataColumn dc in dt.Columns)
{

if (dc.ColumnName == col1 || dc.ColumnName == col2)
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新不可\r\n");
}

else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...\r\n");

strSQL_MRG =
"merge into " + repTN +//strign dbkTN
" using " + xbbTN +//dTN
" on (" + whereON + ")" + //sql_ON
" when matched then update set " +
"R." + dc.ColumnName + " = X." + dc.ColumnName;// //sql_UPSET = DBK.dc.ColumnName + D.dc.ColumnName

strSQL = strSQL_MRG;
dt = DBA(strSQL);
}

i++;
}
}

break;
}

}

public DataTable TableMinus(string repTN, string xbbTN)
{
DataTable dt = new DataTable();

Console.WriteLine("差分抽出開始...\r\n");

strSQL_MINUS =
"select * from " + repTN +
" minus select * from " + xbbTN;

strSQL = strSQL_MINUS;
dt = DBA(strSQL); //SQL実行

Console.WriteLine(dt.Rows.Count + "行が更新対象\r\n");

return dt;
}