public static IPageOfList <OrderAdminInfo> List(SearchSetting settings) { FastPaging fp = new FastPaging(); fp.PageIndex = settings.PageIndex; fp.PageSize = settings.PageSize; fp.TableName = "OrderAdmins"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.OverOrderBy = " CreateDateTime DESC"; IList <OrderAdminInfo> list = new List <OrderAdminInfo>(); OrderAdminInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = Get(dr); if (model != null) { list.Add(model); } } } int count = Count(); return(new PageOfList <OrderAdminInfo>(list, settings.PageIndex, settings.PageSize, count)); }
//public static void Applying(List<ForumApplyUserInfo> modelList) { // string strSQL = "INSERT INTO ForumApplyUsers(UserId,UserName,ForumGroupId,ContactPerson,[Status],CreateDateTime) VALUES(@UserId,@UserName,@ForumGroupId,@ContactPerson,0,GETDATE());"; // SqlParameter[] parms = null; // foreach(var model in modelList){ // parms = ParameterHelper.GetClassSqlParameters(model); // Goodspeed.Library.Data.SQLPlus.ExecuteNonQuery(CommandType.Text,strSQL,parms); // } //} public static IPageOfList <ForumApplyUserInfo> List(int pageIndex, int pageSize) { FastPaging fp = new FastPaging(); fp.PageIndex = pageIndex; fp.PageSize = pageSize; fp.Ascending = false; fp.TableName = "ForumApplyUsers"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*,g.Name AS ForumGroupName"; fp.OverOrderBy = "p.CreateDateTime DESC"; fp.JoinSQL = "LEFT JOIN dbo.ForumGroups AS g ON p.ForumGroupId = g.Id"; IList <ForumApplyUserInfo> list = new List <ForumApplyUserInfo>(); ForumApplyUserInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = Get(dr); if (model != null) { list.Add(model); } } } int count = CountForList(); return(new PageOfList <ForumApplyUserInfo>(list, pageIndex, pageSize, count)); }
/// <summary> /// 广告位列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList <AdPositionInfo> List(AdSearchSetting setting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " CreateDateTime DESC"; fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.QueryFields = "*"; fp.TableName = "AdPosition"; fp.PrimaryKey = "Id"; fp.WithOptions = " WITH(NOLOCK)"; IList <AdPositionInfo> list = new List <AdPositionInfo>(); AdPositionInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByRow(dr); if (model != null) { list.Add(model); } } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); return(new PageOfList <AdPositionInfo>(list, setting.PageIndex, setting.PageSize, count)); }
/// <summary> /// 列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList <JobInfo> List(JobSearchSetting setting) { FastPaging fp = new FastPaging(); fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.Ascending = false; fp.TableName = "Job"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.OverOrderBy = " IsHot DESC,Sort DESC ,CreateDateTime DESC"; fp.WithOptions = " WITH(NOLOCK)"; fp.Condition = " IsDeleted = 0"; if (setting.IsOnlyShowPublished) { fp.Condition += " AND IsPublished = 1 "; } IList <JobInfo> list = new List <JobInfo>(); DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(GetByRow(dr)); } } int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));; return(new PageOfList <JobInfo>(list, setting.PageIndex, setting.PageSize, count)); }
public static IPageOfList <AttachmentInfo> List(SearchSetting settings) { FastPaging fp = new FastPaging(); fp.PageIndex = settings.PageIndex; fp.PageSize = settings.PageSize; fp.TableName = "Attachments"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; StringBuilder sbCondition = new StringBuilder(); sbCondition.AppendFormat(" SiteId = @SiteId "); if (!settings.ShowDeleted) { sbCondition.Append(" AND IsDeleted = 0 "); } if (settings.CategoryId > 0) { sbCondition.Append(@" AND EXISTS( SELECT * FROM dbo.Categories AS AC WITH(NOLOCK) WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0) /*获取此分类下所有的子分类*/ AND SiteId = @SiteId AND IsDeleted = 0 AND p.CategoryId = AC.ID )"); } fp.Condition = sbCondition.ToString(); fp.OverOrderBy = "Sort ASC ,PublishDate DESC"; SqlParameter[] parms = { new SqlParameter("@SiteId", SqlDbType.Int), new SqlParameter("@CID", SqlDbType.Int) }; parms[0].Value = settings.SiteId; parms[1].Value = settings.CategoryId; IList <AttachmentInfo> list = new List <AttachmentInfo>(); AttachmentInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = Get(dr); if (model != null) { list.Add(model); } } } int count = Count(settings); return(new PageOfList <AttachmentInfo>(list, settings.PageIndex, settings.PageSize, count)); }
public static IPageOfList <CompanyInfo> CompanyList(MemberSearchSetting setting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " C.ApplyDateTime DESC"; fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.QueryFields = "*"; fp.TableName = "Members"; fp.PrimaryKey = "Id"; fp.WithOptions = " WITH(NOLOCK)"; fp.TableReName = "M"; fp.JoinSQL = "INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId"; StringBuilder sbSQL = new StringBuilder(); sbSQL.Append(" 1 = 1"); if (setting.CompanyStatus != CompanyStatus.None) { sbSQL.AppendFormat(" AND C.CompanyStatus = {0}", (int)setting.CompanyStatus); } if (!string.IsNullOrEmpty(setting.CompanyName)) { //这块应该检查一下是否有危险字符,防止SQL注入 //目前没有加上 xingbaifang 2012-12-05 sbSQL.AppendFormat(" AND C.CompanyName LIKE '%{0}%'", setting.CompanyName); } fp.Condition = sbSQL.ToString(); //throw new Exception(fp.Build2005()); IList <CompanyInfo> list = new List <CompanyInfo>(); CompanyInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetCompanyByDataRow(dr); if (model != null) { list.Add(model); } } } sbSQL = new StringBuilder(); sbSQL.Append("SELECT COUNT(*) FROM Members AS M WITH(NOLOCK) INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId"); sbSQL.Append(" WHERE 1 = 1 "); if (fp.Condition.Length > 0) { sbSQL.AppendFormat(" AND {0}", fp.Condition); } //throw new Exception(sbSQL.ToString()); int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, sbSQL.ToString())); return(new PageOfList <CompanyInfo>(list, setting.PageIndex, setting.PageSize, count)); }
public static IPageOfList <OrderInfo> List(OrderSearchSetting settings) { FastPaging fp = new FastPaging(); fp.PageIndex = settings.PageIndex; fp.PageSize = settings.PageSize; fp.TableName = "Orders"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; StringBuilder sbCondition = new StringBuilder(" 1 = 1"); if (!string.IsNullOrEmpty(settings.CompanyName)) { sbCondition.Append(" AND OrderCompanyName LIKE '%'+@CompanyName+'%'"); } if (!string.IsNullOrEmpty(settings.OrderNumber)) { sbCondition.Append(" AND OrderNumber = @OrderNumber"); } if (!settings.ShowDeleted) { sbCondition.Append(" AND IsDeleted = 0 "); } fp.Condition = sbCondition.ToString(); fp.OverOrderBy = " CreateDateTime DESC"; SqlParameter[] parms = { new SqlParameter("@CompanyName", SqlDbType.NVarChar), new SqlParameter("@OrderNumber", SqlDbType.NVarChar) }; parms[0].Value = settings.CompanyName; parms[1].Value = settings.OrderNumber; IList <OrderInfo> list = new List <OrderInfo>(); OrderInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = Get(dr); if (model != null) { list.Add(model); } } } int count = Count(settings); return(new PageOfList <OrderInfo>(list, settings.PageIndex, settings.PageSize, count)); }
/// <summary> /// 产品列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList <ProductInfo> List(ProductSearchSetting setting) { FastPaging fp = new FastPaging(); fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.Ascending = false; fp.TableName = "Products"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.OverOrderBy = " Sort ASC,CreateDateTime ASC"; StringBuilder sbCondition = new StringBuilder(); sbCondition.Append(" 1 = 1 "); if (setting.CategoryId > 0) { sbCondition.Append(@" AND EXISTS( SELECT * FROM Categories AS AC WITH(NOLOCK) WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0) AND p.CategoryId = AC.ID )"); } if (!setting.ShowDeleted) { sbCondition.Append(" AND IsDeleted = 0 /*获取未删除的*/"); } SqlParameter[] parms = { new SqlParameter("CID", SqlDbType.Int), }; parms[0].Value = setting.CategoryId; fp.Condition = sbCondition.ToString(); IList <ProductInfo> list = new List <ProductInfo>(); ProductInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByRow(dr); if (model != null) { list.Add(model); } } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(), parms)); return(new PageOfList <ProductInfo>(list, setting.PageIndex, setting.PageSize, count)); }
/// <summary> /// 列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList <AttachmentInfo> List(AttachmentSearchSetting setting) { FastPaging fp = new FastPaging(); fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.Ascending = false; fp.TableName = "Attachment"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.OverOrderBy = " Sort ASC,CreateDateTime DESC"; fp.WithOptions = " WITH(NOLOCK)"; StringBuilder sbCondition = new StringBuilder(); sbCondition.Append(" IsDeleted = 0 "); if (setting.CategoryId > 0) { sbCondition.Append(@" AND EXISTS( SELECT * FROM Categories AS AC WITH(NOLOCK) WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0) AND p.CategoryId = AC.ID )"); } fp.Condition = sbCondition.ToString(); SqlParameter[] param = { new SqlParameter("CID", SqlDbType.Int), }; param[0].Value = setting.CategoryId; IList <AttachmentInfo> list = new List <AttachmentInfo>(); DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), param); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(Get(dr)); } } int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(), param));; return(new PageOfList <AttachmentInfo>(list, setting.PageIndex, setting.PageSize, count)); }
public static IPageOfList <JobInfo> List(SearchSetting settings) { FastPaging fp = new FastPaging(); fp.PageIndex = settings.PageIndex; fp.PageSize = settings.PageSize; fp.TableName = "Jobs"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; StringBuilder sbCondition = new StringBuilder(); sbCondition.AppendFormat(" SiteId = @SiteId "); if (!settings.ShowDeleted) { sbCondition.Append(" AND IsDeleted = 0 "); } fp.Condition = sbCondition.ToString(); fp.OverOrderBy = "Sort ASC , EndDateTime DESC"; SqlParameter[] parms = { new SqlParameter("@SiteId", SqlDbType.Int), }; parms[0].Value = settings.SiteId; IList <JobInfo> list = new List <JobInfo>(); JobInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = Get(dr); if (model != null) { list.Add(model); } } } int count = Count(settings); return(new PageOfList <JobInfo>(list, settings.PageIndex, settings.PageSize, count)); }
/// <summary> /// 列表 /// </summary> /// <param name="searchSetting"></param> /// <returns></returns> public static IPageOfList <CompanyProductInfo> List(CompanyProductSearchSetting searchSetting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " CP.PublishDateTime DESC"; fp.PageIndex = searchSetting.PageIndex; fp.PageSize = searchSetting.PageSize; fp.QueryFields = "*"; fp.TableName = "CompanyProducts"; fp.PrimaryKey = "Id"; fp.TableReName = "CP"; fp.WithOptions = " WITH(NOLOCK)"; fp.Condition = " IsDeleted = 0 "; if (searchSetting.CompanyId > 0) { fp.Condition += string.Format(" AND CompanyId = {0}", searchSetting.CompanyId); } if (searchSetting.CategoryId > 0) { fp.Condition += string.Format(" AND CategoryId = {0}", searchSetting.CategoryId); } if (searchSetting.SystemCategoryId > 0) { fp.Condition += string.Format(" AND SystemCategoryId = {0}", searchSetting.SystemCategoryId); } IList <CompanyProductInfo> list = new List <CompanyProductInfo>(); CompanyProductInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByDataRow(dr); if (model != null) { list.Add(model); } } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); return(new PageOfList <CompanyProductInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count)); }
/// <summary> /// 博客列表 /// </summary> /// <param name="searchSetting"></param> /// <returns></returns> public static IPageOfList <BlogPostInfo> List(BlogSearchSetting searchSetting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " BP.CreateDateTime DESC"; fp.PageIndex = searchSetting.PageIndex; fp.PageSize = searchSetting.PageSize; fp.QueryFields = "*"; fp.TableName = "BlogPosts"; fp.PrimaryKey = "Id"; fp.TableReName = "BP"; fp.WithOptions = " WITH(NOLOCK)"; StringBuilder sbCondition = new StringBuilder(); sbCondition.Append(" IsDeleted = 0 "); if (searchSetting.UserId > 0) { sbCondition.AppendFormat(" AND UserId = {0}", searchSetting.UserId); } if (searchSetting.SystemCategoryId > 0) { sbCondition.AppendFormat(" AND SystemCategoryId = {0}", searchSetting.SystemCategoryId); } fp.Condition = sbCondition.ToString(); IList <BlogPostInfo> list = new List <BlogPostInfo>(); BlogPostInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByDataRow(dr); if (model != null) { list.Add(model); } } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); return(new PageOfList <BlogPostInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count)); }
public static IPageOfList <MemberInfo> BaseInfoList(MemberSearchSetting setting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " CreateDateTime DESC"; fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.QueryFields = "*"; fp.TableName = "Members"; fp.PrimaryKey = "Id"; fp.WithOptions = " WITH(NOLOCK)"; StringBuilder sbSQL = new StringBuilder(); sbSQL.Append(" 1 = 1 "); //根据用户名查询信息 if (!string.IsNullOrEmpty(setting.UserName)) { sbSQL.AppendFormat(" AND UserName LIKE '%{0}%'", Controleng.Common.Utils.ChkSQL(setting.UserName)); } fp.Condition += sbSQL.ToString(); IList <MemberInfo> list = new List <MemberInfo>(); MemberInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByDataRow(dr); if (model != null) { list.Add(model); } } } string strSQL = "SELECT COUNT(*) FROM Members AS M WITH(NOLOCK) WHERE "; strSQL += fp.Condition; int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL)); return(new PageOfList <MemberInfo>(list, setting.PageIndex, setting.PageSize, count)); }
/// <summary> /// 获得前台显示列表 /// </summary> /// <param name="companyId"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public static IPageOfList <CompanyFeedbackFrontInfo> FrontList(int companyId, int pageIndex, int pageSize) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " CF.CreateDateTime DESC"; fp.PageIndex = pageIndex; fp.PageSize = pageSize; fp.QueryFields = "CF.Id,CF.RealName,CF.CreateDateTime,CF.[Type],CF.Content,C.CompanyName AS ForCompanyName,CF.ForCompanyId,CF.UserId,ISNULL(M.Avatar,'nophoto.jpg') AS Avatar,ISNULL(M.UserName,'') AS UserName"; fp.TableName = "CompanyFeedback"; fp.PrimaryKey = "Id"; fp.TableReName = "CF"; fp.WithOptions = " WITH(NOLOCK)"; fp.JoinSQL = "INNER JOIN Companies AS C WITH(NOLOCK) ON CF.ForCompanyId = C.CompanyId LEFT JOIN Members AS M WITH(NOLOCK) ON CF.UserId = M.Id"; fp.Condition = string.Format(" CF.ForCompanyId = {0} AND CF.IsDeleted = 0", companyId); IList <CompanyFeedbackFrontInfo> list = new List <CompanyFeedbackFrontInfo>(); DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { var model = new CompanyFeedbackFrontInfo(); model.Avatar = dr.Field <string>("Avatar"); model.Content = dr.Field <string>("Content"); model.CreateDateTime = dr.Field <DateTime>("CreateDateTime"); model.ForCompanyId = dr.Field <int>("ForCompanyId"); model.ForCompanyName = dr.Field <string>("ForCompanyName"); model.RealName = dr.Field <string>("RealName"); model.Type = dr.Field <string>("Type"); model.UserId = dr.Field <int>("UserId"); model.UserName = dr.Field <string>("UserName"); if (string.IsNullOrEmpty(model.UserName)) { model.UserName = model.RealName; } list.Add(model); } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); return(new PageOfList <CompanyFeedbackFrontInfo>(list, pageIndex, pageSize, count)); }
/// <summary> /// 列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList <AttachmentDownloadLogInfo> ListLog(int attachId, SearchSetting setting) { FastPaging fp = new FastPaging(); fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.Ascending = false; fp.TableName = "AttachmentDownloadLog"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*,A.Title"; fp.OverOrderBy = " LastDownloadDateTime DESC"; fp.WithOptions = " WITH(NOLOCK)"; fp.JoinSQL = " INNER JOIN Attachment AS A WITH(NOLOCK) ON A.Id = P.AttachId"; if (attachId > 0) { fp.Condition = string.Format(" AttachId = {0}", attachId); } IList <AttachmentDownloadLogInfo> list = new List <AttachmentDownloadLogInfo>(); DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(new AttachmentDownloadLogInfo() { Id = dr.Field <int>("Id"), UserId = dr.Field <int>("UserId"), UserName = dr.Field <string>("UserName"), DownloadCount = dr.Field <int>("DownloadCount"), LastDownloadDateTime = dr.Field <DateTime>("LastDownloadDateTime"), AttachTitle = dr.Field <string>("Title") }); } } int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));; return(new PageOfList <AttachmentDownloadLogInfo>(list, setting.PageIndex, setting.PageSize, count)); }
public static IPageOfList <AttachmentInfo> List(SearchSetting setting) { FastPaging fp = new FastPaging(); fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.Ascending = false; fp.TableName = "Attachments"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.WithOptions = " WITH(NOLOCK)"; fp.OverOrderBy = " CreateDateTime DESC"; if (!setting.ShowDeleted) { fp.Condition += " IsDeleted = 0"; } IList <AttachmentInfo> list = new List <AttachmentInfo>(); AttachmentInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = new AttachmentInfo() { Id = dr.Field <int>("ID"), Title = dr.Field <string>("Title"), CreateDateTime = dr.Field <DateTime>("CreateDateTime"), FileType = dr.Field <string>("FileType"), Size = dr.Field <int>("Size"), Url = dr.Field <string>("Url"), IsDeleted = dr.Field <bool>("IsDeleted") }; list.Add(model); } } int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM Attachments")); return(new PageOfList <AttachmentInfo>(list, setting.PageIndex, setting.PageSize, count)); }
/// <summary> /// 根据公司ID获得反馈列表 /// </summary> /// <param name="companyId"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public static IPageOfList <CompanyFeedbackInfo> List(int companyId, int pageIndex, int pageSize) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " CreateDateTime DESC"; fp.PageIndex = pageIndex; fp.PageSize = pageSize; fp.QueryFields = "*"; fp.TableName = "CompanyFeedback"; fp.PrimaryKey = "Id"; fp.WithOptions = " WITH(NOLOCK)"; fp.Condition = string.Format(" CompanyId = {0} AND IsDeleted = 0", companyId); IList <CompanyFeedbackInfo> list = new List <CompanyFeedbackInfo>(); DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(new CompanyFeedbackInfo() { CompanyName = dr.Field <string>("CompanyName"), Content = dr.Field <string>("Content"), CreateDateTime = dr.Field <DateTime>("CreateDateTime"), UserId = dr.Field <int>("UserId"), Email = dr.Field <string>("Email"), Phone = dr.Field <string>("Phone"), ForCompanyId = dr.Field <int>("ForCompanyId"), Id = dr.Field <int>("Id"), IP = dr.Field <string>("IP"), RealName = dr.Field <string>("RealName"), Type = dr.Field <string>("Type"), ReplyList = _ReplyList(dr.Field <int>("Id")) }); } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); return(new PageOfList <CompanyFeedbackInfo>(list, pageIndex, pageSize, count)); }
/// <summary> /// 反馈列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList <FeedbackInfo> List(SearchSetting setting) { FastPaging fp = new FastPaging(); fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.Ascending = false; fp.TableName = "Feedback"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.OverOrderBy = "CreateDateTime DESC"; fp.Condition = " IsDeleted = 0"; IList <FeedbackInfo> list = new List <FeedbackInfo>(); DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(new FeedbackInfo() { Id = dr.Field <int>("Id"), Title = dr.Field <string>("Title"), Content = dr.Field <string>("Content"), Contact = dr.Field <string>("Contact"), Email = dr.Field <string>("Email"), Phone = dr.Field <string>("Phone"), Address = dr.Field <string>("Address"), Fax = dr.Field <string>("Fax"), CreateDateTime = dr.Field <DateTime>("CreateDateTime") }); } } int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));; return(new PageOfList <FeedbackInfo>(list, setting.PageIndex, setting.PageSize, count)); }
public static IPageOfList <FeedbackInfo> List(int pageIndex, int pageSize) { FastPaging fp = new FastPaging(); fp.PageIndex = pageIndex; fp.PageSize = pageSize; fp.Ascending = false; fp.TableName = "Feedbacks"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.OverOrderBy = " CreateDateTime DESC"; IList <FeedbackInfo> list = new List <FeedbackInfo>(); FeedbackInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = new FeedbackInfo() { Id = dr.Field <int>("ID"), Title = dr.Field <string>("Title"), Content = dr.Field <string>("Content"), Phone = dr.Field <string>("Phone"), Email = dr.Field <string>("Email"), CreateDateTime = dr.Field <DateTime>("CreateDateTime"), IP = dr.Field <string>("IP"), Realname = dr.Field <string>("Realname"), FeedbackType = dr.Field <string>("FeedbackType") }; list.Add(model); } } int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM Feedbacks")); return(new PageOfList <FeedbackInfo>(list, pageIndex, pageSize, count)); }
public static IPageOfList <AdminInfo> List(SearchSetting settings) { FastPaging fp = new FastPaging(); fp.PageIndex = settings.PageIndex; fp.PageSize = settings.PageSize; fp.TableName = "Admins"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; StringBuilder sbCondition = new StringBuilder(); if (!settings.ShowDeleted) { sbCondition.Append(" IsDeleted = 0 "); } fp.Condition = sbCondition.ToString(); fp.OverOrderBy = " CreateDateTime DESC"; IList <AdminInfo> list = new List <AdminInfo>(); AdminInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = Get(dr, true); if (model != null) { list.Add(model); } } } int count = Count(settings); return(new PageOfList <AdminInfo>(list, settings.PageIndex, settings.PageSize, count)); }
/// <summary> /// 分页列表(包含CompanyNews和Articles两个部分) /// </summary> /// <param name="searchSetting"></param> /// <returns></returns> public static IPageOfList <CompanyNewsInfo> List(CompanyNewsSearchSetting searchSetting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " CN.PublishDateTime DESC"; fp.PageIndex = searchSetting.PageIndex; fp.PageSize = searchSetting.PageSize; fp.QueryFields = "*"; fp.TableName = string.Format(@"( SELECT Id,CompanyId,[Type],Title,Content,Remark,ImageUrl,Tags,PublishDateTime,ModifyDateTime,CreateDateTime,IsDeleted,Url FROM CompanyNews WITH(NOLOCK) WHERE CompanyId = {0} AND [Type] = '{1}' AND IsDeleted = 0 UNION ALL SELECT Id,CompanyId,'article',Title,Content,Remark,ImageUrl,Tags,PublishDateTime,LastModifyDateTime,CreateDateTime,IsDeleted,Url FROM Articles WITH(NOLOCK) WHERE CompanyId = {0} AND IsDeleted = 0)", searchSetting.CompanyId, searchSetting.Type); fp.PrimaryKey = "Id"; fp.TableReName = "CN"; IList <CompanyNewsInfo> list = new List <CompanyNewsInfo>(); CompanyNewsInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByDataRow(dr); if (model != null) { list.Add(model); } } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); return(new PageOfList <CompanyNewsInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count)); }
/// <summary> /// 带有分页的文章列表 /// </summary> /// <param name="searchSetting"></param> /// <returns></returns> public static IPageOfList <ArticleInfo> List(ArticleSearchSetting searchSetting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " A.PublishDateTime DESC"; fp.PageIndex = searchSetting.PageIndex; fp.PageSize = searchSetting.PageSize; fp.QueryFields = "*"; fp.TableName = "Articles"; fp.PrimaryKey = "Id"; fp.TableReName = "A"; fp.WithOptions = " WITH(NOLOCK)"; StringBuilder sbSQL = new StringBuilder(); if (searchSetting.ColumnIds != null && searchSetting.ColumnIds.Length > 0) { //过滤栏目分类 //跳过为0的值 string temp = Enumerable.Range(0, searchSetting.ColumnIds.Length).Select(i => { if (searchSetting.ColumnIds[i] > 0) { return(string.Format(" A.CategoryId = {0} ", searchSetting.ColumnIds[i])); } return(string.Empty); }).Aggregate((a, b) => a + " OR " + b); if (!string.IsNullOrEmpty(temp)) { sbSQL.AppendFormat(" AND ( {0} )", temp); } } if (searchSetting.TechIds != null && searchSetting.TechIds.Length > 0) { //过滤技术分类 string temp = Enumerable.Range(0, searchSetting.TechIds.Length).Select(i => { if (searchSetting.TechIds[i] > 0) { return(string.Format(" A2C.CategoryId = {0} ", searchSetting.TechIds[i])); } return(string.Empty); }).Aggregate((a, b) => a + " OR " + b); if (!string.IsNullOrEmpty(temp)) { sbSQL.Append(" AND EXISTS("); sbSQL.Append(" SELECT * FROM dbo.Article2Category AS A2C WITH(NOLOCK)"); sbSQL.Append(" WHERE A2C.[Type] = 'tech'"); sbSQL.Append(" AND A.Id = A2C.ArticleId"); sbSQL.AppendFormat(" AND ( {0} )", temp); sbSQL.Append(" )"); } } if (searchSetting.IndustryIds != null && searchSetting.IndustryIds.Length > 0) { //过滤行业分类 string temp = Enumerable.Range(0, searchSetting.IndustryIds.Length).Select(i => { if (searchSetting.IndustryIds[i] > 0) { return(string.Format(" A2C.CategoryId = {0} ", searchSetting.IndustryIds[i])); } return(string.Empty); }).Aggregate((a, b) => a + " OR " + b); if (!string.IsNullOrEmpty(temp)) { sbSQL.Append(" AND EXISTS("); sbSQL.Append(" SELECT * FROM dbo.Article2Category AS A2C WITH(NOLOCK)"); sbSQL.Append(" WHERE A2C.[Type] = 'industry'"); sbSQL.Append(" AND A.Id = A2C.ArticleId"); sbSQL.AppendFormat(" AND ( {0} )", temp); sbSQL.Append(" )"); } } if (!searchSetting.IsDeleted) { sbSQL.Append(" AND IsDeleted = 0"); } fp.Condition = " 1 = 1 " + sbSQL.ToString(); //throw new Exception(fp.Build2005()); IList <ArticleInfo> list = new List <ArticleInfo>(); ArticleInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByDataRow(dr); if (model != null) { list.Add(model); } } } int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); return(new PageOfList <ArticleInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count)); }
public static IPageOfList <ArticleInfo> List(SearchSetting setting) { SqlParameter[] parms = { new SqlParameter("CID", SqlDbType.Int), new SqlParameter("Title", SqlDbType.NVarChar), new SqlParameter("PublishDate", SqlDbType.NVarChar) }; parms[0].Value = setting.CategoryId; parms[1].Value = setting.Title; parms[2].Value = setting.PublishDate; FastPaging fp = new FastPaging(); fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.Ascending = false; fp.TableName = "Articles"; fp.TableReName = "p"; fp.PrimaryKey = "ID"; fp.QueryFields = "p.*"; fp.OverOrderBy = "IsTop DESC,Sort ASC,PublishDateTime DESC"; StringBuilder sbCondition = new StringBuilder(); sbCondition.Append(@"EXISTS( SELECT * FROM Categories AS AC WITH(NOLOCK) WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0) AND p.CategoryId = AC.ID )"); if (!setting.ShowDeleted) { sbCondition.Append(" AND IsDeleted = 0 /*获取未删除的*/"); } if (!string.IsNullOrEmpty(setting.Title)) { sbCondition.Append(" AND CONTAINS(Title,@Title) "); } if (Regex.IsMatch(setting.PublishDate, @"^\d{4}-\d{1,2}-\d{1,2}$", RegexOptions.IgnoreCase)) { sbCondition.Append(" AND CONVERT(VARCHAR(10),PublishDateTime,120) = @PublishDate"); } fp.Condition = sbCondition.ToString(); IList <ArticleInfo> list = new List <ArticleInfo>(); ArticleInfo model = null; DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = Get(dr); if (model != null) { list.Add(model); } } } int count = CountForList(setting); return(new PageOfList <ArticleInfo>(list, setting.PageIndex, setting.PageSize, count)); }