/// <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 <ForumReplyInfo> ReplyList(ForumSearchSetting settings)
{
FastPaging fp = new FastPaging();
fp.PageIndex = settings.PageIndex;
fp.PageSize = settings.PageSize;
fp.TableName = "ForumReplies";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
StringBuilder sbCondition = new StringBuilder();
sbCondition.AppendFormat(" TopicId = @TopicId ");
if (!settings.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 ");
}
fp.Condition = sbCondition.ToString();
fp.OverOrderBy = "PostDateTime ASC";
SqlParameter[] parms =
{
new SqlParameter("@TopicId", SqlDbType.Int),
};
parms[0].Value = settings.TopicId;
IList <ForumReplyInfo> list = new List <ForumReplyInfo>();
ForumReplyInfo 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 = GetReplyInfo(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = ReplyListCount(settings);
return(new PageOfList <ForumReplyInfo>(list, settings.PageIndex, settings.PageSize, count));
}
public static IPageOfList <ArticleInfo> List(ArticleSearchSetting 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) ");
sbCondition.Append(" AND Title LIKE '%'+@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");
}
if (setting.IsOnlyShowPublished)
{
//只显示发布的文章
sbCondition.Append(" AND IsPublished = 1 ");
}
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 = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(), parms));;
return(new PageOfList <ArticleInfo>(list, setting.PageIndex, setting.PageSize, count));
}
public static IPageOfList <UserInfo> List(int pageIndex, int pageSize)
{
FastPaging fp = new FastPaging();
fp.PageIndex = pageIndex;
fp.PageSize = pageSize;
fp.Ascending = false;
fp.TableName = "Users";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = "p.LastLoginDateTime DESC";
IList <UserInfo> list = new List <UserInfo>();
UserInfo 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 <UserInfo>(list, pageIndex, 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>
/// 产品列表
/// </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));
}