你的位置:首页 > ASP.net教程

[ASP.net教程]MySqlHelper、CacheHelper


MySqlHelper代码:

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.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  }}

View Code

CacheHelper代码:

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  }}

View Code

    添加、修改、删除操作十分简捷方便,分别只需要一行代码。在并发量很小的情况下,可以通过GetMaxID方法生产新的ID,否则请采用其它方法。代码如下:

#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

View Code

    查询单个实体也非常方便,可以用ID查询,也可以写原生sql语句查询,十分灵活。代码如下:

/// <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));}

View Code

    查询获取集合使用原生sql语句,非常灵活方便,你可以写非常非常复杂的sql语句,各种join,各种子查询,都可以。代码如下:

/// <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>;}

View Code