/*********************************************************************************** File Name    :    SQLConfig** Copyright (C) 2013 guzhen.net. All Rights Reserved.** Creator        :    SONGGUO\wangxiaoming** Create Date    :    2013/1/23 10:47:36** Update Date    :    ** Description    :    ** Version No    :    *********************************************************************************/using System;using System.Collections.Generic;//using System.Linq;using System.Text;using System.Reflection;using System.Configuration;using System.Runtime.Caching;using System.IO;namespace System.Data{    /// 
    /// 配置映射    /// 
    internal class SQLConfig    {        private ObjectCache _cache;        private string _configPath;        /// 
        /// Constractor        /// 
        /// 配置文件的路径        public SQLConfig(string configPath = null)        {            if (configPath == null)            {                configPath = AppDomain.CurrentDomain.BaseDirectory + @"\DbSetting\";            }            _cache = new MemoryCache(this.GetType().FullName);            _configPath = configPath;        }        private void GenerateKey(MethodBase method, out string key)        {            key = method.DeclaringType.Name + "." + method.Name;        }        private bool TryFindText(string key, out string text, out string configPath)        {            configPath = text = null;            foreach (string filePath in Directory.EnumerateFiles(_configPath, "*.config"))            {                var map = new ExeConfigurationFileMap();                map.ExeConfigFilename = filePath;                var config = ConfigurationManager.OpenMappedExeConfiguration(map, ConfigurationUserLevel.None);                var pair = config.AppSettings.Settings[key];                if (pair != null)                {                    text = pair.Value;                    configPath = filePath;                    return true;                }            }            return false;        }        /// 
        /// 获取调用的方法映射的SQL语句        /// 
        /// 调用的方法        /// 
     
      SQL语句
             /// 
             public string GetSQL(MethodBase method)        {            string key;            this.GenerateKey(method, out key);            string sql = (string)_cache[key], configPath;            if (sql == null)            {                if (!this.TryFindText(key, out sql, out configPath))                {                    throw new InvalidOperationException(string.Format("没有配置{0}该项", key));                }                var policy = new CacheItemPolicy()                {                    AbsoluteExpiration = ObjectCache.InfiniteAbsoluteExpiration,                    //相对过期时间                    SlidingExpiration = TimeSpan.FromMinutes(10D),                };                //监控配置文件变更                try                {                    policy.ChangeMonitors.Add(new HostFileChangeMonitor(new List
     
      () { configPath }));                }                catch (Exception ex)                {                    App.LogError(ex, string.Format("ChangeMonitor:{0}", ex.Message));                }                _cache.Add(key, sql, policy);            }            return sql;        }    }}
     

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.Common;using System.Reflection;using System.Diagnostics;using System.Runtime.Caching;using System.Runtime.CompilerServices;namespace System.Data{    /// 
    /// MultipleActiveResultSets=True;    /// 
    public class Database : IRequiresFactory    {        #region Static        internal const string ReturnParameterName = "@RETURN_VALUE";        internal const string DataTableName = "T";        private static SQLConfig Config;        static Database()        {            Config = new SQLConfig();        }        #endregion        #region Fields        private DbFactory _factory;        protected readonly ObjectCache Cache;        #endregion        #region Properties        public virtual DbFactory Factory        {            get { return _factory; }        }        public bool SupportStoredProc        {            get { return Cache != null; }        }        #endregion        #region Constructors        public Database(DbFactory factory, int? spCacheMemoryLimitMegabytes = null)        {            _factory = factory;            if (spCacheMemoryLimitMegabytes != null)            {                Cache = new MemoryCache(string.Format("Database[{0}]", factory.Name), new System.Collections.Specialized.NameValueCollection() { { "cacheMemoryLimitMegabytes", spCacheMemoryLimitMegabytes.Value.ToString() } });            }        }        #endregion        #region NativeMethods        public DbCommand PrepareCommand(string text, CommandType type)        {            DbCommand cmd;            var scope = DbScope.Current;            if (scope != null)            {                cmd = scope.PrepareCommand(this);                cmd.CommandText = text;            }            else            {                cmd = _factory.CreateCommand(text);            }            cmd.CommandType = type;            return cmd;        }        protected int ExecuteNonQuery(DbCommand cmd)        {            if (cmd.Connection == null)            {                cmd.Connection = _factory.CreateConnection();            }            bool isClosed = cmd.Connection.State == ConnectionState.Closed;            try            {                if (isClosed)                {                    cmd.Connection.Open();                }                return cmd.ExecuteNonQuery();            }            finally            {                if (isClosed)                {                    cmd.Connection.Close();                }            }        }        protected object ExecuteScalar(DbCommand cmd)        {            if (cmd.Connection == null)            {                cmd.Connection = _factory.CreateConnection();            }            bool isClosed = cmd.Connection.State == ConnectionState.Closed;            try            {                if (isClosed)                {                    cmd.Connection.Open();                }                return cmd.ExecuteScalar();            }            finally            {                if (isClosed)                {                    cmd.Connection.Close();                }            }        }        protected DbDataReader ExecuteReader(DbCommand cmd)        {            if (cmd.Connection == null)            {                cmd.Connection = _factory.CreateConnection();            }            bool isClosed = cmd.Connection.State == ConnectionState.Closed;            if (isClosed)            {                cmd.Connection.Open();            }            return cmd.ExecuteReader(isClosed ? CommandBehavior.CloseConnection : CommandBehavior.Default);        }        protected DataTable ExecuteDataTable(DbCommand cmd, int startRecord = -1, int maxRecords = 0)        {            var dt = new DataTable(DataTableName);            if (cmd.Connection == null)            {                cmd.Connection = _factory.CreateConnection();            }            using (DbDataAdapter da = _factory.CreateDataAdapter(cmd))            {                if (startRecord == -1)                {                    da.Fill(dt);                }                else                {                    da.Fill(startRecord, maxRecords, dt);                }            }            return dt;        }        protected DataSet ExecuteDataSet(DbCommand cmd)        {            var ds = new DataSet();            if (cmd.Connection == null)            {                cmd.Connection = _factory.CreateConnection();            }            using (DbDataAdapter da = _factory.CreateDataAdapter(cmd))            {                da.Fill(ds, DataTableName);            }            return ds;        }        #endregion        #region Methods        /// 
        /// 使用调用方法最为映射方法来获取DataReader        /// 
        ///         /// 按SQL语句中定义的Format顺序，对应传递参数值        /// 
     
      DataReader
             [MethodImpl(MethodImplOptions.NoInlining)]        public int MappedExecNonQuery(params object[] paramValues)        {            var stack = new StackTrace();            MethodBase method = stack.GetFrame(1).GetMethod();            string sql = Config.GetSQL(method);            return this.ExecuteNonQuery(sql, paramValues);        }        public int ExecuteNonQuery(string formatSql, params object[] paramValues)        {            string text = DbUtility.GetFormat(formatSql, paramValues);            var cmd = this.PrepareCommand(text, CommandType.Text);            return this.ExecuteNonQuery(cmd);        }        /// 
        /// 使用调用方法最为映射方法来获取DataReader        /// 
        ///         /// 按SQL语句中定义的Format顺序，对应传递参数值        /// 
     
      DataReader
             [MethodImpl(MethodImplOptions.NoInlining)]        public T ExecuteScalar
     
      (params object[] paramValues)        {            var stack = new StackTrace();            MethodBase method = stack.GetFrame(1).GetMethod();            string sql = Config.GetSQL(method);            return this.ExecuteScalar
      
       (sql, paramValues);        }        public T ExecuteScalar
       
        (string formatSql, params object[] paramValues)        {            string text = DbUtility.GetFormat(formatSql, paramValues);            var cmd = this.PrepareCommand(text, CommandType.Text);            return (T)Convert.ChangeType(this.ExecuteScalar(cmd), typeof(T));        }        /// 
        
        /// 使用调用方法最为映射方法来获取DataReader        /// 
        /// 
                /// 
        按SQL语句中定义的Format顺序，对应传递参数值        /// 
        
         DataReader
                [MethodImpl(MethodImplOptions.NoInlining)]        public DbDataReader MappedExecReader(params object[] paramValues)        {            var stack = new StackTrace();            MethodBase method = stack.GetFrame(1).GetMethod();            string sql = Config.GetSQL(method);            return this.ExecuteReader(sql, paramValues);        }        public DbDataReader ExecuteReader(string formatSql, params object[] paramValues)        {            string text = DbUtility.GetFormat(formatSql, paramValues);            var cmd = this.PrepareCommand(text, CommandType.Text);            return this.ExecuteReader(cmd);        }        public DataTable ExecuteDataTable(string formatSql, params object[] paramValues)        {            return this.ExecuteDataTable(-1, 0, formatSql, paramValues);        }        public DataTable ExecuteDataTable(int startRecord, int maxRecords, string formatSql, params object[] paramValues)        {            string text = DbUtility.GetFormat(formatSql, paramValues);            var cmd = this.PrepareCommand(text, CommandType.Text);            return this.ExecuteDataTable(cmd, startRecord, maxRecords);        }        public int UpdateDataTable(DataTable dt, params string[] joinSelectSql)        {            int affected = 0;            var cmd = this.PrepareCommand(string.Empty, CommandType.Text);            using (var da = this.Factory.CreateDataAdapter(cmd))            using (var cb = this.Factory.CreateCommandBuilder(da))            {                da.AcceptChangesDuringUpdate = false;                affected = da.Update(dt);                if (!joinSelectSql.IsNullOrEmpty())                {                    for (int i = 0; i < joinSelectSql.Length; i++)                    {                        cb.RefreshSchema();                        da.SelectCommand.CommandText = joinSelectSql[i];                        affected += da.Update(dt);                    }                }                dt.AcceptChanges();            }            return affected;        }        #endregion        #region StoredProc        #region Command        /// 
        
        /// cmd.CommandType = CommandType.StoredProcedure;        /// Always discoveredParameters[0].ParameterName == Database.ReturnParameterName        /// 
        /// 
                /// 
                protected DbParameter[] GetDeriveParameters(DbCommand cmd)        {            string spName = cmd.CommandText;            DbParameter[] discoveredParameters = (DbParameter[])Cache[spName];            if (discoveredParameters == null)            {                string qualifiedName = cmd.GetType().AssemblyQualifiedName;                Type builderType = Type.GetType(qualifiedName.Insert(qualifiedName.IndexOf(','), "Builder"));                MethodInfo method = builderType.GetMethod("DeriveParameters", BindingFlags.Public | BindingFlags.Static | BindingFlags.InvokeMethod);                if (method == null)                {                    throw new ArgumentException("The specified provider factory doesn't support stored procedures.");                }                if (cmd.Connection == null)                {                    cmd.Connection = _factory.CreateConnection();                }                bool isClosed = cmd.Connection.State == ConnectionState.Closed;                try                {                    if (isClosed)                    {                        cmd.Connection.Open();                    }                    method.Invoke(null, new object[] { cmd });                }                finally                {                    if (isClosed)                    {                        cmd.Connection.Close();                    }                }                Cache[spName] = discoveredParameters = new DbParameter[cmd.Parameters.Count];                cmd.Parameters.CopyTo(discoveredParameters, 0);                cmd.Parameters.Clear();            }            return discoveredParameters;        }        public void DeriveParameters(DbCommand cmd)        {            DbParameter[] originalParameters = GetDeriveParameters(cmd);            for (int i = 0; i < originalParameters.Length; i++)            {                cmd.Parameters.Add(((ICloneable)originalParameters[i]).Clone());            }        }        public void DeriveAssignParameters(DbCommand cmd, object[] values)        {            DbParameter[] discoveredParameters = GetDeriveParameters(cmd);            if (cmd.Parameters.Count > 0 || discoveredParameters.Length - 1 != values.Length)            {                throw new ArgumentException("The number of parameters doesn't match number of values for stored procedures.");            }            cmd.Parameters.Add(((ICloneable)discoveredParameters[0]).Clone());            for (int i = 0; i < values.Length; )            {                object value = values[i] ?? DBNull.Value;                DbParameter discoveredParameter = discoveredParameters[++i];                object cloned = ((ICloneable)discoveredParameter).Clone();                ((DbParameter)cloned).Value = value;                cmd.Parameters.Add(cloned);            }        }        public void SetParameterValue(DbCommand cmd, int index, object value)        {            int startIndex = cmd.Parameters.Count > 0 && cmd.Parameters[0].ParameterName == ReturnParameterName ? 1 : 0;            cmd.Parameters[startIndex + index].Value = value;        }        public void SetParameterValue(DbCommand cmd, string name, object value)        {            cmd.Parameters[_factory.ParameterNamePrefix + name].Value = value;        }        public object GetParameterValue(DbCommand cmd, int index)        {            int startIndex = cmd.Parameters.Count > 0 && cmd.Parameters[0].ParameterName == ReturnParameterName ? 1 : 0;            return cmd.Parameters[startIndex + index].Value;        }        public object GetParameterValue(DbCommand cmd, string name)        {            return cmd.Parameters[_factory.ParameterNamePrefix + name].Value;        }        public object GetParameterReturnValue(DbCommand cmd)        {            if (cmd.Parameters.Count > 0 && cmd.Parameters[0].ParameterName == ReturnParameterName)            {                return cmd.Parameters[0].Value;            }            return null;        }        #endregion        #region Execute        protected virtual void FillOutputValue(DbCommand cmd, object[] values)        {            for (int i = 1; i < cmd.Parameters.Count; i++)            {                var param = cmd.Parameters[i];                if (param.Direction == ParameterDirection.Output || param.Direction == ParameterDirection.InputOutput)                {                    values[i - 1] = param.Value;                }            }        }        public int ExecuteStoredProcNonQuery(string spName, params object[] values)        {            var cmd = this.PrepareCommand(spName, CommandType.StoredProcedure);            DeriveAssignParameters(cmd, values);            FillOutputValue(cmd, values);            return ExecuteNonQuery(cmd);        }        public object ExecuteStoredProcScalar(string spName, params object[] values)        {            var cmd = this.PrepareCommand(spName, CommandType.StoredProcedure);            DeriveAssignParameters(cmd, values);            FillOutputValue(cmd, values);            return ExecuteScalar(cmd);        }        public DbDataReader ExecuteStoredProcReader(string spName, params object[] values)        {            var cmd = this.PrepareCommand(spName, CommandType.StoredProcedure);            DeriveAssignParameters(cmd, values);            FillOutputValue(cmd, values);            return ExecuteReader(cmd);        }        public DataTable ExecuteStoredProcDataTable(string spName, params object[] values)        {            var cmd = this.PrepareCommand(spName, CommandType.StoredProcedure);            DeriveAssignParameters(cmd, values);            FillOutputValue(cmd, values);            return ExecuteDataTable(cmd);        }        public DataSet ExecuteStoredProcDataSet(string spName, params object[] values)        {            var cmd = this.PrepareCommand(spName, CommandType.StoredProcedure);            DeriveAssignParameters(cmd, values);            FillOutputValue(cmd, values);            return ExecuteDataSet(cmd);        }        #endregion        #endregion    }}