public override List <Website> GetWebsites(int pageSize, int pageNumber, out int totalCount) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "Chinaz_Websites"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.PrimaryKey = "WebsiteID"; query.Pager.SortField = "WebsiteID"; query.Pager.SelectCount = true; query.Pager.IsDesc = true; using (XSqlDataReader reader = query.ExecuteReader()) { totalCount = 0; List <Website> results = new List <Website>(); while (reader.Next) { results.Add(new Website(reader)); } if (reader.NextResult()) { while (reader.Next) { totalCount = reader.Get <int>(00); } } return(results); } } }
public override UserCollection GetRoleMembers(int roleID, int pageSize, int pageNumber, out int totalCount) { UserCollection users = new UserCollection(); using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "[bx_Users]"; query.Pager.PageSize = pageSize; query.Pager.PageNumber = pageNumber; query.Pager.SelectCount = true; query.Pager.SortField = "[UserID]"; query.Pager.PrimaryKey = "[UserID]"; query.Pager.Condition = " UserID IN( SELECT UserID FROM bx_UsersInRoles WHERE RoleID = @RoleID AND BeginDate <= GETDATE() AND EndDate >= GETDATE())"; query.CreateParameter <int>("@RoleID", roleID, SqlDbType.Int); totalCount = 0; using (XSqlDataReader reader = query.ExecuteReader()) { users = new UserCollection(reader); if (reader.NextResult()) { if (reader.Read()) { totalCount = reader.Get <int>(0); } } } } return(users); }
public override EmoticonCollection GetEmoticons(int userID, int GroupID, int pageSize, int pageNumber, bool isDesc, out int totalCount) { totalCount = 0; using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_Emoticons"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.PrimaryKey = "EmoticonID"; query.Pager.SelectCount = true; query.Pager.SortField = "SortOrder"; query.Pager.IsDesc = isDesc; query.Pager.Condition = " GroupID = @GroupID AND EXISTS( SELECT * FROM bx_EmoticonGroups WHERE GroupID = @GroupID AND UserID = @UserID)"; query.CreateParameter <int>("@GroupID", GroupID, SqlDbType.Int); query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { EmoticonCollection emoticons = new EmoticonCollection(reader); if (reader.NextResult()) { if (reader.Read()) { emoticons.TotalRecords = reader.Get <int>(0); } } totalCount = emoticons.TotalRecords; return(emoticons); } } }
public override ImpressionRecordCollection GetTargetUserImpressionRecords(int targetUserID, int pageNumber, int pageSize, ref int?totalCount) { using (SqlQuery db = new SqlQuery()) { db.Pager.TableName = "bx_ImpressionRecordsWithTypeInfo"; db.Pager.Condition = "TargetUserID = @TargetUserID"; db.Pager.SortField = "RecordID"; db.Pager.PageNumber = pageNumber; db.Pager.PageSize = pageSize; db.Pager.TotalRecords = totalCount; db.Pager.SelectCount = true; db.CreateParameter <int>("@TargetUserID", targetUserID, System.Data.SqlDbType.Int); using (XSqlDataReader reader = db.ExecuteReader()) { ImpressionRecordCollection result = new ImpressionRecordCollection(reader); if (reader.NextResult() && reader.Read()) { totalCount = reader.Get <int>(0); result.TotalRecords = totalCount.Value; } return(result); } } }
public override CommentCollection GetCommentsByFilter(AdminCommentFilter filter, int operatorUserID, IEnumerable <Guid> excludeRoleIDs, int pageNumber, out int totalCount) { totalCount = 0; using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_Comments"; query.Pager.SortField = filter.Order.ToString(); if (filter.Order != AdminCommentFilter.OrderBy.CommentID) { query.Pager.PrimaryKey = "CommentID"; } query.Pager.IsDesc = filter.IsDesc; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = filter.PageSize; query.Pager.SelectCount = true; query.Pager.Condition = BuildConditionsByFilter(query, filter, false, operatorUserID, excludeRoleIDs); using (XSqlDataReader reader = query.ExecuteReader()) { CommentCollection comments = new CommentCollection(reader); if (reader.NextResult()) { while (reader.Read()) { totalCount = reader.Get <int>(0); } } return(comments); } } }
public override CommentCollection GetCommentsBySearch(int operatorID, Guid[] excludeRoleIDs, AdminCommentFilter filter, int pageNumber) { using (SqlQuery query = new SqlQuery()) { string conditions = BuildConditionsByFilter(query, filter, false, operatorID, excludeRoleIDs); query.Pager.TableName = "[bx_Comments]"; query.Pager.SortField = filter.Order.ToString(); query.Pager.IsDesc = filter.IsDesc; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = filter.PageSize; query.Pager.SelectCount = true; query.Pager.Condition = conditions.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { CommentCollection comments = new CommentCollection(reader); if (reader.NextResult()) { if (reader.Read()) { comments.TotalRecords = reader.Get <int>(0); } } return(comments); } } }
public override EmoticonCollection AdminGetUserEmoticons(int userID, int pageSize, int pageIndex) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_Emoticons"; query.Pager.Condition = " GroupID IN (SELECT GroupID FROM bx_EmoticonGroups WHERE UserID = @UserID)"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); query.Pager.PageSize = pageSize; query.Pager.PageNumber = pageIndex; query.Pager.SortField = "SortOrder"; query.Pager.PrimaryKey = "EmoticonID"; query.Pager.SelectCount = true; using (XSqlDataReader reader = query.ExecuteReader()) { EmoticonCollection Emoticons = new EmoticonCollection(reader); if (reader.NextResult()) { if (reader.Read()) { Emoticons.TotalRecords = reader.Get <int>(0); } } return(Emoticons); } } }
public override AuctionBidInfoCollection GetMyAuctionBids(int userID, int auctionID, int pageSize, int pageNumber) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "Chinaz_AuctionBids"; query.Pager.PrimaryKey = "BidID"; query.Pager.IsDesc = true; query.Pager.SelectCount = true; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.Condition = "UserID = @UserID AND AuctionID = @AuctionID"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); query.CreateParameter <int>("@AuctionID", auctionID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { AuctionBidInfoCollection bids = new AuctionBidInfoCollection(reader); if (reader.NextResult()) { while (reader.Next) { bids.TotalRecords = reader.Get <int>(0); } } return(bids); } } }
public override PointShowCollection GetPointShowList(int pageSize, int pageNumber) { using (SqlQuery query = new SqlQuery()) { query.Pager.SortField = "[Price]"; query.Pager.IsDesc = true; query.Pager.PrimaryKey = "[UserID]"; query.Pager.PageSize = pageSize; query.Pager.PageNumber = pageNumber; query.Pager.SelectCount = true; query.Pager.TableName = "bx_PointShows"; using (XSqlDataReader reader = query.ExecuteReader()) { PointShowCollection result = new PointShowCollection(reader); if (reader.NextResult()) { while (reader.Read()) { result.TotalRecords = reader.Get <int>(0); } } return(result); } } }
/// <summary> /// 高级搜索 /// </summary> public override NotifyCollection AdminGetNotifiesBySearch(AdminNotifyFilter notifyFilter, int pageNumber, IEnumerable <Guid> excludeRoleIds) { using (SqlQuery query = new SqlQuery()) { StringBuilder condition = FilterToCondition(query, notifyFilter); string exlcludeUserIDs = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIds, query); if (!string.IsNullOrEmpty(exlcludeUserIDs)) { condition.Append(" AND " + exlcludeUserIDs); } query.Pager.IsDesc = true; query.Pager.TableName = "[bx_Notify]"; query.Pager.SortField = "[NotifyID]"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = notifyFilter.PageSize; query.Pager.SelectCount = true; query.Pager.Condition = condition.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { NotifyCollection notifies = new NotifyCollection(reader); if (reader.NextResult()) { if (reader.Read()) { notifies.TotalRecords = reader.Get <int>(0); } } return(notifies); } } }
/// <summary> /// 获取指定用户/所有用户的所有通知 /// </summary> /// <param name="userID">指定用户的ID,可以为空,为空则为要获取所有用户</param> /// <returns>返回指定用户的所有通知集合</returns> public override NotifyCollection GetNotifies(int?userID, int pageSize, int pageNumber, ref int?count) { using (SqlQuery query = new SqlQuery()) { query.Pager.IsDesc = true; query.Pager.TableName = "[bx_Notify]"; query.Pager.SortField = "[NotifyID]"; query.Pager.PrimaryKey = "[NotifyID]"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.TotalRecords = count; query.Pager.SelectCount = true; if (userID != null) { query.Pager.Condition = @"[UserID] = @UserID"; } query.CreateParameter <int?>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { NotifyCollection notifies = new NotifyCollection(reader); if (count == null && reader.NextResult()) { if (reader.Read()) { count = reader.Get <int>(0); } } return(notifies); } } }
/// <summary> /// 获取标签 /// </summary> public override TagCollection GetMostTags(bool isLock, int pageNumber, int pageSize, ref int?count) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_Tags"; query.Pager.SortField = "ID"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.TotalRecords = count; query.Pager.IsDesc = true; query.Pager.SelectCount = true; query.Pager.Condition = "[IsLock] = @IsLock"; query.CreateParameter <bool>("@IsLock", isLock, SqlDbType.Bit); using (XSqlDataReader reader = query.ExecuteReader()) { TagCollection tags = new TagCollection(reader); if (count == null && reader.NextResult()) { if (reader.Read()) { count = reader.Get <int>(0); } } return(tags); } } }
/// <summary> /// 获取指定类型的标签 /// </summary> /// <param name="type">类型,如日志标签等</param> public override TagCollection GetTags(TagType type, int pageNumber, int pageSize, ref int?count) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_Tags"; query.Pager.SortField = "ID"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.TotalRecords = count; query.Pager.IsDesc = true; query.Pager.SelectCount = true; query.Pager.Condition = "[ID] IN (SELECT [TagID] FROM [bx_TagRelation] WHERE [Type] = @Type)"; query.CreateParameter <TagType>("@Type", type, SqlDbType.TinyInt); using (XSqlDataReader reader = query.ExecuteReader()) { TagCollection tags = new TagCollection(reader); if (count == null && reader.NextResult()) { if (reader.Read()) { count = reader.Get <int>(0); } } return(tags); } } }
public override CommentCollection GetCommentsByUserID(int userID, CommentType type, int pageNumber, int pageSize, out int totalCount) { totalCount = 0; string sqlCondition = "Type = @Type AND UserID = @UserID AND IsApproved = 1"; using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_Comments"; query.Pager.Condition = sqlCondition; query.Pager.SortField = "CommentID"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.SelectCount = true; query.Pager.ResultFields = "*"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); query.CreateParameter <int>("@Type", (int)type, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { CommentCollection comments = new CommentCollection(reader); if (reader.NextResult()) { while (reader.Read()) { totalCount = reader.Get <int>(0); } } return(comments); } } }
/// <summary> /// 获取所有标签 /// </summary> public override TagCollection GetAllTags(int pageNumber, int pageSize, ref int?count) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_Tags"; query.Pager.SortField = "ID"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.TotalRecords = count; query.Pager.IsDesc = true; query.Pager.SelectCount = true; using (XSqlDataReader reader = query.ExecuteReader()) { TagCollection tags = new TagCollection(reader); if (count == null && reader.NextResult()) { if (reader.Read()) { count = reader.Get <int>(0); } } return(tags); } } }
public override VisitorCollection GetSpaceVisitors(int spaceOwnerID, int pageSize, int pageNumber) { using (SqlQuery query = new SqlQuery()) { query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.SelectCount = true; query.Pager.SortField = "CreateDate"; query.Pager.PrimaryKey = "[ID]"; query.Pager.TableName = "bx_Visitors"; query.Pager.Condition = "UserID = @UserID"; query.Pager.IsDesc = true; query.CreateParameter <int>("@UserID", spaceOwnerID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { VisitorCollection visitors = new VisitorCollection(reader); if (reader.NextResult()) { if (reader.Read()) { visitors.TotalRecords = reader.Get <int>(0); } } return(visitors); } } }
public override UserTempAvatarCollection GetUserTempAvatars(int pageSize, int pageNumber, out int totalCount) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_UserTempAvatar"; query.Pager.PageSize = pageSize; query.Pager.PageNumber = pageNumber; query.Pager.PrimaryKey = "UserID"; query.Pager.SortField = "[CreateDate]"; query.Pager.IsDesc = true; query.Pager.SelectCount = true; totalCount = 0; using (XSqlDataReader reader = query.ExecuteReader()) { UserTempAvatarCollection tempAvatars = new UserTempAvatarCollection(reader); if (reader.NextResult()) { while (reader.Read()) { totalCount = reader.Get <int>(0); } } return(tempAvatars); } } }
public override PointLogCollection GetPointLogs(PointLogFilter filter, int pageNumber) { using (SqlQuery query = new SqlQuery()) { StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(filter.Username)) { sb.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username = @Username )"); query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } if (filter.OperateID != null) { sb.Append(" AND OperateID = @OperateID"); query.CreateParameter <int>("@OperateID", filter.OperateID.Value, SqlDbType.Int); } if (filter.BeginDate != null) { sb.Append(" AND CreateTime >= @BeginDate"); query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime); } if (filter.EndDate != null) { sb.Append(" AND CreateTime <= @EndDate"); query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime); } if (sb.Length > 0) { sb.Remove(0, 4); } query.Pager.TableName = "bx_PointLogs"; query.Pager.PageNumber = pageNumber; query.Pager.PrimaryKey = "LogID"; query.Pager.SortField = "LogID"; query.Pager.IsDesc = true; query.Pager.PageSize = filter.PageSize; query.Pager.SelectCount = true; query.Pager.Condition = sb.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { PointLogCollection logs = new PointLogCollection(reader); if (reader.NextResult()) { while (reader.Next) { logs.TotalRecords = reader.Get <int>(0); } } return(logs); } } }
public override VisitorCollection SelectVisitors(int userID, int pageNumber, int pageSize, out int totalCount) { totalCount = 0; VisitorCollection visitors = new VisitorCollection(); using (SqlQuery query = new SqlQuery()) { query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.SelectCount = true; query.Pager.SortField = "CreateDate"; query.Pager.TableName = "bx_UserVisitors"; query.Pager.Condition = "UserID = @UserID"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { visitors = new VisitorCollection(reader); if (reader.NextResult()) { while (reader.Read()) { totalCount = reader.Get <int>(0); } } } } return(visitors); }
public override ChatSessionCollection GetChatSessions(int userID, int pageNumber, int pageSize) { ChatSessionCollection results = new ChatSessionCollection(); using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_ChatSessions"; query.Pager.PrimaryKey = "ChatSessionID"; query.Pager.SortField = "UpdateDate"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.IsDesc = true; query.Pager.SelectCount = true; query.Pager.Condition = "UserID = @UserID"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { results = new ChatSessionCollection(reader); while (reader.NextResult()) { while (reader.Read()) { results.TotalRecords = reader.Get <int>(00); } } } } return(results); }
public override DoingCollection GetEveryoneDoingsWithComments(int pageNumber, int pageSize, ref int?totalCount) { DoingCollection doings = null; using (SqlSession db = new SqlSession()) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "[bx_Doings]"; query.Pager.SortField = "[DoingID]"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.TotalRecords = totalCount; query.Pager.IsDesc = true; query.Pager.SelectCount = true; using (XSqlDataReader reader = query.ExecuteReader()) { doings = new DoingCollection(reader); if (totalCount == null && reader.NextResult() && reader.Read()) { totalCount = reader.Get <int>(0); } doings.TotalRecords = totalCount.GetValueOrDefault(); } } FillDoingComments(doings, db); } return(doings); }
public override IList <int> SelectMayFriendIDs(int userID, int count) { List <int> mayFriendIDs = new List <int>(); string sql = @"SELECT DISTINCT TOP (@Count) FriendUserID FROM bx_Friends WITH (NOLOCK) WHERE UserID IN(SELECT FriendUserID FROM bx_Friends WHERE UserID=@UserID) AND FriendUserID NOT IN(SELECT FriendUserID FROM bx_Friends WITH (NOLOCK) WHERE UserID=@UserID) AND FriendUserID <> @UserID;"; using (SqlQuery query = new SqlQuery()) { query.CommandText = sql; query.CreateTopParameter("@Count", count); query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { while (reader.Read()) { mayFriendIDs.Add(reader.Get <int>("FriendUserID")); } } } return(mayFriendIDs); }
/// <summary> /// 高级搜索 /// </summary> public override NotifyCollection GetNotifiesBySearch(AdminNotifyFilter notifyFilter, int pageNumber) { using (SqlQuery query = new SqlQuery()) { StringBuilder condition = FilterToCondition(query, notifyFilter); query.Pager.IsDesc = true; query.Pager.TableName = "[bx_Notify]"; query.Pager.SortField = "[NotifyID]"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = notifyFilter.PageSize; query.Pager.SelectCount = true; query.Pager.Condition = condition.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { NotifyCollection notifies = new NotifyCollection(reader); if (reader.NextResult()) { if (reader.Read()) { notifies.TotalRecords = reader.Get <int>(0); } } return(notifies); } } }
public override DeleteResult DeleteDoingsBySearch(AdminDoingFilter filter, IEnumerable <Guid> excludeRoleIDs, int deleteTopCount, out int deletedCount) { deletedCount = 0; using (SqlQuery query = new SqlQuery()) { string conditions = BuildConditionsByFilter(query, filter, excludeRoleIDs, true); StringBuffer sql = new StringBuffer(); sql += @" DECLARE @DeleteData table (UserID int, DoingID int); INSERT INTO @DeleteData SELECT TOP (@TopCount) [UserID],[DoingID] FROM [bx_Doings] " + conditions + @"; DELETE [bx_Doings] WHERE DoingID IN (SELECT [DoingID] FROM @DeleteData); SELECT @@ROWCOUNT; SELECT [UserID],COUNT(*) AS [Count] FROM @DeleteData GROUP BY [UserID];"; query.CreateTopParameter("@TopCount", deleteTopCount); query.CommandText = sql.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { DeleteResult deleteResult = new DeleteResult(); if (reader.Read()) { deletedCount = reader.Get <int>(0); } if (reader.NextResult()) { while (reader.Read()) { deleteResult.Add(reader.Get <int>("UserID"), reader.Get <int>("Count")); } } return(deleteResult); } } }
public override DenouncingCollection GetDenouncingWithReply(DenouncingFilter filter, int pageNumber) { using (SqlSession db = new SqlSession()) { DenouncingCollection denouncings = null; using (SqlQuery query = db.CreateQuery()) { query.Pager.PageNumber = pageNumber; query.Pager.PageSize = filter.PageSize; query.Pager.TableName = "bx_Denouncings"; query.Pager.SortField = "DenouncingID"; query.Pager.IsDesc = filter.IsDesc; query.Pager.SelectCount = true; filter.Type = DenouncingType.Reply; GetSearchDenouncingsCondition(query, filter); using (XSqlDataReader reader = query.ExecuteReader()) { denouncings = new DenouncingCollection(reader); if (reader.NextResult()) { while (reader.Read()) { denouncings.TotalRecords = reader.Get <int>(0); } } } } FillDenouncingContents(denouncings, db); if (denouncings.Count > 0) { int[] targetIDs = GetTargetIDs(denouncings); PostCollectionV5 posts = PostBOV5.Instance.GetPosts(targetIDs); for (int i = 0; i < denouncings.Count; i++) { for (int j = 0; j < posts.Count; j++) { if (denouncings[i].TargetID == posts[j].ThreadID) { denouncings[i].TargetReply = posts[j]; break; } } } } return(denouncings); } }
public override Dictionary <string, DateTime> GetAllJobStatus() { Dictionary <string, DateTime> times = new Dictionary <string, DateTime>(StringComparer.CurrentCultureIgnoreCase); using (SqlQuery query = new SqlQuery()) { query.CommandText = "bx_GetAllJobStatus"; query.CommandType = CommandType.StoredProcedure; using (XSqlDataReader reader = query.ExecuteReader()) { while (reader.Read()) { times.Add(reader.Get <string>("Type"), reader.Get <DateTime>("LastExecuteTime")); } } return(times); } }
public override UserPayCollection GetUserPays(int userID, PaylogFilter filter, int pageSize, int pageNumber) { using (SqlQuery query = new SqlQuery()) { StringBuilder condition = new StringBuilder(" AND UserID = @UserID"); query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); if (filter != null) { if (filter.State < 2) { condition.Append(" AND State=@State"); query.CreateParameter <bool>("@State", (filter.State == 1), SqlDbType.Bit); } if (filter.BeginDate != null) { condition.Append(" AND PayDate>= @BeginDate"); query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime); } if (filter.EndDate != null) { condition.Append(" AND PayDate<= @EndDate"); query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime); } } if (condition.Length > 5) { condition.Remove(0, 5); } query.Pager.TableName = "bx_Pay"; query.Pager.PageSize = pageSize; query.Pager.PageNumber = pageNumber; query.Pager.PrimaryKey = "PayID"; query.Pager.SelectCount = true; query.Pager.SortField = "PayID"; query.Pager.Condition = condition.ToString(); query.Pager.IsDesc = true; using (XSqlDataReader reader = query.ExecuteReader()) { UserPayCollection result = new UserPayCollection(reader); if (reader.NextResult()) { while (reader.Next) { result.TotalRecords = reader.Get <int>(0); } } return(result); } } }
public override MissionCollection GetNewMissions(int userID, int?categoryID, int pageNumber, int pageSize, out int totalCount) { using (SqlQuery query = new SqlQuery()) { query.Pager.IsDesc = false; query.Pager.SortField = "[SortOrder]"; query.Pager.PrimaryKey = "[ID]"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; //query.Pager.TotalRecords = totalCount; query.Pager.SelectCount = true; query.Pager.TableName = "[bx_Missions]"; if (categoryID != null) { query.Pager.Condition = "CategoryID = @CategoryID AND "; query.CreateParameter <int>("@CategoryID", categoryID.Value, SqlDbType.Int); } query.Pager.Condition += @" ParentID IS NULL AND ( getdate() > [BeginDate] AND getdate() < [EndDate] AND [IsEnable] = 1 AND [ID] NOT IN (SELECT [MissionID] FROM [bx_UserMissions] WHERE [UserID] = @UserID) OR ([CycleTime] > 0 AND [IsEnable] = 1 AND [ID] IN( SELECT [MissionID] FROM [bx_UserMissions] WHERE [UserID] = @UserID AND [FinishPercent]=1 AND [Status]<>3 AND [CreateDate]<DATEADD(second,0-[CycleTime],getdate()) AND getdate() > [BeginDate] AND getdate() < [EndDate] ) )) "; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { MissionCollection missions = new MissionCollection(reader); if (reader.NextResult()) { if (reader.Read()) { totalCount = reader.Get <int>(0); } else { totalCount = 0; } } else { totalCount = 0; } return(missions); } } }
public override UserEmoticonInfoCollection AdminGetUserEmoticonInfos(EmoticonFilter filter, int pageIndex, IEnumerable <Guid> excludeRoleIDs) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_UserEmoticonInfo"; query.Pager.PageSize = filter.Pagesize; query.Pager.PageNumber = pageIndex; query.Pager.SortField = "[UserID]"; if (filter.UserName == null) { filter.UserName = string.Empty; } if (filter.Order != null) { switch (filter.Order.Value) { case EmoticonFilter.OrderBy.SpaceSize: query.Pager.SortField = "[TotalSizes]"; break; case EmoticonFilter.OrderBy.EmoticonCount: query.Pager.SortField = "[TotalEmoticons]"; break; } } query.Pager.Condition = " Username LIKE '%'+@Username+'%'"; string excludeRoleUserIds = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIDs, query); if (!string.IsNullOrEmpty(excludeRoleUserIds)) { query.Pager.Condition += " AND " + excludeRoleUserIds; } query.CreateParameter <string>("@Username", filter.UserName, SqlDbType.NVarChar, 50); query.Pager.SelectCount = true; query.Pager.PrimaryKey = "[UserID]"; query.Pager.IsDesc = filter.IsDesc; using (XSqlDataReader reader = query.ExecuteReader()) { UserEmoticonInfoCollection groups = new UserEmoticonInfoCollection(reader); if (reader.NextResult()) { if (reader.Read()) { groups.TotalRecords = reader.Get <int>(0); } } return(groups); } } }
public override ChatMessageCollection GetChatMessages(int userID, int targetUserID, int pageNumber, int pageSize, bool updateIsReaded) { ChatMessageCollection result; using (SqlQuery query = new SqlQuery()) { if (pageNumber != 0) { query.Pager.BeforeExecuteDealcre.Add("@TotalMessages", SqlDbType.Int); query.Pager.BeforeExecute = @"SELECT @TotalMessages = TotalMessages FROM bx_ChatSessions WHERE UserID = @UserID AND TargetUserID = @TargetUserID;"; query.Pager.TableName = "bx_ChatMessages"; query.Pager.SortField = "MessageID"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.IsDesc = true; query.Pager.SelectCount = true; query.Pager.TotalRecordsVariable = "@TotalMessages"; query.Pager.Condition = "UserID = @UserID AND TargetUserID = @TargetUserID"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); query.CreateParameter <int>("@TargetUserID", targetUserID, SqlDbType.Int); if (updateIsReaded) { query.Pager.AfterExecute = @" UPDATE bx_ChatMessages SET IsRead = 1 WHERE UserID = @UserID AND TargetUserID = @TargetUserID AND IsRead = 0; "; } } else { query.CommandText = "SELECT Top " + pageSize + " * FROM bx_ChatMessages WHERE UserID = @UserID AND TargetUserID = @TargetUserID ORDER BY MessageID Desc;"; query.CommandText += "SELECT COUNT(*) FROM bx_ChatMessages WHERE UserID = @UserID AND TargetUserID = @TargetUserID;"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); query.CreateParameter <int>("@TargetUserID", targetUserID, SqlDbType.Int); } using (XSqlDataReader reader = query.ExecuteReader()) { result = new ChatMessageCollection(reader); if (reader.NextResult()) { if (reader.Read()) { result.TotalRecords = reader.Get <int>(0); } } } return(result); } }
private BasicThread GetThread(XSqlDataReader reader, DateTime? expiresDate) { ThreadType threadType = reader.Get<ThreadType>("ThreadType"); switch (threadType) { case ThreadType.Poll: PollThreadV5 poll = new PollThreadV5(reader); if (expiresDate != null) poll.IsClosed = expiresDate.Value <= DateTimeUtil.Now; return (BasicThread)poll; case ThreadType.Polemize: PolemizeThreadV5 polemize = new PolemizeThreadV5(reader); if (expiresDate != null) polemize.IsClosed = expiresDate.Value <= DateTimeUtil.Now; return (BasicThread)polemize; case ThreadType.Question: QuestionThread question = new QuestionThread(reader); if (question.IsClosed == false) { if (expiresDate != null) question.IsClosed = expiresDate.Value <= DateTimeUtil.Now; } return (BasicThread)question; default: return new BasicThread(reader); } }
private PostCollectionV5 GetPosts(XSqlDataReader reader, bool isFirstRead) { PostCollectionV5 posts = new PostCollectionV5(); List<int> replyIDs = new List<int>(); if (isFirstRead) { while (reader.Read()) { PostV5 post = new PostV5(reader); post.Attachments = new AttachmentCollection(); post.PostMarks = new PostMarkCollection(); posts.Add(post); replyIDs.Add(post.PostID); } } else { if (reader.NextResult()) { while (reader.Read()) { PostV5 post = new PostV5(reader); post.Attachments = new AttachmentCollection(); post.PostMarks = new PostMarkCollection(); posts.Add(post); replyIDs.Add(post.PostID); } } } //读取下一个结果集 if (reader.NextResult()) { while (reader.Read()) //附件列表 { Attachment attachment = new Attachment(reader); int replyIndex = replyIDs.IndexOf(attachment.PostID); if (replyIndex != -1) { posts[replyIndex].Attachments.Add(attachment); } } } //历史附件 if (reader.NextResult()) { while (reader.Read()) //附件列表 { Attachment attachment = new Attachment(reader); attachment.AttachType = AttachType.History; int hpostID = reader.Get<int>("HPostID"); int replyIndex = replyIDs.IndexOf(hpostID); if (replyIndex != -1) { posts[replyIndex].Attachments.Add(attachment); } } } if (reader.NextResult())//评分列表 { while (reader.Read()) { PostMark postMark = new PostMark(reader); int replyIndex = replyIDs.IndexOf(postMark.PostID); if (replyIndex != -1) { posts[replyIndex].PostMarks.Add(postMark); } } } if (reader.NextResult()) { string s = null; while (reader.Read()) { s = reader.Get<string>(0); } #if !Publish if (reader.SqlQuery.TempInfo != null) reader.SqlQuery.TempInfo += "-----" + s; else reader.SqlQuery.TempInfo = s; #endif } return posts; }
private void GetThread(XSqlDataReader reader, AttachmentCollection attachments, DateTime? expiresDate, out BasicThread thread, out PostV5 post, out List<int> attachmentIDs, out Dictionary<string, int> fileIDs) { attachmentIDs = new List<int>(); fileIDs = new Dictionary<string, int>(); thread = null; post = null; while (reader.Read()) { thread = GetThread(reader, expiresDate); } if (attachments.Count > 0) { //本地刚上传的附件 ID if (reader.NextResult()) { while (reader.Read()) { attachmentIDs.Add(reader.Get<int>(0)); } } //所有附件 if (reader.NextResult()) { while (reader.Read()) { string fileID = reader.Get<string>("fileID"); if (fileIDs.ContainsKey(fileID) == false) fileIDs.Add(fileID, reader.Get<int>("attachmentID")); } } } if (reader.NextResult()) { while (reader.Read()) { post = new PostV5(reader); } } }
private string GetExtendData(BasicThread thread, ThreadType threadType, XSqlDataReader reader, bool isFirstRead) { string extendData = null; switch (threadType) { case ThreadType.Poll: PollThreadV5 poll = thread == null ? new PollThreadV5() : (PollThreadV5)thread; if (isFirstRead || reader.NextResult()) { while (reader.Read()) { poll.FillPoll(reader); } } if (reader.NextResult()) { poll.PollItems = new PollItemCollectionV5(reader); } if (thread == null || poll.VotedUserIDs == null) poll.VotedUserIDs = new List<int>(); if (reader.NextResult()) { while (reader.Read()) { poll.VotedUserIDs.Add(reader.Get<int>(0)); } } extendData = poll.GetExtendData(); break; case ThreadType.Question: QuestionThread question = thread == null ? new QuestionThread() : (QuestionThread)thread; if (isFirstRead || reader.NextResult()) { while (reader.Read()) { question.FillQuestion(reader); } } if (thread == null || question.Rewards == null) question.Rewards = new Dictionary<int, int>(); if (reader.NextResult()) { while (reader.Read()) { question.Rewards.Add(reader.Get<int>("PostID"), reader.Get<int>("Reward")); } } extendData = question.GetExtendData(); break; case ThreadType.Polemize: PolemizeThreadV5 polemize = thread == null ? new PolemizeThreadV5() : (PolemizeThreadV5)thread; if (isFirstRead || reader.NextResult()) { while (reader.Read()) { polemize.FillPolemize(reader); } } if (reader.NextResult()) { polemize.FillPolemizeUsers(reader); } extendData = polemize.GetExtendData(); break; default: break; } if (thread != null) thread.ExtendDataIsNull = false; return extendData; }