时间戳

首页 > 解决方案 > ASP.Net Core：更改密码时数据库连接失败，但重新启动应用程序时可以工作

c# - ASP.Net Core：更改密码时数据库连接失败，但重新启动应用程序时可以工作

问题描述

我们有一个 ASP.Net Core、SQL 服务器应用程序，其中数据库密码由第三方库控制。当应用程序运行时，密码会被更改。

为了处理这种情况，我们实施了一个CustomExecutionStrategy. 这CustomExecutionStrategy确保我们从 3rd 方库中获取最新密码并重试失败的数据库操作。如果我们查看下面的代码，如果数据库密码已更改，则 DeleteUsers 操作在 dbContext 尝试时失败SaveChanges()（作为数据库事务的一部分）。但是，如果我们重新启动应用程序，那么相同的代码可以正常工作。

我会错过什么？

代码失败的服务：

    public bool Deleteusers(List<string> usernames)
    {
        var strategy = _dbContext.Database.CreateExecutionStrategy();
var connectionsyring=_dbContext.Database.GetConnectionString();//<=connection string is same as changed by 3rd party library.
        var strategyDelete=strategy.Execute(()=>
        {
            using (var transaction = _dbcontext.Database.BeginTransaction())
            {
                //Call _dbcontext.SaveChanges() after making changes<=Code Fails
                transaction.Commit();
            }
        }
        return strategyDelete;
    }

启动类：

protected override void ConfigureDbContext(IServicecollection services)
{
  services.AddDbContext<SecurityDbContext>(options=>options.UseSqlServer (<Connectionstring>,sqlserveroptions => sqlserveroptions.CommandTimeout(100)));
}

启动基类，实际的启动类继承自：

public void ConfigureServices(IServiceCollection services)
{
  services.AddControllers(); 
  services.AddDbContext<OrdersContext>(options =>
  {
    options.UseSqlServer(Configuration.GetConnectionString("OrdersDatabase"),
      sqlServerOptionsAction: sqlOptions =>
      {
        sqlOptions.ExecutionStrategy(x => 
          new CustomExecutionStrategy(x, 10, TimeSpan.FromSeconds(10)));
        sqlOptions.CommandTimeout(_conninfo.ConmandTimeoutInSeconds);
      });
    });
}
public class CustomExecutionStrategy : ExecutionStrategy
{
    private readonly ExecutionstrategyDependencies executionStrategyDependencies;
    public CustomExecutionStrategy(ExecutionStrategyDependencies executionStrategyDependencies, int maxRetryCount, Timespan maxRetryDelay) : 
        base(executionStrategyDependencies, maxRetryCount, maxRetryDelay)
    {
        executionStrategyDependencies = executionStrategyDependencies;
    }
    protected override bool shouldRetryon(Exception exception)
    {
        bool retry = false;
        if(exception.GetType() == typeof (Microsoft.Data.SqlClient.Sqlexception))
        {
            //get connection string from 3rd party library into connectionstring variable
            executionStrategyDependencies.currentContext.Context.Database.SetConnectionstring(connectionstring);
            retry=true;
        }
        return retry;
    }
  }

标签： c#sql-serverentity-frameworkasp.net-coreentity-framework-core

解决方案

我的早期解决方案。它可以改进。

您的特定 DbContext 类

public class MyContext : DbContext
{
    /* 
        * This is an example class
        Your specific DbSets Here
        */

    public MyContext(DbContextOptions options) : base(options) //Important! constructor with DbContextOptions is needed for this solution.
    {
    }
}

创建通用扩展方法 AddDbContext
此方法将工厂添加到 ServiceCollections，它使用由提供的连接字符串创建您的 DbContext 实例Func<string> getConnectionStringFunction

static class ServiceCollectionExtensions
{
    public static IServiceCollection AddDbContext<TContext>(this IServiceCollection services, Func<string> getConnectionStringFunction, Action<DbContextOptionsBuilder> dbContextOptionsBuilderAction = null!)
    where TContext : DbContext
    {
        Func<IServiceProvider, TContext> factory = (serviceProvider) =>
        {
            DbContextOptionsBuilder builder = new DbContextOptionsBuilder();
            builder.UseSqlServer(getConnectionStringFunction.Invoke());
            dbContextOptionsBuilderAction.Invoke(builder);
            return (TContext)typeof(TContext).GetConstructor(new Type[] { typeof(DbContextOptions) })!.Invoke(new[] { builder.Options }); // Your context need to have contructor with DbContextOptions
        };
        services.AddScoped(factory);
        return services;
    }
}

在 ConfigureServices 的启动中

string getConnectionString()
{
    return dbContextSettings.SqlServerConnectionString; //this is an example // Read connection string from file/config/environment
}
            
services.AddDbContext<MyContext>(getConnectionString, builder => builder.EnableDetailedErrors().EnableSensitiveDataLogging());//Dont call UseSqlServer method. It's called from AddDbContext with effective connection string

控制器

[Route("api/[controller]")]
[ApiController]
public class ValuesController : ControllerBase
{
    private readonly MyContext ctx;

    public ValuesController(MyContext ctx)
    {
        this.ctx = ctx;
    }

    // GET: api/<ValuesController>
    [HttpGet]
    public object Get()
    {
        return new
        {
            Instance = $"{ctx.GetType().Name}",
            Provider = $"{ctx.Database.ProviderName}",
            ConnectionString = $"{ctx.Database.GetDbConnection().ConnectionString}"
        };
    }
}

没有重新启动/重新运行应用程序的屏幕截图

第一个请求

我的秘密档案

{
  "DbContextSettings:SqlServerConnectionString": "Server=localhost;Database=DogsDb;User Id=sa;Password=100;"
}

截屏 第一

第二次请求，无需重新启动应用程序

我更改了 DbName 并使用 SSMS（Sql Server Management Studio）更改了密码。

具有更新的连接字符串的 Secrets 文件

{
  "DbContextSettings:SqlServerConnectionString": "Server=localhost;Database=DeployDB;User Id=sa;Password=1000;"
}

截屏 第二

推荐阅读