public async Task <int> CreateCommentAsync(Comment newComment)
{
await base._dBContext.ExecuteTransactionAsync(new Dictionary <string, object>()
{
{
$@"INSERT INTO Comment (ShoutId, UserId, Content)
VALUES ({ newComment.ShoutId.ToString() }, { newComment.UserId.ToString() }, @Content)
",
null
},
{
$@"UPDATE Shout
SET CommentCount = CommentCount + 1
WHERE Id = { newComment.ShoutId }
",
null
}
}, false);
int commentId = await base._dBContext.DbConnection.ExecuteScalarAsync <int>(
DapperHelperQueries.SelectSessionLastInserted("Comment", "id"),
new { Content = newComment.Content }
);
_ = base._dBContext.DbConnection.DisposeAsync();
return(commentId);
}
public async Task <int> CreateReactionAsync(EntityType entityType, int entityId, int userId, short reactionTypeId)
{
string reactionTableName = entityType == EntityType.Shout ? TableNames.ShoutReaction : TableNames.CommentReaction;
string entityTableName = entityType == EntityType.Shout ? TableNames.Shout : TableNames.Comment;
string reactionType = reactionTypeId == ReactionTypeId.Like ?
ReactionTypeColumnNames.Like :
ReactionTypeColumnNames.Dislike;
await base._dBContext.ExecuteTransactionAsync(new Dictionary <string, object>()
{
{
$@"INSERT INTO {reactionTableName} ({entityTableName}Id, UserId, ReactionTypeId)
VALUES ( {entityId.ToString()}, {userId.ToString()}, {reactionTypeId.ToString()} )",
null
},
{
$@"UPDATE {entityTableName}
SET {reactionType} = {reactionType} + 1
WHERE Id = {entityId}",
null
}
}, false);
// Reuse "shoutId" variable to alocate less memory.
entityId = await base._dBContext.DbConnection.ExecuteScalarAsync <int>(DapperHelperQueries.SelectSessionLastInserted(reactionTableName, "id"));
// Do not await.
_ = base._dBContext.DbConnection.DisposeAsync();
return(entityId);
}
public async Task <int> GetLastInsertedUserId()
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return(await base._dBContext.DbConnection.ExecuteScalarAsync <int>(DapperHelperQueries.SelectSessionLastInsertedUserId()));
}
}
public async Task <int> CreateLogAsync(AuditLog auditLog)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return(await base._dBContext.DbConnection.ExecuteAsync(
DapperHelperQueries.CreateNewLog(auditLog.UserId.ToString(), auditLog.EventTypeId),
new { IPv4 = auditLog.IPv4 }
));
}
}
public async Task <bool> UserReactionExistsAsync(int shoutId, int userId)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return(await base._dBContext.DbConnection.ExecuteScalarAsync <int>(
DapperHelperQueries.Exists("ShoutReaction", new string[] { "ShoutId", "UserId" }),
new { Value1 = shoutId, Value2 = userId }
) == 1);
}
}
public async Task <bool> HashtagExists(string hashtag)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return(await base._dBContext.DbConnection.ExecuteScalarAsync <int>(
DapperHelperQueries.Exists("Hashtag", "Content"),
new { Value1 = hashtag }
) == 1);
}
}
public async Task <List <Comment> > GetCommentsByShoutIdAsync(int shoutId, int lastId = 0, short limit = 10)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return((List <Comment>) await base._dBContext.DbConnection.QueryAsync <Comment>(
$@"{DapperHelperQueries.GetCommentQueryWithoutWhere()}
WHERE Comment.ShoutId = { shoutId } AND {DapperHelperQueries.PaginatedQueryDESC( "Comment", lastId, limit )}
"
));
}
}
public async Task <List <Shout> > GetByUserIdAsync(int userId, int lastId = 0, short limit = 10)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return((List <Shout>) await base._dBContext.DbConnection.QueryAsync <Shout>(
$@"{DapperHelperQueries.GetShoutQueryWithoutWhere()}
WHERE Shout.UserId = { userId } AND {DapperHelperQueries.PaginatedQueryDESC( "Shout", lastId, limit )}
"
));
}
}
public async Task <Shout> GetByIdAsync(int shoutId)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return(await base._dBContext.DbConnection.QueryFirstOrDefaultAsync <Shout>(
$@"{DapperHelperQueries.GetShoutQueryWithoutWhere()}
WHERE Id = { shoutId }
"
));
}
}
public async Task <bool> EmailExistsAsync(string email)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return(await base._dBContext.DbConnection.ExecuteScalarAsync <int>(
DapperHelperQueries.Exists("AppUser", "Email"),
new { Value1 = email }
) == 1);
}
}
public async Task <int> CreateAsync(Shout newShout)
{
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return(await base._dBContext.DbConnection.ExecuteScalarAsync <int>(
$@"INSERT INTO Shout (UserId, Content)
VALUES (@UserId, @Content);
{ DapperHelperQueries.SelectSessionLastInsertedShoutId() }
",
new { UserId = newShout.UserId, Content = newShout.Content }
));
}
}
public async Task <List <Shout> > GetFollowingShoutsByUserIdAsync(int userId, int lastId = 0, short limit = 10)
{
throw new NotImplementedException();
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return((List <Shout>) await base._dBContext.DbConnection.QueryAsync <Shout>(
$@"{DapperHelperQueries.GetShoutQueryWithoutWhere()}
WHERE {DapperHelperQueries.PaginatedQueryDESC( "Shout", lastId, limit )}
"
));
}
}
public async Task CreateAsync(AppUser user)
{
await base._dBContext.ExecuteTransactionAsync(new Dictionary <string, object>()
{
{
@"INSERT INTO AppUser (UserName, Email, Password)
VALUES (@UserName, @Email, @Password);
",
new { UserName = user.UserName, Email = user.Email, Password = user.Password }
},
{
$@"INSERT INTO AppUserProfile (UserId)
VALUES ( ( { DapperHelperQueries.SelectSessionLastInsertedUserId() } ) );
",
null
},
{
DapperHelperQueries.CreateNewLog($"( {DapperHelperQueries.SelectSessionLastInsertedUserId()} )", EventTypeId.Register),
new { IPv4 = user.Ipv4 }
}
}, false);
}
public async Task <List <Shout> > GetAsync(ShoutsFilter shoutsFilter, int lastId = 0, short limit = 10)
{
string query = DapperHelperQueries.GetShoutQueryWithoutWhere();
string hashtagClause = "";
if (shoutsFilter?.Hashtags.Length > 0)
{
throw new NotImplementedException();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"IN(
SELECT ShoutId
FROM HashtagShout
INNER JOIN Hashtag
ON HashtagShout.HashtagId = Hashtag.Id
WHERE Hashtag.Content IN(
");
for (int i = 0; i < shoutsFilter.Hashtags.Length; ++i)
{
// TODO: Turn this into a parameterized query.
stringBuilder.Append(shoutsFilter.Hashtags);
if (i < shoutsFilter.Hashtags.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(") ) ");
hashtagClause = stringBuilder.ToString();
}
query += $"WHERE {DapperHelperQueries.PaginatedQuery( "Shout", lastId )} ";
if (shoutsFilter.FilteredBy == FilterType.MostComments)
{
query += $@"AND Id IN(
SELECT ShoutId
FROM Comment
{( shoutsFilter.Hashtags.Length > 0 ? $"WHERE ShoutId {hashtagClause}" : "" )}
GROUP BY ShoutId
ORDER BY COUNT(ShoutId) DESC
LIMIT {limit}
)
";
}
else
{
query += $"{( shoutsFilter.Hashtags.Length > 0 ? $"AND Id {hashtagClause}" : "" )}";
query += " ORDER BY " + shoutsFilter.FilteredBy.Switch(new Dictionary <FilterType, Func <string> >()
{
{ FilterType.Top, () => "Shout.LikesCount, Shout.CreateDate DESC" },
{ FilterType.Last, () => "Shout.CreateDate DESC" }
},
() => "ORDER BY Shout.LikesCount DESC"
);
query += $" LIMIT {limit}";
}
await base._dBContext.OpenDBConnectionAsync();
using (base._dBContext.DbConnection)
{
return((List <Shout>) await base._dBContext.DbConnection.QueryAsync <Shout>(query));
}
}