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
}
