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