C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[5]: SQLServerからAccessMDBへのインサート

分類:[C#]　

お世話になっております。

.NetFramework2.0で提供されているクラスを使って、SQLServerのAテーブルの内容をAccessMDB Aテーブルへインサートしたいと考えております。

Aテーブルは仕様変更が多いため、カラム数を意識しない方法で行いたいと思っております。
(変更がある場合はSQL側が最新でMDB側が古い。)

OleDbCommandオブジェクトからOleDbDataAdapterオブジェクトを生成し、Fillメソッドでデータを追加すると、

AccessMDB側のAテーブルがSQL側の仕様に合わせられるのですが、
(MDBのカラム数が少なければ新たに追加される)

SQL側のAテーブルにカラムが追加となった場合、VALUES句にソースの追加作業が走るかと思います。
→
　SqlDataAdapter adapter = new SqlDataAdapter();
　adapter.SelectCommand = new SqlCommand("SELECT * FROM A", connection);
　DataTable dataTable = new DataTable();
　adapter.Fill(dataTable);

　foreach (DataRow dr in dataTable.Rows)
　{

　　strCmd = "INSERT INTO A VALUES ( " + dr[0] + ", '" + dr[1] + "', '" + dr[2] + ")";
　　//↑SQLServer側Aテーブルの仕様に合わせてdr[x]の追記が必要。

Aテーブルのスキーマ変更(カラム追加)に関わらず、ソースに手をいれなくてもいいような作りがあれば、

ご教授願えれば幸いです。

※最悪は、毎回ドロップ後SELECT INTOでテーブルを意識せずに作成しつつデータコピーしようと考えています。

OPENDATASOURCEをクエリに交えて実行するのかな、と思っておりますが具体的に分かりかねます。

こちらの方も、実装方法を御存じの方がおられましたら、ご教授頂けると幸いです。

SQLサーバへはWindows認証のため次のようなコネクションストリングを考えております。

"Initial Catalog= A;Data Source=(local);Integrated Security=SSPI;"

---
VS2010
.NetFramework2.0

■No55621 (asuka さん) に返信

MDB側にある項目はSQLサーバにすべてあるということであれば
MDB側のテーブル構造をもとにしたDataAdapter,Datatableを作成して
そこにある項目を元にSQLサーバからSelectしてその内容を前記Datatableに追加して
前記DataAdapterをUpdateすればよいのではないでしょうか？

追加した列に default value が指定されていない、もしくは not null 制約が無いのであれば、どうやったって SQL の修正は必要だと思いますけど。SELECT INTO を利用しない場合。

イマイチ利用形態がみえませんが、自分なら SSIS の package 作るかも。問題は何も解消しませんがね。

そういや MDB も link できれば MERGE statement 使えたっけな？

■No55621 (asuka さん) に返信
> SQLServerのAテーブルの内容をAccessMDB Aテーブルへインサートしたいと考えております。
その場合、たとえば
　(1) SQL Server 側が mdb にデータを吐き出す
　(2) mdb 側が SQL Server にデータを取りに行く
　(3) C# 側の DataTable に取り込み、そこから mdb に書き出す
の 3 パターンが思いつきます。


たとえば、(2) のパターンであれば、Microsoft.JET.OLEDB.4.0 等で接続した上で
　INSERT INTO A SELECT * FROM [ODBC;DRIVER={SQL Server};SERVER=～;DATABASE=～;…].A
といった感じです。両者の列定義が同じである必要がありますけれども。

DataTable 側に取り込むと、細かい判定制御などが可能になりますが、
一度データを取り込んでから再送するので、パフォーマンスでは劣ります。


> (変更がある場合はSQL側が最新でMDB側が古い。)
MDB 側の修正は、手動ですか? プログラムで行いますか?
単に DROP & CREATE で良いなら、SELECT INTO が手っ取り早い気がします。

プログラムで構造同期をとる場合は、GetSchema メソッド(または ADO の OpenSchema メソッド)で
列定義を得られます。あるいは、システムテーブルから変更後の列定義を参照するという手も。


> SQL側のAテーブルにカラムが追加となった場合、VALUES句にソースの追加作業が走るかと思います。
SQL を作成する場合は、データの型に注意が必要ですね。(日付、数値、文字列、バイナリなど)
文字列中に「'」が含まれていた場合なども考慮すると、パラメータクエリにした方が安全かと。

2010/12/07(Tue) 23:15:57 編集(投稿者)

名前を間違えたため、削除

■No55621 (asuka さん) に返信
> Aテーブルは仕様変更が多いため、カラム数を意識しない方法で行いたいと思っております。
> (変更がある場合はSQL側が最新でMDB側が古い。)

仕様変更が多いなら、外だししてストアドで処理した方がいいと思います。
.NET側はストアドを呼び出すだけにする。

名前を書き間違えたので(^^;

皆様御回答並びに多数の案を提示して頂きありがとうございます。ｍ(＿＿)ｍ

僕の情報提供不足および分かりづらいところもあり恐縮です。

＞ shu さま
分かりづらい記述で申し訳ありません。
Access側とSQL側でスキーマが違うことがあります。
違いがある場合は、[AccessMDB Aテーブルカラム数 < SQL Aテーブルカラム数]
よってUpdateしようとすると違いがある場合にないカラムをアップデートしようとするので問題になります。

＞ちゃっぴさま
おっしゃる通りです。
そもそもSELECT INTOでないと実現しないという決断に現状達しました。

＞魔界の仮面弁士さま
分かりやすく場合わけして頂きありがとうございます。
ちゃっぴさんにもご指摘頂いた通り、結局MDB側のスキーマに変更がある可能性があり、
そのスキーマ変更は今回データを挿入するプログラムでやらなければならないので、
SELECT INTOしか道がないように思っております。
(以前はご指摘の通りDataTableで取得したColumnsをDataTypeで場合わけ処理しておりましたが、
SELECT INTOでテーブルを作成すれば意識しなくて良いかと再認識しております。)
ですんで、(1)で実装するのかな、と思っております。

＞やじゅさま
今回はストアドの経験者がおらずWin32アプリケーションをC# .Net2.0で作れという仕様だった
のでストアドの使用は皆無でした。
情報の記述がなくて申し訳ありません。

結局SELECT INTOで実装しました。
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\test.mdb");
conn.Open();
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("SELECT * INTO A FROM [ODBC;DRIVER={SQL Server};SERVER=～;DATABASE=～;UID=sa;PWD=xxx].A")
System.Data.OleDb.OleDbCommand command;
command = new System.Data.OleDb.OleDbCommand(sql.ToString(), conn);
command.ExecuteNonQuery();
command.Dispose();

更に2点質問で申し訳ありません。
１．SQL認証では接続出来ますが、Windows認証(混合？)で認証は出来ないでしょうか？
sql.Appendに記載したクエリをSQL認証ではなく、
Initial Catalog=TESTDB;Data Source=(local);Integrated Security=SSPI;
のようにしたい。
実装すると「引数が不正です」とExceptionが発生します。
ODBC;Driver=SQL Server の文字列を付けると「ODBC--呼び出しが失敗しました」となってしまいます。

２．データはコピー出来たのですが(SQL認証時)、インデックスのコピーは可能でしょうか？

その他問題があればご指摘頂けると幸いです。

■No55648 (asuka さん) に返信
> １．SQL認証では接続出来ますが、Windows認証(混合？)で認証は出来ないでしょうか？
ODBC 接続文字列に、Trusted_Connection=Yes を含めてみてください。UID/PWD は不要です。

■No55648 (asuka さん) に返信
> ２．データはコピー出来たのですが(SQL認証時)、インデックスのコピーは可能でしょうか？
>
出来ない事はありませんが、SQLサーバからインデックスを取得するのが結構大変です。

こんな投稿がありました。
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=16052&forum=26

＞魔界の仮面弁士さま
ありがとうございます。
上手くいきました。

＞shuさま
なるほど・・・
これはバージョン分けをしてシステムテーブルの仕様ごとにクエリを書かなければならなそうで、
かなり大変そうですね。。。
ありがとうございました。
参考にさせて頂きます。

皆様方にご教授頂いたおかげで当初の問題を解決することが出来ました。

心からお礼申し上げます。

ありがとうございました。
ｍ(＿＿)ｍ

C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[5]: SQLServerからAccessMDBへのインサート

過去ログには書き込み不可