using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ADO.接続型
{
public partial class 接続型_frm : Form
{
string _sProvider = @"Microsoft.ACE.OLEDB.12.0";
string _sDataSorce = @"D:\_Prog\CS\ADO\Northwind.accdb";
string _sPass = @"pass";
public 接続型_frm()
{
InitializeComponent();
}
private void 接続型_frm_Load(object sender, EventArgs e)
{
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_254_DB接続_Click(object sender, EventArgs e)
{
mth_254_DB接続();
}
<summary>
</summary>
private void mth_254_DB接続()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
try
{
cn.Open();
System.Windows.Forms.MessageBox.Show("接続しました", "通知");
cn.Close();
}
catch (Exception)
{
throw;
}
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_255_DB接続_状態確認_Click(object sender, EventArgs e)
{
mth_255_DB接続_状態確認();
}
<summary>
</summary>
private void mth_255_DB接続_状態確認()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
try
{
cn.Open();
System.Windows.Forms.MessageBox.Show($"接続状態 : {cn.State}", "通知");
cn.Close();
System.Windows.Forms.MessageBox.Show($"接続状態 : {cn.State}", "通知");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "通知");
}
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_256_テーブルリスト_Click(object sender, EventArgs e)
{
DataTable dt = mth_256_テーブルリスト();
this.dgv.DataSource = dt;
this.dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
<summary>
</summary>
private DataTable mth_256_テーブルリスト()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
DataTable dt = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" }
);
cn.Close();
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr["TABLE_NAME"]);
}
return dt;
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_257_フィールドリスト_Click(object sender, EventArgs e)
{
DataTable dt = mth_257_フィールドリスト();
this.dgv.DataSource = dt;
this.dgv.Font = new Font("Arial", 8.00F, FontStyle.Regular);
this.dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
<summary>
</summary>
<returns></returns>
private DataTable mth_257_フィールドリスト()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
DataTable dt = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, "Employees", null }
);
cn.Close();
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr[3]);
Console.WriteLine(dr["COLUMN_NAME"]);
}
return dt;
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_258_テーブル作成_Click(object sender, EventArgs e)
{
mth_258_テーブル作成();
}
<summary>
</summary>
private void mth_258_テーブル作成()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "CREATE TABLE テスト(コード INT, 商品名 TEXT)";
try
{
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("Create TB", "通知");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "通知");
}
finally
{
cn.Close();
}
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_259_テーブル削除_Click(object sender, EventArgs e)
{
mth_259_テーブル削除();
}
<summary>
</summary>
private void mth_259_テーブル削除()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "DROP TABLE テスト";
try
{
var cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("Delete TB", "通知");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "通知");
}
finally
{
cn.Close();
}
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_260_レコード追加_Click(object sender, EventArgs e)
{
mth_260_レコード追加();
}
<summary>
</summary>
private void mth_260_レコード追加()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "INSERT INTO 社員 VALUES(100091, '百田かなこ', 'C-001')";
sSQL = "INSERT INTO 社員 ([社員番号], [氏名], [部門コード])" ;
sSQL += " VALUES(200091, '百田かなこ', 'C-001')";
try
{
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("Add Record", "通知");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "通知");
}
finally
{
cn.Close();
}
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_261_レコード更新_Click(object sender, EventArgs e)
{
mth_261_レコード更新();
}
<summary>
</summary>
private void mth_261_レコード更新()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "UPDATE 社員 SET 氏名 ='高橋かなこ' WHERE 社員番号=100091";
sSQL = "UPDATE 社員";
sSQL += " SET 氏名 ='高橋かなこ'";
sSQL += " WHERE 社員番号=200091";
try
{
var cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("Update Record", "通知");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "通知");
}
finally
{
cn.Close();
}
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_262_レコード削除_Click(object sender, EventArgs e)
{
mth_262_レコード削除();
}
<summary>
</summary>
private void mth_262_レコード削除()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "DELETE FROM 社員 WHERE 社員番号=100091";
try
{
var cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("Delete Record", "通知");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "通知");
}
finally
{
cn.Close();
}
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_263_レコード参照_Click(object sender, EventArgs e)
{
mth_263_レコード参照();
}
<summary>
</summary>
private void mth_263_レコード参照()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "SELECT * FROM 社員";
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine("{0}, {1}, {2}", dr["社員番号"], dr["氏名"], dr["部門コード"]);
}
dr.Close();
cn.Close();
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_264_レコード数_Click(object sender, EventArgs e)
{
mth_264_レコード数();
}
<summary>
</summary>
private void mth_264_レコード数()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "SELECT COUNT(*) FROM 社員";
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
int num = (int)cmd.ExecuteScalar();
cn.Close();
System.Windows.Forms.MessageBox.Show(num + "件のレコードがあります。", "通知");
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_265_フィールド数_Click(object sender, EventArgs e)
{
mth_265_フィールド数();
}
<summary>
</summary>
private void mth_265_フィールド数()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "SELECT * FROM 社員";
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
OleDbDataReader dr = cmd.ExecuteReader();
System.Windows.Forms.MessageBox.Show(dr.FieldCount + "フィールドがあります。", "通知");
dr.Close();
cn.Close();
}
<summary>
</summary>
<param name="sender"></param>
<param name="e"></param>
private void btn_266_フィールド名_Click(object sender, EventArgs e)
{
mth_266_フィールド名();
}
<summary>
</summary>
private void mth_266_フィールド名()
{
OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder();
cnstr["Provider"] = _sProvider;
cnstr["Data Source"] = _sDataSorce;
cnstr["Jet OLEDB:Database Password"] = _sPass;
OleDbConnection cn = new OleDbConnection(cnstr.ToString());
cn.Open();
string sSQL = "";
sSQL += "SELECT * FROM 社員";
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
OleDbDataReader dr = cmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
Console.WriteLine(dr.GetName(i));
}
dr.Close();
cn.Close();
}
}
}