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 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 DoingCollection GetUserDoingsWithComments(int doingOwnerID, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int?totalCount) { DoingCollection doings = null; using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery()) { query.Pager.TableName = "[bx_Doings]"; query.Pager.SortField = "[DoingID]"; query.Pager.SelectCount = true; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.TotalRecords = totalCount; query.Pager.Condition = "[UserID]=@UserID"; query.CreateParameter <int>("@UserID", doingOwnerID, SqlDbType.Int); //if (dataAccessLevel == DataAccessLevel.Normal) //{ // query.Pager.Condition += " AND [PrivacyType] IN (0, 3)"; //} //else if (dataAccessLevel == DataAccessLevel.Friend) //{ // query.Pager.Condition += " AND [PrivacyType] IN (0, 1, 3)"; //} 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 DenouncingCollection GetDenouncingWithUser(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.Space; 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) { UserBO.Instance.WaitForFillSimpleUsers <Denouncing>(denouncings); } return(denouncings); } }
/// <summary> /// 获取系统启动时数据库当前时间与程序时间的相差毫秒数 /// </summary> public override double GetTimeIntervalFromDatabase() { using (SqlSession session = new SqlSession()) { string connectionString = session.Connection.ConnectionString; Regex regexConnString1 = new Regex(@"^.*\s?(?>Server|Data\s+Source)\s*=\s*(?>\.|\(local\)|127\.0\.0\.1|localhost|::1)\s*;.*?", RegexOptions.IgnoreCase); if (regexConnString1.IsMatch(connectionString)) { return(0.0f); } using (SqlQuery query = new SqlQuery()) { query.CommandText = "SELECT GETDATE();"; return((query.ExecuteScalar <DateTime>() - DateTimeUtil.Now).TotalMilliseconds); } } }
/// <summary> /// 获取系统启动时数据库当前时间与程序时间的相差毫秒数 /// </summary> public override double GetTimeIntervalFromDatabase() { using (SqlSession session = new SqlSession()) { string connectionString = session.Connection.ConnectionString; Regex regexConnString1 = new Regex(@"^.*\s?(?>Server|Data\s+Source)\s*=\s*(?>\.|\(local\)|127\.0\.0\.1|localhost|::1)\s*;.*?", RegexOptions.IgnoreCase); if (regexConnString1.IsMatch(connectionString)) { return 0.0f; } using (SqlQuery query = new SqlQuery()) { query.CommandText = "SELECT GETDATE();"; return (query.ExecuteScalar<DateTime>() - DateTimeUtil.Now).TotalMilliseconds; } } }
private void FillDenouncingContents(DenouncingCollection denouncings, SqlSession db) { if (denouncings.Count == 0) { return; } DenouncingContentCollection contents = null; using (SqlQuery query = db.CreateQuery()) { query.CommandText = "SELECT * FROM bx_DenouncingContents WHERE DenouncingID IN (@DenouncingIDs) ORDER BY [DenouncingID]"; query.CreateInParameter <int>("@DenouncingIDs", denouncings.GetDenouncingIDs()); using (XSqlDataReader reader = query.ExecuteReader()) { contents = new DenouncingContentCollection(reader); } } db.Connection.Close(); Denouncing denouncing = null; int lastDenouncingID = -1; for (int i = 0; i < contents.Count; i++) { int denouncingID = contents[i].DenouncingID; if (denouncingID != lastDenouncingID) { denouncing = denouncings.GetValue(denouncingID); lastDenouncingID = denouncingID; } denouncing.ContentList.Add(contents[i]); } }
public override DoingCollection GetUserCommentedDoingsWithComments(int userID, 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; query.Pager.Condition = "[DoingID] IN (SELECT [TargetID] FROM [bx_Comments] WHERE [Type]=3 AND [UserID] = @UserID)"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); 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 UserCollection GetUserShows(int userID, int pageNumber, int pageSize, out int userTotalCount) { userTotalCount = 0; UserCollection users = new UserCollection(); using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery()) { query.Pager.TableName = "bx_PointShowUsers"; query.Pager.SortField = "Price"; query.Pager.IsDesc = true; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.SelectCount = true; query.Pager.PrimaryKey = "UserID"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { users = new UserCollection(reader); if (reader.NextResult()) { if (reader.Read()) { users.TotalRecords = reader.Get <int>(0); userTotalCount = users.TotalRecords; } } } } return(users); } }
public override void DeleteExperisValidateCodeActionRecord(List <string> actions, List <int> limitedTimes) { using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery(QueryMode.Batch)) { StringBuilder sql = new StringBuilder(); for (int i = 0; i < actions.Count; i++) { sql.AppendFormat(@" DELETE [bx_ValidateCodeActionRecords] WHERE [Action] = @Action_{0} AND [CreateDate] < @LimitedTime_{0}; ", i); query.CreateParameter <string>("@Action_" + i, actions[i], SqlDbType.VarChar, 200); query.CreateParameter <DateTime>("@LimitedTime_" + i, DateTimeUtil.Now.AddSeconds(0 - limitedTimes[i]), SqlDbType.DateTime); //query.ExecuteNonQuery(); } //query.Submit(); if (actions.Count > 0) { query.CommandText += "DELETE [bx_ValidateCodeActionRecords] WHERE [Action] NOT IN (@Actions);"; query.CreateInParameter <string>("@Actions", actions); } else { query.CommandText += "DELETE [bx_ValidateCodeActionRecords]"; } query.ExecuteNonQuery(); } } }
private void FillDenouncingContents(DenouncingCollection denouncings, SqlSession db) { if (denouncings.Count == 0) return; DenouncingContentCollection contents = null; using (SqlQuery query = db.CreateQuery()) { query.CommandText = "SELECT * FROM bx_DenouncingContents WHERE DenouncingID IN (@DenouncingIDs) ORDER BY [DenouncingID]"; query.CreateInParameter<int>("@DenouncingIDs", denouncings.GetDenouncingIDs()); using (XSqlDataReader reader = query.ExecuteReader()) { contents = new DenouncingContentCollection(reader); } } db.Connection.Close(); Denouncing denouncing = null; int lastDenouncingID = -1; for (int i = 0; i < contents.Count; i++) { int denouncingID = contents[i].DenouncingID; if (denouncingID != lastDenouncingID) { denouncing = denouncings.GetValue(denouncingID); lastDenouncingID = denouncingID; } denouncing.ContentList.Add(contents[i]); } }
private void FillDoingComments(DoingCollection doings, SqlSession db) { if (doings.Count == 0) { return; } List <int> minIds = new List <int>(); List <int> maxIds = new List <int>(); for (int i = 0; i < doings.Count; i++) { if (doings[i].TotalComments == 0) { continue; } else if (doings[i].TotalComments == 1) { minIds.Add(doings[i].DoingID); } else { minIds.Add(doings[i].DoingID); maxIds.Add(doings[i].DoingID); } } if (minIds.Count == 0) { return; } using (SqlQuery query = db.CreateQuery()) { query.CommandText = @" SELECT * FROM bx_Comments WHERE CommentID IN( SELECT Min(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MinTargetIDs) GROUP BY TargetID ); "; if (maxIds.Count > 0) { query.CommandText += @" SELECT * FROM bx_Comments WHERE CommentID IN( SELECT Max(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MaxTargetIDs) GROUP BY TargetID ); "; } query.CreateInParameter <int>("@MinTargetIDs", minIds); query.CreateInParameter <int>("@MaxTargetIDs", maxIds); using (XSqlDataReader reader = query.ExecuteReader()) { while (reader.Read()) { Comment comment = new Comment(reader); Doing doing = doings.GetValue(comment.TargetID); if (doing != null) { if (doing.CommentList.ContainsKey(comment.CommentID) == false) { doing.CommentList.Add(comment); } } } if (reader.NextResult()) { while (reader.Read()) { Comment comment = new Comment(reader); Doing doing = doings.GetValue(comment.TargetID); if (doing != null) { if (doing.CommentList.ContainsKey(comment.CommentID) == false) { doing.CommentList.Add(comment); } } } } } } db.Connection.Close(); //Doing doing = null; //int lastDoingID = -1; //for (int i = 0; i < comments.Count; i++) //{ // int doingID = comments[i].TargetID; // if (doingID != lastDoingID) // { // doing = doings.GetValue(doingID); // lastDoingID = doingID; // } // doing.CommentList.Add(comments[i]); //} }
public override void SetUserNoAddFeedApps(int userID, UserNoAddFeedAppCollection userNoAddFeedApps) { using (SqlSession db = new SqlSession()) { db.BeginTransaction(); //using (SqlQuery query = db.CreateQuery()) //{ // query.CommandText = "DELETE [bx_UserNoAddFeedApps] WHERE [UserID]=@UserID;"; // query.CreateParameter<int>("@UserID", userID, SqlDbType.Int); // query.ExecuteNonQuery(); //} using (SqlQuery query = db.CreateQuery(QueryMode.Prepare)) { query.CommandText = @" IF EXISTS(SELECT * FROM [bx_UserNoAddFeedApps] WHERE AppID=@AppID AND UserID=@UserID AND ActionType=@ActionType) UPDATE [bx_UserNoAddFeedApps] SET [Send]=@Send WHERE AppID=@AppID AND UserID=@UserID AND ActionType=@ActionType; ELSE INSERT INTO [bx_UserNoAddFeedApps]( [AppID] ,[UserID] ,[ActionType] ,[Send] ) VALUES ( @AppID ,@UserID ,@ActionType ,@Send ); "; foreach (UserNoAddFeedApp userNoAddFeedApp in userNoAddFeedApps) { query.CreateParameter<Guid>("@AppID", userNoAddFeedApp.AppID, SqlDbType.UniqueIdentifier); query.CreateParameter<int>("@ActionType", userNoAddFeedApp.ActionType, SqlDbType.Int); query.CreateParameter<int>("@UserID", userID, SqlDbType.Int); query.CreateParameter<bool>("@Send", userNoAddFeedApp.Send, SqlDbType.Bit); query.ExecuteNonQuery(); } } db.CommitTransaction(); } }
public override DoingCollection GetUserDoingsWithComments(int doingOwnerID, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int? totalCount) { DoingCollection doings = null; using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery()) { query.Pager.TableName = "[bx_Doings]"; query.Pager.SortField = "[DoingID]"; query.Pager.SelectCount = true; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.TotalRecords = totalCount; query.Pager.Condition = "[UserID]=@UserID"; query.CreateParameter<int>("@UserID", doingOwnerID, SqlDbType.Int); //if (dataAccessLevel == DataAccessLevel.Normal) //{ // query.Pager.Condition += " AND [PrivacyType] IN (0, 3)"; //} //else if (dataAccessLevel == DataAccessLevel.Friend) //{ // query.Pager.Condition += " AND [PrivacyType] IN (0, 1, 3)"; //} 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 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; } }
/// <summary> /// 如果UserID为空,则完全删除这一组Feed; /// 如果UserID不为空,则把用户从这些Feed移除。如果某个Feed只关联了这一个用户,则整条Feed被删除 /// </summary> /// <param name="userID"></param> /// <param name="feedIDs"></param> public override void DeleteFeeds(int? userID, IEnumerable<int> feedIDs) { bool emptyFeeds = true; foreach (int id in feedIDs) { emptyFeeds = false; break; } if (emptyFeeds) return; if (userID == null) { using (SqlQuery query = new SqlQuery()) { query.CommandText = @" DELETE [bx_Feeds] WHERE [ID] IN(@FeedIDs); "; query.CreateInParameter("@FeedIDs", feedIDs); query.ExecuteNonQuery(); return; } } using (SqlSession db = new SqlSession()) { db.BeginTransaction(); using (SqlQuery query = db.CreateQuery()) { query.CommandText = "DELETE [bx_UserFeeds] WHERE [UserID]=@UserID AND [FeedID] IN(@FeedIDs);"; query.CreateParameter<int>("@UserID", userID.Value, SqlDbType.Int); query.CreateInParameter("@FeedIDs", feedIDs); query.ExecuteNonQuery(); } using (SqlQuery query = db.CreateQuery(QueryMode.Prepare)) { query.CommandText = @" IF NOT EXISTS(SELECT * FROM [bx_UserFeeds] WHERE [FeedID]=@FeedID) DELETE [bx_Feeds] WHERE [ID] = @FeedID; --以下是删除 类似加好友的动态 DECLARE @FeedCount int; SELECT @FeedCount = COUNT(*) FROM bx_UserFeeds WHERE [FeedID]=@FeedID IF @FeedCount = 1 AND EXISTS(SELECT * FROM bx_Feeds F INNER JOIN bx_UserFeeds UF ON F.[ID]=UF.[FeedID] WHERE F.[ID]=@FeedID AND UF.[UserID]=F.[TargetUserID]) BEGIN DELETE [bx_Feeds] WHERE [ID] = @FeedID; END "; foreach (int feedID in feedIDs) { query.CreateParameter<int>("@FeedID", feedID, SqlDbType.Int); query.ExecuteNonQuery(); } } db.CommitTransaction(); } #region /* using (DbSession db = new DbSession()) { if (userID == null) { db.ExecuteNonQuery(@" DELETE [bx_Feeds] WHERE [ID] IN(@FeedIDs); " , db.ParamReplace("@FeedIDs", StringUtil.Join(feedIDs)) ); return; } StringBuilder sql = new StringBuilder(); sql.Append("DELETE [bx_UserFeeds] WHERE [UserID]=@UserID AND [FeedID] IN(@FeedIDs);"); int i = 0; foreach (int feedID in feedIDs) { string s = db.FormatSql(@" IF NOT EXISTS(SELECT * FROM [bx_UserFeeds] WHERE [FeedID]=@FeedID) DELETE [bx_Feeds] WHERE [ID] = @FeedID; --以下是删除 类似加好友的动态 DECLARE @Feed" + i +@"Count int; SELECT @Feed" + i + @"Count = COUNT(*) FROM bx_UserFeeds WHERE [FeedID]=@FeedID IF @Feed" + i + @"Count = 1 AND EXISTS(SELECT * FROM bx_Feeds F INNER JOIN bx_UserFeeds UF ON F.[ID]=UF.[FeedID] WHERE F.[ID]=@FeedID AND UF.[UserID]=F.[TargetUserID]) BEGIN DELETE [bx_Feeds] WHERE [ID] = @FeedID; END " , db.Param<int>("@FeedID",feedID) ); sql.AppendLine(s); i++; } db.ExecuteNonQuery( sql.ToString() , db.Param<int>("@UserID",userID.Value) , db.ParamReplace("@FeedIDs", StringUtil.Join(feedIDs)) ); } */ #endregion }
/// <summary> /// 保存标签 /// </summary> /// <param name="tags">标签集</param> /// <param name="type">类型,如日志标签等</param> /// <param name="targetID">标签的对象ID</param> public override bool SaveTags(TagCollection tags, TagType type, int targetID) { using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery()) { query.CommandText = @"DELETE FROM [bx_TagRelation] WHERE [Type]=@Type AND [TargetID] = @TargetID;"; query.CreateParameter<int>("@TargetID", targetID, SqlDbType.Int); query.CreateParameter<TagType>("@Type", type, SqlDbType.TinyInt); query.ExecuteNonQuery(); } if (tags == null || tags.Count == 0) return true; using (SqlQuery query = db.CreateQuery(QueryMode.Prepare)) { query.CommandText = @" IF EXISTS (SELECT * FROM [bx_Tags] WHERE [Name] = @TagName) BEGIN DECLARE @TagID int; SET @TagID = (SELECT TOP 1 [ID] FROM [bx_Tags] WHERE [Name] = @TagName); DECLARE @PrivacyType tinyint; IF @Type = 1 SET @PrivacyType = (SELECT [PrivacyType] FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID); ELSE SET @PrivacyType = 0; IF NOT EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID) BEGIN IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID); END END ELSE BEGIN IF EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID) BEGIN IF @PrivacyType <> 0 AND @PrivacyType <> 3 BEGIN DELETE FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID; END END ELSE BEGIN IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID); END END END END ELSE BEGIN DECLARE @NewTagID int; INSERT INTO [bx_Tags]([Name]) VALUES (@TagName); SET @NewTagID = @@IDENTITY; IF @Type = 1 AND EXISTS (SELECT * FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID AND [PrivacyType] IN (0,3)) BEGIN INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@NewTagID, @Type, @TargetID); END END"; foreach (Tag tag in tags) { query.CreateParameter<string>("@TagName", tag.Name, SqlDbType.NVarChar, 50); query.CreateParameter<int>("@TargetID", targetID, SqlDbType.Int); query.CreateParameter<TagType>("@Type", type, SqlDbType.TinyInt); query.ExecuteNonQuery(); } } } return true; }
private void FillDoingComments(DoingCollection doings, SqlSession db) { if (doings.Count == 0) return; List<int> minIds = new List<int>(); List<int> maxIds = new List<int>(); for (int i = 0; i < doings.Count; i++) { if (doings[i].TotalComments == 0) continue; else if (doings[i].TotalComments == 1) minIds.Add(doings[i].DoingID); else { minIds.Add(doings[i].DoingID); maxIds.Add(doings[i].DoingID); } } if (minIds.Count == 0) return; using (SqlQuery query = db.CreateQuery()) { query.CommandText = @" SELECT * FROM bx_Comments WHERE CommentID IN( SELECT Min(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MinTargetIDs) GROUP BY TargetID ); "; if (maxIds.Count > 0) { query.CommandText += @" SELECT * FROM bx_Comments WHERE CommentID IN( SELECT Max(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MaxTargetIDs) GROUP BY TargetID ); "; } query.CreateInParameter<int>("@MinTargetIDs", minIds); query.CreateInParameter<int>("@MaxTargetIDs", maxIds); using (XSqlDataReader reader = query.ExecuteReader()) { while (reader.Read()) { Comment comment = new Comment(reader); Doing doing = doings.GetValue(comment.TargetID); if (doing != null) { if (doing.CommentList.ContainsKey(comment.CommentID) == false) doing.CommentList.Add(comment); } } if (reader.NextResult()) { while (reader.Read()) { Comment comment = new Comment(reader); Doing doing = doings.GetValue(comment.TargetID); if (doing != null) { if (doing.CommentList.ContainsKey(comment.CommentID) == false) doing.CommentList.Add(comment); } } } } } db.Connection.Close(); //Doing doing = null; //int lastDoingID = -1; //for (int i = 0; i < comments.Count; i++) //{ // int doingID = comments[i].TargetID; // if (doingID != lastDoingID) // { // doing = doings.GetValue(doingID); // lastDoingID = doingID; // } // doing.CommentList.Add(comments[i]); //} }
public override DoingCollection GetUserCommentedDoingsWithComments(int userID, 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; query.Pager.Condition = "[DoingID] IN (SELECT [TargetID] FROM [bx_Comments] WHERE [Type]=3 AND [UserID] = @UserID)"; query.CreateParameter<int>("@UserID", userID, SqlDbType.Int); 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 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 DenouncingCollection GetDenouncingWithShare(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.Share; 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); ShareCollection shares = null; using (SqlQuery query = db.CreateQuery()) { query.CommandText = "SELECT * FROM bx_SharesView WHERE ShareID IN (@IDs)"; query.CreateInParameter<int>("@IDs", targetIDs); using (XSqlDataReader reader = query.ExecuteReader()) { shares = new ShareCollection(reader); } } ShareBO.Instance.ProcessKeyword(shares, ProcessKeywordMode.FillOriginalText); for (int i = 0; i < denouncings.Count; i++) { for (int j = 0; j < shares.Count; j++) { if (denouncings[i].TargetID == shares[j].ShareID) { denouncings[i].TargetShare = shares[j]; break; } } } } return denouncings; } }
public override DenouncingCollection GetDenouncingWithUser(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.Space; 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) { UserBO.Instance.WaitForFillSimpleUsers<Denouncing>(denouncings); } return denouncings; } }
/// <summary> /// 保存标签 /// </summary> /// <param name="tags">标签集</param> /// <param name="type">类型,如日志标签等</param> /// <param name="targetID">标签的对象ID</param> public override bool SaveTags(TagCollection tags, TagType type, int targetID) { using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery()) { query.CommandText = @"DELETE FROM [bx_TagRelation] WHERE [Type]=@Type AND [TargetID] = @TargetID;"; query.CreateParameter <int>("@TargetID", targetID, SqlDbType.Int); query.CreateParameter <TagType>("@Type", type, SqlDbType.TinyInt); query.ExecuteNonQuery(); } if (tags == null || tags.Count == 0) { return(true); } using (SqlQuery query = db.CreateQuery(QueryMode.Prepare)) { query.CommandText = @" IF EXISTS (SELECT * FROM [bx_Tags] WHERE [Name] = @TagName) BEGIN DECLARE @TagID int; SET @TagID = (SELECT TOP 1 [ID] FROM [bx_Tags] WHERE [Name] = @TagName); DECLARE @PrivacyType tinyint; IF @Type = 1 SET @PrivacyType = (SELECT [PrivacyType] FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID); ELSE SET @PrivacyType = 0; IF NOT EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID) BEGIN IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID); END END ELSE BEGIN IF EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID) BEGIN IF @PrivacyType <> 0 AND @PrivacyType <> 3 BEGIN DELETE FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID; END END ELSE BEGIN IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID); END END END END ELSE BEGIN DECLARE @NewTagID int; INSERT INTO [bx_Tags]([Name]) VALUES (@TagName); SET @NewTagID = @@IDENTITY; IF @Type = 1 AND EXISTS (SELECT * FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID AND [PrivacyType] IN (0,3)) BEGIN INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@NewTagID, @Type, @TargetID); END END"; foreach (Tag tag in tags) { query.CreateParameter <string>("@TagName", tag.Name, SqlDbType.NVarChar, 50); query.CreateParameter <int>("@TargetID", targetID, SqlDbType.Int); query.CreateParameter <TagType>("@Type", type, SqlDbType.TinyInt); query.ExecuteNonQuery(); } } } return(true); }
public override void DeleteExperisValidateCodeActionRecord(List<string> actions, List<int> limitedTimes) { using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery(QueryMode.Batch)) { StringBuilder sql = new StringBuilder(); for (int i = 0; i < actions.Count; i++) { sql.AppendFormat(@" DELETE [bx_ValidateCodeActionRecords] WHERE [Action] = @Action_{0} AND [CreateDate] < @LimitedTime_{0}; ", i); query.CreateParameter<string>("@Action_" + i, actions[i], SqlDbType.VarChar, 200); query.CreateParameter<DateTime>("@LimitedTime_" + i, DateTimeUtil.Now.AddSeconds(0 - limitedTimes[i]), SqlDbType.DateTime); //query.ExecuteNonQuery(); } //query.Submit(); if (actions.Count > 0) { query.CommandText += "DELETE [bx_ValidateCodeActionRecords] WHERE [Action] NOT IN (@Actions);"; query.CreateInParameter<string>("@Actions", actions); } else query.CommandText += "DELETE [bx_ValidateCodeActionRecords]"; query.ExecuteNonQuery(); } } }
public override DenouncingCollection GetDenouncingWithShare(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.Share; 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); ShareCollection shares = null; using (SqlQuery query = db.CreateQuery()) { query.CommandText = "SELECT * FROM bx_SharesView WHERE ShareID IN (@IDs)"; query.CreateInParameter <int>("@IDs", targetIDs); using (XSqlDataReader reader = query.ExecuteReader()) { shares = new ShareCollection(reader); } } ShareBO.Instance.ProcessKeyword(shares, ProcessKeywordMode.FillOriginalText); for (int i = 0; i < denouncings.Count; i++) { for (int j = 0; j < shares.Count; j++) { if (denouncings[i].TargetID == shares[j].ShareID) { denouncings[i].TargetShare = shares[j]; break; } } } } return(denouncings); } }
public override UserCollection GetUserShows(int userID, int pageNumber, int pageSize, out int userTotalCount) { userTotalCount = 0; UserCollection users = new UserCollection(); using (SqlSession db = new SqlSession()) { using (SqlQuery query = db.CreateQuery()) { query.Pager.TableName = "bx_PointShowUsers"; query.Pager.SortField = "Price"; query.Pager.IsDesc = true; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.SelectCount = true; query.Pager.PrimaryKey = "UserID"; query.CreateParameter<int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { users = new UserCollection(reader); if (reader.NextResult()) { if (reader.Read()) { users.TotalRecords = reader.Get<int>(0); userTotalCount = users.TotalRecords; } } } } return users; } }
public override FeedCollection SearchFeeds(int pageNumber, FeedSearchFilter filter, ref int totalCount) { using (SqlSession db = new SqlSession()) { List<int> feedIDs = new List<int>(); using (SqlQuery query = db.CreateQuery()) { query.Pager.IsDesc = filter.IsDesc; if (filter.UserID != null) { query.Pager.ResultFields = "[FeedID]"; if (filter.Order == FeedSearchFilter.OrderBy.ID) query.Pager.SortField = "[ID]"; else { query.Pager.SortField = "[CreateDate]"; query.Pager.PrimaryKey = "[ID]"; } query.Pager.TableName = "[bx_UserFeeds]"; query.Pager.Condition = @" UserID = @UserID " + (filter.AppID == null ? "" : (@" AND [FeedID] IN(SELECT [ID] FROM [bx_Feeds] WHERE [AppID]=@AppID " + (filter.AppActionType == null ? "" : (" AND [ActionType] = @ActionType)")))) + (filter.BeginDate == null ? "" : (" AND [CreateDate] > @BeginDate")) + (filter.EndDate == null ? "" : (" AND [CreateDate] < @EndDate")); } else { query.Pager.ResultFields = "[ID]"; if (filter.Order == FeedSearchFilter.OrderBy.ID) query.Pager.SortField = "[ID]"; else { query.Pager.SortField = "[CreateDate]"; query.Pager.PrimaryKey = "[ID]"; } query.Pager.TableName = "[bx_Feeds]"; query.Pager.Condition = @" 1 = 1 " + (filter.AppID == null ? "" : (@" AND [AppID]=@AppID ") + (filter.AppActionType == null ? "" : (" AND [ActionType] = @ActionType"))) + (filter.BeginDate == null ? "" : (" AND [CreateDate] > @BeginDate")) + (filter.EndDate == null ? "" : (" AND [CreateDate] < @EndDate")); } query.Pager.PageNumber = pageNumber; query.Pager.PageSize = filter.PageSize; query.Pager.TotalRecords = totalCount; query.Pager.SelectCount = true; query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int); query.CreateParameter<Guid?>("@AppID", filter.AppID, SqlDbType.UniqueIdentifier); query.CreateParameter<int?>("@ActionType", filter.AppActionType, SqlDbType.Int); query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime); query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime); using (XSqlDataReader reader = query.ExecuteReader()) { while (reader.Read()) { feedIDs.Add(reader.GetInt32(0)); } if (reader.NextResult()) { while (reader.Read()) { totalCount = reader.GetInt32(0); } } } } if (feedIDs.Count == 0) return new FeedCollection(); string sql; string sortField = (filter.Order == FeedSearchFilter.OrderBy.ID ? "[ID]" : "[CreateDate]"); string order = (filter.IsDesc ? " DESC " : " ASC "); if (filter.UserID == null) { sql = @" SELECT * FROM [bx_Feeds] WHERE [ID] IN(@FeedIDs) ORDER BY " + sortField + order + @"; SELECT * FROM [bx_UserFeeds] WHERE [FeedID] IN(@FeedIDs) ORDER BY [FeedID] DESC,[CreateDate] DESC; "; } else { sql = @" SELECT * FROM [bx_Feeds] WHERE [ID] IN(@FeedIDs) ORDER BY " + sortField + order + @"; SELECT * FROM [bx_UserFeeds] WHERE [FeedID] IN(@FeedIDs) ORDER BY [FeedID] DESC,[CreateDate] DESC; SELECT * FROM [bx_UserFeeds] WHERE [FeedID] IN(SELECT [FeedID] FROM [bx_UserFeeds] UF INNER JOIN [bx_Feeds] F ON UF.[FeedID]=F.[ID] WHERE F.[ID] IN(@FeedIDs) AND UF.[UserID]=F.[TargetUserID] AND F.[TargetUserID]=@UserID) ORDER BY [CreateDate] DESC; "; } using (SqlQuery query = db.CreateQuery()) { query.CommandText = sql; query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int); query.CreateInParameter<int>("@FeedIDs", feedIDs); using (XSqlDataReader reader = query.ExecuteReader()) { FeedCollection feeds = new FeedCollection(reader); if (reader.NextResult()) { Feed currentFeed = null; while (reader.Read()) { UserFeed userFeed = new UserFeed(reader); currentFeed = ProcessFeed(feeds, currentFeed, userFeed, false); } } if (reader.NextResult())// 这一组是特殊的 处理类似加好友的 如果TargetUserID 是好友 需要把UserFeeds 表里与该动态有关的用户都取出来 { Feed currentFeed = null; while (reader.Read()) { UserFeed userFeed = new UserFeed(reader); currentFeed = ProcessFeed(feeds, currentFeed, userFeed, true); } } return feeds; } } } }