どうもです、猫サムライです。
本日はお仕事の備忘録です。
今後も使える、って思ったため残しとこうと。
現在、VB2010、PostgreSQLを使用したプロジェクトに関わっています。
Npgsqlを使用しています。
大量データ（郵便番号とか）のやりとりが必要になり、通常の追加、更新処理では
とてもじゃないけど間に合わない、ってことでCOPYコマンド実行に至ったわけです。
自分の中で試したのは以下3通り
（MSDN：「方法：データベースに新しいレコードを挿入する」
http://msdn.microsoft.com/ja-jp/library/ms233812(VS.80).aspx ）
・TableAdapter.Insert メソッドを使用してデータベースに新規レコードを挿入
・TableAdapter.Update メソッドを使用してデータベースに新規レコードを挿入
・コマンド オブジェクトによる新規レコードの挿入
まず、pgadminがインストールされていれば、
「COPY [テーブル名] (列1,列2,......) FROM [CSVファイル名] WITH CSV」
をpsqlのコマンド実行であっというまに12万件の郵便番号なんかはインポートできます。
（だいたい2秒ぐらいで完了しますかね）
COPYコマンドであればものの2～3秒で完了する処理が
試しで実行した上記3通りの方法では10分以上かかってしまいました。
（ソースの書き方が悪いって言うな）
参考にさせてもらった先人のページでも1分ちょっとでした。
（mdbへのインポートなのでPostgreSQLとはまたちょっと違うかもしれません）
接続型の方が非接続型よりも早い、なんて書いてるページも見ましたが
ある程度大きなデータのやり取りなので接続型はチョイスしませんでした。
で、本題のCOPYコマンド実行です。
基本的にはNpgsqlのユーザマニュアルで実行可能です（笑）。
じゃあ、わざわざ書く必要がないじゃないか、なんて声も聞こえてきますが
単純なCSVファイルしか対応できません。
つまり、カンマ区切り、にしか対応できません。
CSVと言えば当然カンマ区切りのデータファイルですが
データ中のカンマなんかをエスケープするために
データをダブルクォーテーションで囲ったりしますよね。
ユーザマニュアルで示されている記述では
このダブルクォーテーションは文字としてカウントされます。
テーブルの列定義で長さを余分に取りたくない、
なおかつ、CSVではダブルクォーテーションで囲まれてしまっているが
CSVを編集なんてしたくない、って場合には不向きです。
そんな感じで猫サムライが思いついた方法、
１．CSVをデータテーブルに格納
２．ユーザマニュアルと同じ形に加工（Byte配列）
３．COPYコマンド実行
まず「１．CSVをデータテーブルに格納」。
今回はTextFieldParserクラスを使用しています。
CSVをJetProviderで開いてもいいんですが
今回の郵便番号みたいにダブルクォーテーションで囲まれていない「01101」などの値は
JetProviderでデータテーブルに格納すると「1101」になってしまいました。
なのでTextFieldParserで1レコードずつ格納する形をとりました。
ここでReplaceするって方法も考えましたが1レコードずつ加工するのも
リソース食いそうなので自分の中で却下。
そのソースが以下になります。
''=======================================================================
'取り込みもととするDataTableの作成
Dim dt As New DataTable
For i As Integer = 0 To 14 '郵便番号CSVは15列あるため
dt.Columns.Add(New DataColumn(i.ToString))
Next
'CSVファイル読み込み(CSVはSJIS)
Dim FilePath As String = "C:\KEN_ALL.CSV"
Dim parser As TextFieldParser = New TextFieldParser(FilePath, Encoding.GetEncoding(932))
parser.TextFieldType = FieldType.Delimited '区切り設定
parser.SetDelimiters(",") 'カンマ区切りである事を設定
'DataTableにCSVデータ取り込み
While Not parser.EndOfData
Dim fields As String() = parser.ReadFields
dt.Rows.Add(fields)
End While
''=======================================================================
この時点でDataTable「dt」にはCSVファイルが取り込まれます。
確認する場合は、DataGridViewにでも流し込んでみてください。
そして「２．ユーザマニュアルと同じ形に加工（Byte配列）」
ユーザマニュアル記載の通りに実行させていくと
CSVデータで「01101,"050 ",.......」というデータは
ダブルクォーテーションだけでなく半角スペースまでも
Byte配列として認識されてしまいます。
１でデータテーブルに流した時点で、「"」「 （半角スペース）」はTrimされています。
データテーブルの中身をまたCSVと同じような形の文字列に変換します。
''=======================================================================
'文字列 「&=」 で結合してもいいとは思いますが「StringBuilder」の方がいいかも
Dim sb As New StringBuilder
'データテーブル全行をループ
For Each row As DataRow In dt.Rows
'DataRowの全列データを結合
For j As Integer = 0 To dt.Columns.Count - 1
sb.Append(row.Item(j).ToString)
If j <> dt.Columns.Count - 1 Then
'区切りに「,」を挿入
sb.Append(",")
Else
'行の最後に改行を挿入する
sb.Append(vbCrLf)
End If
Next
Next
'結合した文字列を格納
Dim val As String = sb.ToString
'格納した文字列をUTF8でByte配列に格納
'猫サムライのPostgreSQLの文字コードがUTF8のため（適宜、実行環境に合わせてください）
Dim buf() As Byte = Encoding.UTF8.GetBytes(val)
''=======================================================================
行の最後に改行（vbCrLf）を挿入しているのは郵便番号のCSVを
ユーザマニュアルのソース通りに流すとデータ行の最後のByte配列が
CrLfに相当しているものであったためにそうしています。
これがもし「Cr」や「Lf」の場合はまた対応が違うのかもしれません。
ここまででようやくCOPYコマンドの実行前の準備が整いました。
最後に「３．COPYコマンド実行」です。
これはユーザマニュアルと同じでも大丈夫です。
''=======================================================================
Dim conn As NpgsqlConnection = New NpgsqlConnection
conn.ConnectionString = "[接続文字列]"
conn.Open()
'PostgreSQL上に15列ある「zip_table」を用意しときます
Dim sql As String = "COPY zip_table (col1,col2,........col15) FROM STDIN DELIMITERS ','"
Dim copy As NpgsqlCopyIn = New NpgsqlCopyIn(New NpgsqlCommand(sql, conn), conn)
Try
'COPYコマンド開始
copy.Start()
'Byte配列書き出し
copy.CopyStream.Write(buf, 0, buf.Length)
Catch ex As Exception
copy.Cancel("Undo copy")
Finally
If copy.CopyStream IsNot Nothing Then
Try
copy.CopyStream.Close()
Catch ex As Exception
copy.Cancel("Undo copy")
End Try
End If
copy.End()
conn.Close()
conn = Nothing
End Try
''=======================================================================
エラーハンドラは適宜自分の都合のいいように変更するのがいいと思います。
そんなこんなで試行錯誤した結果、このソースを組み込む事で
12万件の郵便番号CSVを10秒足らずでインポートできるようになりましたとさ。
ちょっと頑張った猫サムライでした。
P.S
質問も承っておりますゆえｗ
レスポンス悪いかもしれませんけどね（爆）
こんな方法もあるよってのも教えてください。
