Tableのコピー方法
2014年11月11日
16:16
SqlBulkCopy
２．SqlBulkCopyの利用
MSDNの「SqlBulkCopyクラス」の解説を熟読すると、「同一サーバー上または異なるサーバー間で、データをあるテーブルから別のテーブルに移動するための手段として、bcpというコマンドプロンプトユーティティを備えています。SqlBulkCopuyクラスを使用すると、これと同様の機能を提供するマネージコードソリューションを作成できます。」とあった。ただし、「データの書き込み先がSQL Serverのテーブルの場合にしか使用できません。ただし、データソースはSQL Serverに限定されません。」「SqlDateTime型のDataTable列を一括読み込みしようとすると失敗します。」と但し書きがある。「例」は何故か、同一データベース間のコピーをSqlDataReaderを使用して実行する例が示されている。異なるデーターベース間についての例はない。また、「同じSQL Serverインスタンスにコピー元テーブルとコピー先テーブルが存在する場合、Trnsact-SQLのINSERT ・・・SELECTステートメントを使用した方が容易かつ迅速にデータをコピーできます。」と注釈がついている。
ただ、例をよく読むと、SqlCommandを用いて、ExecuteScalar()、ExecuteReader()メソッドを使用していることが分かる。また、一度メモリ上にテーブルを読み込んでその上で書き込みを行っていることが読み取れる。異なるデータベース間では当然と言えば当然のことである。つまり、SqlCommandの記述の仕方からも分かるのであるが、テーブルをオープンして読み書きするときに、Connectionを指定する必要があり、Commandに対応して1つしか記述できない仕様であるからである。
(1) connectionString
通常はVisual Studioで自動生成されるのであるが、おそらく手動で記述する場合も同一のものであろうと推測して、「app.config」のconnectionStringをコピーして使用した。ものの本にあるように、"Data Source =・・・"の前に＠をつけなければ動作しなかった。それ以外は、全くのコピーで動作する。例：
string connectionStringBK_1 = @"Data Source=itmaps12\itmaps12;Initial Catalog=ConfulBK;Integrated Security=True;Pooling=False";
(2) using
初めて見るもので、意味は不明であったが、最初はそのまま使用した。調べると次のようである。
「一般に、IDisposable オブジェクトを使用するときは、それを using ステートメントで宣言して、インスタンス化する必要があります。 using ステートメントは、オブジェクトで正しく Dispose メソッドを呼び出します。(前述のようにこのステートメントを使用する場合) Dispose が呼び出されるとすぐに、オブジェクト自体がスコープの外側に出されます。 オブジェクトは、using ブロック内では読み取り専用です。変更したり再割り当てしたりすることはできません。
using ステートメントを使うと、オブジェクトでのメソッドの呼び出し中に例外が発生した場合でも Dispose が必ず呼び出されます。 オブジェクトを try ブロックに配置し、finally ブロックで Dispose を呼び出しても、同じ結果が得られます。実際には、コンパイラは using ステートメントをこのように変換します。 前のコード例は、コンパイル時に次のコードに展開されます (オブジェクトのスコープの範囲を定義する中かっこが加えられています)。」
すなわち、基本的には閉じなければならないリソースのインスタンスを作るときにusingで囲ってあげればいいということである。これについては、「例」の中で、Open文があるのにClose文がないので不思議に思い、なくても良いのかな？と思ったが、次の私の書いたコードのようにClose文をつけておいた。
(3) SqlCommand
長らく、SqlCommandのC#コード内での記述の方法が分からなかった（MSDNなどで、ExecuteNonQuery()を発行するくらいはわかっていたが、最初のDBのオープンにかかわるところに自信がなかった。今回、真似事で(1)で書いたようにオープンができたので、SQL Queryの発行を試行できた）。
ものの本にはデリートコマンドなど載っているので、その通りに記述して試してみた次第である。
ソースとしては次のようなものであるが、先に述べたように、sourceConnectionBK_1の部分が１つのDBにしか対応できないので、順番にしか処理ができないし、また、別のデーターベースから他のデータベースへのコピーは出来ないのである。一度、メモリの中に取り込む必要がある。
例：
SqlCommand commandDeleteBK_1 = new SqlCommand("DELETE FROM BK_1;", sourceConnectionBK_1);
(4) ExecuteNonQuery()メソッド
上のコマンドを設定した後、次のメソッドを実行する。
例：
//消去の実行 戻り値は処理行数
int rowcount = commandDeleteBK_1.ExecuteNonQuery();
(5) SqlDataReaderクラス
MSDNでの説明は「解説：SqlDataReader を作成するには、コンストラクターを直接使用せずに、SqlCommand オブジェクトの ExecuteReader メソッドを呼び出す必要があります。
SqlDataReader の使用中は、関連付けられた SqlConnection は、その SqlDataReader によって使用されるためビジー状態になります。この間、SqlConnection に対して、閉じる以外の操作は実行できません。 SqlDataReader の Close メソッドを呼び出すまでこの状態が続きます。 たとえば、Close を呼び出すまでは、出力パラメーターは取得できません。
」すなわち「SqlDataReader reader = command.ExecuteReader();」と使用する。
テーブルSheet1$の全データのメモリ読み込みは次のようである。
例：
SqlCommand commandSourceCopy = new SqlCommand("SELECT * FROM Sheet1$;",sourceConnectionSource);
SqlDataReader reader = commandSourceCopy.ExecuteReader();
(注)Read()メソッドを持っており、「SqlDataReader を次のレコードに進めます。 (DbDataReader.Read() をオーバーライドします。)」
使用例として次がある。
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
ReadSingleRow((IDataRecord)reader);
}
(6) SqlBulkCopyメソッド
(注)コンストラクター
SqlBulkCopy(SqlConnection)： 既に接続の確立されている SqlConnection のインスタンス。一括コピー操作を実行するために使用されます。 接続文字列が Integrated Security = true を使用しない場合は、SqlCredential を使用すると、ユーザー ID とパスワードを接続文字列内のテキストとして指定する場合よりも安全に、ユーザー ID とパスワードを渡すことができます。
(注)WritrToServerメソッド
① WriteToServer(DataRow[]) ： 指定した DataRow 配列のすべての行を、SqlBulkCopy オブジェクトの DestinationTableName プロパティで指定された対象テーブルにコピーします。
② WriteToServer(DataTable) ： 指定した DataTable のすべての行を、SqlBulkCopy オブジェクトの DestinationTableName プロパティで指定された対象テーブルにコピーします。
③ WriteToServer(IDataReader) ： 指定した IDataReader のすべての行を、SqlBulkCopy オブジェクトの DestinationTableName プロパティで指定された対象テーブルにコピーします。
④ WriteToServer(DataTable, DataRowState) ： 指定された DataTable から、特定の状態に一致する行だけを、SqlBulkCopy オブジェクトの DestinationTableName プロパティで指定された対象テーブルにコピーします。
(注)SqlBulkCopy.DestinationTableName プロパティ
WriteToServer を呼び出す時点で DestinationTableName が設定されていなかった場合、ArgumentNullException がスローされます。
WriteToServer 操作の実行中に DestinationTableName を変更しても、現在の操作には影響しません。 次回 WriteToServer メソッドを呼び出したときに、新しい DestinationTableName 値が使用されます。
DestinationTableName は 3 つの部分から成る名前です (<database>.<owningschema>.<name>)。 必要に応じて、データベースや所有スキーマでテーブル名を修飾できます。 ただし、テーブル名にアンダースコア ("_") またはその他の特殊文字が使用されている場合は、([<database>.<owningschema>.<name_01>]) のように名前を角かっこで囲んでエスケープする必要があります。 詳細については、SQL Server Books Online の「識別子」を参照してください。
DestinationTableName プロパティの tempdb..#table や tempdb.<owner>.#table の値を使用して、一時テーブルへの一括コピーを実行できます。
以上を踏まえて、実際のコードは次のように書く。先ほど(5)で示したようにSqlDataReader reader = commandSourceCopy.ExecuteReader()でデータベースConfulのテーブルSheet1$のデータを読み込んであるので、これをデータベースConfulBKのテーブルBK_1に一括コピーを行うものである。
例：
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sourceConnectionBK_1))
{
bulkCopy.DestinationTableName = "BK_1";
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
reader.Close();
}
}
C#/SQL Fir...
