C#とSQL文 mdfファイルのdataをSQLserverに追加したい SqlBulkCopyでロケールID違いのエラー

Question

素人ですみません。(質問を2人の意見を聞いて再編集しました。)
素人なので変な風に理解してるかもしれません!よろしくお願いします。

ローカルにあるmdfファイルのテーブルごと
違うPCのSQLserverのテーブルに追加したいです。。
↓例 追加したいmdfファイルのデータ

A_field B_field C_field D_field
LCno1   名前1  電話1  メール1
LCno2   名前2  電話2  メール2
LCno3   名前3  電話3  メール3

↓SQLserverのテーブル 追加後こうなってほしい (0_fieldは自動採番(オートナンバー)field)

0_field  A_field B_field C_field D_field
All_No1  LCno1   名前1    電話1  メール1
All_No2  LCno2   名前2    電話2  メール2
All_No3  LCno3   名前3    電話3  メール3

この様に共有サーバーに更新したいのですが…
２つ浮かびましたが…
・1のパターン SQL文で一括
・2のパターン insert文をC#で書いて一行つつでなら… (LINQも使える??)
2のパターンは何回もinsertするのでサーバーに負担かかりませんか?
今回は１のパターンを思考しています。。
↓ 1のパターンで考えた場合 (ごめんなさい2のパターンまで頭が回ってません。。。)

SQL接続文字 = @"Data Source=…"

INSERT文 ="INSERT INTO SQLserverのテーブル 名 (A_field,B_field,C_field,D_field)
SELECT * FROM [mdfデータベース名].テーブル名 WHERE 条件のあるフィールド = 何らか条件"

 ★問題点★
     **[mdfデータベース名]**この部分
        ↓
     ①**[mdfデータベース名]**visual studioで追加してるから、mdfファイル名(サーバー名でだけいい?)
     →やってみたが駄目だった。(.mdfまで書く?)
      ②**[フルパス + mdfデータベース名]**(.mdfまで書く?)
     →ちょっと、やってみたがうまく行かず…
      ③プロバイダみたいなものが必要?
     →**[Provider=]**or**[Driver=]**???mdf用の書き方がいまいち分からない
 
 そもそもこのやり方でうまく行かないのか…

↓つくったINERT文をこれで

 SQL接続.ExecuteNonQuery(INSERT文, SQL接続文字);

↓SQL接続クラス

public class SQL接続
{
public static void ExecuteNonQuery(string sql, string 接続文字列)
 {
using (SqlCommand command = new SqlCommand())
 {
 SqlConnection conn = new SqlConnection();
 conn.ConnectionString = conn.ConnectionString = 接続文字列;
 // トランザクションを開始します。
conn.Open();
 SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
 try
 {
 command.CommandText = sql;
 command.Connection = conn;
 command.Transaction = transaction;
 command.ExecuteNonQuery();
 //トランザクションをコミットします。
transaction.Commit();
 }
 catch (System.Exception)
 {
 //トランザクションをロールバックします。
transaction.Rollback();
 throw;                     ← ここでエラーになってる
 }
 finally
 {
 conn.Close();
 }
 }
 }

tamotoさんのをコメントをみて ↑あきらめて…
1/17追記 新解決策 SqlBulkCopy あと一歩で解決か!?汗。。

public static void ExecuteCopy(string tableName)
		{
			using (var mdfcn =  new SqlConnection(mdf接続文字列))
			using (var SQLcn = new SqlConnection(SQLserver接続文字))
			{
				mdfcn.Open();
				SQLcn.Open();
				                      
				var mdfsql = string.Format("SELECT * FROM [{0}]", tableName);
				var mdfcmd = new SqlCommand(mdfsql, mdfcn);
				using (var rd = mdfcmd.ExecuteReader())
				{
					using (var bulk = new SqlBulkCopy(SQLcn))
					{
						bulk.BulkCopyTimeout = 10000;
						bulk.DestinationTableName = string.Format("dbo.{0}",tableName);
						bulk.WriteToServer(rd);    //← ここでエラー ロケールIDが 
                          //追加情報:ソース列 'フィルード名' のロケール ID '1033' と
                               //ターゲット列 'フィルード名' のロケール ID '1041' は
                              //一致していません。
					}

				}

			}

あと一歩なのかロケールIDが一致しないといわれる。
このやり方でSQLserver側の0fieldが mdf側になくても行けるのかがいまいち分かりませんが
ただ '1033'→英語 '1041'→日本語を指定出来るのかな… ?? またググってる最中です。
###2017/02/06追記 修正箇所

using (DataTable datatable = new DataTable())// 2017/02/06追記
{// 2017/02/06追記
    datatable.Load(rd);// 2017/02/06追記
    using (var bulk = new SqlBulkCopy(SQLcn))
	{
		bulk.BulkCopyTimeout = 10000;
		bulk.DestinationTableName = string.Format("dbo.{0}",tableName);
		bulk.WriteToServer(datatable);    //2017/02/06 書換
    }
}// 2017/02/06追記

tomatoさんのアドバイスで完璧にクリアできました。

Answer 1

SQLServerManagementStudioを使ってのデータベースのインポートとは違うのですか？

Comments

[hamaa]

データベースのインポートは違うかな…冷汗(素人で分かりません。)
ローカルにmdfファイルをいてデータ管理してます。
それを共有サーバーに更新したいのですが…　
SQL文?一括?? それとも insert文を一行つつでなら
C#で書いて掛けそうですが…　
SqlDataAdapterとか?
SqlConnectionでかな?
のやり方がいまいち分からないので…　

[dojikko]

ローカルのmdfのデータをSQLServer側に移したい(Insert)したいのですよね
その時にローカルのmdfよりSQLServer側のテーブルの方が拡張されている(例示だと0_fieldが追加されている)ということですよね
1回ものの話ならインポートしてあげれば済む話だと思いますが..
もしこれが反復して起こるなら2回目以降の時にSQLServer側にあるレコードならUpdate、ないレコードならInsertとして扱ってあげないといけないと思います
ただしそういう時にはレコードを一意に特定できるフィールド(所謂主キー)を定義しておいてあげる必要があります

コードよりデータベースのことをもう少し知った方が捗ると思いますよ

[hamaa]

失礼しました。
INSERT INTO SQLserverのテーブル 名 (A_field,B_field,C_field,D_field)
SELECT * FROM mdfファイルのテーブル名.[ココ?]　WHERE 条件のあるフィールド = 何らか条件

だとは　思うのですが…　接続部分の書き方?
[ココ?]と　書いたところ　後は通常時のSQLサーバーのconnectionの書き方で良いのでしょうか?

[dojikko]

「INSERT ～ SELECT ～」はそのデータベースから見えるデータベースのテーブルに対してまでじゃなかったかな？
なんかリンクサーバーとかにも手を出さないといけない感じじゃないですかね
もしローカルにあるmdfがSQLServerから見えてたら
> mdfファイルのテーブル名.[ココ?]
の部分は
mdfデータベース名.テーブル名
でいけると思いますが

それなら地道にコードから、ローカルのmdfファイルに対するコネクションと、SQLServerに対するコネクションを張って、SelectとInsertを繰り返した方が手っ取り早い気がする

[hamaa]

dojikkoさん
ありがとうございます。
SQL接続文字 = @"Data Source=…"

INSERT文 ="INSERT INTO SQLserverのテーブル 名 (A_field,B_field,C_field,D_field)
SELECT * FROM mdfデータベース名.テーブル名　WHERE 条件のあるフィールド = 何らか条件"
　　　　　　　　　　　　↓
　　　　　　　　　①visual studioで追加してるから、mdfファイル名(サーバー名でだけいい?)
　　　　　　　　　→やってみたが駄目だった。
　　　　　　　　　②ここの部分がフルパスだけでいい?
　　　　　　　　　→ちょっとちやってみます。
　　　　　　　　　③プロバイダみたいなものが必要?
　　　　　　　　　→???

SQL接続.ExecuteNonQuery(INSERT文, SQL接続文字);

↓SQL接続クラス

public class SQL接続
{
public static void ExecuteNonQuery(string sql, string 接続文字列)
{
using (SqlCommand command = new SqlCommand())
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = conn.ConnectionString = 接続文字列;
// トランザクションを開始します。
conn.Open();
SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
command.CommandText = sql;
command.Connection = conn;
command.Transaction = transaction;
command.ExecuteNonQuery();
//トランザクションをコミットします。
transaction.Commit();
}
catch (System.Exception)
{
//トランザクションをロールバックします。
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}

Answer 2

こんにちは。
いまいち質問の意味がわかってないのですが……

• どんどん更新されるmdfの中身を逐一SQLServerに放り込みたい(何度もデータを放り込みたい)
• 今までmdfで管理してたデータをこれからはSQLServerで扱いたい(1回限り、全データを放り込みたい)

この2つのうち、後者が目的だと思うのですが、あってますでしょうか？
後者を行う場合、SQLServerのGUI（SQL Server Management Studio）にmdfの中身を丸ごとSQLServerにインポートする機能があったと思うのですが、それを使うのではダメなんですかね？SQL文を書くとか書かないとかそれ以前の話ですよ。。

---

01/17追記
ローカルのmdfからリモートへデータを追記する方針は変わらないということなので、その方向で回答してみます。

まず、根本的な勘違いがある可能性があります。
ローカルのSQL Server（mdfファイル）とリモートのSQL Serverは「別のSQLサーバ」なので、一つのSQL文でデータを移行するのは不可能です。（正確には、リンクサーバとかのサーバ側の機能を利用すればできるのかもしれないですが、あんまり詳しくないので今回は無視します）
つまり、2つのサーバ間でデータを移行するためには、「ローカルmdfに接続するSQLConnection」と「リモートSQL Serverに接続するSQLConnection」の2つの接続が必要なのです。
それぞれ別々に考えてしまえば、「ローカルからデータを取り出す処理」「リモートにデータを送る処理」の異なる2つの手順を順番に行うだけでいいのです。
まずはデータを取り出す処理だけ実装し、意図したデータが取り出せているかどうか確認してみて下さい。
データが取り出せていれば、後はそれをリモートに放り込むだけです。整合性のチェックとかはがんばってください。

Comments

[hamaa]

ローカルのmdfファイルでデータを蓄積しています。
それを一括での中央管理SQLサーバーにインサートしたい　朝1回です。
>SQLServerのGUI（SQL Server Management Studio）にmdfの中身を丸ごとSQLServerにインポートする機能があったと思うのですが、
↑やっぱりそういう機能あるんですか!?　
0_fieldが1列多いですが出来そうですか?　ちょっと自分でもSQL Server Management Studio　もう少し調べてみます　汗

[tamoto]

朝1回ということは、複数回実行するということですか……
最初の1回はインポート操作でできるはずですが、それ以降は「同期操作」になるので、整合性を考慮してInsert/Updateを行わないといけないので結構大変です。
一度SQLServerにデータを入れた後は、「ローカル蓄積は止めて」「直接SQLServerの方にデータを蓄積」できるならそっちのほうが楽ですよ。
というか、上のdojikkoさんの回答とコメントを一文字ずつちゃんと読みなおしてみてください……

[hamaa]

そういった意味だとInsertで大丈夫です。
更新をする時は個別でします。

[hamaa]

日本語があまりうまくなくて失礼します。
ローカルでやらなきゃいけない理由としては中央のSQLサーバーにログを残せない時、
回線切断状態や、SQLサーバーダウン時にも、ローカルでログを残しておきたいからなんです。

[hamaa]

tomotoさんやはりそうなんですね。二つの接続が必要ですよね。
VBAのようにプロバイダ部分をSQLの中に書けるか探していたんですが…。
解決策としてググって頑張ってSqlBulkCopyなるものを発見コードを追記します。

[hamaa]

上記に追記しました。　あともう一歩?　勘違いしてたらすいません。汗

[tamoto]

ロケールの不一致についてちょっと調べてみましたがなんだかよくわかりませんね……一応、mdfから読み出したSqlDataReaderを直接BulkCopyせず、一度DataTableに読み込んでからそれを放り込むとうまくいくという情報もありますね。ちょっと調べられる環境がないので間違ってたらすみません。
それと、SqlDataReaderをそのままリモートSQLServerにBulkCopyすると、リモート側にデータの重複が発生してしまうと思うのですが、そこら辺はどう処理するのでしょう？BulkCopyする前にコピーするデータの加工が必要だと思います。

[hamaa]

>一応、mdfから読み出したSqlDataReaderを直接BulkCopyせず、一度DataTableに読み込んでからそれを放り込むとうまくいくという情報もありますね。
bulkcopyは高速ですと出ているので、せっかくですのでもう少し調べてみます。　ダメでしたら　tamotoさんの方向に変更して探してみます。(><

[tamoto]

あっと、WriteToServerはDbDataReaderを引数に取るもの以外にDataTableをそのまま渡せるオーバーロードが存在するようなので、一旦SqlDataReaderからDataTableに読み込んで、そのDataTableをSqlBulkCopyで放り込めばうまくいくのではないか、という話です。

[hamaa]

tamotoさん　別件の仕事でおくれておりますが、
>DataTableに読み込んで、そのDataTableをSqlBulkCopyで放り込めばうまくいくのではないか、という話です。
やってみます。もう少々おまちください。汗

[hamaa]

tamotoさん!!!!　ありがとうございます!　別件の仕事で離れておりましたが…
tamotoさんの言うとおりです!!完璧にできました!!　ありがとうございます!!!