Csharp

【C#】Dapperの使い方

Posted on by PGlife

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 列名 データ型
〇 ID int
Name nvarchar(50)
Age Smallint

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

ID Name Age
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テーブルに「血液型」の列を追加して、下記のデータを追加したとします。

ID Name Age BloodType
1 永野芽郁 21 AB
2 本田翼 28 O
3 戸田恵梨香 32 AB
4 川口春奈 26 O
5 吉岡里帆 28 B
6 新垣結衣 33 A 
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文を書く必要が無くなります。
使い方をこちらにまとめました。

Leave a Reply

Your email address will not be published. Required fields are marked *