MSSqlEF.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace Koubou.SQLServer.EF
{
    /// <summary>
    /// DBアクセスクラス
    /// </summary>
    public class SqlEF : DbContext
    {
        private static readonly string ConnectString = ConfigurationManager.ConnectionStrings[""].ConnectionString;

        private static readonly SqlConnection Connection = new SqlConnection(ConnectString);

        private DbContextTransaction _currentTransaction = null;

        #region Default constructor

        public SqlEF()
            : base(Connection, false)
        {}

        #endregion

        #region Private methods

        /// <summary>
        /// SQLCommandオブジェクト作成
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        private SqlCommand BuildCommand(string commandText, params SqlParameter[] args)
        {
            var command = new SqlCommand(commandText, Connection);

            command.Parameters.Clear();

            if (args != null && 0 < args.Length)
            {
                command.Parameters.AddRange(args);
            }

            return command;
        }

        /// <summary>
        /// コマンド実行
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        private int ExecuteCommand(SqlCommand command)
        {
            try
            {
                Connection.Open();
                return command.ExecuteNonQuery();
            }
            finally
            {
                command.Parameters.Clear();
                Connection.Close();
            }
        }

        /// <summary>
        /// DBNull変換
        /// </summary>
        /// <param name="value">変換対象の値</param>
        /// <returns>value = Null:DBNull else:変換なし</returns>
        private object ToDBNull(object value)
        {
            return string.IsNullOrEmpty(Convert.ToString(value)) ? DBNull.Value : value;
        }

        /// <summary>
        /// Model→SQLParameter変換
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private SqlParameter[] ToSqlParameters<TModel>(TModel model)
           where TModel : class
        {
            if (model == null) { throw new NullReferenceException(); }

            var list = new List<SqlParameter>();

            foreach (var p in typeof(TModel).GetProperties())
            {
                var param = new SqlParameter()
                {
                    Direction = ParameterDirection.Input,
                    ParameterName = string.Format("@{0}", p.Name),
                    Value = this.ToDBNull(p.GetValue(model, null))
                };

                list.Add(param);
            }

            return list.ToArray();
        }

        /// <summary>
        /// テーブル型パラメータ変換
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="models"></param>
        /// <returns></returns>
        private SqlParameter ToTableParameter<TModel>(List<TModel> models)
            where TModel : class
        {
            var properties = typeof(TModel).GetProperties();
            var table = new DataTable();

            //列生成
            foreach (var p in properties)
            {
                table.Columns.Add(p.Name, p.PropertyType);
            }

            //行データ追加
            foreach (var m in models)
            {
                var row = table.NewRow();

                for (int i = 0; i < table.Columns.Count; i++)
                {
                    row[i] = this.ToDBNull(properties[i].GetValue(m, null));
                }

                table.Rows.Add(row);
            }

            var param = new SqlParameter()
            {
                ParameterName = string.Format("@{0}", typeof(TModel).Name.ToLower()),
                Direction = ParameterDirection.Input,
                SqlDbType = SqlDbType.Structured,
                Value = table
            };

            return param;
        }

        #endregion

        #region Execute command

        /// <summary>
        /// コマンド実行
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(string sql)
        {
            return base.Database.ExecuteSqlCommand(sql);
        }

        /// <summary>
        /// コマンド実行
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="sql"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Execute<TModel>(string sql, TModel model)
            where TModel : class
        {
            var args = this.ToSqlParameters(model);

            return base.Database.ExecuteSqlCommand(sql, args);
        }

        /// <summary>
        /// コマンド実行
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="sql"></param>
        /// <param name="models"></param>
        /// <returns></returns>
        public int Execute<TModel>(string sql, List<TModel> models)
            where TModel : class
        {
            int affectedRow = 0;

            models.ForEach(x => affectedRow += this.Execute(sql, x));

            return affectedRow;
        }

        /// <summary>
        /// ストアド・プロシージャ実行
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="commandText"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ExecuteStoredProcedure<TModel>(string commandText, TModel model)
            where TModel : class
        {
            try
            {
                var args = this.ToSqlParameters(model);

                using (var comm = this.BuildCommand(commandText, args))
                {
                    comm.CommandType = CommandType.StoredProcedure;

                    return this.ExecuteCommand(comm);
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// ストアド・プロシージャ実行
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="commandText"></param>
        /// <param name="models"></param>
        /// <returns></returns>
        public int ExecuteStoredProcedure<TModel>(string commandText, List<TModel> models)
            where TModel : class
        {
            try
            {
                var table = this.ToTableParameter(models);

                using (var comm = this.BuildCommand(commandText, table))
                {
                    comm.CommandType = CommandType.StoredProcedure;

                    return this.ExecuteCommand(comm);
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// ストアド・プロシージャ実行
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <typeparam name="TList"></typeparam>
        /// <param name="commandText"></param>
        /// <param name="model"></param>
        /// <param name="models"></param>
        /// <returns></returns>
        public int ExecuteStoredProcedure<TModel, TList>(string commandText, TModel model, List<TList> models)
            where TModel : class
            where TList : class
        {
            try
            {
                var args = this.ToSqlParameters(model);
                var table = this.ToTableParameter(models);
                args.ToList().Add(table);

                using (var comm = this.BuildCommand(commandText, table))
                {
                    comm.CommandType = CommandType.StoredProcedure;

                    return this.ExecuteCommand(comm);
                }
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Read data

        /// <summary>
        /// １件のデータを取得します
        /// </summary>
        /// <typeparam name="TResult">戻り値の型情報(クラス)</typeparam>
        /// <param name="sql">SQL文</param>
        /// <returns>取得データ</returns>
        public TResult Find<TResult>(string sql)
            where TResult : class
        {
            return this.FindAll<TResult>(sql).First();
        }

        /// <summary>
        /// 条件に合致する１件のデータを取得します
        /// </summary>
        /// <typeparam name="TResult">戻り値の型情報(クラス)</typeparam>
        /// <typeparam name="TCondition">検索条件の型情報(クラス)</typeparam>
        /// <param name="sql">SQL文</param>
        /// <param name="condition">検索条件モデル</param>
        /// <returns>取得データ</returns>
        public TResult Find<TResult, TCondition>(string sql, TCondition condition)
            where TResult : class
            where TCondition : class
        {
            return this.FindBy<TResult, TCondition>(sql, condition).First();
        }

        /// <summary>
        /// 全てのデータを取得します
        /// </summary>
        /// <typeparam name="TResult">戻り値の型情報(クラス)</typeparam>
        /// <param name="sql">SQL文</param>
        /// <returns>取得データ</returns>
        public List<TResult> FindAll<TResult>(string sql)
            where TResult : class
        {
            return base.Database.SqlQuery<TResult>(sql).ToList();
        }

        /// <summary>
        /// 条件に合致するデータを取得します
        /// </summary>
        /// <typeparam name="TResult">戻り値の型情報(クラス)</typeparam>
        /// <typeparam name="TCondition">検索条件の型情報(クラス)</typeparam>
        /// <param name="sql">SQL文</param>
        /// <param name="condition">検索条件モデル</param>
        /// <returns>取得データ</returns>
        public List<TResult> FindBy<TResult, TCondition>(string sql, TCondition condition)
            where TResult : class
            where TCondition : class
        {
            var args = this.ToSqlParameters(condition);
            return this.Database.SqlQuery<TResult>(sql, condition).ToList();
        }

        #endregion

        #region Transaction control

        /// <summary>
        /// トランザクション開始
        /// </summary>
        public void BeginTransaction()
        {
            this._currentTransaction = base.Database.BeginTransaction();
        }

        /// <summary>
        /// コミット
        /// </summary>
        public void Commit()
        {
            if (this._currentTransaction != null)
            {
                this._currentTransaction.Commit();
                this._currentTransaction.Dispose();
                this._currentTransaction = null;
            }
        }

        /// <summary>
        /// ロールバック
        /// </summary>
        public void Rollback()
        {
            if (this._currentTransaction != null)
            {
                this._currentTransaction.Rollback();
                this._currentTransaction.Dispose();
                this._currentTransaction = null;
            }
        }

        #endregion
    }
}