首页 > SqlSugar

SqlServer开启了错误的数据库事务[疑似BUG] 返回

SqlSugar 沟通中

 5  456

C.J 发布于2024/8/25

悬赏：0 飞吻

SqlSugar 版本：5.1.4.168-preview03

开发环境：.NET CORE 8

IDE: Rider

数据库: SqlServer 2012

代码：

这是我的多数据源配置：

"Datasource": [
  {
    "Key": "Main",
    "Type": "MSSQL",
    "Connection": "Server=43.137.40.151,16436;Database=Fast_Core;Uid=Sa;Pwd=151@#ddaqws2a3;",
    "ShowSql": true
  },
  {
    "Key": "Local",
    "Type": "MSSQL",
    "Connection": "Server=192.168.0.128,1433;Database=Fast_Core;Uid=Sa;Pwd=P@ssw0rd;",
    "ShowSql": true
  }
],

这是SqlSugar全局上下文注册

using Autofac;
using Core.Persistence.Config;
using log4net;
using Microsoft.Extensions.Configuration;
using SqlSugar;
namespace Core.Persistence;
public static class SqlSugarExtensions
{
    private static ILog Log = LogManager.GetLogger("SqlSugarLog");
    private static ILog SqlLog = LogManager.GetLogger("SqlSugarLog.SQL");
    public static void AddPersistence(this ContainerBuilder builder, IConfiguration configuration)
    {
        var dataSourceConfigs = new List<DataSourceConfig>();
        configuration.GetSection("Datasource").Bind(dataSourceConfigs);
        List<ConnectionConfig> connectionConfigs = new List<ConnectionConfig>();
        foreach (var dataSourceConfig in dataSourceConfigs)
        {
            ConnectionConfig connectionConfig = new ConnectionConfig();
            if (dataSourceConfig.Type.Equals("MSSQL"))
            {
                connectionConfig.DbType = DbType.SqlServer;
            }
            else
            {
                throw new NotSupportedException($"不支持的数据库{dataSourceConfig.Type}");
            }
            //多数据源配置
            connectionConfig.ConfigId = dataSourceConfig.Key;
            connectionConfig.ConnectionString = dataSourceConfig.Connection;
            connectionConfig.IsAutoCloseConnection = true;
            connectionConfigs.Add(connectionConfig);
        }
        builder.Register(r =>
        {
            return new SqlSugarScope(connectionConfigs,
                db =>
                {
                    //(A)全局生效配置点，一般AOP和程序启动的配置扔这里面 ，所有上下文生效
                    //调试SQL事件，可以删掉
                    // db.Aop.OnLogExecuting = (sql, pars) =>
                    // {
                    //     //获取原生SQL推荐 5.1.4.63  性能OK
                    //
                    //     SqlLog.Info(UtilMethods.GetNativeSql(sql, pars));
                    //     //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
                    //     //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))
                    // };
                    foreach (var o in connectionConfigs.Select(x => x.ConfigId))
                    {
                        db.GetConnection(o).Aop.OnLogExecuting = (sql, p) =>
                        {
                            SqlLog.Info($"数据库 {o} Exec Sql:{sql} ===>> Params:{p}");
                        };
                    }
                    
                    //多个配置就写下面
                    db.Ado.IsDisableMasterSlaveSeparation = true;
                    //注意多租户 有几个设置几个
                    //db.GetConnection(i).Aop
                });
        }).SingleInstance().PropertiesAutowired();
    }
}

Bug描述：

Key为Main的数据库是连接正常的。

Key为Local的数据库是无法连接的，是离线的数据库。

目前在项目中没有使用到Local的数据库。

报错代码：

public async Task AddRole(RoleReq addRoleReq)
{
    if (string.IsNullOrWhiteSpace(addRoleReq.RoleCode))
    {
        throw new BusinessException("角色代码不能为空");
    }
    if (string.IsNullOrWhiteSpace(addRoleReq.RoleName))
    {
        throw new BusinessException("角色名称不能为空");
    }
    
    if (addRoleReq.RoleCode.Equals(Role.FINAL_ROLE))
    {
        throw new BusinessException($"不能添加 {Role.FINAL_ROLE} ");
    }
    
    Role role = mapper.Map<RoleReq, Role>(addRoleReq);
    List<RolePermission> rolePermissions = new List<RolePermission>();
    
    var trs = sqlSugarScope.UseTran();
    int roleId =  await sqlSugarScope.Insertable<Role>(role).ExecuteReturnIdentityAsync();
    foreach (var id in addRoleReq.PermissionIds)
    {
        rolePermissions.Add(new RolePermission()
        {
            RoleId = roleId,
            PermissionId = id
        });
    }
    
    await sqlSugarScope.Insertable<RolePermission>(rolePermissions).ExecuteCommandAsync();
    
    trs.CommitTran();
}

报错在

var trs = sqlSugarScope.UseTran();

这一行，报错内容为：

SqlSugar.SqlSugarException: 中文提示 : 连接数据库过程中发生错误，检查服务器是否正常连接字符串是否正确，错误信息：A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - 由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败。)DbType="SqlServer";ConfigId="Local".

English Message : Connection open error . A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - 由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败。)DbType="SqlServer";ConfigId="Local"

at SqlSugar.Check.Exception(Boolean isException, String message, String[] args)

at SqlSugar.AdoProvider.CheckConnection()

at SqlSugar.AdoProvider.BeginTran()

at SqlSugar.SqlSugarClient.<>c.<BeginTran>b__222_0(ISqlSugarClient it)

at SqlSugar.SqlSugarClient.AllClientEach(Action`1 action)

at SqlSugar.SqlSugarClient.BeginTran()

at SqlSugar.SqlSugarTransaction..ctor(SqlSugarClient client)

at SqlSugar.SqlSugarClient.UseTran()

at SqlSugar.SqlSugarScope.UseTran()

at Sys.Service.RoleMgrImpl.AddRole(RoleReq addRoleReq) in D:\Project\NanXiang\FastNetCore\Sys.Service\Role\RoleMgrImpl.cs:line 247

这里提示连接Local失败？可是我根本没有使用Local，我调试查看

sqlSugarScope

Connection里面的信息是Main数据库的连接信息，可是报错信息的确是Local的；

这是一些完整代码：

using AutoMapper;
using Core.Entity.Enum;
using Core.Service;
using Core.Service.Exception;
using Core.WebApi.Storage;
using Microsoft.AspNetCore.Components;
using NHibernate.Mapping;
using SqlSugar;
using Sys.Entity;
using Sys.Service.Model;
using Sys.Service.Model.Req;
using Role = Sys.Entity.Role;

namespace Sys.Service
{
    public class RoleMgrImpl : BaseMgr, IRoleMgr
    {
        public SqlSugarScope sqlSugarScope { get; set; }
        public IMapper mapper { get; set; }

        /// <summary>
        /// 查询ById
        /// </summary>
        public async Task<Role> GetRoleAsync(int id)
        {
            return await sqlSugarScope.Queryable<Role>().Where(x => x.Id == id).FirstAsync();
        }

        /// <summary>
        /// 获取所有
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<Role>> GetAllRolesAsync()
        {
            return await sqlSugarScope.Queryable<Role>().ToListAsync();
        }

        /// <summary>
        /// 创建
        /// </summary>
        /// <returns></returns>
        public async Task<Role> CreateRoleAsync(Role role)
        {
            if (string.IsNullOrWhiteSpace(role.RoleCode))
            {
                throw new BusinessException($" 角色代码 Can't Be Null !");
            }

            if (string.IsNullOrWhiteSpace(role.RoleName))
            {
                throw new BusinessException($" 角色名称 Can't Be Null !");
            }


            await sqlSugarScope.Insertable<Role>(role).ExecuteCommandAsync();
            return role;
        }

        /// <summary>
        /// 更新信息
        /// </summary>
        /// <returns></returns>
        public async Task<Role> UpdateRoleAsync(Role role)
        {
            if (string.IsNullOrWhiteSpace(role.RoleCode))
            {
                throw new BusinessException($" 角色代码 Can't Be Null !");
            }

            if (string.IsNullOrWhiteSpace(role.RoleName))
            {
                throw new BusinessException($" 角色名称 Can't Be Null !");
            }


            await sqlSugarScope.Updateable<Role>(role).ExecuteCommandAsync();

            return role;
        }


        /// <summary>
        /// 删除信息
        /// </summary>
        /// <returns></returns>
        public async Task<Role> DeleteRoleAsync(int id)
        {
            throw new NotImplementedException();
        }

        public async Task<IList<Role>> GetUserHasRoleAsync(User user)
        {
            if (user == null)
            {
                throw new BusinessException("100002");
            }

            // return await genericMgr.FindByHqlAsync<Role>(@"from Role  r where EXISTS(SELECT  1 from UserRole  sur where r.Id = sur.RoleId and sur.UserId = ?)", user.Id);
            return await sqlSugarScope.Queryable<Role>().Where(x => SqlFunc.Subqueryable<UserRole>().Where(ur => ur.RoleId == x.Id && ur.UserId == user.Id).Any()).ToListAsync();
        }

        public Task<RespDetailModel> SwitchRole(string roleCode)
        {
            throw new NotImplementedException();
        }


        public async Task SetUserRoles(UserStorage storage, SetUserRolesReq setUserRolesReq)
        {
            if (string.IsNullOrWhiteSpace(setUserRolesReq.UserName))
            {
                throw new NullReferenceException($"用户名不能为空");
            }

            if (!setUserRolesReq.RoleIds.Any())
            {
                throw new BusinessException("至少有一个角色");
            }

            if (setUserRolesReq.RoleIds.All(x => x != storage.CurrentRole.Id))
            {
                throw new BusinessException("不能取消当前角色");
            }


            //刪除
            // await genericMgr.DeleteByHqlAsync($"from UserRole ur where ur.UserId = ? and ur.RoleId not in ({string.Join(",", setUserRolesReq.roleIds)})",storage.Id);


            using (var trans = sqlSugarScope.Ado.UseTran())
            {
                List<Role> roles = await sqlSugarScope.Queryable<Role>().LeftJoin<UserRole>((o, ur) => o.Id == ur.RoleId)
                    .Where((o, ur)
                        => ur.UserId == storage.Id)
                    .Select((o, ur) => o).ToListAsync();
                // await sqlSugarScope.Deleteable<UserRole>().Where(x => !setUserRolesReq.roleIds.Contains(x.RoleId)).ExecuteCommandAsync();

                List<Role> deleteRoles = roles.Where(x => !setUserRolesReq.RoleIds.Contains(x.Id)).ToList();

                List<UserRole> newRoleIds = new List<UserRole>();

                foreach (var id in setUserRolesReq.RoleIds)
                {
                    if (roles.All(x => x.Id != id))
                    {
                        newRoleIds.Add(new UserRole()
                        {
                            RoleId = id,
                            UserId = storage.Id
                        });
                    }
                }

                await sqlSugarScope.Deleteable<UserRole>().Where(x => deleteRoles.Any(j => j.Id.Equals(x.RoleId))
                                                                      && x.UserId == storage.Id).ExecuteCommandAsync();
                await sqlSugarScope.Insertable<UserRole>(newRoleIds).ExecuteCommandAsync();

                trans.CommitTran();
            }
        }

        public async Task UpdateRole(RoleReq RoleReq)
        {
            if (string.IsNullOrWhiteSpace(RoleReq.RoleCode))
            {
                throw new BusinessException("角色代码不能为空");
            }

            if (string.IsNullOrWhiteSpace(RoleReq.RoleName))
            {
                throw new BusinessException("角色名称不能为空");
            }

            Role role = await sqlSugarScope.Queryable<Role>().Where(x => x.RoleCode.Equals(RoleReq.RoleCode)).FirstAsync();

            if (role == null)
            {
                throw new BusinessException("Role Can't Be Null");
            }

            if (Role.FINAL_ROLE.Equals(role.RoleCode))
            {
                throw new BusinessException($"不能修改 {role.RoleCode} ");
            }

            role.RoleName = RoleReq.RoleName;

            role.Enable = RoleReq.Enable ? RoleType.启用 : RoleType.禁用;

            var trs = sqlSugarScope.Ado.UseTran();
            //分配权限
            if (!RoleReq.PermissionIds.Any())
            {
                await sqlSugarScope.Deleteable<RolePermission>().Where(x => x.RoleId == role.Id).ExecuteCommandAsync();
            }
            else
            {
                var permissions = await sqlSugarScope.Queryable<Permission>()
                    .LeftJoin<RolePermission>((p, rp) => p.Id == rp.PermissionId)
                    .Where((p, rp) => rp.RoleId == role.Id)
                    .ToListAsync();
                var deletePermission = permissions.Where(x => !RoleReq.PermissionIds.Contains(x.Id)).ToList();

                var newPermission = new List<RolePermission>();

                foreach (var id in RoleReq.PermissionIds)
                {
                    if (permissions.All(x => x.Id != id))
                    {
                        newPermission.Add(new RolePermission()
                        {
                            PermissionId = id,
                            RoleId = role.Id
                        });
                    }
                }

                await sqlSugarScope.Deleteable<RolePermission>().Where(x => deletePermission.Any(y =>
                    y.Id.Equals(x.PermissionId) && x.RoleId == role.Id)).ExecuteCommandAsync();
                await sqlSugarScope.Insertable<RolePermission>(newPermission).ExecuteCommandAsync();
                trs.CommitTran();
            }
        }

        public async Task AddRole(RoleReq addRoleReq)
        {
            if (string.IsNullOrWhiteSpace(addRoleReq.RoleCode))
            {
                throw new BusinessException("角色代码不能为空");
            }

            if (string.IsNullOrWhiteSpace(addRoleReq.RoleName))
            {
                throw new BusinessException("角色名称不能为空");
            }
            

            if (addRoleReq.RoleCode.Equals(Role.FINAL_ROLE))
            {
                throw new BusinessException($"不能添加 {Role.FINAL_ROLE} ");
            }
            
            Role role = mapper.Map<RoleReq, Role>(addRoleReq);

            List<RolePermission> rolePermissions = new List<RolePermission>();
            
            var trs = sqlSugarScope.UseTran();

            int roleId =  await sqlSugarScope.Insertable<Role>(role).ExecuteReturnIdentityAsync();

            foreach (var id in addRoleReq.PermissionIds)
            {
                rolePermissions.Add(new RolePermission()
                {
                    RoleId = roleId,
                    PermissionId = id
                });
            }
            
            await sqlSugarScope.Insertable<RolePermission>(rolePermissions).ExecuteCommandAsync();
            
            trs.CommitTran();

        }

        public async Task DeleteRole(int id)
        {
            Role role = await sqlSugarScope.Queryable<Role>().Where(x => x.Id == id).FirstAsync();
            if (role.RoleCode.Equals(Role.FINAL_ROLE))
            {
                throw new BusinessException("不能删除超级管理员");
            }
            var tas = sqlSugarScope.UseTran();
            await sqlSugarScope.Deleteable<Role>(role).ExecuteCommandAsync();
            await sqlSugarScope.Deleteable<RolePermission>().Where(x => x.RoleId == role.Id).ExecuteCommandAsync();
            await sqlSugarScope.Deleteable<UserRole>().Where(x => x.RoleId == role.Id).ExecuteCommandAsync();
            tas.CommitTran();;
        }
    }
}

using System.Text;
using AutoMapper;
using Core.Entity.Enum;
using Core.WebApi.Authorization;
using Core.WebApi.Base;
using Core.WebApi.Storage;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using Sys.Entity;
using Sys.Service;
using Sys.Service.Model;
using Sys.Service.Model.Req;
namespace Sys.WebApi.Controller;
[ApiController]
[Route("[controller]")]
public class RoleController : BaseController
{
    public IUserMgr userMgr { get; set; }
    public IMapper mapper { get; set; }
    public IRoleMgr roleMgr { get; set; }
    public IPermissionMgr permissionMgr { get; set; }
    public SqlSugarScope sqlSugarScope { get; set; }
    [HttpPost("[action]")]
    public async Task<IActionResult> SetUserRoles(SetUserRolesReq setUserRolesReq)
    {
        UserStorage user = HttpContext.GetUser();
        await roleMgr.SetUserRoles(user, setUserRolesReq);
        return Ok("分配成功");
    }
    [HttpGet("[action]")]
    public async Task<IActionResult> QueryRole(string? RoleName, string? RoleCode, bool? Enable, int PageSize, int PageNo)
    {
        UserStorage user = HttpContext.GetUser();
        if (PageSize == 0)
        {
            PageSize = DEFAULT_PER_PAGE;
        }
        if (PageNo == 0)
        {
            PageNo = DEFAULT_PAGE_NO;
        }
        var conModels = new List<IConditionalModel>();
        if (!string.IsNullOrWhiteSpace(RoleName))
        {
            conModels.Add(new ConditionalModel
            {
                FieldName = "RoleName",
                ConditionalType = ConditionalType.Like,
                FieldValue = RoleName
            });
        }
        if (!string.IsNullOrWhiteSpace(RoleCode))
        {
            conModels.Add(new ConditionalModel
            {
                FieldName = "RoleCode",
                ConditionalType = ConditionalType.Like,
                FieldValue = RoleCode
            });
        }
        Enable = Enable ?? true;
        conModels.Add(new ConditionalModel
        {
            FieldName = "Enable",
            ConditionalType = ConditionalType.Equal,
            FieldValue = Enable.ToString()
        });
        RefAsync<int> total = 0;
        // var pageData = await genericMgr.FindByHqlAsync<Role>(statementStringBuilder.ToString(), parms.ToArray(), pageSize * (pageNo - 1), pageSize);
        // total = pageData.Count;
        var pageList = await sqlSugarScope.Queryable<Role>().Includes(x => x.PermissionIds).Where(conModels)
            .OrderBy(x => x.Id)
            .ToPageListAsync(PageNo, PageSize, total);
        
        var pageData = mapper.Map<List<RespRoleModel>>(pageList);
        return Ok(new { pageData, total = total.Value });
    }
    [HttpGet("[action]")]
    public async Task<IActionResult> GetAllRole(int enable)
    {
        IList<Role> roles = await sqlSugarScope.Queryable<Role>().Where(x => x.Enable == RoleType.启用).ToListAsync();
        return Ok(roles);
    }
    
    [HttpPost("[action]")]
    public async Task<IActionResult> UpdateRole(RoleReq roleReq)
    {
        await roleMgr.UpdateRole(roleReq);
        return Ok("更新成功");
    }
       
    [HttpPost("[action]")]
    public async Task<IActionResult> AddRole(RoleReq roleReq)
    {
        await roleMgr.AddRole(roleReq);
        return Ok("添加成功");
    }
         
    [HttpPost("[action]/{id:int}")]
    public async Task<IActionResult> DeleteRole(int id)
    {
        await roleMgr.DeleteRole(id);
        return Ok("删除成功");
    }
}

using System;
using System.Linq;
using System.Text;
using Core.Entity.Enum;
using SqlSugar;
namespace Sys.Entity
{
    ///<summary>
    ///
    ///</summary>
    [SugarTable("Sys_Role")]
    public partial class Role
    {
        public static string FINAL_ROLE = "SUPER_ADMIN";
           public Role(){
           }
           /// <summary>
           /// Desc:
           /// Default:
           /// Nullable:False
           /// </summary>           
           [SugarColumn(IsPrimaryKey=true,IsIdentity=true)]
           public int Id {get;set;}
           /// <summary>
           /// Desc:角色代码
           /// Default:
           /// Nullable:False
           /// </summary>           
           public string RoleCode {get;set;} = null!;
           /// <summary>
           /// Desc:角色名称
           /// Default:
           /// Nullable:False
           /// </summary>           
           public string RoleName {get;set;} = null!;
           /// <summary>
           /// Desc:1启动 0禁用
           /// Default:
           /// Nullable:False
           /// </summary>           
           public RoleType Enable {get;set;}
           [Navigate(typeof(RolePermission),  nameof(RolePermission.RoleId),nameof(RolePermission.PermissionId))]//注意顺序
           public List<Permission> PermissionIds{ get; set; }
    }
}

我在项目中其他地方都用到了不止一次事务，但我测试都没问题，就AddRole这个方法开启事务会出现这个问题。。。有点邪门。

PS:这个编辑器control + z 会乱码所有代码，很影响心情。

热忱回答（5）

C.J VIP0
2024/8/25

发布也乱码，我研究一下...

0 回复
fate sta VIP0
2024/8/25

应该是没有连上数据库
db.Open()测试能不能执行

0 回复
fate sta VIP0
2024/8/25

0 回复
fate sta VIP0
2024/8/25

https://learn.microsoft.com/zh-cn/sql/connect/ado-net/sqlclient-troubleshooting-guide?view=sql-server-ver15

连不上的解决方案

0 回复
C.J VIP0
2024/8/26

@fate sta：是这样的，有两个数据库，Main和Local，Local这个数据库本就是连不上的，就是这个报错逻辑不太明白，如果是连不上那么可以在初始化SqlSugar的时候就报错连不上，不应该在我打开事务的时候报错呀？更何况我开启事务的时候用的是Main数据库，这个数据库是连的上的，然而却报错另一个连不上的数据库Local。

0 回复