SQLの窓

2017年09月19日


System.Data.OleDb で select into 構文を使って簡単に Microsoft Access のデータを他の データベースにエクスポートする

結論から言うと、System.Data.OleDb と ADO で Microsoft Access に接続すると同等の処理ができます。元々、ADO で可能だった処理を System.Data.OleDb で可能かどうか確認し、System.Data.Odbc でとこまで同じ事が可能か試してみました。

結果的に、System.Data.Odbc では ODBC の動的接続では処理不可で、DSN を作成すると可能でした。

テスト用コード
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace OdbcAccessTest {
	public partial class Form1 : Form {

		// *************************************
		// System.Data.Odbc
		// *************************************
		private OdbcConnection myCon;
		private OdbcCommand myCommand;
		private OdbcDataReader myReader;

		// *************************************
		// System.Data.OleDb
		// *************************************
		private OleDbConnection myConAccess;

		// *************************************
		// ADO
		// *************************************
		private dynamic objAdoCon;

		public Form1() {
			InitializeComponent();
		}

		// *************************************
		// System.Data.Odbc で Access データの読み込み
		// *************************************
		private void button1_Click(object sender, EventArgs e) {

			string myQuery = "SELECT * from 社員マスタ";

			using (myCommand = new OdbcCommand()) {
				// 実行する為に必要な情報をセット
				myCommand.CommandText = myQuery;
				myCommand.Connection = myCon;

				using (myReader = myCommand.ExecuteReader()) {

					// 読み出し

					while (myReader.Read()) {
						// 文字列
						Console.Write(GetValue(myReader, "社員コード") + " : ");
						Console.Write(GetValue(myReader, "氏名") + " : ");
						Console.Write(GetValue(myReader, "フリガナ") + " : ");
						// 整数
						Console.Write(GetValue(myReader, "給与") + " : ");
						Console.Write(GetValue(myReader, "手当") + " : ");
						// 日付
						Console.Write(GetValue(myReader, "作成日") + " : ");
						Console.Write(GetValue(myReader, "更新日") + " : ");

						Console.WriteLine();

					}

					myReader.Close();
				}

			}

		}

		// *************************************
		// テスト用簡易列データ取得
		// *************************************
		private string GetValue(OdbcDataReader myReader, string p) {

			string ret = "";
			int fld = 0;

			// 指定された列名より、テーブル内での定義順序番号を取得
			fld = myReader.GetOrdinal(p);
			// 定義順序番号より、NULL かどうかをチェック
			if (myReader.IsDBNull(fld)) {
				ret = "";
			}
			else {
				// NULL でなければ内容をオブジェクトとして取りだして文字列化する
				ret = myReader.GetValue(fld).ToString();
			}

			// 列の値を返す
			return ret;
		}

		// *************************************
		// 接続処理
		// *************************************
		private void Form1_Load(object sender, EventArgs e) {

			// *************************************
			// System.Data.Odbc
			// *************************************
			OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();

			builder.Driver = "Microsoft Access Driver (*.mdb, *.accdb)";
			builder.Add("dbq", @"C:\tmp\販売管理C.accdb");
			// 内容を確認
			Console.WriteLine(builder.ConnectionString);

			// 新しい OdbcConnection オブジェクトを作成
			myCon = new OdbcConnection();

			// 接続文字列を設定
			myCon.ConnectionString = builder.ConnectionString;

			// 接続を開く
			try {
				myCon.Open();
			}
			catch (OdbcException ex) {
				MessageBox.Show(this, "接続エラーです:" + ex.Message);
			}

			// *************************************
			// System.Data.OleDb
			// *************************************
			myConAccess = new OleDbConnection();
			myConAccess.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};", @"C:\tmp\販売管理C.accdb");
			// 接続を開く
			try {
				myConAccess.Open();
			}
			catch (Exception ex) {
				MessageBox.Show(this, "接続エラーです:" + ex.Message);
			}
	
			// *************************************
			// ADO
			// *************************************
			objAdoCon = Activator.CreateInstance(Type.GetTypeFromProgID("ADODB.Connection"));

			string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\tmp\001\販売管理C.accdb;";
			try {
				objAdoCon.Open(connectionString);
			}
			catch (Exception ex) {
				MessageBox.Show(this, ex.Message);
			}
		}

		// *************************************
		// 接続解除
		// *************************************
		private void Form1_FormClosed(object sender, FormClosedEventArgs e) {

			myCon.Close();
			myConAccess.Close();
			objAdoCon.Close();

		}

		// *************************************
		// System.Data.OleDb
		// *************************************
		private void button2_Click(object sender, EventArgs e) {

			string myQuery;

			myQuery = @"select * into [Excel 8.0;DATABASE=c:\tmp\販売管理C.xls].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [Excel 12.0 xml;DATABASE=c:\tmp\販売管理C.xlsx].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [;DATABASE=C:\tmp\データベース1.accdb].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [;DATABASE=C:\tmp\データベース1.mdb].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=SQS64;Database=lightbox;UID=sa;PWD=password].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=SQS32;Database=lightbox;UID=sa;PWD=password].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=MYSQL32;Database=lightbox;UID=root;PWD=password].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [ODBC;Driver={SQL Server Native Client 11.0};server=layla;database=lightbox;uid=sa;pwd=password].商品マスタ_OleDb from 商品マスタ";
			//myQuery = @"select * into [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=layla;database=lightbox;uid=root;pwd=password].商品マスタ_OleDb from 商品マスタ";

			using (OleDbCommand myCommand = new OleDbCommand()) {

				// *********************
				// 接続
				// *********************
				try {
					// コマンドオブジェクトに接続をセット
					myCommand.Connection = myConAccess;
					myCommand.CommandText = myQuery;
					myCommand.ExecuteNonQuery();

				}
				catch (Exception ex) {
					MessageBox.Show(this, ex.Message);
				}
			}

		}

		// *************************************
		// System.Data.Odbc
		// *************************************
		private void button3_Click(object sender, EventArgs e) {

			string myQuery;

			myQuery = @"select * into [Excel 8.0;DATABASE=c:\tmp\販売管理C.xls].商品マスタ_Odbc from 商品マスタ";
			//myQuery = @"select * into [Excel 12.0 xml;DATABASE=c:\tmp\販売管理C.xlsx].商品マスタ_Odbc from 商品マスタ";
			//myQuery = @"select * into [;DATABASE=C:\tmp\データベース1.accdb].商品マスタ_Odbc from 商品マスタ";
			//myQuery = @"select * into [;DATABASE=C:\tmp\データベース1.mdb].商品マスタ_Odbc from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=SQS64;Database=lightbox;UID=sa;PWD=password].商品マスタ_Odbc from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=SQS32;Database=lightbox;UID=sa;PWD=password].商品マスタ_Odbc from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=MYSQL32;Database=lightbox;UID=root;PWD=password].商品マスタ_Odbc from 商品マスタ";

			using (myCommand = new OdbcCommand()) {

				try {
					// コマンドオブジェクトに接続をセット
					myCommand.Connection = myCon;
					myCommand.CommandText = myQuery;
					myCommand.ExecuteNonQuery();
				}
				catch (Exception ex) {
					MessageBox.Show(this, ex.Message);
				}
			}

		}

		// *************************************
		// ADO を使う場合
		// *************************************
		private void button4_Click(object sender, EventArgs e) {

			string myQuery;

			myQuery = @"select * into [Excel 8.0;DATABASE=c:\tmp\販売管理C.xls].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [Excel 12.0 xml;DATABASE=c:\tmp\販売管理C.xlsx].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [;DATABASE=C:\tmp\データベース1.accdb].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [;DATABASE=C:\tmp\データベース1.mdb].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=SQS64;Database=lightbox;UID=sa;PWD=password].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=SQS32;Database=lightbox;UID=sa;PWD=password].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [ODBC;DSN=MYSQL32;Database=lightbox;UID=root;PWD=password].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [ODBC;Driver={SQL Server Native Client 11.0};server=layla;database=lightbox;uid=sa;pwd=password].商品マスタ_ADO from 商品マスタ";
			//myQuery = @"select * into [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=layla;database=lightbox;uid=root;pwd=password].商品マスタ_ADO from 商品マスタ";

			try {
				objAdoCon.Execute(myQuery);
			}
			catch (Exception ex) {
				MessageBox.Show(this, ex.Message);
			}

		}

	}
}

ここでは、into 側の表で使用していますが、from 側の表でも使用できるはずです。エクスポートは高速で実行されますが、キーの定義はエクスポートされないので、後から定義する必要があります。

SQL 一覧
@"select * into [Excel 8.0;DATABASE=c:\tmp\販売管理C.xls].商品マスタ_ADO from 商品マスタ";
@"select * into [Excel 12.0 xml;DATABASE=c:\tmp\販売管理C.xlsx].商品マスタ_ADO from 商品マスタ";
@"select * into [;DATABASE=C:\tmp\データベース1.accdb].商品マスタ_ADO from 商品マスタ";
@"select * into [;DATABASE=C:\tmp\データベース1.mdb].商品マスタ_ADO from 商品マスタ";
@"select * into [ODBC;DSN=SQS64;Database=lightbox;UID=sa;PWD=password].商品マスタ_ADO from 商品マスタ";
@"select * into [ODBC;DSN=SQS32;Database=lightbox;UID=sa;PWD=password].商品マスタ_ADO from 商品マスタ";
@"select * into [ODBC;DSN=MYSQL32;Database=lightbox;UID=root;PWD=password].商品マスタ_ADO from 商品マスタ";
@"select * into [ODBC;Driver={SQL Server Native Client 11.0};server=layla;database=lightbox;uid=sa;pwd=password].商品マスタ_ADO from 商品マスタ";
@"select * into [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=layla;database=lightbox;uid=root;pwd=password].商品マスタ_ADO from 商品マスタ";


外部参照部分一覧
[Excel 8.0;DATABASE=c:\tmp\販売管理C.xls].テーブル名
[Excel 12.0 xml;DATABASE=c:\tmp\販売管理C.xlsx].テーブル名
[;DATABASE=C:\tmp\データベース1.accdb].テーブル名
[;DATABASE=C:\tmp\データベース1.mdb].テーブル名
[ODBC;DSN=SQS64;Database=lightbox;UID=sa;PWD=password].テーブル名
[ODBC;DSN=SQS32;Database=lightbox;UID=sa;PWD=password].テーブル名
[ODBC;DSN=MYSQL32;Database=lightbox;UID=root;PWD=password].テーブル名
[ODBC;Driver={SQL Server Native Client 11.0};server=layla;database=lightbox;uid=sa;pwd=password].テーブル名
[ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=layla;database=lightbox;uid=root;pwd=password].テーブル名

Excel と Microsoft Access は特殊ですが、ODBC は同じ書式なので他の DB にも使用可能なはずです。接続パラメータは、実際 DSN を作成してから、レジストリを見れば必要なものが全て書かれています。

▼ エクスポートでは使えませんが、以下は Excel を ODBC で定義した場合の例です


PHP で ODBC 接続するとなると、上記データを使って以下のようになります
$ms_excel = "./data/syain.xls";
$ms_excel = realpath($ms_excel);
$excel_dir = dirname($ms_excel);
$connect_string = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};dbq={$ms_excel};DefaultDir={$excel_dir};FIL=Excel 8.0;DriverID=790";$user="";$pass="";




【VS(C#)の最新記事】
posted by lightbox at 2017-09-19 00:48 | Comment(0) | VS(C#) | このブログの読者になる | 更新情報をチェックする
バッチ処理

Microsoft Office
container 終わり

フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

Android SDK ポケットリファレンス
改訂版 Webデザイナーのための jQuery入門
今すぐ使えるかんたん ホームページ HTML&CSS入門
CSS ドロップシャドウの参考デモ
Google Hosted Libraries
cdnjs
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり