MySqlHelper、CacheHelper

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Xml.Linq;
using System.Data.Objects.DataClasses;
using Models;
using System.Text.RegularExpressions;

namespace DBHelper
{
    /// <summary>
    /// MySql操作类
    /// 2015年6月20日
    /// 写程序之前，首先引用MySql.Data.MySqlClient
    /// </summary>
    public class MySqlHelper
    {
        #region 静态变量
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        #endregion

        #region MySqlConnection 获取数据库连接
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        private static MySqlConnection GetConn()
        {
            MySqlConnection connection = null;

            string key = "Simpo2016_MySqlConnection";

            if (HttpContext.Current.Items[key] == null)
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();
                HttpContext.Current.Items[key] = connection;
            }
            else
            {
                connection = (MySqlConnection)HttpContext.Current.Items[key];
            }

            return connection;
        }
        #endregion

        #region MySqlTransaction 获取事务对象
        /// <summary>
        /// 获取事务对象
        /// </summary>
        private static MySqlTransaction GetTran()
        {
            MySqlTransaction tran = null;

            string key = "Simpo2016_MySqlTransaction";

            if (HttpContext.Current.Items[key] == null)
            {
                tran = GetConn().BeginTransaction();
                HttpContext.Current.Items[key] = tran;
            }
            else
            {
                tran = (MySqlTransaction)HttpContext.Current.Items[key];
            }

            return tran;
        }
        #endregion

        #region 开起事务标志
        /// <summary>
        /// 事务标志
        /// </summary>
        private static string tranFlagKey = "Simpo2016_MySqlTransaction_Flag";
        /// <summary>
        /// 添加事务标志
        /// </summary>
        public static void AddTranFlag()
        {
            HttpContext.Current.Items[tranFlagKey] = true;
        }
        /// <summary>
        /// 移除事务标志
        /// </summary>
        public static void RemoveTranFlag()
        {
            HttpContext.Current.Items[tranFlagKey] = false;
        }
        /// <summary>
        /// 事务标志
        /// </summary>
        public static bool TranFlag
        {
            get
            {
                bool tranFlag = false;

                if (HttpContext.Current.Items[tranFlagKey] != null)
                {
                    tranFlag = (bool)HttpContext.Current.Items[tranFlagKey];
                }

                return tranFlag;
            }
        }
        #endregion

        #region 用于查询的数据库连接
        /// <summary>
        /// 用于查询的数据库连接
        /// </summary>
        private MySqlConnection m_Conn;
        #endregion

        #region 构造函数
        public MySqlHelper()
        {
            m_Conn = new MySqlConnection(connectionString);
        }
        #endregion

        #region 基础方法
        #region  执行简单SQL语句
        #region Exists
        public bool Exists(string sqlString)
        {
            using (MySqlCommand cmd = new MySqlCommand(sqlString, m_Conn))
            {
                try
                {
                    m_Conn.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    m_Conn.Close();
                }
            }
        }
        #endregion

        #region 执行SQL语句，返回影响的记录数
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string sqlString)
        {
            MySqlConnection connection = GetConn();
            using (MySqlCommand cmd = new MySqlCommand(sqlString, connection))
            {
                try
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    if (TranFlag) cmd.Transaction = GetTran();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    if (!TranFlag) connection.Close();
                }
            }
        }
        #endregion

        #region 执行一条计算查询结果语句，返回查询结果
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）
        /// </summary>
        /// <param name="sqlString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string sqlString)
        {
            using (MySqlCommand cmd = new MySqlCommand(sqlString, m_Conn))
            {
                try
                {
                    m_Conn.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    m_Conn.Close();
                }
            }
        }
        #endregion

        #region 执行查询语句，返回SQLiteDataReader
        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        public MySqlDataReader ExecuteReader(string sqlString)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(sqlString, connection);
            try
            {
                connection.Open();
                MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 执行查询语句，返回DataSet
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string sqlString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(sqlString, connection);
                    command.Fill(ds, "ds");
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
        #endregion
        #endregion

        #region 执行带参数的SQL语句
        #region 执行SQL语句，返回影响的记录数
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
        {
            MySqlConnection connection = GetConn();
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    if (TranFlag) cmd.Transaction = GetTran();
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (!TranFlag) connection.Close();
                }
            }
        }
        #endregion

        #region 执行查询语句，返回SQLiteDataReader
        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        public MySqlDataReader ExecuteReader(string sqlString, params MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            try
            {
                PrepareCommand(cmd, m_Conn, null, sqlString, cmdParms);
                MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region 执行查询语句，返回DataSet
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string sqlString, params MySqlParameter[] cmdParms)
        {
            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, m_Conn, null, sqlString, cmdParms);
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    m_Conn.Close();
                }
                return ds;
            }
        }
        #endregion

        #region PrepareCommand
        private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null) cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion
        #endregion
        #endregion

        #region 增删改查
        #region 获取最大编号
        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <typeparam name="T">实体Model</typeparam>
        /// <param name="key">主键</param>
        public int GetMaxID<T>(string key)
        {
            Type type = typeof(T);

            string sql = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
            using (MySqlCommand cmd = new MySqlCommand(sql, m_Conn))
            {
                try
                {
                    m_Conn.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return 1;
                    }
                    else
                    {
                        return int.Parse(obj.ToString()) + 1;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    m_Conn.Close();
                }
            }
        }
        #endregion

        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        public void Insert(object obj)
        {
            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            CacheHelper.Remove(type);//删除缓存
            strSql.Append(string.Format("insert into {0}(", type.Name));

            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            List<string> propertyNameList = new List<string>();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                propertyNameList.Add(propertyInfo.Name);
            }

            strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));
            strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => "@" + a).ToArray())));
            MySqlParameter[] parameters = new MySqlParameter[propertyInfoList.Length];
            for (int i = 0; i < propertyInfoList.Length; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                object val = propertyInfo.GetValue(obj, null);
                MySqlParameter param = new MySqlParameter("@" + propertyInfo.Name, val == null ? DBNull.Value : val);
                parameters[i] = param;
            }

            ExecuteSql(strSql.ToString(), parameters);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        public void Update(object obj)
        {
            object oldObj = Find(obj, false);
            if (oldObj == null) throw new Exception("无法获取到旧数据");

            StringBuilder strSql = new StringBuilder();
            Type type = obj.GetType();
            CacheHelper.Remove(type);//删除缓存
            strSql.Append(string.Format("update {0} ", type.Name));

            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            List<string> propertyNameList = new List<string>();
            int savedCount = 0;
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                object oldVal = propertyInfo.GetValue(oldObj, null);
                object val = propertyInfo.GetValue(obj, null);
                if (!object.Equals(oldVal, val))
                {
                    propertyNameList.Add(propertyInfo.Name);
                    savedCount++;
                }
            }

            strSql.Append(string.Format(" set "));
            MySqlParameter[] parameters = new MySqlParameter[savedCount];
            StringBuilder sbPros = new StringBuilder();
            int k = 0;
            for (int i = 0; i < propertyInfoList.Length; i++)
            {
                PropertyInfo propertyInfo = propertyInfoList[i];
                object oldVal = propertyInfo.GetValue(oldObj, null);
                object val = propertyInfo.GetValue(obj, null);
                if (!object.Equals(oldVal, val))
                {
                    sbPros.Append(string.Format(" {0}=@{0},", propertyInfo.Name));
                    MySqlParameter param = new MySqlParameter("@" + propertyInfo.Name, val == null ? DBNull.Value : val);
                    parameters[k++] = param;
                }
            }
            if (sbPros.Length > 0)
            {
                strSql.Append(sbPros.ToString(0, sbPros.Length - 1));
            }
            strSql.Append(string.Format(" where {0}='{1}'", GetIdName(obj.GetType()), GetIdVal(obj).ToString()));

            if (savedCount > 0)
            {
                ExecuteSql(strSql.ToString(), parameters);
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据Id删除
        /// </summary>
        public void Delete<T>(int id)
        {
            Type type = typeof(T);
            CacheHelper.Remove(type);//删除缓存
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {2}='{1}'", type.Name, id, GetIdName(type)));

            ExecuteSql(sbSql.ToString());
        }
        /// <summary>
        /// 根据Id集合删除
        /// </summary>
        public void BatchDelete<T>(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return;

            Type type = typeof(T);
            CacheHelper.Remove(type);//删除缓存
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {2} in ({1})", type.Name, ids, GetIdName(type)));

            ExecuteSql(sbSql.ToString());
        }
        /// <summary>
        /// 根据条件删除
        /// </summary>
        public void Delete<T>(string conditions)
        {
            if (string.IsNullOrWhiteSpace(conditions)) return;

            Type type = typeof(T);
            CacheHelper.Remove(type);//删除缓存
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(string.Format("delete from {0} where {1}", type.Name, conditions));

            ExecuteSql(sbSql.ToString());
        }
        #endregion

        #region 获取实体
        #region 根据实体获取实体
        /// <summary>
        /// 根据实体获取实体
        /// </summary>
        private object Find(object obj, bool readCache = true)
        {
            Type type = obj.GetType();

            object result = Activator.CreateInstance(type);
            bool hasValue = false;
            IDataReader rd = null;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, GetIdVal(obj), GetIdName(obj.GetType()));
            //获取缓存
            if (readCache && CacheHelper.Exists(type, sql))
            {
                return CacheHelper.Get(type, sql);
            }

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                CacheHelper.Add(type, sql, result);//添加缓存
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        private object FindById(Type type, int id)
        {
            object result = Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));
            //获取缓存
            if (CacheHelper.Exists(type, sql))
            {
                return CacheHelper.Get(type, sql);
            }

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                CacheHelper.Add(type, sql, result);//添加缓存
                return result;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据Id获取实体
        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        public T FindById<T>(string id) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            string sql = string.Format("select * from {0} where {2}='{1}'", type.Name, id, GetIdName(type));
            //获取缓存
            if (CacheHelper.Exists(type, sql))
            {
                return (T)CacheHelper.Get(type, sql);
            }

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                CacheHelper.Add(type, sql, result);//添加缓存
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region 根据sql获取实体
        /// <summary>
        /// 根据sql获取实体
        /// </summary>
        public T FindBySql<T>(string sql) where T : new()
        {
            Type type = typeof(T);
            T result = (T)Activator.CreateInstance(type);
            IDataReader rd = null;
            bool hasValue = false;

            //获取缓存
            if (CacheHelper.Exists(type, sql))
            {
                return (T)CacheHelper.Get(type, sql);
            }

            try
            {
                rd = ExecuteReader(sql);

                PropertyInfo[] propertyInfoList = GetEntityProperties(type);

                int fcnt = rd.FieldCount;
                List<string> fileds = new List<string>();
                for (int i = 0; i < fcnt; i++)
                {
                    fileds.Add(rd.GetName(i).ToUpper());
                }

                while (rd.Read())
                {
                    hasValue = true;
                    IDataRecord record = rd;

                    foreach (PropertyInfo pro in propertyInfoList)
                    {
                        if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                        {
                            continue;
                        }

                        pro.SetValue(result, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            if (hasValue)
            {
                CacheHelper.Add(type, sql, result);//添加缓存
                return result;
            }
            else
            {
                return default(T);
            }
        }
        #endregion
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<T> FindListBySql<T>(string sql) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            //获取缓存
            Type type = GetBaseType(typeof(T));
            if (CacheHelper.Exists(type, sql))
            {
                return (List<T>)CacheHelper.Get(type, sql);
            }

            try
            {
                rd = ExecuteReader(sql);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            CacheHelper.Add(type, sql, list);//添加缓存
            return list;
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表
        /// </summary>
        public List<T> FindListBySql<T>(string sql, params MySqlParameter[] cmdParms) where T : new()
        {
            List<T> list = new List<T>();
            object obj;
            IDataReader rd = null;

            //获取缓存
            Type type = GetBaseType(typeof(T));
            if (CacheHelper.Exists(type, sql))
            {
                return (List<T>)CacheHelper.Get(type, sql);
            }

            try
            {
                rd = ExecuteReader(sql, cmdParms);

                if (typeof(T) == typeof(int))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else if (typeof(T) == typeof(string))
                {
                    while (rd.Read())
                    {
                        list.Add((T)rd[0]);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfoList = (typeof(T)).GetProperties();

                    int fcnt = rd.FieldCount;
                    List<string> fileds = new List<string>();
                    for (int i = 0; i < fcnt; i++)
                    {
                        fileds.Add(rd.GetName(i).ToUpper());
                    }

                    while (rd.Read())
                    {
                        IDataRecord record = rd;
                        obj = new T();


                        foreach (PropertyInfo pro in propertyInfoList)
                        {
                            if (!fileds.Contains(pro.Name.ToUpper()) || record[pro.Name] == DBNull.Value)
                            {
                                continue;
                            }

                            pro.SetValue(obj, record[pro.Name] == DBNull.Value ? null : getReaderValue(record[pro.Name], pro.PropertyType), null);
                        }
                        list.Add((T)obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rd != null && !rd.IsClosed)
                {
                    rd.Close();
                    rd.Dispose();
                }
            }

            CacheHelper.Add(type, sql, list);//添加缓存
            return list;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity，尽量少的字段)
        /// </summary>
        public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            //获取缓存
            string cacheKey = string.Format("sql:{0},orderby:{1},pageSize:{2},currentPage:{3}", sql, orderby, pageSize, currentPage);
            Type type = GetBaseType(typeof(T));
            if (CacheHelper.Exists(type, cacheKey))
            {
                return (PagerModel)CacheHelper.Get(type, cacheKey);
            }

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string commandText = string.Format("select count(*) from ({0}) T", sql);
                IDbCommand cmd = new MySqlCommand(commandText, connection);
                pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());

                int startRow = pageSize * (currentPage - 1);

                StringBuilder sb = new StringBuilder();
                sb.Append("select * from (");
                sb.Append(sql);
                if (!string.IsNullOrWhiteSpace(orderby))
                {
                    sb.Append(" ");
                    sb.Append(orderby);
                }
                sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);

                List<T> list = FindListBySql<T>(sb.ToString());
                pagerModel.result = list;
            }

            CacheHelper.Add(type, cacheKey, pagerModel);
            return pagerModel;
        }
        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity，尽量少的字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PagerModel FindPageBySql<T>(string sql, string orderby, int pageSize, int currentPage, params MySqlParameter[] cmdParms) where T : new()
        {
            PagerModel pagerModel = new PagerModel(currentPage, pageSize);

            //获取缓存
            string cacheKey = string.Format("sql:{0},orderby:{1},pageSize:{2},currentPage:{3}", sql, orderby, pageSize, currentPage);
            Type type = GetBaseType(typeof(T));
            if (CacheHelper.Exists(type, cacheKey))
            {
                return (PagerModel)CacheHelper.Get(type, cacheKey);
            }

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string commandText = string.Format("select count(*) from ({0}) T", sql);
                MySqlCommand cmd = new MySqlCommand(commandText, connection);
                PrepareCommand(cmd, connection, null, commandText, cmdParms);
                pagerModel.totalRows = int.Parse(cmd.ExecuteScalar().ToString());
                cmd.Parameters.Clear();

                int startRow = pageSize * (currentPage - 1);

                StringBuilder sb = new StringBuilder();
                sb.Append("select * from (");
                sb.Append(sql);
                if (!string.IsNullOrWhiteSpace(orderby))
                {
                    sb.Append(" ");
                    sb.Append(orderby);
                }
                sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);

                List<T> list = FindListBySql<T>(sb.ToString(), cmdParms);
                pagerModel.result = list;
            }

            CacheHelper.Add(type, cacheKey, pagerModel);
            return pagerModel;
        }


        #endregion

        #region 分页获取列表
        /// <summary>
        /// 分页(任意entity，尽量少的字段)
        /// </summary>
        public DataSet FindPageBySql(string sql, string orderby, int pageSize, int currentPage, out int totalCount, params MySqlParameter[] cmdParms)
        {
            DataSet ds = null;

            //获取缓存
            string cacheKey = string.Format("sql:{0},orderby:{1},pageSize:{2},currentPage:{3}", sql, orderby, pageSize, currentPage);
            Regex reg = new Regex(@"from[\s]+([^\(\),\s]+)", RegexOptions.IgnoreCase);
            Match match = reg.Match(sql);
            string tableName = match.Groups[1].Value;
            Dictionary<string, object> dic;
            if (CacheHelper.Exists(tableName, cacheKey))
            {
                dic = (Dictionary<string, object>)CacheHelper.Get(tableName, cacheKey);
                totalCount = (int)dic["totalCount"];
                return (DataSet)dic["DataSet"];
            }

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string commandText = string.Format("select count(*) from ({0}) T", sql);
                IDbCommand cmd = new MySqlCommand(commandText, connection);
                totalCount = int.Parse(cmd.ExecuteScalar().ToString());

                int startRow = pageSize * (currentPage - 1);

                StringBuilder sb = new StringBuilder();
                sb.Append("select * from (");
                sb.Append(sql);
                if (!string.IsNullOrWhiteSpace(orderby))
                {
                    sb.Append(" ");
                    sb.Append(orderby);
                }
                sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);

                ds = Query(sql, cmdParms);
            }

            dic = new Dictionary<string, object>();
            dic.Add("totalCount", totalCount);
            dic.Add("DataSet", ds);
            CacheHelper.Add(tableName, cacheKey, dic);
            return ds;
        }
        #endregion

        #region getReaderValue 转换数据
        /// <summary>
        /// 转换数据
        /// </summary>
        private Object getReaderValue(Object rdValue, Type ptype)
        {
            if (ptype == typeof(double))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(decimal))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(int))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(long))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(DateTime))
                return Convert.ToDateTime(rdValue);

            if (ptype == typeof(Nullable<double>))
                return Convert.ToDouble(rdValue);

            if (ptype == typeof(Nullable<decimal>))
                return Convert.ToDecimal(rdValue);

            if (ptype == typeof(Nullable<int>))
                return Convert.ToInt32(rdValue);

            if (ptype == typeof(Nullable<long>))
                return Convert.ToInt64(rdValue);

            if (ptype == typeof(Nullable<DateTime>))
                return Convert.ToDateTime(rdValue);

            return rdValue;
        }
        #endregion

        #region 获取主键名称
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public string GetIdName(Type type)
        {
            PropertyInfo[] propertyInfoList = GetEntityProperties(type);
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(IsIdAttribute), false).Length > 0)
                {
                    return propertyInfo.Name;
                }
            }
            return "Id";
        }
        #endregion

        #region 获取主键值
        /// <summary>
        /// 获取主键名称
        /// </summary>
        public object GetIdVal(object val)
        {
            string idName = GetIdName(val.GetType());
            if (!string.IsNullOrWhiteSpace(idName))
            {
                return val.GetType().GetProperty(idName).GetValue(val, null);
            }
            return 0;
        }
        #endregion

        #region 获取实体类属性
        /// <summary>
        /// 获取实体类属性
        /// </summary>
        private PropertyInfo[] GetEntityProperties(Type type)
        {
            List<PropertyInfo> result = new List<PropertyInfo>();
            PropertyInfo[] propertyInfoList = type.GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfoList)
            {
                if (propertyInfo.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), false).Length == 0
                    && propertyInfo.GetCustomAttributes(typeof(BrowsableAttribute), false).Length == 0)
                {
                    result.Add(propertyInfo);
                }
            }
            return result.ToArray();
        }
        #endregion

        #region 获取基类
        /// <summary>
        /// 获取基类
        /// </summary>
        public Type GetBaseType(Type type)
        {
            while (type.BaseType != null && type.BaseType.Name != typeof(Object).Name)
            {
                type = type.BaseType;
            }
            return type;
        }
        #endregion
        #endregion

        #region 事务
        #region 开始事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public static void BeginTransaction()
        {
            GetTran();
            AddTranFlag();
        }
        #endregion

        #region 提交事务
        /// <summary>
        /// 提交事务
        /// </summary>
        public static void CommitTransaction()
        {
            try
            {
                if (GetConn().State == ConnectionState.Open)
                {
                    GetTran().Commit();
                    RemoveTranFlag();
                }
            }
            catch (Exception ex)
            {
                GetTran().Rollback();
                RemoveTranFlag();
            }
            finally
            {
                if (GetConn().State == ConnectionState.Open) GetConn().Close();
            }
        }
        #endregion

        #region 回滚事务(出错时调用该方法回滚)
        /// <summary>
        /// 回滚事务(出错时调用该方法回滚)
        /// </summary>
        public static void RollbackTransaction()
        {
            GetTran().Rollback();
            RemoveTranFlag();
            GetConn().Close();
        }
        #endregion
        #endregion

    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Objects.DataClasses;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.Caching;

namespace DBHelper
{
    /// <summary>
    /// 缓存类
    /// </summary>
    public static class CacheHelper
    {
        #region 变量
        /// <summary>
        /// 缓存整个页面的键
        /// </summary>
        public static string pageCacheKey = "pageCacheKey";
        #endregion

        #region 是否存在
        /// <summary>
        /// 是否存在
        /// </summary>
        public static bool Exists<T>(string key)
        {
            return Exists(typeof(T).Name, key);
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        public static bool Exists(Type type, string key)
        {
            return Exists(type.Name, key);
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        public static bool Exists(string tableName, string key)
        {
            return false; //禁用缓存
            if (HttpRuntime.Cache[tableName] != null)
            {
                Dictionary<string, object> dic = (Dictionary<string, object>)HttpRuntime.Cache[tableName];
                if (dic.Keys.Contains<string>(key))
                {
                    return true;
                }
            }
            return false;
        }
        #endregion

        #region 添加缓存
        /// <summary>
        /// 添加缓存
        /// </summary>
        public static void Add<T>(string key, object value)
        {
            Add(typeof(T).Name, key, value);
        }
        /// <summary>
        /// 添加缓存
        /// </summary>
        public static void Add(Type type, string key, object value)
        {
            Add(type.Name, key, value);
        }
        /// <summary>
        /// 添加缓存
        /// </summary>
        public static void Add(string tableName, string key, object value)
        {
            return; //禁用缓存
            if (HttpRuntime.Cache[tableName] == null)
            {
                Dictionary<string, object> dic = new Dictionary<string, object>();
                dic.Add(key, value);
                HttpRuntime.Cache.Insert(tableName, dic);
            }
            else
            {
                Dictionary<string, object> dic = (Dictionary<string, object>)HttpRuntime.Cache[tableName];
                if (dic.Keys.Contains<string>(key))
                {
                    dic[key] = value;
                }
                else
                {
                    dic.Add(key, value);
                }
                HttpRuntime.Cache[tableName] = dic;
            }
        }
        #endregion

        #region 获取缓存
        /// <summary>
        /// 获取缓存
        /// </summary>
        public static object Get<T>(string key)
        {
            return Get(typeof(T).Name, key);
        }
        /// <summary>
        /// 获取缓存
        /// </summary>
        public static object Get(Type type, string key)
        {
            return Get(type.Name, key);
        }
        /// <summary>
        /// 获取缓存
        /// </summary>
        public static object Get(string tableName, string key)
        {
            return null; //禁用缓存
            if (HttpRuntime.Cache[tableName] != null)
            {
                Dictionary<string, object> dic = (Dictionary<string, object>)HttpRuntime.Cache[tableName];
                if (dic.Keys.Contains<string>(key))
                {
                    return dic[key];
                }
            }
            return null;
        }
        #endregion

        #region 删除缓存
        /// <summary>
        /// 删除所有缓存
        /// </summary>
        public static void Clear()
        {
            return; //禁用缓存
            string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            int start = connectionString.IndexOf("database=") + 9;
            int end = connectionString.IndexOf("user id=");
            string owner = connectionString.Substring(start, end - start).Replace(";", "").ToUpper();
            MySqlHelper dbHelper = new MySqlHelper();
            DataTable dt = dbHelper.Query(string.Format(@"
                SELECT TABLE_NAME as TABLE_NAME,TABLE_COMMENT as COMMENTS 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = '{0}'", owner)).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                HttpRuntime.Cache.Remove(dr["TABLE_NAME"].ToString());
                HttpRuntime.Cache.Remove(dr["TABLE_NAME"].ToString() + "_ext");
            }
            HttpRuntime.Cache.Remove(pageCacheKey);
        }
        /// <summary>
        /// 删除缓存
        /// </summary>
        public static void Remove<T>()
        {
            Remove(typeof(T).Name);
        }
        /// <summary>
        /// 删除缓存
        /// </summary>
        public static void Remove(Type type)
        {
            Remove(type.Name);
        }
        /// <summary>
        /// 删除缓存
        /// </summary>
        public static void Remove(string tableName)
        {
            return; //禁用缓存
            HttpRuntime.Cache.Remove(tableName);
            HttpRuntime.Cache.Remove(tableName + "_ext");
            HttpRuntime.Cache.Remove(pageCacheKey);
        }
        #endregion

    }
}

#region 添加
/// <summary>
/// 添加
/// </summary>
public void Insert(cms_content model)
{
    model.id = dbHelper.GetMaxID<cms_content>("id");
    dbHelper.Insert(model);
}
#endregion

#region 修改
/// <summary>
/// 修改
/// </summary>
public void Update(cms_content model)
{
    dbHelper.Update(model);
}
#endregion

#region 删除
/// <summary>
/// 删除
/// </summary>
public void Del(string ids)
{
    dbHelper.BatchDelete<cms_content>(ids);
}
#endregion

/// <summary>
/// 获取
/// </summary>
public cms_content Get(int id)
{
    return dbHelper.FindById<cms_content>(id.ToString());
}
/// <summary>
/// 根据channelId获取一条内容详情
/// </summary>
public cms_content GetByChannelId(int channelId)
{
    return dbHelper.FindBySql<cms_content>(string.Format("select * from cms_content where channelId={0} and audit=1", channelId));
}

/// <summary>
/// 获取列表
/// </summary>
public List<cms_content_ext> GetList(ref PagerModel pager, int channelId, string title, int audit)
{
    StringBuilder sql = new StringBuilder(string.Format(@"
        select content.*, channel.title as channelName, user.showName
        from cms_content content
        left join cms_channel channel on channel.id=content.channelId
        left join sys_user user on user.id=content.publishUserId
        where 1=1 "));

    if (channelId != -1)
    {
        sql.AppendFormat(" and content.channelId = {0}", channelId);
    }
    if (!string.IsNullOrWhiteSpace(title))
    {
        sql.AppendFormat(" and content.title like '%{0}%'", title);
    }
    if (audit != -1)
    {
        sql.AppendFormat(" and content.audit = {0}", audit);
    }

    string orderby = string.Format("order by content.publishTime desc,id desc");
    PagerModel pagerModel = dbHelper.FindPageBySql<cms_content_ext>(sql.ToString(), orderby, pager.rows, pager.page);
    pager.totalRows = pagerModel.totalRows;
    pager.result = pagerModel.result;
    return pagerModel.result as List<cms_content_ext>;
}