#「ちょろいっすよ！」
私は入社2年目の駆け出し技術者です。
ある日上司がSQL初心者の私に頼み事をしてきました。
上司「ちょっと相談があるんやけど、、、」
上司「今、DBにこんなテーブルがあるんだけど、」
TABLE1
| ID | 名称 | サービスID |
|---|---|---|
| 1 | 名前A | 3 |
| 2 | 名前B | 2 |
| 3 | 名前C | 2 |
上司「本来このテーブルのサービスIDカラムに対応するサービス名称があるんだけど、
使わんから、このCSVの対応表だけ作って終わりにしたんや」
Service.csv
1,サービスA
2,サービスB
3,サービスC
4,サービスD
5,サービスE
上司「でもやっぱりサービス名称これから使いそうやから、JOINで取れるようにCSVから対応表通りのテーブルを作ってくれない？」
上司「クライアント側にはCSVは定期的に変更してもいいって言っているから、テーブル作成するスクリプトやらなんやらをタスクスケジューラで定期実行してな」
私「よくある話ですね。多分SQLのBulkInsert使えば一発なんでちょろいっすよｗｗｗ」
#ちょろいはずだった・・・
私「よーし、ちゃっちゃとSQL書いちゃおー」
私「まずはテーブル作成して、」
CreateTable.sql
CREATE TABLE SERVICE_TABLE
(
Service_ID int not null,
Service_Name varchar(102) not null
);
私「あとはBulkInsertして終いや！」
私「定期実行するから頭でTRUNCATEしとこ。」
BulkInsert.sql
TRUNCATE TABLE SERVICE_TABLE
BULK INSERT SERVICE_TABLE
FROM 'C:\hoge\Service.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
私「あとは"C:\hoge"にService.csvを置いて、、、実行！」
私「、、、え！？」
ファイル"C:\hoge\Service.csv"を開けなかったので、一括読み込みできません。
私「なんでや、、、」
#悪戦苦闘
この後、ググりながらhogeフォルダのセキュリティ設定やDB側の設定をいじってみたのですが、1時間かけても結局できませんでした。
SQLサーバ認証を用いているとファイルのアクセス権がーとかBulkInsertで指定するパスはDBサーバ上のパスでーとかいろいろ書いてあり、もう何が本当かもわからなくなりました。
ちなみにこの作業は別のサーバ上で行いたかったのでDBサーバ上にcsvファイル置いてーはできません。
もしかしたら、わかる人がやれば一瞬なのかもしれません。
設定でなんとかなるよっていう方はコメントでご教授お願いします。
#めんどくさくなった
私「めんどくさいなー。帰ってゲームしたいなー」
私「もうC#でBulkInsertと同じことするコンソールアプリ作ったほうが早くね？（やけくそ）」
私「そのアプリのexeをタスクスケジューラで定期実行したら同じじゃん！（天才）」
私「まずはデータクラスを作って」
ServiceData.cs
class ServiceData
{
/// <summary>
/// サービスID
/// </summary>
public int ServiceID { get; set; }
/// <summary>
/// サービス名称
/// </summary>
public string ServiceName { get; set; }
}
私「SQLServer操作クラスを作るか。今回の機能的にこれくらいあればいいかな」
MSSqlManager.cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace BulkInsertApp
{
public class MSSqlManager
{
private SqlConnection sqlConnection;
private SqlTransaction sqlTransaction;
/// <summary>
/// 接続文字列生成
/// </summary>
/// <returns>接続文字列</returns>
private string GetConnectionString()
{
string connectionString = null;
string userId = "<SQL認証のユーザ名>";
string password = "<SQL認証のパスワード>";
string dbname = "<DB名>";
string dbpath = "<DBサーバアドレス>";
connectionString =
"Persist Security Info=False;"
+ "User ID = " + userId
+ "; Password = " + password
+ "; Initial Catalog = " + dbname
+ "; Data Source = " + dbpath;
}
return connectionString;
}
public MSSqlManager()
{
try
{
string connectString = GetConnectionString();
this.sqlConnection = new SqlConnection(connectString);
this.sqlConnection.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
public void Close()
{
try
{
this.sqlConnection.Close();
this.sqlConnection.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
public void BeginTran()
{
try
{
this.sqlTransaction = this.sqlConnection.BeginTransaction();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
public void CommitTran()
{
try
{
if (this.sqlTransaction.Connection != null)
{
this.sqlTransaction.Commit();
this.sqlTransaction.Dispose();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
public void RollBack()
{
try
{
if (this.sqlTransaction.Connection != null)
{
this.sqlTransaction.Rollback();
this.sqlTransaction.Dispose();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
public void ExecuteInsert(string query, Dictionary<string, Object> paramDict)
{
SqlCommand sqlCom = new SqlCommand();
try
{
//クエリー送信先、トランザクションの指定
sqlCom.Connection = this.sqlConnection;
sqlCom.Transaction = this.sqlTransaction;
sqlCom.CommandText = query;
foreach (KeyValuePair<string, Object> item in paramDict)
{
sqlCom.Parameters.Add(new SqlParameter(item.Key, item.Value));
}
// SQLを実行
sqlCom.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
public void ExecuteQuery(string query)
{
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.Connection = this.sqlConnection;
sqlCom.Transaction = this.sqlTransaction;
sqlCom.CommandText = query;
sqlCom.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
}
}
私「あとはCSV読み込み→テーブルTruncate→CSVデータInsertすれば終いや！」
ServiceBulk.cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Text;
namespace BulkInsertApp
{
public class ServiceBulk
{
public void ServiceBulkManager()
{
List<ServiceData> ServiceDataList = CSVDataGet();
TruncateTable();
BulkInsert(ServiceDataList);
}
private List<ServiceData> CSVDataGet()
{
List<ServiceData> retList = new List<ServiceData>();
StreamReader sr = new StreamReader(@"C:\hoge\Service.csv", Encoding.GetEncoding("Shift_JIS"));
try
{
while (!sr.EndOfStream)
{
ServiceData ServiceData = new ServiceData();
string line = sr.ReadLine();
string[] values = line.Split(',');
List<string> items = new List<string>();
items.AddRange(values);
if (items.Count == 2)
{
ServiceData.ServiceID = int.Parse(items[0]);
ServiceData.ServiceName = items[1];
retList.Add(ServiceData);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
sr.Close();
}
return retList;
}
private void TruncateTable()
{
MSSqlManager manager = new MSSqlManager();
try
{
manager.BeginTran();
string query = "TRUNCATE TABLE SERVICE_TABLE";
manager.ExecuteQuery(query);
manager.CommitTran();
}
catch (SqlException sqle)
{
string error = "Number: " + sqle.Number + " Message: " + sqle.Message;
Console.WriteLine(error);
manager.RollBack();
throw;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
finally
{
manager.Close();
}
}
private void BulkInsert(List<ServiceData> listData)
{
MSSqlManager manager = new MSSqlManager();
try
{
manager.BeginTran();
foreach (ServiceData data in listData)
{
string sqlstr = "INSERT INTO SERVICE_TABLE " +
"(Service_ID, ServiceName)" +
" values " +
"(@Service_ID, @Service_Name)";
Dictionary<string, Object> paramDict = new Dictionary<string, object>();
paramDict.Add("@Service_ID", data.ServiceID);
paramDict.Add("@Service_Name", data.ServiceName);
manager.ExecuteInsert(sqlstr, paramDict);
}
manager.CommitTran();
}
catch (SqlException sqle)
{
string error = "Number: " + sqle.Number + " Message: " + sqle.Message;
Console.WriteLine(error);
manager.RollBack();
throw;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
finally
{
manager.Close();
}
}
}
}
私「あとはこいつをメインから呼び出せばええんや」
Program.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
namespace BulkInsertApp
{
class Program
{
public static void Main(string[] args)
{
ServiceBulk sb = new ServiceBulk();
sb.ServiceBulkManager();
}
}
}
私「よっしゃできたやで」
私「ビルドして実行や！よしテーブル見てみよう！」
SERVICE_TABLE
| Service_ID | Service_Name |
|---|---|
| 1 | サービスA |
| 2 | サービスB |
| 3 | サービスC |
| 4 | サービスD |
| 5 | サービスE |
私「よっしゃOKや！」
私「できましたよー。これを実行してくださいー！」
上司「ありがとうやで。（なんでこいつexe渡してきたんや？まぁできてるならいいか）」
#あとがき
最後まで読んでいただきありがとうございます。
業務内容に関わる箇所や処理等、都合により一部割愛しております。
まだまだ経験が浅いのでクソみたいなコードです。（StringBuilder使え）
コメントでコードレビューお願い致します