SQL Serverのテーブルに登録されているデータをもとに、INSERTスクリプトを作成するプログラムを作成します。
C#のプログラムでデータベースへ接続して、指定されたテーブルのデータを挿入するINSERT文のSQLスクリプトを作成し、クリップボードにコピーします。
SQLスクリプトを作成するプログラムを実行するプログラムは、Visual StudioのWindowsフォームアプリケーションで作成します。
目次
データベースへの接続
データベースに接続するために接続文字列（ConnectionString）を作成します。
SQL Server認証を用いて、ユーザーIDとパスワードを指定する場合の接続文字列は、以下のようにします。
|
1 2 |
// 接続文字列を作成 string connectionString = $"Data Source={serverName};Initial Catalog={databaseName};User ID={userId};Password={password}"; |
Windows認証を用いて接続文字列を作成する場合は、以下のようにします。
|
1 2 |
// 接続文字列を作成 string connectionString = $"Data Source={serverName};Initial Catalog={databaseName};Integrated Security=True"; |
databaseName: データベース名
userId: ユーザーID
password: パスワード
データベース（SQL Server）に接続するために、System.Data.SqlClient名前空間にあるSqlConnectionクラスを使用して、コネクションオブジェクトを生成します。
|
1 2 |
// SQL Serverに接続するコネクションオブジェクトを生成 SqlConnection connection = new SqlConnection(connectionString); |
テーブルのデータを取得してDataTableに格納
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
// データを格納するデータテーブルを生成 DataTable dataTable = new DataTable(); // SQLコマンドを発行するコマンドオブジェクトを生成 SqlCommand command = new SqlCommand(); // SqlCommandオブジェクトの設定 command.Connection = connection; command.CommandType = CommandType.Text; // 対象テーブルのすべての列のデータを取得するSQLを設定 command.CommandText = sql; // データテーブルにデータを格納するアダプターオブジェクトを生成 SqlDataAdapter dataAdapter = new SqlDataAdapter(command); // コネクションを開く connection.Open(); // データを取得してDataTableオブジェクトに格納 dataAdapter.Fill(dataTable); // コネクションを閉じる connection.Close(); |
sql: テーブルのデータを取得するSQL文字列
テーブルのデータを格納して保持するDataTableクラス（System.Data名前空間）のインスタンスを生成します。（2行目）
SQLコマンドを発行するSqlCommandクラス（System.Data.SqlClient名前空間）のインスタンスを生成します。（5行目）
SqlCommandオブジェクトのConnectionプロパティにコネクションオブジェクトを設定します。（8行目）
SqlCommandオブジェクトのCommandTypeプロパティにSystem.Data名前空間にあるCommandType 列挙型のTextを設定します。（9行目）
SqlCommandオブジェクトのCommandTextプロパティにテーブルのデータを取得するSQL文字列を設定します。（12行目）
DataTableオブジェクトにデータを格納するSqlDataAdapterクラス（System.Data.SqlClient名前空間）のインスタンスを生成します。（15行目）
SqlConnectionクラスのOpenメソッドでコネクションを開きます。（18行目）
SqlDataAdapterオブジェクトのFillメソッドを使用して取得するデータをDataTableオブジェクトに格納します。（21行目）
SqlConnectionクラスのCloseメソッドでコネクションを閉じます。（24行目）
データベースに接続してテーブルのデータを取得してDataTableに保持するクラス
データベースに接続してテーブルのデータを取得してDataTableに保持するクラスのソースコードです。クラスは「DatabaseAccessor」という名前で作成しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
using System; // 追加の名前空間 using System.Data; using System.Data.SqlClient; namespace WindowsFormsApp1 { // データベースアクセスクラス public class DatabaseAccessor { // DataTableオブジェクトを取得する public static DataTable GetDataTable( string serverName, string databaseName, string userId, string password, string schemaName, string tableName, string where = null, string order = null ) { // 接続文字列を作成 // SQL Server認証 string connectionString = $"Data Source={serverName};Initial Catalog={databaseName};User ID={userId};Password={password}"; // Windows認証の場合は以下 //string connectionString = $"Data Source={serverName};Initial Catalog={databaseName};Integrated Security=True"; // テーブル string table = $"{schemaName}.{tableName}"; // SQL SELECTステートメント string sql = $"SELECT * FROM {table}"; if (!string.IsNullOrEmpty(where)) { sql += $" WHERE {where}"; } if (!string.IsNullOrEmpty(order)) { sql += $" ORDER BY {order}"; } // データベースに接続するコネクションオブジェクトを生成 using (SqlConnection connection = new SqlConnection(connectionString)) { // データを格納するデータテーブルを生成 DataTable dataTable = new DataTable(); // SQLコマンドを発行するコマンドオブジェクトを生成 using (SqlCommand command = new SqlCommand()) { // SqlCommandオブジェクトの設定 command.Connection = connection; command.CommandType = CommandType.Text; // 対象テーブルの全カラムを取得するSQLを設定 command.CommandText = sql; // データテーブルにデータを格納するアダプターオブジェクトを生成 SqlDataAdapter dataAdapter = new SqlDataAdapter(command); // コネクションを開く connection.Open(); // データを取得してDataTableオブジェクトに格納 dataAdapter.Fill(dataTable); // コネクションを閉じる connection.Close(); } // データテーブルを返す return dataTable; } } } } |
DataTableからINSERT文のSQLスクリプトを作成
DataTableオブジェクトからINSERT文のSQLスクリプトを作成する処理は、DataTableオブジェクトが持つDataRowオブジェクトを読み込みながら行います。
DataRowオブジェクトには、インサート（挿入）対象のテーブルのレコードのデータがあるので、INSERT文のVALUES句を作成します。
1行ずつINSERT文を作成してもよいのですが、実行時の処理時間等を考え、今回は1回のINSERT文で複数件のレコードを挿入（追加）する形式のSQLを作成します。
INSERT文のSQLスクリプトを作成するクラス
INSERT文のSQLスクリプトを作成するクラスのソースコードです。クラスは「InsertSqlScriptMaker」という名前で作成しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
using System; using System.Collections.Generic; using System.Text; // 追加の名前空間 using System.Data; namespace WindowsFormsApp1 { // INSERT SQLスクリプト作成クラス public class InsertSqlScriptMaker { // コンストラクタ public InsertSqlScriptMaker( string serverName, string databaseName, string userId, string password, string schemaName, string tableName ) { ServerName = serverName; DatabaseName = databaseName; UserId = userId; Password = password; SchemaName = schemaName; TableName = tableName; } // プロパティ public string ServerName { get; private set; } public string DatabaseName { get; private set; } public string UserId { get; private set; } public string Password { get; private set; } public string SchemaName { get; private set; } public string TableName { get; private set; } // SQLを作成する public string MakeSql(string where = null, string order = null) { // DataTableオブジェクトを取得 // 「データベースに接続してテーブルのデータを取得してDataTableに保持するクラス」を使用 DataTable dataTable = DatabaseAccessor.GetDataTable( ServerName, DatabaseName, UserId, Password, SchemaName, TableName); // 列文字列ビルダー StringBuilder columnsBuilder = new StringBuilder(); // レコードビルダーリスト（1000件ごとのレコードビルダーを保持） List<StringBuilder> recordBuilderList = new List<StringBuilder>(); for (int i = 0; i < dataTable.Rows.Count; i++) { DataRow dataRow = dataTable.Rows[i]; // 値文字列ビルダー StringBuilder valuesBuilder = new StringBuilder(); foreach (DataColumn dataColumn in dataTable.Columns) { if (i == 0) { string columnName = dataColumn.ColumnName; // 列名追加 columnsBuilder.Append(columnsBuilder.Length > 0 ? "," : ""); columnsBuilder.Append(columnName); } string value = dataRow[dataColumn].ToString(); // 「'」は2重化しておく value = value.Replace("'", "''"); // 値追加 valuesBuilder.Append(valuesBuilder.Length > 0 ? "," : ""); if (dataColumn.DataType == typeof(string)) { // 文字列の場合は「'」をつける valuesBuilder.Append($"'{value}'"); } else { valuesBuilder.Append(value); } } if (i % 1000 == 0) { // 1000行ごとにレコードビルダーを生成してリストに追加 recordBuilderList.Add(new StringBuilder()); } int maxIndex = recordBuilderList.Count - 1; StringBuilder recordBuilder = recordBuilderList[maxIndex]; if (recordBuilder.Length > 0) { recordBuilder.AppendLine(","); } recordBuilder.Append($"({valuesBuilder})"); } string insert = $"INSERT INTO {SchemaName}.{TableName}"; string columns = $"({columnsBuilder})"; string values = "VALUES"; string insertColumnsValues = $"{insert}\r\n{columns}\r\n{values}"; // INSERTステートメントビルダーを生成 StringBuilder insertStatementBuilder = new StringBuilder(); foreach (StringBuilder recordBuilder in recordBuilderList) { if (insertStatementBuilder.Length > 0) { insertStatementBuilder.AppendLine(); } insertStatementBuilder.AppendLine(insertColumnsValues); insertStatementBuilder.AppendLine($"{recordBuilder};"); } string sql = insertStatementBuilder.ToString(); return sql; } } } |
1回のINSERT文で複数件のレコードを挿入（追加）する場合は、SQL Serverの仕様として1000レコードまでという制限がありますので、1000レコードずつINSERT文が作成されるようにします。
上記のクラスのMakeSqlメソッドでは、DataTableが保持しているすべての行を1000レコードずつに区切って、INSERT文で挿入（追加）するSQL文字列を返します。
例えば、「ID」と「STRING」と「NUMBER」という列を持つ「TEST_TABLE」というテーブルがある場合は、以下のようなSQLが生成されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
INSERT INTO dbo.TEST_TABLE (ID,STRING,NUMBER) VALUES (1,'STRING_1',1000000), (2,'STRING_2',2000000), (3,'STRING_3',3000000), (4,'STRING_4',4000000), (5,'STRING_5',5000000), ...中略... (996,'STRING_996',996000000), (997,'STRING_997',997000000), (998,'STRING_998',998000000), (999,'STRING_999',999000000), (1000,'STRING_1000',1000000000); INSERT INTO dbo.TEST_TABLE (ID,STRING,NUMBER) VALUES (1001,'STRING_1001',1001000000), (1002,'STRING_1002',1002000000), (1003,'STRING_1003',1003000000), (1004,'STRING_1004',1004000000), (1005,'STRING_1005',1005000000), ...中略... (1995,'STRING_1995',1995000000), (1996,'STRING_1996',1996000000), (1997,'STRING_1997',1997000000), (1998,'STRING_1998',1998000000), (1999,'STRING_1999',1999000000), (2000,'STRING_2000',2000000000); INSERT INTO dbo.TEST_TABLE (ID,STRING,NUMBER) VALUES (2001,'STRING_2001',2001000000), (2002,'STRING_2002',2002000000), (2003,'STRING_2003',2003000000), (2004,'STRING_2004',2004000000), (2005,'STRING_2005',2005000000), 以下略... |
実行プログラムサンプル
「INSERT文のSQLスクリプトを作成するクラス」を使用してSQLを生成するためのUI（ユーザーインターフェース）を持つプログラムを作成します。
VisualStudioでWindowsフォームアプリケーションのプロジェクトを作成します。
ユーザーインターフェース
フォームには、サーバー名やデータベース名などの接続に関する情報を入力するコントロールと、取得するデータを制御する情報を入力するコントロールを配置します。
データベース名: textBox2
ユーザーID: textBox3
パスワード: textBox4
スキーマ名: textBox5
テーブル名: textBox6
WHERE: textBox7
ORDER: textBox8
button1のクリックイベントで、SQLスクリプトを生成します。
ソースコード
フォームに記述するソースコードになります。
ボタン（button1）がクリックされた時のイベント処理を実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
using System; using System.Windows.Forms; namespace WindowsFormsApp1 { public partial class Form1 : Form { // コンストラクタ public Form1() { InitializeComponent(); // ボタンのクリックイベント button1.Click += Button1_Click; } // ボタンクリック時のイベントメソッド private void Button1_Click(object sender, EventArgs e) { // INSERT SQLスクリプト作成クラスを生成 InsertSqlScriptMaker maker = new InsertSqlScriptMaker( textBox1.Text, // サーバー名 textBox2.Text, // データベース名 textBox3.Text, // ユーザーID textBox4.Text, // パスワード textBox5.Text, // スキーマ名 textBox6.Text // テーブル名 ); // SQLを作成 string sql = maker.MakeSql(textBox7.Text, textBox8.Text); if (!string.IsNullOrEmpty(sql)) { // SQLが作成できた場合はクリップボードに設定（保持） Clipboard.SetText(sql); MessageBox.Show("作成したSQLスクリプトをクリップボードにコピーしました。"); } } } } |
生成したSQL文字列は、クリップボードにコピーし、メッセージをダイアログボックスで表示します。
プログラムの実行
プロジェクトをビルドしてプログラムを実行するとフォームが表示されますので、SQLスクリプトの作成に実行に必要な情報をテキストボックスに入力します。
テキストボックスに接続情報等を入力してボタンをクリックすると、INSERT文のSQLスクリプトが生成されてクリップボードに設定されます。
テーブルのバックアップスクリプトの生成や、別環境のデータベースにデータを移行する際には使えると思います。
