public string RolesRetrieve(RolesRetrieveReq req) { RolesRetrieveRes res = new RolesRetrieveRes(); if (Session["ID"] == null) { res.ReturnStatus = new ReturnStatus(ReturnCode.SESSION_TIMEOUT); } else { try { Log("Req=" + JsonConvert.SerializeObject(req)); res = new Roles("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 RolesRetrieveRes ReportData(RolesRetrieveReq req) { RolesRetrieveRes res = new RolesRetrieveRes() { ROLES = new List <ROLES>(), 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) SN,NAME,MODE,CDATE,CUSER,MDATE,MUSER, FROM ROLES{0} ORDER BY SN DESC;"; string where = ""; Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000000); //if (!string.IsNullOrEmpty(req.ROLES.SN)) //{ // where += " AND SN=@SN"; // Db.AddInParameter(cmd, "SN", DbType.String, req.ROLES.SN); //} if (!string.IsNullOrEmpty(req.ROLES.NAME)) { where += " AND NAME=@NAME"; Db.AddInParameter(cmd, "NAME", DbType.String, req.ROLES.NAME); } if (!string.IsNullOrEmpty(req.ROLES.MODE)) { where += " AND MODE=@MODE"; Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.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)) { while (reader.Read()) { var row = new ROLES { SN = reader["SN"] as Int32? ?? null, NAME = reader["NAME"] as string, 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.ROLES.Add(row); } } } res.Pagination.EndTime = DateTime.Now; return(res); }
public RolesRetrieveRes PaginationRetrieve(RolesRetrieveReq req) { RolesRetrieveRes res = new RolesRetrieveRes() { ROLES = new List <ROLES>(), 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 ROLES{0}) A; SELECT TOP(@TOP) SN,NAME,MODE,CDATE,CUSER,MDATE,MUSER FROM ROLES{0} ORDER BY SN DESC;"; string where = ""; Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000); if (req.ROLES.SN != null) { where += " AND SN=@SN"; Db.AddInParameter(cmd, "SN", DbType.Int16, req.ROLES.SN); } if (!string.IsNullOrEmpty(req.ROLES.NAME)) { where += " AND NAME LIKE @NAME"; Db.AddInParameter(cmd, "NAME", DbType.String, "%" + req.ROLES.NAME + "%"); } if (!string.IsNullOrEmpty(req.ROLES.MODE)) { where += " AND MODE=@MODE"; Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.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 ROLES { SN = reader["SN"] as Int32? ?? null, NAME = reader["NAME"] as string, 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.ROLES.Add(row); } else if (i > res.Pagination.MaxNumber) { reader.Close(); break; } } } } } res.Pagination.EndTime = DateTime.Now; return(res); }