public string PhraseRetrieve(PhraseRetrieveReq req) { PhraseRetrieveRes res = new PhraseRetrieveRes(); if (Session["ID"] == null) { res.ReturnStatus = new ReturnStatus(ReturnCode.SESSION_TIMEOUT); } else { try { Log("Req=" + JsonConvert.SerializeObject(req)); res = new KYL_CMS.Models.BusinessLogic.Phrase("SCC").PaginationRetrieve(req); res.ReturnStatus = new ReturnStatus(ReturnCode.SUCCESS); } catch (Exception ex) { Log("Err=" + ex.Message); Log(ex.StackTrace); res.ReturnStatus = new ReturnStatus(ReturnCode.SERIOUS_ERROR); } } var json = JsonConvert.SerializeObject(res); Log("Res=" + json); return(json); }
public PhraseRetrieveRes PaginationRetrieve(PhraseRetrieveReq req) { PhraseRetrieveRes res = new PhraseRetrieveRes() { PHRASE = new List <PHRASE>(), 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 PHRASE{0}) A; SELECT TOP(@TOP) SN,PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC,SORT,dbo.PHRASE_NAME('mode',MODE) AS MODE,CDATE,CUSER,MDATE,MUSER FROM PHRASE{0} ORDER BY SN DESC;"; string where = ""; Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000); if (!string.IsNullOrEmpty(req.PHRASE.PHRASE_GROUP)) { where += " AND PHRASE_GROUP=@PHRASE_GROUP"; Db.AddInParameter(cmd, "PHRASE_GROUP", DbType.String, req.PHRASE.PHRASE_GROUP); } if (!string.IsNullOrEmpty(req.PHRASE.PHRASE_KEY)) { where += " AND PHRASE_KEY LIKE @PHRASE_KEY"; Db.AddInParameter(cmd, "PHRASE_KEY", DbType.String, "%" + req.PHRASE.PHRASE_KEY + "%"); } if (!string.IsNullOrEmpty(req.PHRASE.PHRASE_VALUE)) { where += " AND PHRASE_VALUE LIKE @PHRASE_VALUE"; Db.AddInParameter(cmd, "PHRASE_VALUE", DbType.String, "%" + req.PHRASE.PHRASE_VALUE + "%"); } if (!string.IsNullOrEmpty(req.PHRASE.MODE)) { where += " AND MODE=@MODE"; Db.AddInParameter(cmd, "MODE", DbType.String, req.PHRASE.MODE); } 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 PHRASE { SN = reader["SN"] as Int32? ?? null, PHRASE_GROUP = reader["PHRASE_GROUP"] as string, PHRASE_KEY = reader["PHRASE_KEY"] as string, PHRASE_VALUE = reader["PHRASE_VALUE"] as string, PHRASE_DESC = reader["PHRASE_DESC"] as string, SORT = reader["SORT"] as Int32? ?? null, MODE = reader["MODE"] as string, CDATE = reader["CDATE"] as DateTime?, CUSER = reader["CUSER"] as string, MDATE = reader["MDATE"] as DateTime?, MUSER = reader["MUSER"] as string }; res.PHRASE.Add(row); } else if (i > res.Pagination.MaxNumber) { reader.Close(); break; } } } } } res.Pagination.EndTime = DateTime.Now; return(res); }