public async Task <Quote> Create(Quote quote, Author author, Language language)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
await using var transaction = connection.BeginTransaction();
try
{
var quoteHash = HashGenerator.Sha256(quote.Text);
var quoteIds = await GetQuoteIdsByHash(quoteHash);
if (quoteIds != default)
{
quote.Id = quoteIds.Id;
quote.Uuid = quoteIds.Uuid;
return(quote);
}
var quoteUuid = IdGenerator.Instance.Next();
var quoteText = quote.Text;
var authorId = author.Id;
var languageId = language.Id;
var addedAt = DateTime.UtcNow;
var insertQuoteSql = $@"
INSERT INTO {QuoteSchema.Table}
(
{QuoteSchema.Columns.Uuid},
{QuoteSchema.Columns.Text},
{QuoteSchema.Columns.Hash},
{QuoteSchema.Columns.AuthorId},
{QuoteSchema.Columns.LanguageId},
{QuoteSchema.Columns.AddedAt}
)
VALUES
(
@{nameof(quoteUuid)}, @{nameof(quoteText)}, @{nameof(quoteHash)},
@{nameof(authorId)}, @{nameof(languageId)}, @{nameof(addedAt)}
);
SELECT {QuoteSchema.Columns.Id} FROM {QuoteSchema.Table} WHERE {QuoteSchema.Columns.Uuid} = @{nameof(quoteUuid)};";
var quoteId = await connection.QueryFirstAsync <int>(
insertQuoteSql,
new { quoteUuid, quoteText, quoteHash, authorId, languageId, addedAt },
transaction);
transaction.Commit();
quote.Id = quoteId;
quote.Uuid = quoteUuid;
return(quote);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
return(null);
}
}
public async Task <Author> Create(string name, Language language)
{
await using var connection = ConfigurationExtensions.GetConnection(_configuration);
await using var transaction = connection.BeginTransaction();
try
{
var authorUuid = IdGenerator.Instance.Next();
var insertAuthorSql = $@"
INSERT INTO {AuthorSchema.Table} ({AuthorSchema.Columns.Uuid})
VALUES (@{nameof(authorUuid)});
SELECT LAST_INSERT_ID();";
var authorId = await connection.QueryFirstAsync <int>(insertAuthorSql, new { authorUuid }, transaction);
var languageId = language.Id;
var insertAuthorNameSql = $@"
INSERT INTO {AuthorNameSchema.Table}
(
{AuthorNameSchema.Columns.Name},
{AuthorNameSchema.Columns.AuthorId},
{AuthorNameSchema.Columns.LanguageId}
)
VALUES (@{nameof(name)}, @{nameof(authorId)}, @{nameof(languageId)})";
await connection.ExecuteAsync(insertAuthorNameSql, new { name, authorId, languageId }, transaction);
transaction.Commit();
return(new Author {
Id = authorId, Uuid = authorUuid, Name = name
});
}
catch (Exception)
{
transaction.Rollback();
return(null);
}
}
