ダッパーというのを触ってみた（Sqlite,F#）

# Summary

F#でSQLを楽にかくのにはダッパーというのがいいらしい
ということで
ダッパーというのを触ってみました

## ダッパーとは？
SQLiteとfsharpの橋渡し的な何か

## コード
下記においてます

[Dapperというのを触ってみた！](https://gist.github.com/callmekohei/31718b6780feb3cce69227ab61dddf5c)

## パッケージ

下記を dotnet add package する

```
dapper
system.data.sqlite
```

## オープン

```fsharp
open System.Data.SQLite
open System.Collections.Generic
open System.Data
open System.Dynamic
open Dapper
```

## 便利関数

```fsharp
module Util =

  (*
    便利関数

    vbfox/Dapper.fs
    https://gist.github.com/vbfox/1e9f42f6dcdd9efd6660
    https://www.nekoni.net/Blog/Article/nekonidotnet-admin-development-part6-first-part

  *)

  let query<'Result> (sql:string) (connection:IDbConnection) : 'Result seq =
    connection.Query<'Result>(sql)

  let parametrizedQuery<'Result> (sql:string) (param:obj) (connection:IDbConnection) : 'Result seq =
    connection.Query<'Result>(sql, param)

  let parametrizedQuery2<'Result,'Result2,'R> (sql:string) (param:obj) (connection:IDbConnection)  =
    connection.Query<'R>(sql, param)

  let mapParametrizedQuery<'Result> (sql:string) (param : Map<string,_>) (connection:IDbConnection) : 'Result seq =
    let expando = ExpandoObject()
    let expandoDictionary = expando :> IDictionary<string,obj>
    for paramValue in param do
      expandoDictionary.Add(paramValue.Key, paramValue.Value :> obj)

    connection |> parametrizedQuery sql expando

  let execute (sql:string) (param:_) (connection:IDbConnection) =
    let response = connection.Execute(sql, param)
    printfn "response:%d" response
    response

  // 単純にレコードをseq<string>をに変換する
  // http://kiyokura.hateblo.jp/entry/2016/12/09/131602
  let simpleRecord (dapperSqlMapperDapperRows:seq<obj>) =
    dapperSqlMapperDapperRows
    |> Seq.map(
      fun (dapperSqlMapperDapperRow:obj)  ->
        dapperSqlMapperDapperRow :?> IDictionary<string, obj>
        |> Seq.map( fun (KeyValue(k,v)) -> v.ToString() )
    )


``` 

## 01.テーブルを作成する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149269/f3ff2fef-50ff-2723-e841-2e588b59b746.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149269/3ca5d983-ec29-d7c5-0ae5-b2ba1edd6dc2.png)


```fsharp
  let private createTables () =

    let sql = @"

      DROP TABLE IF EXISTS [FruitsTable] ;
      CREATE TABLE [FruitsTable] (
          [商品CD] INTEGER
        , [商品名称]	TEXT
        , [色味]	TEXT
      )
      ;

      DROP TABLE IF EXISTS [PriceTable] ;
      CREATE TABLE [PriceTable] (
          [商品CD] INTEGER
        , [価格]	INTEGER
      )
      ;

    "
    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.execute sql ""
    |> printfn "%A"
```

## 02. データを投入する

```fsharp
  let private insertDatas () =

    let sql = @"
      INSERT
        INTO [FruitsTable]
        VALUES( @id , @name , @color ) ;
    "
    let rcds = [ ["1";"りんご";"うすい赤"] ; ["2";"バナナ";"あざやかな黄色"] ; ["3";"チェリー";"黒みがかった赤"]  ]
    let queryParams = rcds |> Seq.map(fun rcd -> {|id = rcd.[0]; name = rcd.[1]; color = rcd.[2]|})
    let con = new SQLiteConnection (@"URI=file:tmp.sqlite")

    con
    |> Util.execute sql queryParams
    |> printfn "%A"

    let sql2 = @"
      INSERT
        INTO [PriceTable]
        VALUES( @id , @price ) ;
    "
    let rcds2= [ ["1";"250"] ; ["2";"120"] ; ["3";"450"] ]
    let queryParams2= rcds2 |> Seq.map(fun rcd -> {|id = rcd.[0]; price = rcd.[1]|})

    con
    |> Util.execute sql2 queryParams2
    |> printfn "%A"
```

## 03. テーブルからデータを取得する（テーブルスキーマを指定）

### 実行結果

```fsharp

seq [{ 商品CD = 1L
       商品名称 = "りんご"
       色味 = "うすい赤" }]

seq [{ 商品CD = 2L
       商品名称 = "バナナ"
       色味 = "あざやかな黄色" }]

seq [{ 商品CD = 3L
       商品名称 = "チェリー"
       色味 = "黒みがかった赤" }]

seq [{ 商品名称 = "チェリー"
       価格 = 450L }]
```

### 取得用のコード

```fsharp

  type Fruits =
    {
      ``商品CD`` : int64
      ``商品名称`` : string
      ``色味`` : string
    }

  type Price =
    {
      ``商品CD`` : int64
      ``価格`` : int64
    }

  type Foo =
    {
      ``商品名称`` : string
      ``価格`` : int64
    }

  // no query parameter
  let private select01 () =
    let sql = "SELECT * FROM [FruitsTable] WHERE [商品名称] = 'りんご' ;"
    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")

    con
    |> Util.query<Fruits> sql
    |> printfn "%A"

  // query parameter is anonimous record type
  let private select02 () =
    let sql = "SELECT * FROM [FruitsTable] WHERE [商品名称] = @name ;"
    let parameter = "バナナ"
    let queryParams = {|name = parameter|}

    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.parametrizedQuery<Fruits> sql queryParams
    |> printfn "%A"

  // query parameter is map
  let private select03 () =
    let sql = "SELECT * FROM [FruitsTable] WHERE [商品名称] = @name ;"
    let parameter = "チェリー"
    let queryParams = Map["name" , box parameter]

    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.mapParametrizedQuery<Fruits> sql  queryParams
    |> printfn "%A"

  // Multi Mapping
  let private select04 () =
    let sql = @"
      SELECT
          ft.[商品名称]
        , pt.[価格]
      FROM [FruitsTable] AS ft
      INNER JOIN [PriceTable] AS pt ON
        ft.[商品CD] = pt.[商品CD]
      WHERE
        pt.[価格] > @price
      ;
    "
    let queryParams = {|price = 300|}

    new SQLiteConnection (@"URI=file:tmp.sqlite")
    |> Util.parametrizedQuery2<Fruits,Price,Foo> sql queryParams
    |> printfn "%A"
```

## 04. テーブルからデータを取得する（テーブルスキーマを使用しない）

### 実行結果

```fsharp
seq [seq ["1"; "りんご"; "うすい赤"]]

seq [seq ["2"; "バナナ"; "あざやかな黄色"]]

seq [seq ["3"; "チェリー"; "黒みがかった赤"]]

seq [seq ["チェリー"; "450"]]

```

### 取得用のコード

```fsharp

  // no query parameter
  let private selectA () =

    let sql = "SELECT * FROM FruitsTable WHERE 商品名称 = 'りんご' ;"

    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql)
    |> Util.simpleRecord
    |> printfn "%A"

  // query parameter is anonimous record type
  let private selectB () =

    let sql = "SELECT * FROM FruitsTable WHERE 商品名称 = @name ;"
    let parameter = "バナナ"
    let queryParams = {|name = parameter|}

    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql,queryParams)
    |> Util.simpleRecord
    |> printfn "%A"

  // query parameter is map
  let private selectC () =

    let sql = "SELECT * FROM FruitsTable WHERE 商品名称 = @name ;"
    let parameter = "チェリー"
    let queryParams = Map["name" , box parameter]

    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql,queryParams)
    |> Util.simpleRecord
    |> printfn "%A"

  // Multi Mapping
  let private selectD () =
    let sql = @"
      SELECT
          ft.[商品名称]
        , pt.[価格]
      FROM [FruitsTable] AS ft
      INNER JOIN [PriceTable] AS pt ON
        ft.[商品CD] = pt.[商品CD]
      WHERE
        pt.[価格] > @price
      ;
    "
    let queryParams = {|price = 300|}
    use con = new SQLiteConnection (@"URI=file:tmp.sqlite")
    con.Query(sql,queryParams)
    |> Util.simpleRecord
    |> printfn "%A"
```

## 参考

[Dapper github](https://github.com/StackExchange/Dapper)
[vbfox/Dapper.fs](https://gist.github.com/vbfox/1e9f42f6dcdd9efd6660)
[猫に.NET - F#でDapperを使ったDBアクセス](https://www.nekoni.net/Blog/Article/nekonidotnet-admin-development-part6-first-part)

## 感想

ほむほむ
お仕事コードがきれいになりそうな予感