C#でのDB接続に関するDAO/DACライブラリを作ってみた

本章では、C#でWPFやASP.NET MVCで活用できる、DB(SQL Server)からデータ取得できるライブラリについて共有します。

DB接続の方法

DB接続の方法として、現在以下の様な手法があります。

ADO.NET Data Provider
ADO.NET Entity Framework
LINQ to SQL

最近主流の方法は、EntityFrameworkです。コードドリブンで開発ができ、アプリ側のエンジニアにとって非常に利便な仕組みです。ただデータドリブンで開発する場合、複雑なクエリを実行する場合、一手間かかります。そこで今回、データドリブンで自由にクエリが発行できる手法として、「ADO.NET Data Provider」を採用しました。

前準備

今回のライブラリは、EnterpriseLibraryを利用しています。
第 1 章 - Enterprise Library へようこそ
そのため事前に、今回のライブラリを利用するプロジェクトに対して、Nugetで以下のライブラリを取得しておく必要があります。

Enterprise Library Data

そして、「config」ファイルを用意し、「configuration」セクションに以下のような設定を記述しましょう。

  <connectionStrings>
    <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=localhost;Initial Catalog=【データベース名】;User ID=【ユーザ名】;Password=【パスワード】" />
  </connectionStrings>

ライブラリ本体

以下に、DBへの接続ライブラリを示します。

[Dac.cs]

using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;

namespace DacLibrary
{
    /// <summary>
    /// DAC/DAOライブラリのインタフェースです
    /// </summary>
    public interface IDac : IDisposable
    {
        /// <summary>
        /// トランザクション進行確認フラグです
        /// </summary>
        bool IsTran { get; }

        /// <summary>
        /// トランザクションを開始します
        /// </summary>
        /// <param name="level"></param>
        void BeginTran(IsolationLevel level = IsolationLevel.ReadCommitted);

        /// <summary>
        /// トランザクションをコミットします
        /// </summary>
        void CommitTran();

        /// <summary>
        /// トランザクションをロールバックします
        /// </summary>
        void RollbackTran();

        /// <summary>
        /// トランザクションを伴うCUDのクエリを実行します
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        int ExecuteTranSql(string query, params SqlParameter[] parameters);

        /// <summary>
        /// OUTPUT句を含むCUDのクエリを実行します
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        T ExecuteOutputTranSql<T>(string query, params SqlParameter[] parameterValues);

        /// <summary>
        /// トランザクションを伴うReadのクエリを実行します
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        IEnumerable<T> ExecuteTranSql<T>(string query, params SqlParameter[] parameters) where T : new();

        /// <summary>
        /// トランザクションを伴わないクエリを実行します
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        IEnumerable<T> ExecuteNonTranSql<T>(string query, params SqlParameter[] parameters) where T : new();

        /// <summary>
        /// トランザクションを伴わない最初の列を返すクエリを実行します
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        object ExecuteScalarSql(string query, params SqlParameter[] parameterValues);
    }

    public class Dac : IDac
    {
        private bool _isTran = false;
        private Database _db;
        private IDbConnection _con;
        private IDbTransaction _tran;

        public Dac(string connectionString = "DefaultConnection")
        {
            if (this._db == null)
            {
                this._db = new DatabaseProviderFactory().Create(connectionString);
            }
            if (this._con == null)
            {
                this._con = this._db.CreateConnection();
                this._con.Open();
            }
        }

        /// <summary>
        /// トランザクション進行確認フラグです
        /// </summary>
        public bool IsTran
        {
            get
            {
                return this._isTran;
            }

            private set
            {
                this._isTran = value;
            }
        }

        /// <summary>
        /// トランザクションを開始します
        /// </summary>
        /// <param name="level"></param>
        public void BeginTran(IsolationLevel level = IsolationLevel.ReadCommitted)
        {
            if (this._con == null)
            {
                this._con = this._db.CreateConnection();
                this._con.Open();
            }
            if (this._con != null && this._tran == null)
            {
                this._tran = this._con.BeginTransaction(level);
                this.IsTran = true;
            }
        }

        /// <summary>
        /// トランザクションをコミットします
        /// </summary>
        public void CommitTran()
        {
            if (this._tran != null && this.IsTran)
            {
                this._tran.Commit();
                this._tran.Dispose();
                this._tran = null;
                this.IsTran = false;
            }
        }

        /// <summary>
        /// トランザクションをロールバックします
        /// </summary>
        public void RollbackTran()
        {
            if (this._tran != null)
            {
                this._tran.Rollback();
                this._tran.Dispose();
                this._tran = null;
                this.IsTran = false;
            }
        }

        /// <summary>
        /// DBへのコネクションをクローズします
        /// </summary>
        public void Dispose()
        {
            this.RollbackTran();
            if (this._tran != null)
            {
                this._tran.Dispose();
                this._tran = null;
            }
            if (this._con != null)
            {
                this._con.Close();
                this._con.Dispose();
            }
        }

        /// <summary>
        /// トランザクションを伴わないSQLを実行します
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="query"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IEnumerable<TClass> ExecuteNonTranSql<TClass>(string query, params SqlParameter[] parameterValues) where TClass : new()
        {
            if (Regex.IsMatch(query, @"^INSERT|UPDATE|DELETE"))
                throw new InvalidOperationException("Cannot execute query transaction needed");

            using (var cmd = this._db.GetSqlStringCommand(query))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                var accessor = this._db.CreateSqlStringAccessor<TClass>(query, NameParameterMapper.Default);
                return accessor.Execute(parameterValues);
            }
        }

        /// <summary>
        /// トランザクションを伴うCUDのSQLを実行します
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteTranSql(string query, params SqlParameter[] parameterValues)
        {
            if (this._tran == null || !this.IsTran)
                throw new InvalidOperationException("Not call 'begin tran' method!");

            using (var cmd = this._db.GetSqlStringCommand(query))
            {
                cmd.Transaction = (DbTransaction)this._tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                foreach (var item in parameterValues)
                {
                    cmd.Parameters.Add(item);
                }
                return this._db.ExecuteNonQuery(cmd, (DbTransaction)this._tran);
            }
        }

        /// <summary>
        /// トランザクションを伴うReadのSQLを実行します
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="query"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IEnumerable<TClass> ExecuteTranSql<TClass>(string query, params SqlParameter[] parameterValues)
            where TClass : new()
        {
            if (this._tran == null || !this.IsTran)
                throw new InvalidOperationException("Not call 'begin tran' method!");

            using (var cmd = this._db.GetSqlStringCommand(query))
            {
                cmd.Transaction = (DbTransaction)this._tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                foreach (var item in parameterValues)
                {
                    cmd.Parameters.Add(item);
                }
                var list = this.ReadRows<TClass>(cmd);
                return list;
            }
        }

        /// <summary>
        /// トランザクションを伴わない最初の列を返すクエリを実行します
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalarSql(string query, params SqlParameter[] parameterValues)
        {
            if (Regex.IsMatch(query, @"^INSERT|UPDATE|DELETE"))
                throw new InvalidOperationException("Cannot execute query transaction needed");

            using (var cmd = this._db.GetSqlStringCommand(query))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                foreach (var item in parameterValues)
                {
                    cmd.Parameters.Add(item);
                }
                return this._db.ExecuteScalar(cmd);
            }
        }

        private List<TClass> ReadRows<TClass>(DbCommand cmd) where TClass : new()
        {
            var list = new List<TClass>();
            using (var reader = this._db.ExecuteReader(cmd, (DbTransaction)this._tran))
            {
                while (reader.Read())
                {
                    var obj = new TClass();
                    var props = typeof(TClass).GetProperties();
                    foreach (var property in props)
                    {
                        if (reader[property.Name] == DBNull.Value)
                        {
                            if (property.PropertyType == typeof(int))
                                property.SetValue(obj, 0);
                            else if (property.PropertyType == typeof(string))
                                property.SetValue(obj, null);
                            else if (property.PropertyType == typeof(decimal))
                                property.SetValue(obj, 0m);
                            else if (property.PropertyType == typeof(bool))
                                property.SetValue(obj, false);
                            else if (property.PropertyType == typeof(long))
                                property.SetValue(obj, 0L);
                            else if (property.PropertyType == typeof(double))
                                property.SetValue(obj, 0d);
                            else if (property.PropertyType == typeof(float))
                                property.SetValue(obj, 0f);
                            else if (property.PropertyType == typeof(uint))
                                property.SetValue(obj, 0U);
                        }
                        else
                            property.SetValue(obj, reader[property.Name]);
                    }
                    list.Add(obj);
                }
            }
            return list;
        }

        private class NameParameterMapper : IParameterMapper
        {
            public static readonly IParameterMapper Default = new NameParameterMapper();

            public void AssignParameters(DbCommand command, object[] parameterValues)
            {
                if (parameterValues != null
                    && parameterValues.Count() > 0)
                {
                    var cmdParams = parameterValues
                        .Select(value =>
                       {
                           var sqlParam = (SqlParameter)value;
                           var param = command.CreateParameter();
                           param.ParameterName = sqlParam.ParameterName;
                           param.Value = sqlParam.Value;
                           return param;
                       }).ToArray();
                    command.Parameters.AddRange(cmdParams);
                }
            }
        }
    }
}

利用例

利用方法について簡単に説明します。
まず、以下のように、データをマッピングさせるクラスを用意します。

        private class Products
        {
            public int ProductID { get; set; }
            public string ProductName { get; set; }
            public int SupplierID { get; set; }
            public int CategoryID { get; set; }
            public string QuantityPerUnit { get; set; }
            public decimal UnitPrice { get; set; }
            public int UnitsInStock { get; set; }
            public int UnitsOnOrder { get; set; }
            public int ReorderLevel { get; set; }
            public bool Discontinued { get; set; }
        }

トランザクションが不要なSELECTクエリは、以下のように利用します。

        private IDac dac = new Dac();

        [TestMethod]
        [TestCategory("正常系")]
        public void ExecuteNonTranSql_NoCondition_Test()
        {
            var rows = dac.ExecuteNonTranSql<Products>(【SELECT文】);
            Assert.AreEqual(8, rows.Count());
        }

トランザクションでは以下のように利用します。

        private IDac dac = new Dac();

        [TestMethod]
        [TestCategory("正常系-Tran")]
        public void ExecuteTranSql_UPDATE_ROLLBACK_Test()
        {
            dac.BeginTran();

            var upRow = dac.ExecuteTranSql("UPDATE Products SET UnitsOnOrder = @unit WHERE ProductName = @name", 【パラメータ】);

            var tranRows = dac.ExecuteTranSql<Products>(【SELECT文】,【パラメータ】,【パラメータ】);
            var tranResult = tranRows.FirstOrDefault().UnitsOnOrder;

            dac.RollbackTran();

            var backRows = dac.ExecuteNonTranSql<Products>(【SELECT文】,【パラメータ】);
            var backResult = backRows.FirstOrDefault().UnitsOnOrder;

            Assert.IsTrue(
                upRow == 1
                && tranResult == 10
                && backResult == 5
                );
        }

筆休め

本章では、C#での開発が楽になるようなライブラリについて共有しました。開発を始める前に、アーキテクチャ周りを設計し予め用意しておくと、その後の開発が楽になります。DB接続やロギング、例外処理など、考えるべき項目については、開発着手前に確認してみましょう。

以上、「C#でのDB接続に関するDAO/DACライブラリを作ってみた」でした。

kikki's tech note

技術ブログです。UnityやSpine、MS、Javaなど技術色々について解説しています。

C#でのDB接続に関するDAO/DACライブラリを作ってみた

DB接続の方法

前準備

ライブラリ本体

利用例

筆休め