C# DbConnection.ExecuteAsync方法代码示例

        public override async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
        {
            var dialect = SqlDialectFactory.For(connection);
            var type = Index.GetType();
            var documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);

            if (Index is MapIndex)
            {
                var sql = Inserts(type) + $" {dialect.IdentitySelectString} id";
                Index.Id = await connection.ExecuteScalarAsync<int>(sql, Index, transaction);
                await connection.ExecuteAsync($"update [{_tablePrefix}{type.Name}] set DocumentId = @mapid where Id = @Id", new { mapid = Index.GetAddedDocuments().Single().Id, Id = Index.Id }, transaction);
            }
            else
            {
                var reduceIndex = Index as ReduceIndex;

                var sql = Inserts(type) + $"; {dialect.IdentitySelectString} id";
                Index.Id = await connection.ExecuteScalarAsync<int>(sql, Index, transaction);

                var bridgeTableName = type.Name + "_" + documentTable;
                var columnList = $"[{type.Name}Id], [DocumentId]";
                var parameterList = $"@Id, @DocumentId";
                var bridgeSql = $"insert into [{_tablePrefix}{bridgeTableName}] ({columnList}) values ({parameterList});";

                await connection.ExecuteAsync(bridgeSql, _addedDocumentIds.Select(x => new { DocumentId = x, Id = Index.Id }), transaction);
            }
        }

        public override async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
        {
            var name = Index.GetType().Name;

            var bridgeTableName = name + "_Document";
            var bridgeSql = $"delete from [{_tablePrefix}{bridgeTableName}] where {name}Id = @id";

            await connection.ExecuteAsync(bridgeSql, new { Id = Index.Id }, transaction);

            await connection.ExecuteAsync($"delete from [{_tablePrefix}{name}] where Id = @Id", new { Id = Index.Id }, transaction);
        }

        public override async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
        {
            var name = Index.GetType().Name;

            var documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);
            var bridgeTableName = name + "_" + documentTable;
            var bridgeSql = $"delete from [{_tablePrefix}{bridgeTableName}] where {name}Id = @Id";

            await connection.ExecuteAsync(bridgeSql, new { Id = Index.Id }, transaction);

            await connection.ExecuteAsync($"delete from [{_tablePrefix}{name}] where Id = @Id", new { Id = Index.Id }, transaction);
        }

        public override async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
        {
            var type = Index.GetType();

            var sql = Updates(type);
            await connection.ExecuteAsync(sql, Index, transaction);

            // Update the documents list
            var reduceIndex = Index as ReduceIndex;
            if (reduceIndex != null)
            {
                var documentTable = CollectionHelper.Current.GetPrefixedName(Store.DocumentTable);
                var bridgeTableName = type.Name + "_" + documentTable;
                var columnList = $"[{type.Name}Id], [DocumentId]";
                var parameterList = $"@Id, @DocumentId";
                var bridgeSqlAdd = $"insert into [{_tablePrefix}{bridgeTableName}] ({columnList}) values ({parameterList});";
                var bridgeSqlRemove = $"delete from [{_tablePrefix}{bridgeTableName}] where DocumentId = @DocumentId and {type.Name}Id = @Id;";

                await connection.ExecuteAsync(bridgeSqlAdd, _addedDocumentIds.Select(x => new { DocumentId = x, Id = Index.Id }), transaction);
                await connection.ExecuteAsync(bridgeSqlRemove, _deletedDocumentIds.Select(x => new { DocumentId = x, Id = Index.Id }), transaction);
            }
        }

        private async Task UpdateUserSocialsAsync(DbConnection context, DbTransaction transaction, User user)
        {
            var userSocials = await context.QueryAsync<UserSocial>("SELECT * FROM UserSocials WHERE Username = @Username",
                new { user.Username }, transaction);

            await context.ExecuteAsync("DELETE FROM UserSocials WHERE Username = @Username and SocialId = @SocialId;",
                userSocials.Where(
                    u => user.Socials.All(s => u.SocialId != s.SocialId)),
                transaction);

            await context.ExecuteAsync(
                "UPDATE UserSocials set Site = @Site, Url = @Url WHERE Username = @Username and SocialId = @SocialId;",
                user.Socials, transaction);

            await context.ExecuteAsync("INSERT UserSocials(Username,Site,Url) VALUES(@Username,@Site,@Url);",
                user.Socials.Where(
                    s => userSocials.All(u => s.Username != u.Username && u.SocialId != s.SocialId)).Select(s => new { user.Username, s.Site, s.Url }),
                transaction);
        }

 public virtual async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
 {
     await connection.ExecuteAsync($"delete from [{_tablePrefix}{_indexType.Name}] where DocumentId = @Id", new { Id = _documentId }, transaction);
 }

        private async Task UpdateBreweryMembersAsync(DbConnection context, DbTransaction transaction, Brewery brewery)
        {
            var breweryMembers = await context.QueryAsync<BreweryMember>(
                "SELECT * FROM BreweryMembers WHERE BreweryId = @BreweryId", new { brewery.BreweryId }, transaction);

            await context.ExecuteAsync("DELETE FROM BreweryMembers WHERE BreweryId = @BreweryId and MemberUsername = @MemberUsername;",
                breweryMembers.Where(bm => brewery.Members.All(m => bm.MemberUsername != m.MemberUsername)).Select(bm => new { brewery.BreweryId, bm.MemberUsername }), transaction);

            await context.ExecuteAsync(
                "UPDATE BreweryMembers set Role = @Role, Confirmed = @Confirmed WHERE BreweryId = @BreweryId and MemberUsername = @MemberUsername;",
                brewery.Members.Where(m => breweryMembers.Any(bm => m.MemberUsername == bm.MemberUsername)), transaction);

            await context.ExecuteAsync("INSERT BreweryMembers(BreweryId,MemberUsername,Role,Confirmed) VALUES(@BreweryId,@MemberUsername,@Role,@Confirmed);",
                brewery.Members.Where(m => breweryMembers.All(bm => m.MemberUsername != bm.MemberUsername)).Select(bm => new { brewery.BreweryId, bm.MemberUsername,bm.Role,bm.Confirmed }), transaction);

        }

        private async Task UpdateBrewerySocialsAsync(DbConnection context, DbTransaction transaction, Brewery brewery)
        {
            var brewerySocials = await context.QueryAsync<BrewerySocial>("SELECT * FROM BrewerySocials WHERE BreweryId = @BreweryId",
                new { brewery.BreweryId }, transaction);

            await context.ExecuteAsync("DELETE FROM BrewerySocials WHERE BreweryId = @BreweryId and SocialId = @SocialId;",
                brewerySocials.Where(
                    u => brewery.Socials.All(s => u.SocialId != s.SocialId)),
                transaction);

            await context.ExecuteAsync(
                "UPDATE BrewerySocials set Site = @Site, Url = @Url WHERE BreweryId = @BreweryId and SocialId = @SocialId;",
                brewery.Socials, transaction);

            await context.ExecuteAsync("INSERT BrewerySocials(BreweryId,Site,Url) VALUES(@BreweryId,@Site,@Url);",
                brewery.Socials.Where(
                    s => brewerySocials.All(u => u.SocialId != s.SocialId)).Select(s => new { brewery.BreweryId, s.Site, s.Url }),
                transaction);
        }

        private async Task UpdateAromaWheelAsync(DbConnection context, SqlTransaction transaction, Hop hop)
        {
            var aromaWheels = (await context.QueryAsync<HopFlavour>(@"SELECT * FROM HopAromaWheels WHERE HopId = @HopId", new { hop.HopId },
                transaction)).ToList();

            var toDelete = aromaWheels.Where(h => hop.AromaWheel.All(f => f.FlavourId != h.FlavourId));
            await context.ExecuteAsync("DELETE FROM HopAromaWheels WHERE HopId = @HopId and FlavourId = @FlavourId;",
                toDelete.Select(h => new { h.HopId, h.FlavourId }), transaction);

            var toAdd = hop.AromaWheel.Where(h => aromaWheels.All(f => f.FlavourId != h.FlavourId));
            await context.ExecuteAsync(@"INSERT HopAromaWheels(FlavourId, HopId) VALUES(@FlavourId,@HopId);", toAdd.Select(h => new { h.HopId, h.FlavourId }), transaction);

        }

        private async Task UpdateHopSubstituteAsync(DbConnection context, SqlTransaction transaction, Hop hop)
        {
            var hopSubstitutes = (await context.QueryAsync<Substitute>(@"SELECT * FROM Substitute WHERE HopId = @HopId",
                new { hop.HopId }, transaction)).ToList();

            var toDelete = hopSubstitutes.Where(h => hop.Substituts.All(s => s.HopId != h.HopId && h.SubstituteId != s.HopId));
            await context.ExecuteAsync("DELETE FROM Substitute WHERE HopId = @HopId", toDelete, transaction);

            var toAdd = hop.Substituts.Where(h => hopSubstitutes.All(s => s.HopId != h.HopId && h.HopId != s.SubstituteId)).Select(c => new Substitute { HopId = hop.HopId, SubstituteId = c.HopId });
            await context.ExecuteAsync(@"INSERT Substitute(SubstituteId, HopId) VALUES(@SubstituteId,@HopId);", toAdd, transaction);
        }

 public override async Task ExecuteAsync(DbConnection connection, DbTransaction transaction)
 {
     var dialect = SqlDialectFactory.For(connection);
     var deleteCmd = $"delete from [{_tablePrefix}Document] where [Id] = @Id;";
     await connection.ExecuteAsync(deleteCmd, Document, transaction);
 }