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