public async Task <IEnumerable <CommentUserVote> > GetCommentUserVotesAsync(int commentId, int count)
{
using (var connection = DbConnectionAccessor.CreateConnection())
{
await connection.OpenAsync();
try
{
var dialect = Store.Configuration.SqlDialect;
var sqlBuilder = dialect.CreateBuilder(this.TablePrefix);
sqlBuilder.Select();
sqlBuilder.Table(nameof(CommentUserVote));
sqlBuilder.Selector("*");
if (count > 0)
{
sqlBuilder.Take(count.ToString());
}
sqlBuilder.WhereAnd($"{dialect.QuoteForColumnName("CommentId")} = @CommentId");
return(await connection.QueryAsync <CommentUserVote>(sqlBuilder.ToSqlString(), new { CommentId = commentId }));
}
catch (Exception e)
{
Logger.LogError(e, "An error occurred while reading CommentUserVote records");
throw;
}
}
}
public async Task RemoveCommentUserVotesAsync(int commentId)
{
if (string.IsNullOrEmpty(commentId.ToString()))
{
// Ignore that case, when Update is called on a content item which has not be "created" yet
return; // Task.CompletedTask;
}
try
{
var tablePrefix = this.TablePrefix;
if (!String.IsNullOrEmpty(tablePrefix))
{
tablePrefix += '_';
}
var table = $"{tablePrefix}{nameof(CommentUserVote)}";
using (var connection = DbConnectionAccessor.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
var dialect = Store.Configuration.SqlDialect;
try
{
if (Logger.IsEnabled(LogLevel.Debug))
{
Logger.LogDebug(
$"Removing CommentUserVote for comment: {commentId}");
}
var deleteCmd = $"delete from {dialect.QuoteForTableName(table)} where {dialect.QuoteForColumnName("CommentId")} = {dialect.GetSqlValue(commentId)}";
await transaction.Connection.ExecuteAsync(deleteCmd, null, transaction);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Logger.LogError(e, "An error occurred while removing CommentUserVote for record");
throw;
}
}
}
//return await connection.QueryAsync<CommentRecord>(sqlBuilder.ToSqlString(), new { ContentItemId = contentItemId });
}
catch (Exception e)
{
Logger.LogError(e, "An error occurred while removing CommentUserVote for record");
throw;
}
}
public async Task <IEnumerable <Notification> > GetNotificationsAsync(string correlationId, int count)
{
using (var connection = DbConnectionAccessor.CreateConnection())
{
await connection.OpenAsync();
try
{
var dialect = Store.Configuration.SqlDialect;
var sqlBuilder = dialect.CreateBuilder(this.TablePrefix);
sqlBuilder.Select();
sqlBuilder.Table(nameof(Notification));
sqlBuilder.Selector("*");
if (count > 0)
{
sqlBuilder.Take(count.ToString());
}
if (!string.IsNullOrEmpty(correlationId))
{
sqlBuilder.WhereAnd($"{dialect.QuoteForColumnName("CorrelationId")} = @CorrelationId");
return(await connection.QueryAsync <Notification>(sqlBuilder.ToSqlString(), new { CorrelationId = correlationId }));
}
else
{
return(await connection.QueryAsync <Notification>(sqlBuilder.ToSqlString()));
}
}
catch (Exception e)
{
Logger.LogError(e, "An error occurred while reading Notification records");
throw;
}
}
}