public override UserWebsiteCollection AdminSearchUserWebsites(AdminWebsiteFilter filter, int pageNumber) { using (SqlQuery query = new SqlQuery()) { string SordField = filter.Order.ToString(); StringBuilder condition = new StringBuilder(); if (filter != null) { if (!string.IsNullOrEmpty(filter.Username)) { condition.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%' + @Username +'%')"); query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } if (filter.IsHaveImage < 2) { condition.Append(" AND IsHaveImage = @IsHaveImage"); query.CreateParameter <bool>("@IsHaveImage", (filter.IsHaveImage == 1), SqlDbType.Bit); } if (!string.IsNullOrEmpty(filter.WebsiteName)) { condition.Append(" AND WebsiteName LIKE '%' + @WebsiteName +'%'"); query.CreateParameter <string>("@WebsiteName", filter.WebsiteName, SqlDbType.NVarChar, 50); } if (!string.IsNullOrEmpty(filter.Url)) { condition.Append(" AND Url LIKE '%' + @Url + '%'"); query.CreateParameter <string>("@Url", filter.Url, SqlDbType.NVarChar, 200); } if (filter.categoryID > 0) { condition.Append(" AND CategoryID = @CategoryID"); query.CreateParameter <int>("@CategoryID", filter.categoryID, SqlDbType.Int); } if (filter.IsVerified < 2) { condition.Append(" AND Verified = @Verified"); query.CreateParameter <bool>("@Verified", (filter.IsVerified == 1), SqlDbType.Bit); } if (filter.BeginDate != null) { condition.Append(" AND CreateDate >= @BeginDate"); query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime); } if (filter.EndDate != null) { condition.Append(" AND CreateDate <= @EndDate"); query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime); } } if (condition.Length > 5) { condition.Remove(0, 5); } query.Pager.TableName = "Chinaz_UserWebsitesView"; query.Pager.PageSize = filter.Pagesize; query.Pager.PageNumber = pageNumber; query.Pager.PrimaryKey = "UserWebsiteID"; query.Pager.SortField = SordField; query.Pager.Condition = condition.ToString(); query.Pager.SelectCount = true; query.Pager.IsDesc = filter.IsDesc; using (XSqlDataReader reader = query.ExecuteReader()) { UserWebsiteCollection websitelist = new UserWebsiteCollection(reader); if (reader.NextResult()) { while (reader.Next) { websitelist.TotalRecords = reader.Get <int>(0); } } return(websitelist); } } }
public override UserWebsiteCollection AdminSearchUserWebsites(AdminWebsiteFilter filter,int pageNumber) { using (SqlQuery query = new SqlQuery()) { string SordField = filter.Order.ToString(); StringBuilder condition = new StringBuilder(); if (filter != null) { if (!string.IsNullOrEmpty(filter.Username)) { condition.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%' + @Username +'%')"); query.CreateParameter<string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } if (filter.IsHaveImage < 2) { condition.Append(" AND IsHaveImage = @IsHaveImage"); query.CreateParameter<bool>("@IsHaveImage", (filter.IsHaveImage == 1), SqlDbType.Bit); } if (!string.IsNullOrEmpty(filter.WebsiteName)) { condition.Append(" AND WebsiteName LIKE '%' + @WebsiteName +'%'"); query.CreateParameter<string>("@WebsiteName", filter.WebsiteName, SqlDbType.NVarChar, 50); } if (!string.IsNullOrEmpty(filter.Url)) { condition.Append(" AND Url LIKE '%' + @Url + '%'"); query.CreateParameter<string>("@Url", filter.Url, SqlDbType.NVarChar, 200); } if (filter.categoryID > 0) { condition.Append(" AND CategoryID = @CategoryID"); query.CreateParameter<int>("@CategoryID", filter.categoryID, SqlDbType.Int); } if (filter.IsVerified < 2) { condition.Append(" AND Verified = @Verified"); query.CreateParameter<bool>("@Verified", (filter.IsVerified == 1), SqlDbType.Bit); } if (filter.BeginDate != null) { condition.Append(" AND CreateDate >= @BeginDate"); query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime); } if (filter.EndDate != null) { condition.Append(" AND CreateDate <= @EndDate"); query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime); } } if (condition.Length > 5) condition.Remove(0, 5); query.Pager.TableName = "Chinaz_UserWebsitesView"; query.Pager.PageSize = filter.Pagesize; query.Pager.PageNumber = pageNumber; query.Pager.PrimaryKey = "UserWebsiteID"; query.Pager.SortField = SordField; query.Pager.Condition = condition.ToString(); query.Pager.SelectCount = true; query.Pager.IsDesc = filter.IsDesc; using (XSqlDataReader reader = query.ExecuteReader()) { UserWebsiteCollection websitelist = new UserWebsiteCollection(reader); if (reader.NextResult()) { while (reader.Next) websitelist.TotalRecords = reader.Get<int>(0); } return websitelist; } } }