public async Task<ResponseData<yy_News_Type>> Get(RequestEntity value) { //查询的表名称 Type Table = typeof(yy_News_Type); var FormData = await Request.Content.ReadAsAsync<Dictionary<String, String>>(); #region where condition //筛选条件 var Where = String.Empty; var WhereBuild = new List<string>(); #region 新闻标题 if (!String.IsNullOrEmpty(value.Title)) { WhereBuild.Add("Title like '%" + value.Title + "%'"); } #endregion #region 新闻分类 if (value.TypeID > 0) { WhereBuild.Add("TypeIDs like '%," + value.TypeID + ",%'"); } #endregion #region 根据时间过滤 #region 大于等于 开始时间 && 小于等于 结束时间 if (!String.IsNullOrEmpty(value.StartTime) && !String.IsNullOrEmpty(value.EndTime)) { DateTime st, et; if (DateTime.TryParse(value.StartTime, out st) && DateTime.TryParse(value.EndTime, out et)) { WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00' AND CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'"); } } #endregion #region 大于等于开始时间 else if (!String.IsNullOrEmpty(value.StartTime)) { DateTime st; if (DateTime.TryParse(value.StartTime, out st)) { WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00'"); } } #endregion #region 小于等于结束时间 else if (!String.IsNullOrEmpty(value.EndTime)) { DateTime et; if (DateTime.TryParse(value.EndTime, out et)) { WhereBuild.Add(" CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'"); } } #endregion #endregion if (WhereBuild.Count > 0) { Where = " WHERE " + String.Join(" AND ", WhereBuild); } #endregion #region OrderBy //排序规则 String OrderBy = " ID DESC "; if (!String.IsNullOrEmpty(value.OrderBy)) { OrderBy = " " + value.OrderBy + " " + (value.IsDesc ? "DESC" : "ASC"); } #endregion #region 拼接sql语句 String colsStr = " * "; #region 查询数据 String QuertCMD = String.Format(@"SELECT TOP {0} * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {4}) AS RowNumber," + colsStr + @" FROM {2} WITH(NOLOCK) {3} ) A WHERE RowNumber > {0} * ({1}-1)", value.PageSize, value.PageIndex + 1, "[" + Table.Name + "]", Where, OrderBy); #endregion #region 查询总数 String DataCountCMD = @"SELECT COUNT(1) FROM [" + Table.Name + "] WITH(NOLOCK) " + Where; #endregion #endregion #region 执行查询并返回数据 var DataCount = DB.Database.SqlQuery<int>(DataCountCMD).FirstOrDefault(); return new ResponseData<yy_News_Type>(value.PageSize, value.PageIndex, DataCount, (DataCount % value.PageSize == 0 ? DataCount / value.PageSize : DataCount / value.PageSize + 1), DB.Database.SqlQuery<yy_News_Type>(QuertCMD).ToList()); #endregion }
public async Task<ResponseData<yy_Page_Type2>> Get(RequestEntity value) { //查询的表名称 Type Table = typeof(yy_Page_Type); var FormData = await Request.Content.ReadAsAsync<Dictionary<String, String>>(); #region where condition //筛选条件 var Where = String.Empty; var WhereBuild = new List<string>(); #region 页面标题 if (!String.IsNullOrEmpty(value.Title)) { WhereBuild.Add("Title like '%" + value.Title + "%'"); } #endregion #region 页面分类 if (value.TypeID > 0) { WhereBuild.Add("TypeIDs like '%," + value.TypeID + ",%'"); } #endregion #region 根据时间过滤 #region 大于等于 开始时间 && 小于等于 结束时间 if (!String.IsNullOrEmpty(value.StartTime) && !String.IsNullOrEmpty(value.EndTime)) { DateTime st, et; if (DateTime.TryParse(value.StartTime, out st) && DateTime.TryParse(value.EndTime, out et)) { WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00' AND CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'"); } } #endregion #region 大于等于开始时间 else if (!String.IsNullOrEmpty(value.StartTime)) { DateTime st; if (DateTime.TryParse(value.StartTime, out st)) { WhereBuild.Add(" CreateDate >= '" + st.ToString("yyyy-MM-dd") + " 00:00:00'"); } } #endregion #region 小于等于结束时间 else if (!String.IsNullOrEmpty(value.EndTime)) { DateTime et; if (DateTime.TryParse(value.EndTime, out et)) { WhereBuild.Add(" CreateDate <= '" + et.ToString("yyyy-MM-dd") + " 23:59:59'"); } } #endregion #endregion if (WhereBuild.Count > 0) { Where = " WHERE " + String.Join(" AND ", WhereBuild); } #endregion #region OrderBy //排序规则 String OrderBy = " ID DESC "; if (!String.IsNullOrEmpty(value.OrderBy)) { OrderBy = " " + value.OrderBy + " " + (value.IsDesc ? "DESC" : "ASC"); } #endregion #region 拼接sql语句 String colsStr = " * "; #region 查询数据 String QuertCMD = String.Format(@"SELECT TOP {0} * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {4}) AS RowNumber," + colsStr + @" FROM {2} WITH(NOLOCK) {3} ) A WHERE RowNumber > {0} * ({1}-1)", value.PageSize, value.PageIndex + 1, "[" + Table.Name + "]", Where, OrderBy); #endregion #region 查询总数 String DataCountCMD = @"SELECT COUNT(1) FROM [" + Table.Name + "] WITH(NOLOCK) " + Where; #endregion #endregion #region 执行查询并返回数据 var DataCount = DB.Database.SqlQuery<int>(DataCountCMD).FirstOrDefault(); return new ResponseData<yy_Page_Type2>(value.PageSize, value.PageIndex, DataCount, (DataCount % value.PageSize == 0 ? DataCount / value.PageSize : DataCount / value.PageSize + 1), DB.Database.SqlQuery<yy_Page_Type>(QuertCMD).ToList().Select(x => new yy_Page_Type2() { ID = x.ID, Author = x.Author, CreateDate = x.CreateDate, Email = x.Email, //配合客户端angular输出,这里必须做处理 Images = x.Images.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries) .Select(m => new yy_Page_Type2Image (){ src = m }).ToList(), IsMaster = x.IsMaster, Mobile = x.Mobile, Name = x.Name, Name_En = x.Name_En, QQ = x.QQ, Summary = x.Summary, SupportPlatform = x.SupportPlatform, UserID = x.UserID, Version = x.Version, Website = x.Website }).ToList()); #endregion }