using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace DBTest_Console
{
internal class Program
{
string strSQL;
//表示SQL
string selTN = "DEPT_BK"; //表示テーブル名
string strSQL_SEL = "select * from ";
//string strSQL_SEL2 = " order by DEPTNO";
//新規追加SQL(テーブル複製)
string creTN = "DEPT_BK2"; //複製テーブル名
string strSQL_CRET;
string sql_creTbl = "create table ";
string sql_asSel = " as select * from DEPT";
//削除SQL
string drpTN = "DEPT_BK"; //削除テーブル名
string strSQL_DRP = "drop table ";
//distinct
string strSQL_DIST;
//更新SQL
string strSQL_UP;
string dbkTN = "DEPT_BK DBK";
string dTN = "DEPT D";
string sql_ON = "(DBK.DEPTNO = D.DEPTNO and DBK.KOUSHINBI < D.KOUSHINBI)";
string col1;
string col2;
string col3;
string whereON;
string RTN;
string XTN;
string strSQL_DTCHK;
string strSQL_MRG;
string strSQL_INS; //INSERT SQL文
string strSQL_MINUS;
//DataView dv = new DataView(dt);
//DataTable dt2 = dv.ToTable(true);
//int i = 0;
//int j = 0;
///
/// MAIN
///
///
static void Main(string args)
{
Console.WriteLine("--- STRAT ---\r\n" + DateTime.Now + "\r\n");
Program pg = new Program();
DataTable dt = new DataTable();
//★実行内容★
//dt = pg.DateCheck_Marge();
pg.setList();
//pg.MINUS();
//pg.A_Table_marge();
//pg.TableDistinct();
//dt = pg.OraMarge();
//pg.OraTableDrop(); //テーブル削除
//pg.OraCopyCreate(); //テーブル複製
Console.WriteLine(DateTime.Now + "\r\n\"--- END ---\r\n");
Console.ReadKey();
}
public void setList()
{
RTN = "DEPT_BK DBK";
XTN = "DEPT D";
//リスト設定
var sampleList = new List
DataTable dt = new DataTable();
//全件数
strSQL_DIST = "select * from " + RTN;
strSQL = strSQL_DIST;
dt = DBA(strSQL); //SQL実行
foreach (DataColumn dc in dt.Columns)
{
Console.WriteLine("■列名：" + dc.ColumnName);
//リスト格納→表示(実験)
sampleList.Add(dc.ColumnName);
foreach (var str in sampleList)
{
Console.WriteLine(str);
/* strSQL_DIST = "select distinct " + str + " from " + RTN;
strSQL = strSQL_DIST;
dt = DBA(strSQL); //SQL実行
Console.WriteLine(dc.ColumnName + ":" + dt.Rows.Count + "件のデータ\r\n");*/
}
}
strSQL_DIST = "select distinct " + sampleList + " from " + RTN;
strSQL = strSQL_DIST;
dt = DBA(strSQL); //SQL実行
}
///
/// https://itsakura.com/sql-minus
/// MINUS
///
public void MINUS()
{
RTN = "DEPT_BK DBK";
XTN = "DEPT D";
col1 = "DEPTNO";
col2 = "DNAME";
col3 = "REGISTEDDT";
whereON = "DBK." + col1 + " = D." + col1;
DataTable dt = new DataTable();
DataRow dt_row;
//差分抽出
strSQL_MINUS = "select * from " +
RTN +
" minus select * from " +
XTN;
strSQL = strSQL_MINUS;
dt = DBA(strSQL); //SQL実行
Console.WriteLine(dt.Rows.Count + "件が更新対象\r\n");
dt_row = dt.Select(); // データテーブルから一致するデータを取得（条件なし）
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName != col1)
{
strSQL_MRG =
"merge into " + RTN +//strign dbkTN
" using " + XTN +//dTN
" on (" + whereON + ")" + //sql_ON
" when matched then update set " +
"DBK." + dc.ColumnName + " = D." + dc.ColumnName;// //sql_UPSET = DBK.dc.ColumnName + D.dc.ColumnName
//抽出後データにMARGE実行
strSQL = strSQL_MRG;
DBA(strSQL); //SQL実行
/* foreach (DataRow dr in dt_row)
{
Console.WriteLine(dc.ColumnName + ":" + dr[dc.ColumnName] + " merge...");
}*/
}
}
Console.WriteLine(); //改行
}
public void TableDistinct()
{
RTN = "DEPT_BK DBK";
XTN = "DEPT D";
DataTable dt = new DataTable();
//全件数
strSQL_DIST = "select * from " + RTN;
strSQL = strSQL_DIST;
dt = DBA(strSQL); //SQL実行
Console.WriteLine("全" + dt.Rows.Count + "件\r\n");
//これで一意の値を決める
strSQL_DIST = "select distinct " + "DEPTNO,DNAME,LOC,KOUSHINBI" + " from " + RTN; //Lsitを突っ込む？
strSQL = strSQL_DIST;
dt = DBA(strSQL); //SQL実行
Console.WriteLine("chk:" + dt.Rows.Count + "件のデータ\r\n");
//リスト設定
var sampleList = new List
foreach (DataColumn dc in dt.Columns)
{
Console.WriteLine("■列名：" + dc.ColumnName);
//リスト格納→表示(実験)
sampleList.Add(dc.ColumnName);
foreach (var str in sampleList)
{
Console.WriteLine(str);
strSQL_DIST = "select distinct " + str + " from " + RTN;
strSQL = strSQL_DIST;
dt = DBA(strSQL); //SQL実行
Console.WriteLine(dc.ColumnName + ":" + dt.Rows.Count + "件のデータ\r\n");
}
//strSQL_DIST = "select distinct " + "DEPTNO,DNAME,LOC,KOUSHINBI" + " from " + RTN; //Lsitを突っ込む？
strSQL_DIST = "select distinct " + dc.ColumnName + " from " + RTN;
strSQL = strSQL_DIST;
//DBA_non_dt(strSQL); //SQL実行
dt = DBA(strSQL); //SQL実行
Console.WriteLine(dc.ColumnName + ":" + dt.Rows.Count + "件のデータ\r\n");
/* foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr[dc.ColumnName]);
}*/
//strSQL_DIST = "select count (distinct " + dc.ColumnName + ") from " + RTN;
//strSQL = strSQL_DIST;
//DBA_non_dt(strSQL); //SQL実行
}
}
public void A_Table_marge()
{
DataColumn dc = new DataColumn();
RTN = "DEPT_BK DBK";
XTN = "DEPT D";
col1 = "DEPTNO";
col2 = "DNAME";
col3 = "REGISTEDDT";
whereON = "DBK." + col1 + " = D." + col1;
//whereON =
// "DBK." + col1 + " = D." + col1 +
// "DBK." + col2 + " = D." + col2;
strSQL_DTCHK =
"select * from " + RTN + " where DBK.KOUSHINBI " + //UPDATEEDDT
"< (select D.KOUSHINBI from " + XTN + //UPDATEEDDT
" where " + whereON +
")";
strSQL_INS =
"INSERT INTO " + RTN +
" SELECT * FROM " + XTN +
" WHERE NOT EXISTS" +
"(SELECT * FROM " + RTN +
" WHERE " + whereON +
")";
strSQL_MRG =
"merge into " + RTN +//strign dbkTN
" using " + XTN +//dTN
" on (" + whereON + ")" + //sql_ON
" when matched then update set " +
"DBK." + dc.ColumnName + " = D." + dc.ColumnName;// //sql_UPSET = DBK.dc.ColumnName + D.dc.ColumnName
DateCheck_Marge();
//OraInsert();
}
public DataTable DateCheck_Marge()
{
Console.WriteLine("日付確認を開始...");
DataTable dt = new DataTable();
DataRow dt_row;
//DataColumn dc = new DataColumn();
//更新日を確認
strSQL = strSQL_DTCHK;
dt = DBA(strSQL); //SQL実行
dt_row = dt.Select(); // データテーブルから一致するデータを取得（条件なし）
Console.WriteLine(dt.Rows.Count + "件が更新対象\r\n");
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName != col1)
{
foreach (DataRow dr in dt_row)
{
Console.WriteLine(dc.ColumnName + ":" + dr[dc.ColumnName] + " merge...");
//抽出後データにMARGE実行
strSQL = strSQL_MRG;
DBA_non_dt(strSQL); //SQL実行
//dt = DBA(strSQL); //SQL実行
//strSQL = strSQL_DTCHK;
//dt = DBA(strSQL); //SQL実行
//dt_row = dt.Select(); // データテーブルから一致するデータを取得（条件なし）
}
}
}
Console.WriteLine(); //改行
/* foreach (DataColumn dc in dt.Columns)
{
Console.WriteLine(dt.Rows[i][dc.ColumnName]);
i++;
}*/
return dt;
}
///
/// MARGE関数(単体)
/// 参考:
/// https://zukucode.com/2017/08/sql-merge.html
/// https://sql-oracle.com/?p=1484
///
///
public DataTable OraMarge()
{
Console.WriteLine("merge開始...");
DataTable dt = new DataTable();
DataRow dt_row;
strSQL = strSQL_MRG;
dt = DBA(strSQL);
dt_row = dt.Select();
foreach (DataRow dr in dt_row)
{
Console.WriteLine("DEPTNO" + ":" + dr["DEPTNO"] + " merge...");
}
//Console.WriteLine(dt.Rows.Count + ":件 merge完了\r\n");
return dt;
}
public DataTable OraTableSelect(string strSQL)
{
Console.WriteLine(selTN + " select実行...");
DataTable dt = new DataTable();
dt = DBA(strSQL);
Console.WriteLine(selTN + " select完了");
return dt;
}
public void OraTableUpdate()
{
Console.WriteLine("update開始...");
DataTable dt = new DataTable();
//データテーブル取得
strSQL = strSQL_SEL + selTN;
MessageBox.Show(strSQL, "確認", MessageBoxButtons.YesNo);
dt = OraTableSelect(strSQL);
//update実行
foreach (DataColumn dc in dt.Columns)
{
//strSQL = "UPDATE DEPT_BK DBK SET DBK.DNAME = 'AAAAA' ";
string upTN = " DEPT_BK DBK ";
string cpTN = " DEPT D ";
string sql_upWhere = " where DBK.DEPTNO = D.DEPTNO ";
string sql_upExis = "where exists (select * from " + cpTN + sql_upWhere + "and D.KOUSHINBI > DBK.KOUSHINBI)";
string sql_upCase =
"case when DBK.KOUSHINBI < (select D.KOUSHINBI from" + cpTN + sql_upWhere + ")" +
"then (select D." + dc.ColumnName + " from" + cpTN + sql_upWhere + ") END " +
sql_upExis;
strSQL_UP = "update" + upTN + "set DBK." + dc.ColumnName + " = " + sql_upCase;
strSQL = strSQL_UP;
//MessageBox.Show(strSQL, "確認", MessageBoxButtons.YesNo);
//case when [更新日比較] then [更新する値代入] end
//where exist(select * ～：対象行のみを条件とする
//参考：
//https://dream-target.jp/2018/02/17/130/
//https://dxo.co.jp/blog/archives/8971
Console.WriteLine(strSQL);
//strSQL = strSQL_UP;
dt = DBA(strSQL);
Console.WriteLine(dc.ColumnName);
}
Console.WriteLine("update完了");
}
public DataTable OraInsert()
{
Console.WriteLine("Insert実行...");
DataTable dt = new DataTable();
strSQL = strSQL_INS;
dt = DBA(strSQL);
Console.WriteLine(dt.Rows.Count + "件：Insert完了\r\n");
return dt;
}
public void OraCopyCreate()
{
strSQL_CRET = sql_creTbl + creTN + sql_asSel;
string msg = strSQL_CRET + " を実行しますか？";
string msg_title = "新規テーブル作成";
// メッセージボックスを表示
DialogResult result = MessageBox.Show(msg, msg_title, MessageBoxButtons.YesNo);
if (result == System.Windows.Forms.DialogResult.Yes)
{
Console.WriteLine(creTN + " creat実行...\r\n");
strSQL = strSQL_CRET;
DBA_non_dt(strSQL);
Console.WriteLine(creTN + " create完了\r\n");
//strSQL = strSQL_SEL + creTN;
////strSQL = "select * from " + creTN + " order by DEPTNO";
//dataGridView1.DataSource = OraTableSelect(strSQL);
}
else if (result == System.Windows.Forms.DialogResult.No)
{
//MessageBox.Show("いいえ");
}
}
public void OraTableDrop()
{
DataTable dt = new DataTable();
strSQL_DRP = strSQL_DRP + drpTN;
string msg = strSQL_DRP + " を実行しますか？";
string msg_title = "テーブル削除";
// メッセージボックスを表示
DialogResult result = MessageBox.Show(msg, msg_title, MessageBoxButtons.YesNo);
if (result == System.Windows.Forms.DialogResult.Yes)
{
Console.WriteLine(drpTN + " drop実行...");
strSQL = strSQL_DRP;
DBA_non_dt(strSQL);
Console.WriteLine(drpTN + " drop完了");
}
else if (result == System.Windows.Forms.DialogResult.No)
{
//MessageBox.Show("いいえ");
}
//return dt;
}
///
/// DBA SQL実行(dt)
///
public DataTable DBA(string strSQL)
{
//接続文字列
string strConn =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";
DataTable dt = new DataTable();//データテーブル
using (OracleConnection conn = new OracleConnection(strConn))
using (OracleCommand cmd = new OracleCommand(strSQL, conn))
using (var da = new OracleDataAdapter(cmd))
{
conn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("SQL実行\r\n");
da.Fill(dt);
}
return dt;
}
///
/// DBA_non_dt SQL実行ExecuteNonQuery
///
public void DBA_non_dt(string strSQL)
{
//接続文字列
string strConn =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";
//DataTable dt = new DataTable();//データテーブル
using (OracleConnection conn = new OracleConnection(strConn))
using (OracleCommand cmd = new OracleCommand(strSQL, conn))
//using (var da = new OracleDataAdapter(cmd))
{
conn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("SQL実行\r\n");
//da.Fill(dt);
}
//return dt;
//dataGridView1.DataSource = dt; //グリッドビューにデータ表示
}
}
}
