20221211 - unote 書けば書くほどに

まとめ

■update句でDB1テーブル→DB2テーブルの更新する処理完了

条件は、更新日時でコードが同じものが対象
複数ある場合は、他の条件を盛り込む

string strSql =
"update DEPT_BK DBK " +
"set DBK.DNAME = " +
"(" +
"select D.LOC " +
"from DEPT D " +
"where DBK.DEPTNO = D.DEPTNO " +
//"and KOUSHINBI < " + strKIJYUNBI +
"and D.KOUSHINBI > DBK.KOUSHINBI" +
") " +
"where exists (select * from DEPT D where DBK.DEPTNO = D.DEPTNO and D.KOUSHINBI > DBK.KOUSHINBI)";
//where existsで1行存在する箇所だけを更新する条件を設定、これがないと該当しない箇所がNULLで上書きされる
//

■新規追加のレコード(行)はinsert文で実行

//SQL設定：★なかったらインサートする★
string strSql =
"insert into DEPT_BK4 " +
"select * " +
"from DEPT D " +
"where not exists(" +
"select * " +
"from DEPT_BK4 BK " +
"where BK.DEPTNO = D.DEPTNO" +
")";

■この後やること

関数化して、データベースコピーを行う。
更新条件が一意に決まるように複数条件を確認する必要がある

■C#コード
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;

namespace OracleTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
StartPosition = FormStartPosition.CenterScreen;
}

private void button1_Click(object sender, EventArgs e)
{

//接続文字列
string connStr =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";

//日付の条件：2022 / 11 / 2より後
String strKIJYUNBI = "'" + textBox1.Text + "'";
//string strKIJYUNBI = "'2022/11/1'";

//SQL設定：
string sqlStr = "select * from DEPT where KOUSHINBI >" + strKIJYUNBI;//どうすればいい？
string sqlStr2 = "create table DEPT_BK as select * from DEPT";
string sqlStr3 = "select * from DEPT_BK";

//string sqldelStr = "drop table DEPT_BK3";

try //例外処理
{
//コネクションを生成する
using (OracleConnection conn = new OracleConnection(connStr))
{
//データベースの接続開始
conn.Open();
Console.WriteLine("接続完了");

//コマンドを生成する
using (OracleCommand cmd = new OracleCommand(sqlStr2))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

//SQL実行
DataTable dt = new DataTable();//データテーブル
using (var da = new OracleDataAdapter(cmd))
{
//connection.Open();
da.Fill(dt);
}
}

//dataGridView1.DataSource = dt;

//コマンドを生成する
using (OracleCommand cmd2 = new OracleCommand(sqlStr3))
{
cmd2.Connection = conn;
cmd2.CommandType = CommandType.Text;

//SQL実行
DataTable table = new DataTable();
var adapter = new OracleDataAdapter(cmd2);
adapter.Fill(table);
}

using (OracleCommand cmd3 = new OracleCommand(sqlStr3))
{
cmd3.Connection = conn;
cmd3.CommandType = CommandType.Text;

//コピー先テーブル:DEPT_BK
DataTable dt2 = new DataTable();
using (var da2 = new OracleDataAdapter(cmd3))
{
da2.Fill(dt2);
}

dataGridView2.DataSource = dt2;
}
}
}
catch (Exception ex) //例外時の出力メッセージ
{
Console.WriteLine(ex);
}
}

private void button2_Click(object sender, EventArgs e)
{
//接続文字列
string connStr =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";

//string strKIJYUNBI = "'2022/11/2'";
//string strKIJYUNBI = Max(KOUSHINBI);

//SQL設定：LOCで更新
//string strSql = "update DEPT_BK4 DBK set DBK.DNAME = (select D.LOC from DEPT D where DBK.DEPTNO = D.DEPTNO)";

//コマンドを生成する
using (OracleCommand cmd = new OracleCommand(strSql))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

//SQL実行
cmd.ExecuteNonQuery();
Console.WriteLine("SQL実行完了");
}
}
}

catch (Exception ex) //例外時の出力メッセージ
{
Console.WriteLine(ex);
}
}

private void button3_Click(object sender, EventArgs e)
{
//接続文字列
string connStr =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";

//SQL設定：
string strSql =
"select *" +
"from DEPT_BK";

//コマンドを生成する
using (OracleCommand cmd = new OracleCommand(strSql))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

//cmd.ExecuteNonQuery();

//SQL実行
DataTable dt = new DataTable();
using (var da = new OracleDataAdapter(cmd))
{
da.Fill(dt);
}
dataGridView2.DataSource = dt;//テーブル表示
//dataGridView2.Rows[1].Cells[1].Value = "United States";

//データグリッドビューからの値取り出し
foreach (DataColumn dc in dt.Columns)
{
Console.WriteLine(dc.ColumnName);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr[dc.ColumnName]);
}

}
}
}
}

catch (Exception ex) //例外時の出力メッセージ
{
Console.WriteLine(ex);
}
}

private void button4_Click(object sender, EventArgs e)
{
//接続文字列
string connStr =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";

//コマンドを生成する
using (OracleCommand cmd = new OracleCommand(strSql))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

//SQL実行
cmd.ExecuteNonQuery();
Console.WriteLine("SQL実行完了");

}
}
}

catch (Exception ex) //例外時の出力メッセージ
{
Console.WriteLine(ex);
}
}
}
}

■クラス DB.cs
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;
using System.Xml.Linq;

namespace OracleTest
{
internal class DB
{
OracleConnection conn;

//コンストラクタ
public DB()
{
//データベース接続設定
string connStr =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";

//データベース接続実行
using (OracleConnection conn = new OracleConnection(connStr))
{
conn.Open();
Console.WriteLine("接続完了");
}

}

//SQL実行：dataGridView表示
public DataTable TableShow(string strSql)
{
DataTable dt = new DataTable();
using (OracleCommand cmd = new OracleCommand(strSql))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

using (var da = new OracleDataAdapter(cmd))
{
da.Fill(dt);
}
return dt;//テーブル表示

}
}

//SQL実行
public void ExecuteNonQuery(string strSql)
{
//コマンドを生成する
using (OracleCommand cmd = new OracleCommand(strSql))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}

//参考）
//https://bhunji2000.hatenadiary.org/entry/20100204/1265296229
//https://bhunji2000.hatenadiary.org/entry/20100207/1265518305
//https://extralab.org/wp/csharp-sqlite-db-class-create/

}
}