(C#)C# ORM - Dapper

C#のORMというと標準の Entity Framework があるが、
「遅い」、「学習コストが高い」などというのがあるそうだ。
それにやっぱりSQLは生で書くことが多い。

「C# ORM」で検索すると Entity Framework と Dapper がよくヒットする。
ということで Dapper を使ってみるとすごく手に馴染んだ。

以下、 Dapper を使っているコードと、動作確認ソース。

Dapper使用コード

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using Dapper;

namespace LibraryDapper.DBNS
{
    /// <summary>
    /// データベース関係クラス
    /// </summary>
    class DB
    {
        /// <summary>
        /// Privateコンストラクタ
        /// </summary>
        private DB() { }

        public static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
        }

        /// <summary>
        /// SQL SELECT文実行
        /// </summary>
        /// <param name="sql">SQL文字列</param>
        /// <returns>SELECT検索結果</returns>
        public static IEnumerable<dynamic> ExecuteQuery(string sql, object parameter = null)
        {
            dynamic result;

            // 接続文字列の取得
            var connectionString = GetConnectionString();

            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    // SQLの実行（これがDapperのメソッド）
                    result = connection.Query(sql, parameter); 
                }
                catch (Exception exception)
                {
                    Console.WriteLine(exception.Message);
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// SQL(INSERT/UPDATE/DELETE)実行
        /// </summary>
        /// <param name="sql">SQL文字列</param>
        public static int ExecuteNonQuery(List<string> sqlList, List<Object> targetList)
        {
            int affected = 0;

            // 接続文字列の取得
            var connectionString = GetConnectionString();

            // DBコネクション
            var connection = new SqlConnection(connectionString);

            SqlTransaction transaction = null;

            try
            {
                connection.Open();

                // トランザクション開始
                transaction = connection.BeginTransaction();

                for (var i = 0; i < sqlList.Count; i++)
                {

                    // SQLの実行（これがDapperのメソッド）
                    affected += connection.Execute(sqlList[i], targetList[i], transaction);   // これがDapperのメソッド
                }

                // コミット
                transaction.Commit();
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
                    
                // ロールバック
                transaction.Rollback();
                    
                throw;
            }
            finally
            {
                transaction.Dispose();
                connection.Close();
            }

            return affected;
        }
    }
}

動作確認コード

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using LibraryDapper.DBNS;
using LibraryDapper.LibraryNS;

namespace LibraryDapper
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("DBアクセス サンプル");
            
            Console.WriteLine("--- INSERT サンプル");
            Insert();

            Console.WriteLine("--- SELECT サンプル");
            Select();

            Console.WriteLine("--- UPDATE サンプル");
            Update();

            Console.WriteLine("--- DELETE サンプル");
            Delete();
        }

        /// <summary>
        /// SQL Select サンプル
        /// </summary>
        static void Select()
        {
            // Authors検索結果
            var authors = DB.ExecuteQuery(@"SELECT * FROM AUTHORS");

            // Books検索結果
            var books = DB.ExecuteQuery(@"SELECT * FROM BOOKS");
            var bookList = books.OrderBy(b => b.Id)
                .Select(b => new Book
                {
                    Id = b.Id,
                    Title = b.Title,
                    Published = b.Published,
                    AuthorId = b.AuthorId
                }
                ).ToList();

            // Authors作成
            var authorList = authors.Select(
                author =>
                {
                    var authorsBooks = bookList.Where(b => author.Id == b.AuthorId).ToList();

                    // 著者作成
                    var instance = new Author
                    {
                        Id = author.Id,
                        LastName = author.LastName,
                        FirstName = author.FirstName,
                        Books = authorsBooks
                    };
                    return instance;
                }
               ).ToList();

            authorList.ForEach(a => Console.WriteLine(a.ToString()));
        }

        /// <summary>
        /// SQL UPDATE テスト
        /// </summary>
        static void Update()
        {
            var sqlList = new List<string>
            {
                @"UPDATE AUTHORS SET LastName = @LastName WHERE Id = @Id"
            };

            var targetList = new List<object> {
                new Author { Id = 1, LastName = "LastName 1（改）" }
            };

            int affected = DB.ExecuteNonQuery(sqlList, targetList);
            Console.WriteLine("affected: {0}", affected);
            Select();
        }
        
        /// <summary>
        /// SQL INSERT テスト
        /// </summary>
        static void Insert()
        {
            var sqlList = new List<string>
            {
                @"INSERT INTO Test.dbo.Authors(Id, LastName, FirstName)VALUES(@Id, @LastName, @FirstName)",
                @"INSERT INTO Test.dbo.Authors(Id, LastName, FirstName)VALUES(@Id, @LastName, @FirstName)"
            };

            var targetList = new List<object>{
                new Author { Id = 1, LastName = "Author LastName 1", FirstName = "Author FirstName 1"},
                new Author { Id = 2, LastName = "Author LastName 2", FirstName = "Author FirstName 2"},
            };

            int affected = DB.ExecuteNonQuery(sqlList, targetList);
            Console.WriteLine("affected: {0}", affected);
            //Select();
        }

        /// <summary>
        /// SQL DELETE テスト
        /// </summary>
        static void Delete()
        {
            var sqlList = new List<string>
            {
                @"DELETE FROM AUTHORS WHERE Id = @Id",
                @"DELETE FROM AUTHORS WHERE Id = @Id"
            };

            var targetList = new List<object>{
                new Author { Id = 1 },
                new Author { Id = 2 },
            };

            int affected = DB.ExecuteNonQuery(sqlList, targetList);
            Console.WriteLine("affected: {0}", affected);
            Select();
        }
        
    }
}

Entityなクラス群

using System;
using System.Collections.Generic;
using System.Linq;

namespace LibraryDapper.LibraryNS
{

    /// <summary>
    /// 書籍クラス
    /// </summary>
    class Book
    {
        /// <summary>
        /// 書籍ID
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// 書籍名
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 出版日
        /// </summary>
        public DateTime Published { get; set; }


        /// <summary>
        /// 著者ID
        /// </summary>
        public int AuthorId { get; set; }

        /// <summary>
        /// 書籍コンストラクタ
        /// </summary>
        public Book() { }

        /// <summary>
        /// 書籍コンストラクタ
        /// </summary>
        /// <param name="id">書籍ID</param>
        /// <param name="title">書籍名</param>
        /// <param name="published">出版日</param>
        /// <param name="authorId">著者ID</param>
        public Book(int id, string title, DateTime published, int authorId)
        {
            this.Id = id;
            this.Title = title;
            this.Published = published;
            this.AuthorId = authorId;
        }

        /// <summary>
        /// 書籍の文字列表現
        /// </summary>
        /// <returns>書籍の文字列表現</returns>
        public override string ToString()
        {
            return String.Format(
                "Book(Id:{0}, Title:{1}, Published:{2}, AuthorId:{3})", 
                this.Id, this.Title, this.Published, this.AuthorId);
        }
    }

    /// <summary>
    /// 著者クラス
    /// </summary>
    class Author
    {
        /// <summary>
        /// 著者ID
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// 著者名（姓）
        /// </summary>
        public string LastName { get; set; }

        /// <summary>
        /// 著者名（名）
        /// </summary>
        public string FirstName { get; set; }

        /// <summary>
        /// 著作物
        /// </summary>
        public List<Book> Books { get; set; }

        /// <summary>
        /// 著者コンストラクタ
        /// </summary>
        public Author() { }

        /// <summary>
        /// 著者コンストラクタ
        /// </summary>
        /// <param name="id">著者ID</param>
        /// <param name="lastName">著者名（姓）</param>
        /// <param name="firstName">著者名（名）</param>
        /// <param name="books">著作物</param>
        public Author(int id, string lastName, string firstName, List<Book> books)
        {
            this.Id = id;
            this.LastName = lastName;
            this.FirstName = firstName;
            this.Books = books;
        }

        /// <summary>
        /// 著者の文字列表現
        /// </summary>
        /// <returns>著者の文字列表現</returns>
        public override string ToString()
        {
            var books = this.Books.Select(book => book.ToString()).ToArray();

            string format = "Author(Id: {0}, LastName: {1}, FirstName: {2}, Books({3}))";
            
            return String.Format(format, this.Id, this.LastName, this.FirstName, String.Join(", ", books));
        }
    }
}