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

siegrainwong · 2019-04-01T14:20:31Z

找不到MultipleActiveResultSets的示例，不知道怎么写。
可能是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()

The text was updated successfully, but these errors were encountered:

RocherKong · 2019-04-02T02:08:52Z

declare @NAMEs table ([name] nvarchar(30))
insert into @NAMEs values
(@T_For__tags_0)
,
(@T_For__tags_1).......

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

siegrainwong · 2019-04-02T03:55:33Z

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

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

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

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

Ahoo-Wang · 2019-04-02T08:02:31Z

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

RocherKong · 2019-04-02T08:09:12Z

异步方法前面统一带上await

siegrainwong · 2019-04-03T08:59:31Z

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

Ahoo-Wang · 2019-04-03T09:03:33Z

@Seanwong933 方便的话可以提供一下 #13

siegrainwong · 2019-04-03T09:11:16Z

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

Ahoo-Wang · 2019-04-03T09:52:13Z

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

OK，感谢。

siegrainwong closed this as completed Apr 3, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

siegrainwong commented Apr 1, 2019

RocherKong commented Apr 2, 2019

siegrainwong commented Apr 2, 2019

Ahoo-Wang commented Apr 2, 2019

RocherKong commented Apr 2, 2019

siegrainwong commented Apr 3, 2019

Ahoo-Wang commented Apr 3, 2019

siegrainwong commented Apr 3, 2019

Ahoo-Wang commented Apr 3, 2019

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

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

Comments

siegrainwong commented Apr 1, 2019

RocherKong commented Apr 2, 2019

siegrainwong commented Apr 2, 2019

Ahoo-Wang commented Apr 2, 2019

RocherKong commented Apr 2, 2019

siegrainwong commented Apr 3, 2019

Ahoo-Wang commented Apr 3, 2019

siegrainwong commented Apr 3, 2019

Ahoo-Wang commented Apr 3, 2019