public FeelingRetrieveRes ReportData(FeelingRetrieveReq req) { FeelingRetrieveRes res = new FeelingRetrieveRes() { FEELING = new List <FEELING>(), Pagination = new Pagination { PageCount = 0, RowCount = 0, PageNumber = 0, MinNumber = 0, MaxNumber = 0, StartTime = DateTime.Now } }; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @"SELECT TOP(@TOP) FROM FEELING{0} ORDER BY SN DESC;"; string where = ""; Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000000); if (where.Length > 0) { where = " WHERE" + where.Substring(4); } sql = String.Format(sql, where); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { var row = new FEELING { }; res.FEELING.Add(row); } } } res.Pagination.EndTime = DateTime.Now; return(res); }
public FeelingRetrieveRes PaginationRetrieve(FeelingRetrieveReq req) { FeelingRetrieveRes res = new FeelingRetrieveRes() { FEELING = new List <FEELING>(), Pagination = new Pagination { PageCount = 0, RowCount = 0, PageNumber = 0, MinNumber = 0, MaxNumber = 0, StartTime = DateTime.Now } }; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @"SELECT COUNT(1) FROM (SELECT TOP(@TOP) NULL AS N FROM FEELING{0}) A; SELECT TOP(@TOP) FROM FEELING{0} ORDER BY SN DESC;"; string where = ""; Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000); if (where.Length > 0) { where = " WHERE" + where.Substring(4); } sql = String.Format(sql, where); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { reader.Read(); int.TryParse(reader[0].ToString(), out res.Pagination.RowCount); if (res.Pagination.RowCount > 0) { reader.NextResult(); res.Pagination.PageCount = Convert.ToInt32(Math.Ceiling(1.0 * res.Pagination.RowCount / req.PageSize)); res.Pagination.PageNumber = req.PageNumber < 1 ? 1 : req.PageNumber; res.Pagination.PageNumber = req.PageNumber > res.Pagination.PageCount ? res.Pagination.PageCount : res.Pagination.PageNumber; res.Pagination.MinNumber = (res.Pagination.PageNumber - 1) * req.PageSize + 1; res.Pagination.MaxNumber = res.Pagination.PageNumber * req.PageSize; res.Pagination.MaxNumber = res.Pagination.MaxNumber > res.Pagination.RowCount ? res.Pagination.RowCount : res.Pagination.MaxNumber; int i = 0; while (reader.Read()) { i++; if (i >= res.Pagination.MinNumber && i <= res.Pagination.MaxNumber) { var row = new FEELING { }; res.FEELING.Add(row); } else if (i > res.Pagination.MaxNumber) { reader.Close(); break; } } } } } res.Pagination.EndTime = DateTime.Now; return(res); }