事务中执行多条语句，提示 The connection does not support MultipleActiveResultSets。

[siegrainwong]

1. 找不到MultipleActiveResultSets的示例，不知道怎么写。
2. 可能是MultipleResultMaps？那么我的三条语句分布在三个xml里，写到哪里呢？

代码：

        public async Task<int> InsertAsync(ArticleUpdateParameter parameter)
        {
            try
            {
                _mapper.BeginTransaction();

                var id = await ArticleRepository.InsertAsync(parameter);
                var categoryTask = CategoryRepository.SetArticleCategoriesAsync(articleId, categories);
                var tagTask = TagRepository.SetArticleTagsAsync(articleId, tags);
                await Task.WhenAll(categoryTask, tagTask);

                _mapper.CommitTransaction();
                return id;
            }
            catch (Exception)
            {
                _mapper.RollbackTransaction();
                throw;
            }
        }

日志：

dbug: SmartSql.SmartSqlMapper[0]
      BeginTransaction DbSession.Id:11970789-3f5f-413d-be61-b1baad9c1285
dbug: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare->Statement.Id:[Article.Insert],Sql:
      INSERT INTO Article
            (
            Cover,
            Author,
            Title,
            Content,
            Digest,
            ViewCount,
            CommentCount,
            UpdatedAt,
            CreatedAt,
            IsDeleted,
            Remark
            )
            VALUES
            (
            @Cover,
            @Author,
            @Title,
            @Content,
            @Digest,
            default,
            default,
            @UpdatedAt,
            @CreatedAt,
            default,
            @Remark
            )
            ;Select Scope_Identity();
      Parameters:[Cover=assets/img/write-bg.jpg,Author=1,Title=这是标题,Content=---
title: 这是标题
date: 2018-12-03 00:00
categories:
- 分类1
- 分类2
tags:
  - 标签1
  - 标签2
---

这是内容,Digest=,UpdatedAt=2019/4/1 22:11:23,CreatedAt=2018/12/3 0:00:00,Remark=]
      Sql with parameter value:
      INSERT INTO Article
            (
            Cover,
            Author,
            Title,
            Content,
            Digest,
            ViewCount,
            CommentCount,
            UpdatedAt,
            CreatedAt,
            IsDeleted,
            Remark
            )
            VALUES
            (
            'assets/img/write-bg.jpg',
            1,
            '这是标题',
            '---
title: 这是标题
date: 2018-12-03 00:00
categories:
- 分类1
- 分类2
tags:
  - 标签1
  - 标签2
---

这是内容',
            NULL,
            default,
            default,
            '2019/4/1 22:11:23',
            '2018/12/3 0:00:00',
            default,
            NULL
            )
            ;Select Scope_Identity();
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Category.SetArticleCategories:can not find ParamterName:names!
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Category.SetArticleCategories:can not find ParamterName:names!
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Category.SetArticleCategories:can not find ParamterName:names!
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Category.SetArticleCategories:can not find ParamterName:names!
dbug: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare->Statement.Id:[Category.SetArticleCategories],Sql:
      declare @names table ([name] nvarchar(30))
              insert into @names values
              (@T_For__categories_0)
             ,
              (@T_For__categories_1)

            -- insert only not exists in Category table
            insert INTO Category
            select name, getdate(), getdate(), null, 0 from
            (select name from @names where name not in (select name from Category)) T

            -- rebuild middle-table related data
            delete from ArticleCategories where Article = @articleId
            insert into ArticleCategories select @articleId, Id, getdate(), getdate() from
              (select id from Category where Name in (select name from @names)) T;
            select 1;
      Parameters:[T_For__categories_0=分类1,T_For__categories_1=分类2,articleId=45]
      Sql with parameter value:
      declare @names table ([name] nvarchar(30))
              insert into @names values
              ('分类1')
             ,
              ('分类2')

            -- insert only not exists in Category table
            insert INTO Category
            select name, getdate(), getdate(), null, 0 from
            (select name from @names where name not in (select name from Category)) T

            -- rebuild middle-table related data
            delete from ArticleCategories where Article = 45
            insert into ArticleCategories select 45, Id, getdate(), getdate() from
              (select id from Category where Name in (select name from @names)) T;
            select 1;
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Tag.SetArticleTags:can not find ParamterName:names!
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Tag.SetArticleTags:can not find ParamterName:names!
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Tag.SetArticleTags:can not find ParamterName:names!
warn: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare:StatementKey:Tag.SetArticleTags:can not find ParamterName:names!
dbug: SmartSql.Command.PreparedCommand[0]
      PreparedCommand.Prepare->Statement.Id:[Tag.SetArticleTags],Sql:
      declare @names table ([name] nvarchar(30))
              insert into @names values
              (@T_For__tags_0)
             ,
              (@T_For__tags_1)

            -- insert only not exists in Tag table
            insert INTO Tag
            select name, getdate(), getdate(), null, 0 from
            (select name from @names where name not in (select name from Tag)) T

            -- rebuild middle-table related data
            delete from ArticleTags where Article = @articleId
            insert into ArticleTags select @articleId, Id, getdate(), getdate() from
              (select id from Tag where Name in (select name from @names)) T;
            select 1;
      Parameters:[T_For__tags_0=标签1,T_For__tags_1=标签2,articleId=45]
      Sql with parameter value:
      declare @names table ([name] nvarchar(30))
              insert into @names values
              ('标签1')
             ,
              ('标签2')

            -- insert only not exists in Tag table
            insert INTO Tag
            select name, getdate(), getdate(), null, 0 from
            (select name from @names where name not in (select name from Tag)) T

            -- rebuild middle-table related data
            delete from ArticleTags where Article = 45
            insert into ArticleTags select 45, Id, getdate(), getdate() from
              (select id from Tag where Name in (select name from @names)) T;
            select 1;
fail: SmartSql.SmartSqlMapper[0]
      [null]
dbug: SmartSql.SmartSqlMapper[0]
      RollbackTransaction DbSession.Id:11970789-3f5f-413d-be61-b1baad9c1285
dbug: SmartSql.DbSession.DbConnectionSession[0]
      RollbackTransaction .
dbug: SmartSql.DbSession.DbConnectionSession[0]
      CloseConnection 39184750:WriteDB
dbug: SmartSql.DbSession.DbConnectionSession[0]
      Dispose.
fail: Blog.API.Filters.GlobalExceptionFilter[-2146233079]
      The connection does not support MultipleActiveResultSets.
System.InvalidOperationException: The connection does not support MultipleActiveResultSets.
   at SmartSql.SmartSqlMapper.<>c__DisplayClass42_0`1.<<ExecuteWrapAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at SmartSql.SmartSqlMapper.WrapWithTransactionAsync[T](RequestContext context, Func`2 executeFun)
   at SmartSql.SmartSqlMapper.ExecuteWrapAsync[T](Func`2 execute, RequestContext context, DataSourceChoice sourceChoice)
   at SmartSql.SmartSqlMapper.ExecuteScalarAsync[T](RequestContext context)
   at Blog.Service.ArticleService.InsertAsync(ArticleUpdateParameter parameter) in F:\Projects\siegrain.blog\Blog.Service\ArticleService.cs:line 39
   at Blog.API.Controllers.ArticleController.Insert(ArticleUpdateParameter parameter) in F:\Projects\siegrain.blog\Blog.API\Controllers\ArticleController.cs:line 36
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextExceptionFilterAsync()

[RocherKong]

declare @names table ([name] nvarchar(30)) insert into @names values (@T_Fortags_0) , (@T_Fortags_1).......

这段Sql能执行成功吗？每句Sql后面加上分号试试。

[siegrainwong]

假设完整的业务链是这样的：插入文章-> 插入Tags 跟 Categories -> 完成。

然后根据你的提示我做了以下尝试：

1. 插入文章 -> 插入Tags -> 成功
2. 插入文章 -> 插入Categories -> 成功 （到这里说明SQL应该是没有问题的。）
3. 插入文章 -> 插入 Tags 和 Categories -> 失败，提示：The connection does not support MultipleActiveResultSets
4. 加入分号再执行第 3 步，结果同 3
5. 把第 4 步日志中的带参 SQL 拼接好到查询窗口执行 -> 成功

还有没有可以尝试的方法呢？

[Ahoo-Wang]

@Seanwong933 连接字符串增加 MultipleActiveResultSets=true

[RocherKong]

异步方法前面统一带上await

[siegrainwong]

谢谢帮助，才知道MultipleActiveResultSets是连接字符串的东西。

[Ahoo-Wang]

@Seanwong933 方便的话可以提供一下 https://github.com/Smart-Kit/SmartSql/issues/13

[siegrainwong]

@Ahoo-Wang 我是个人开源项目使用的，可能不符合你们的条件，如果可以的话我在开发完毕后再评论上去。

[Ahoo-Wang]

@Ahoo-Wang 我是个人开源项目使用的，可能不符合你们的条件，如果可以的话我在开发完毕后再评论上去。

OK，感谢。