public bool CreateUserBoard(long userId, long boardid, UserBoardRelations ubRelation)
{
var ownerId = this.GetBoardOwnerId(boardid);
if (userId == ownerId) return false;
if (ownerId != 0 && ubRelation == UserBoardRelations.Owner) return false;
if (ownerId == 0 && ubRelation != UserBoardRelations.Owner) return false;
var relation = RelationString[(int)ubRelation];
string mergeQuery =
@"MERGE userboards
USING (VALUES (@userid,@boardid, @relation) ) AS source(userid, boardid, relation)
ON
userboards.userid = source.userid AND userboards.boardid = source.boardid
WHEN MATCHED THEN
UPDATE SET relation = source.relation, timestamp = GETDATE()
WHEN NOT MATCHED THEN
INSERT(userid, boardid, relation) VALUES(source.userid, source.boardid, source.relation);";
return dbContext.Execute(mergeQuery, new
{
userid = userId,
boardid = boardid,
relation = relation
});
}
public IEnumerable<long> GetUserBoardsIds(long userId, UserBoardRelations ubRelation)
{
var relation = RelationString[(int)ubRelation];
var query = string.Format("select boardid from userboards where userid = {0} and relation = '{1}'", userId, relation);
return dbContext.Query<long>(query);
}
public IEnumerable<Board> GetUserBoards(long userId, UserBoardRelations ubRelation)
{
var query = string.Format("select * from boards where id in (SELECT boardid from userboards where userid = {0} and relation = '{1}')",
userId,
RelationString[(int)ubRelation]);
return dbContext.Query<Board>(query);
}
