C#

【C#】Dapperの使い方

DapperはC#でDBアクセスするためのライブラリです。

特徴としてはORマッパーであることです。C#でORマッパーと言えばEntity Frameworkが思いつきますが、速度面ではDapperの方が圧倒的に優れています。

Entity Frameworkと違ってシンプルな機能のみ用意されているため、使い方も簡単で学習コストが低いためおすすめです。

【検証環境】.NET 5.0 / SQL Server 2019 localDB

利用準備

パッケージのインストール

PM> Install-Package Dapper

https://www.nuget.org/packages/Dapper/

DBとテーブルの作成

今回はSQL ServerのlocalDBを使用します。
DB「TEST」にテーブル「Person」を作成します。

PK列名データ型
IDint
Namenvarchar(50)
AgeSmallint

下記データをあらかじめ登録しておきます。

IDNameAge
1永野芽郁21
2本田翼28
3戸田恵梨香32

マッピング用クラスの作成

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }

    public override string ToString()
    {
        return $"{Name}:{Age}歳";
    }
}

使い方

https://dapper-tutorial.net/dapper

SELECT

Query

単純にSELECT文を実行したい場合は、Query メソッドを使用します。

コード下部にパラメータ化クエリの書き方についても記載しています。
ちなみに第2引数に渡すパラメータは匿名型でもPerson型でもどちらでも構いません。(プロパティ名とパラメータ名を一致させる必要があります)

using Dapper;
using System;
using System.Data.SqlClient;
using System.Linq;

//接続文字列
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";

using (var connection = new SqlConnection(connectionString))
{
    //接続
    connection.Open();
    //SQL
    string sql = "SELECT * FROM Person";
    //データマッピング (IEnumerable型で返すため、LINQが使える)
    var people = connection.Query<Person>(sql)
                           .OrderBy(p => p.Age);

    foreach (var person in people)
    {
        Console.WriteLine(person.ToString());
    }
    //永野芽郁: 21歳
    //本田翼:28歳
    //戸田恵梨香:32歳

    //クエリのパラメータ化 (SQLインジェクション対策)
    string sql2 = "SELECT * FROM Person WHERE Age >= @AgeFrom AND Age <= @AgeTo";

  var param = new { AgeFrom = 25, AgeTo = 30, };
    var people2 = connection.Query<Person>(sql2, param)
                .OrderBy(p => p.Age);
    foreach (var person in people2)
    {
        Console.WriteLine(person.ToString());
    }
    //本田翼:28歳
}

QueryFirstOrDefault

単一レコードを取得したい場合は、QueryFirstOrDefault メソッドを使用します。
Query メソッドで取得した結果にFirstOrDefaultを使っても同じ結果になりますが、パフォーマンスが落ちますので、単一のレコードを取得する場合はQueryFirstOrDefaultを使用するようにしましょう。

//接続文字列
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";
using (var connection = new SqlConnection(connectionString))
{
  //接続
    connection.Open();
  //SQL
    string sql = "SELECT * FROM Person WHERE ID = @ID";
  //パラメータ
    var param = new { ID = 3 };

    var person = connection.QueryFirstOrDefault<Person>(sql, param);

    //結果は同じだが、QueryFirstOrDefaultの方がパフォーマンスが高い
    //var person = connection.Query<Person>(sql).FirstOrDefault();
}

ExecuteScalar

単一レコードの特定の列を取得したい場合は、ExecuteScalar メソッドを使用します。
レコードを取得してから任意のプロパティを取得はできますが、QueryFirstOrDefaultと同様に、パフォーマンスが高くなるため、特定の列を取得する場合はExecuteScalarを使用するようにしましょう。

//接続文字列
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";
using (var connection = new SqlConnection(connectionString))
{

  //接続
    connection.Open();
  //SQL
    string sql = "SELECT Name FROM Person WHERE ID = @ID";
  //パラメータ
    var param = new { ID = 3 };

    var name = connection.ExecuteScalar<string>(sql, param);
}

INSERT

INSERT文を実行したい場合は、Execute メソッドを使用します。
複数件行いたい場合も、第2引数に渡すデータをListにするだけでOKです。

ちなみにBulk Insertには対応していません。拡張ライブラリのDapper Plus(有料)を使えば可能です。
大量のデータをInsertする場合は、Bulk Copy を使うと高速に処理が可能です。

//接続文字列
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";

using (var connection = new SqlConnection(connectionString))
{
    //接続
    connection.Open();
    //SQL
    var queryInsert = "INSERT INTO Person (Name, Age) VALUES (@Name, @Age)";
    //パラメータ
    var person = new Person()
    {
        Name = "川口春奈",
        Age = 26,
    };
    //戻り値は処理件数
    var insert = connection.Execute(queryInsert, person);


    //複数INSERT
    var people = new List<Person>()
    {
        new Person()
        {
            Name = "深田恭子",
            Age = 38,
        },
        new Person()
        {
            Name = "綾瀬はるか",
            Age = 36,
        }
    };

  //戻り値は処理件数
    var insertMultiple = connection.Execute(queryInsert, people);
}

UPDATE

UPDATE文を実行したい場合は、Execute メソッドを使用します。
複数件行いたい場合も、第2引数に渡すデータをListにするだけでOKです。

ちなみにBulk Updateには対応していません。無料の拡張ライブラリで可能なものもあるようです。

//接続文字列
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";

using (var connection = new SqlConnection(connectionString))
{
    //接続
    connection.Open();
    //SQL
    var queryUpdate = "UPDATE Person Set Name = @Name, Age = @Age WHERE ID = @ID";
    //パラメータ
    var person = new Person()
    {
        ID = 5,
        Name = "篠原涼子",
        Age = 47,
    };
    //戻り値は処理件数
    var update = connection.Execute(queryUpdate, person);


    //複数UPDATE
    people = new List<Person>()
    {
        new Person()
        {
            ID = 5,
            Name = "深田恭子",
            Age = 38,
        },
        new Person()
        {
            ID = 6,
            Name = "篠原涼子",
            Age = 47,
        }
    };
  //戻り値は処理件数
    var updateMultiple = connection.Execute(queryUpdate, people);
}

DELETE

DELETE文を実行したい場合は、Execute メソッドを使用します。
複数件行いたい場合も、第2引数に渡すデータをListにするだけでOKです。

ちなみにBulk Deleteには対応していません。無料の拡張ライブラリで可能なものもあるようです。

//接続文字列
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";

using (var connection = new SqlConnection(connectionString))
{
    //接続
    connection.Open();
    //SQL
    var queryDelete = "DELETE FROM Person WHERE ID = @ID";
  //パラメータ
    var id = new 
    {
        ID = 7,
    };

    //戻り値は処理件数
    var delete = connection.Execute(queryDelete, id);


  //複数DELETE
    var ids = new[]
    {
         new {ID = 8 },
         new {ID = 9 },
         new {ID = 10 },
    };
    var delete = connection.Execute(queryDelete, ids);
}

トランザクション

トランザクションを行いたい場合は、Execute メソッドの第3引数にトランザクション変数を渡すことで可能です。

//接続文字列
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";

using (var connection = new SqlConnection(connectionString))
{
    //接続
    connection.Open();
    //SQL
    var queryUpdate = "UPDATE Person Set Name = @Name, Age = @Age WHERE ID = @ID";
    //パラメータ
    var person = new List<Person>()
    {
        new Person()
        {
            ID = 5,
            Name = "篠原涼子",
            Age = 47,
        },
    };

    using (var tx = connection.BeginTransaction())
    {
        try
        {
            //第3引数に渡す
            var update = connection.Execute(queryUpdate, person, tx);
            //コミット
            tx.Commit();
        }
        catch (Exception)
        {
            //ロールバック
            tx.Rollback();
            throw;
        }
    }
}

応用編

動的なWhere句の生成

public class Option
{
    public string Name { get; set; }
    public int? AgeFrom { get; set; }
    public int? AgeTo { get; set; }
}

//動的にパラメータを生成する
var option = new Option()
{
    AgeFrom = 25,
    AgeTo = 30,
};
//SQL文
var sql = @"SELECT * FROM Person 
            WHERE (@Name IS NULL OR Name = @Name) 
            AND (@AgeFrom IS NULL OR Age >= @AgeFrom) 
            AND (@AgeTo IS NULL OR Age <= @AgeTo);";
var param = new { Name = option?.Name, AgeFrom = option?.AgeFrom, AgeTo = option?.AgeTo };
var people = connection.Query<Person>(sql, param);


もしくはDapper.SqlBuilder を使用する

https://github.com/DapperLib/Dapper/tree/main/Dapper.SqlBuilder

PM> Install-Package Dapper.SqlBuilder
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    //動的にパラメータを生成する
    var option = new Option()
    {
        AgeFrom = 25,
        AgeTo = 30,
    };

    var builder = new SqlBuilder();
    var template = builder.AddTemplate("SELECT * FROM Person /**where**/");
    if(option != null)
    {
        if (!string.IsNullOrEmpty(option.Name))
        {
            builder.Where("Name = @Name", new { Name = option.Name});
        }
        if (option.AgeFrom != null)
        {
            builder.Where("Age >= @AgeFrom", new { AgeFrom = option.AgeFrom });
        }
        if (option.AgeTo != null)
        {
            builder.Where("Age <= @AgeTo", new { AgeTo = option.AgeTo });
        }
    }
    var people = connection.Query<Person>(template.RawSql, template.Parameters);
}

GROUP BY

DapperでGROUP BYを行いたい場合は、GROUP BYの結果取得用のモデルをあらかじめ作成しておきます。SELECTする列名もしくはASを使った別名と、プロパティ名を一致させる必要があります。

例として、Personテーブルに「血液型」の列を追加して、下記のデータを追加したとします。

IDNameAgeBloodType
1永野芽郁21AB
2本田翼28O
3戸田恵梨香32AB
4川口春奈26O
5吉岡里帆28B
6新垣結衣33A
public class GroupingData
{
    //血液型
    public string BloodType { get; set; }
    //人数
    public int count { get; set; }
}
var connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=TEST;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{

  var sql = @"SELECT BloodType, COUNT(*) AS count 
              FROM Person 
              GROUP BY BloodType";
  var gruop = connection.Query<GroupingData>(sql);
  foreach (var item in gruop)
  {
      Console.WriteLine($"{item.BloodType}型:{item.count}人");
  }
    //A型:1人
    //AB型:2人
    //B型:1人
    //O型:2人
}

拡張ライブラリ

Dapper の拡張ライブラリとしてDapperExtensions があります。簡単な操作であればSQL文を書く必要が無くなります。
使い方をこちらにまとめました。

エンジニアの転職ならこれ!

【第二新卒向け】マイナビジョブ20's

マイナビジョブ20'sは、20代・第二新卒・既卒向けの転職エージェントです。

▼こんな方におすすめ
・はじめて転職しようと思っている
・転職できるだけのスキルが自分にあるか不安
・手厚いサポートを受けたい

【フリーランス向け】安心保障と豊富な案件紹介 Midworks

Midworksは豊富な案件と「フリーランス」と「正社員」の良いとこ取りをした働き方を実現する手厚い保障が特徴です。

▼こんな方におすすめ
・現在正社員でフリーランスになろうか悩んでいる
・フリーランスとして働いているが、先行きが不安がある  (安定的な案件確保や保障など)
・自分の市場価値を知りたい、見合った案件で参画したい
・今後のキャリアビジョンを踏まえて案件を選びたい

【未経験向け】自宅で現役エンジニアから学べる TechAcademy

テックアカデミーは、現役エンジニアから学べるオンラインに特化したプログラミングスクールです。
講師は全員、通過率10%の選考に合格した現役エンジニア。
確かなスキルをもとに受講生をマンツーマンサポートします。


▼こんな方におすすめ
・自宅にいながらオンライン完結で勉強できる
・受講生に1人ずつ現役エンジニアのパーソナルメンターが専属でつく
・チャットで質問すればすぐに回答が返ってくる
・オリジナルサービスやオリジナルアプリなどの開発までサポート