DB接続共通クラス


``` SQLManager.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Sample
{
    public class SQLManager
    {
        private SqlConnection _con;
        private SqlTransaction _tran;
        private string _connectString;

        public SQLManager(string connectionString = "DefaultConnection")
        {
            // 接続文字列を生成
            _connectString = ConfigurationManager.ConnectionStrings[connectionString].ConnectionString;
            if (this._con == null)
            {
                this._con = new SqlConnection(_connectString);
                _con.Open();
            }
        }
        

        /// <summary>
        /// トランザクション進行確認フラグです
        /// </summary>
        public bool IsTran { get; private set; } = false;

        /// <summary>
        /// トランザクションを開始します
        /// </summary>
        /// <param name="level"></param>
        public void BeginTran(IsolationLevel level = IsolationLevel.ReadCommitted)
        {
            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()
        {
            Dispose(true);
        }
        protected virtual void Dispose(bool disposing)
        {
            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 (SqlCommand sqlCom = new SqlCommand())
            {

                //クエリー送信先、トランザクションの指定
                sqlCom.Connection = this._con;
                sqlCom.CommandText = query;

                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = query;
                foreach (var item in parameterValues)
                {
                    sqlCom.Parameters.Add(item);
                }
                var list = this.ReadRows<TClass>(sqlCom);
                return list;
            }
        }
        public IEnumerable<TClass> ExecuteNonTranSql<TClass>(string query) where TClass : new()
        {
            return this.ExecuteNonTranSql<TClass>(query, new SqlParameter());
        }

        /// <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 (SqlCommand sqlCom = new SqlCommand())
            {
                sqlCom.Connection = this._con;
                sqlCom.Transaction = (SqlTransaction)this._tran;
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = query;
                foreach (var item in parameterValues)
                {
                    sqlCom.Parameters.Add(item);
                }
                return sqlCom.ExecuteNonQuery();
            }
        }

        /// <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 (SqlCommand sqlCom = new SqlCommand())
            {
                sqlCom.Connection = this._con;
                sqlCom.Transaction = (SqlTransaction)this._tran;
                sqlCom.CommandType = CommandType.Text;
                sqlCom.CommandText = query;
                foreach (var item in parameterValues)
                {
                    sqlCom.Parameters.Add(item);
                }
                var list = this.ReadRows<TClass>(sqlCom);
                return list;
            }
        }

        private List<TClass> ReadRows<TClass>(SqlCommand cmd) where TClass : new()
        {
            var list = new List<TClass>();
            using (var reader = cmd.ExecuteReader())
            {
                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;
        }

    }
}
```