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(); } }
/// <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 }