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