public int CreateTeam(Team team, IEnumerable <string> memberEmails) { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var teamId = conn.ExecuteScalar <int>( @" insert into Teams values (@Name, @CreatedBy) declare @teamId int = scope_identity(); insert into TeamMembers values (@teamId, @CreatedBy) select @teamId", team); var teamMembers = memberEmails .Select(email => new { TeamId = teamId, Email = email }); conn.Execute( @" insert into TeamMembers(TeamId, UserId) select @TeamId, Id from Users where email = @email and not exists ( select 1 from TeamMembers tm where tm.UserId = Id and tm.TeamId = @TeamId)" , teamMembers); return(teamId); } }
public void CreateUser(User user) { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var command = "insert into dbo.Users (Username, Email, PasswordHash) values(@Username, @Email, @PasswordHash)"; conn.Execute(command, user); } }
public IEnumerable <User> GetUsers() { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var users = conn.Query <User>("select * from users"); return(users); } }
public int GetCardPoints(int cardId, int userId) { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var points = conn.QueryFirstOrDefault <int>( @"select top 1 Points from Points where CardId = @cardId and PointedBy = @userId order by DateCreated desc", new { cardId, userId }); return(points); } }
public IEnumerable <Team> GetTeamsByUser(int memberUserId) { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var teams = conn.Query <Team>( @"select [Id], [Name], [CreatedBy] from Teams t inner join TeamMembers tm on t.Id = tm.TeamId where tm.UserId = @userId", new { userId = memberUserId }); return(teams); } }
public int CreateCard(Card card) { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var command = @"insert into Cards (Description, CreatedBy, ClosedBy, TeamId) values (@Description, @CreatedBy, @ClosedBy, @TeamId) select scope_identity()"; var id = conn.ExecuteScalar <int>(command, card); return(id); } }
public int CreatePoint(Point point) { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var id = conn.ExecuteScalar <int>( @"insert into Points (PointedBy, CardId, Points) values (@PointedBy, @CardId, @Points) select scope_identity()", point); return(id); } }
public bool DoesCardCreatorExist(int creatorId) { using (var conn = _connectionProvider.GetOpenPointingPokerConnection()) { var query = @" select case when exists( select 1 from Users where Id = @creatorId) then cast(1 as bit) else cast(0 as bit) end as [exists]"; var exists = conn.QueryFirst <bool>(query, new { creatorId }); return(exists); } }