MyDBUtils

using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Reflection;
using System.Transactions;

namespace System.Data
{
    /// <summary>
    /// 基于ADO.NET的，多种数据库访问类，提供CRUD基本方法，更好的支持参数化语句与存储过程的执行。
    /// 其中包括：ToEntity()、ToList() ... 方法
    /// </summary>
    public static partial class DBUtils
    {
        static readonly DbProviderFactory factory = null;

        static DBUtils()
        {
            string prvdName = ConfigurationManager.AppSettings["providerName"];
            factory = DbProviderFactories.GetFactory(prvdName); // 由配置反射创建对应工厂实例
        }

        public static bool ExeUpdate(string sql, bool isProc, params IDataParameter[] paras)
        {
            using (IDbConnection conn = GetConnection())
            {
                return conn.PreparedCommand(sql, paras, isProc).ExecuteNonQuery() > 0;
            }
        }

        /// <summary>
        /// 执行简单事务
        /// </summary>
        public static bool ExeTransaction(string cmdText, bool isProc, params IDbDataParameter[] cmdParams)
        {
            using (IDbConnection conn = GetConnection())
            {
                IDbTransaction tran = null;
                try
                {
                    IDbCommand cmd = conn.PreparedCommand(cmdText, cmdParams, isProc);
                    tran = cmd.Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted); // 设置隔离级别
                    int i = cmd.ExecuteNonQuery();
                    tran.Commit();
                    return i > 0;
                }
                catch
                {
                    tran.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 执行分布式事务
        /// </summary>
        /// <param name="action">无参无返的方法或委托</param>
        public static void ExeTransaction(Action action)
        {
            using (TransactionScope tran = TransactionScope())
            {
                action();
                tran.Complete();
            }
        }

        public static object ExeScalar(string sql, bool isProc, params IDataParameter[] paras)
        {
            using (IDbConnection conn = GetConnection())
            {
                return conn.PreparedCommand(sql, paras, isProc).ExecuteScalar();
            }
        }

        public static IDataReader GetReader(string sql, bool isProc, params IDataParameter[] paras)
        {
            return GetConnection().PreparedCommand(sql, paras, isProc).ExecuteReader(CommandBehavior.CloseConnection);
        }

        public static DataTable GetTable(string sql, bool isProc, params IDataParameter[] paras)
        {
            DataTable dt = new DataTable();
            IDataReader aReader = GetReader(sql, isProc, paras);
            dt.Load(aReader);
            aReader.Close();
            return dt;
        }

        public static DataSet GetDataSet(string sql, bool isProc, params IDataParameter[] paras)
        {
            using (IDbConnection conn = GetConnection())
            {
                IDbDataAdapter da = factory.CreateDataAdapter();
                da.SelectCommand = conn.PreparedCommand(sql, paras, isProc);
                DataSet ds = new DataSet();
                da.Fill(ds); // 注意连接泄漏问题，若已打开，执行Fill()、Update()操作后必须显示关闭。
                return ds;
            }
        }

        public static T ToEntity<T>(string sql, bool isProc, params IDataParameter[] paras) where T : class, new()
        {
            IDataReader aReader = GetReader(sql, isProc, paras);
            var model = new T();
            if (aReader.Read())
            {
                Array.ForEach(typeof(T).GetProperties(), p =>
                {
                    object value = null;
                    try { value = aReader[p.Name]; }
                    catch (IndexOutOfRangeException) { value = null; }  // 实体类映射有误！
                    finally { if (value != DBNull.Value) p.SetValue(model, value, null); }
                });
            }
            aReader.Close();
            return model;
        }

        /// <summary>
        /// DataTable 转换为List＜T＞集合
        /// </summary>
        /// <typeparam name="T">对应的实体类</typeparam>
        public static IList<T> ToList<T>(string sql, bool isProc, params IDataParameter[] paras) where T : class, new()
        {
            DataTable tab = GetTable(sql, isProc, paras);

            // 取出和 Table 列名一致的属性，放入prList集合，避免异常。
            var prList = new List<PropertyInfo>(tab.Columns.Count);
            Array.ForEach(typeof(T).GetProperties(), p => { if (tab.Columns.Contains(p.Name)) prList.Add(p); });

            // 创建一个动态控制容量的集合
            var list = new List<T>(tab.Rows.Count);

            foreach (DataRow row in tab.Rows) // 给要返回的集合设值
            {
                var m = new T();
                prList.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(m, row[p.Name], null); });
                list.Add(m);
            }

            return list;
        }

        /// <summary>
        /// 返回存储过程分页的数据
        /// </summary>
        /// <param name="selectList">字段列表： name,age...</param>
        /// <param name="tableName">* 数据源名、表名、视图名称：view_User</param>
        /// <param name="searchExpression">查询表达式：name='zhangsan'</param>
        /// <param name="orderExpression">* 排序表达式：id</param>
        /// <param name="pageIndex">页码：1</param>
        /// <param name="pageSize">页记录数：10</param>
        /// <param name="totalCount">总记录数</param>
        public static DataTable GetByPager(string selectList, string tableName, string searchExpression, string orderExpression, int pageIndex, int pageSize, out int totalCount)
        {
            string proc = "proc_GeneralPaging"; // 需提前创建好
            var para0 = factory.CreateParameter();
            para0.ParameterName = "selectList";
            para0.Value = selectList;
            var para1 = factory.CreateParameter();
            para1.ParameterName = "tableSource";
            para1.Value = tableName;
            var para2 = factory.CreateParameter();
            para2.ParameterName = "searchCondition";
            para2.Value = searchExpression;
            var para3 = factory.CreateParameter();
            para3.ParameterName = "orderExpression";
            para3.Value = orderExpression;
            var para4 = factory.CreateParameter();
            para4.ParameterName = "pageIndex";
            para4.Value = pageIndex;
            var para5 = factory.CreateParameter();
            para5.ParameterName = "pageSize";
            para5.Value = pageSize;
            var para6 = factory.CreateParameter();
            para6.ParameterName = "totalCount";
            para6.Value = 0;
            para6.Direction = ParameterDirection.Output;
            DataTable tab = GetTable(proc, true, para0, para1, para2, para3, para4, para5, para6);
            totalCount = (int)para6.Value;
            return tab;
        }

        /// <summary>
        /// * 返回一个准备就绪的命令对象
        /// </summary>
        /// <param name="cmdText">执行的一组T-SQL语句、存储过程</param>
        /// <param name="cmdParams">填充占位符的，长度可变参数数组</param>
        /// <param name="isProc">是否执行存储过程的标识</param>
        public static IDbCommand PreparedCommand(this IDbConnection conn, string cmdText, IDataParameter[] cmdParams, bool isProc)
        {
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = cmdText;
            if (cmdParams != null && cmdParams.Length > 0)
                foreach (var item in cmdParams)
                    cmd.Parameters.Add(item); // 给参数化语句设值
            cmd.CommandType = isProc ? CommandType.StoredProcedure : CommandType.Text;
            return cmd;
        }

        /// <summary>
        /// 返回一个由数据工厂创建的连接对象
        /// </summary>
        public static IDbConnection GetConnection()
        {
            IDbConnection conn = factory.CreateConnection();
            conn.ConnectionString = ConfigurationManager.AppSettings["connStr"];
            return conn;
        }
    }
}