SqliteにSystem.Data.Sqliteを使用してinsertする

# Summary

`Sqlite`に`System.Data.Sqlite`を使用して'insert'する

個人的メモ

## env.

```
Microsoft Windows [Version 10.0.18363.657]
memory 16GB
SSD 256GB
```

## 対象Csv

```
Size 139,209KB
Columns 104
rows 265,809
```

## 結論

|関数名|メモ|処理時間|
|------|----|--------|
|Insert|トランザクション|2:00:268|
|InsertAll|トランザクション、Insertの別表記|1:55:303|
|InsertWithParam|トランザクション、パラメータ|2:30:601|

## 設定コード

```fsharp
let SQ3 fpDatabase : Sq3Impl =
  Encoding.RegisterProvider(CodePagesEncodingProvider.Instance)
  let connectionString =
    (
      SQLiteConnectionStringBuilder(
          DataSource = fpDatabase
        , Version = 3
        , ForeignKeys = true
        , SyncMode = SynchronizationModes.Off
        , JournalMode = SQLiteJournalModeEnum.Memory
      )
    ).ToString()
  Sq3Impl( new SQLiteConnection( connectionString ) )
```

## コード１

トランザクションで囲んで、コマンドためて一気にコミット

```fsharp
member this.Insert (tablename:string) (recordArray:seq<seq<string>>) =

  command.Transaction <- con.BeginTransaction()

  let reg = Regex("'", RegexOptions.Compiled)
  try
    recordArray
    |> Seq.iter ( fun x ->
      x |> Seq.fold ( fun acc s -> acc + "," + "'" + reg.Replace(s,"''") + "'"  |> fun s -> s.TrimStart(',') ) ""
      |> fun s ->
        command.CommandText <- "INSERT INTO " + tablename + " VALUES (" + s + ");"
        try
          command.ExecuteNonQuery() |> ignore
        with _ ->
          stdout.WriteLine ("failed! Insert : " + s )
    )
  with _ ->
    stdout.WriteLine ("failed! Insert : " + recordArray.ToString() )

  try
    command.Transaction.Commit()
  with _ ->
    stdout.WriteLine ("failed! Insert commit : " )
    command.Transaction.Rollback()
```

## コード２

コード１の別表記

```fsharp
member this.InsertAll (tablename:string) (columnNames:string[]) (recordArray:seq<seq<string>>) =

  /// create insert value : (a,b,c) , (d,e,f) ... ;
  let insertValues =
    let reg = Regex("'", RegexOptions.Compiled)
    let sb = StringBuilder()
    recordArray
    |> Seq.iter ( fun rcd ->
      rcd |> Seq.iter(fun fld -> sb.Append("'" + reg.Replace(fld,"''") + "'") |> ignore)
      sb.AppendJoin(",") |> ignore
    )
    sb.AppendLine(@"),(") |> ignore
    "((" + sb.ToString() + "))"

  command.Transaction <- con.BeginTransaction()
  command.CommandText <-
    @"INSERT INTO "
    + tablename
    + "(\'" + (columnNames |> String.concat "\',\'") + "\')"
    + " VALUES "
    + insertValues + ";"
  try
    command.ExecuteNonQuery() |> ignore
    command.Transaction.Commit()
  with _ ->
    stdout.WriteLine ("failed! InsertAll : " )
    command.Transaction.Rollback()
```

## コード３

パラメータを使う方法

```fsharp
member this.InsertWithParam2 (tablename:string) (headerLength:int) (recordArray:seq<seq<string>>) =

  command.Transaction <- con.BeginTransaction()

  let parameterNames = [| for i in 1 .. headerLength -> "$foo" + string(i) |]
  command.CommandText <- @"INSERT INTO " + tablename + " VALUES (" + (parameterNames |> String.concat ",") + ");"

  let parameters = query{
    for pr in parameterNames do
      select (
        let parameter = command.CreateParameter()
        parameter.ParameterName <- pr
        command.Parameters.Add(parameter) |> ignore
        parameter
      )
  }

  let reg = Regex("'", RegexOptions.Compiled)

  recordArray
  |> Seq.iter( fun rcd ->
    (rcd,parameters)
    ||> Seq.iter2(
      fun fld param ->
        param.DbType <- System.Data.DbType.String
        param.Value <- "'" + reg.Replace(fld,"''") + "'"
    )
    command.ExecuteNonQuery() |> ignore
  )

  try
      command.Transaction.Commit()
    with _ ->
      stdout.WriteLine ("failed! InsertWithParam commit : " )
      command.Transaction.Rollback()
```

現場からは以上です