/// <summary> /// Checks the in parameter. /// </summary> /// <param name="para">The para.</param> /// <returns></returns> private string CheckInParam(MPagerInParam para) { if (para == null) { return("分页查询,输入参数为null。"); } if (string.IsNullOrEmpty(para.DataBaseName)) { return("分页查询,数据库名为空。"); } if (para.DataBaseType == Model.MDataBaseType.UNKNOW) { return("分页查询,数据库类型未知"); } if (string.IsNullOrEmpty(para.FieldNames)) { return("分页查询,无查询字段。"); } if (string.IsNullOrEmpty(para.TableName)) { return("分页查询,表名为空。"); } return(string.Empty); }
/// <summary> /// Pagers the query. /// </summary> /// <param name="para">The para.</param> /// <returns></returns> /// <exception cref="System.Exception"></exception> public MPagerReturn PagerQuery(MPagerInParam para) { string errorstr = this.CheckInParam(para); if (!string.IsNullOrEmpty(errorstr)) { throw new Exception(errorstr); } if (para.PageIndex < 1) { para.PageIndex = 1; } if (para.PageSize == 0) { // 默认每页20条 para.PageSize = 20; } var dao = DALFactory.GetPagerQueryDAO(para.DataBaseType); MPagerReturn ret = null; using (var conn = ConnectionFactory.GetDbConn(para.DataBaseName, para.DataBaseType)) { ret = dao.PagerQuery(conn, para); } return(ret); }
public MPagerReturn PagerQuery(IDbConnection conn, MPagerInParam para) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(1)"); sb.AppendFormat(" FROM {0}", para.TableName); if (!string.IsNullOrEmpty(para.Condition)) { sb.AppendFormat(" WHERE {0}", para.Condition); } string countsql = sb.ToString(); sb = new StringBuilder(); sb.AppendFormat("SELECT {0}", para.FieldNames); sb.AppendFormat(" FROM {0}", para.TableName); if (!string.IsNullOrEmpty(para.Condition)) { sb.AppendFormat(" WHERE {0}", para.Condition); } if (!string.IsNullOrEmpty(para.Sort)) { sb.AppendFormat(" ORDER BY {0}", para.Sort); } int start = (para.PageIndex - 1) * para.PageSize; int end = para.PageIndex * para.PageSize; sb.AppendFormat(" LIMIT {0},{1}", start, end); string sql = sb.ToString(); List <MySqlParameter> list = new List <MySqlParameter>(); foreach (var item in para.Parameters) { MySqlParameter model = new MySqlParameter() { DbType = item.DbType, ParameterName = item.ParameterName, Value = item.Value, }; list.Add(model); } object count = MySqlHelper.ExecuteScalar((MySqlConnection)conn, countsql, list.ToArray()); MPagerReturn ret = new MPagerReturn(); ret.RowCount = long.Parse(count.ToString()); DataSet ds = MySqlHelper.ExecuteDataset((MySqlConnection)conn, sql, list.ToArray()); if (ds != null && ds.Tables.Count > 0) { ret.PageData = ds.Tables[0]; } int total = (int)(ret.RowCount / para.PageSize); int yu = (int)(ret.RowCount % para.PageSize); total = yu == 0 ? total : total + 1; ret.PageCount = total; ret.PageIndex = para.PageIndex; return(ret); }