How to enable MultipleActiveResultSets on SqlServer database connection string?

[Kralizek]

I have the following block

var sqlPasswordResource = builder.AddParameter("SqlServerPassword", secret: true);

var mssql = builder.AddSqlServer("db", sqlPasswordResource)
    .PublishAsConnectionString()
    .WithDataVolume("VolumeMount.sqlserver.data");

var db = mssql.AddDatabase("Database", "identity-manager");

var legacyDb = mssql.AddDatabase("LegacyDatabase", "legacy");

I'd like to enable MultipleActiveResultSets on the connection string while keeping the connection string as "automatically generated" as possible.

Do I need to create an Annotation to be added to the databases?

[davidfowl]

See #2964.

Today you would have to build the connection string manually:

var builder = DistributedApplication.CreateBuilder(args);

var db = builder.AddSqlServer("sql-svr")
                .AddDatabase("db");

builder.AddProject<Projects.WebApplication1>("webapplication1")
       .WithEnvironment($"ConnectionStrings__{db.Resource.Name}",  $"{db.Resource};MultipleActiveResultSets=true");

builder.Build().Run();

We have a special string interpolation handler that captures the right information.

If you want to reuse this in multiple calls then you can do:

var builder = DistributedApplication.CreateBuilder(args);

var db = builder.AddSqlServer("sql-svr")
                .AddDatabase("db");

var connectionString = ReferenceExpression.Create($"{db.Resource};MultipleActiveResultSets=true");

builder.AddProject<Projects.WebApplication1>("webapplication1")
       .WithEnvironment(context => context.EnvironmentVariables[$"ConnectionStrings__{db.Resource.Name}"] = connectionString);

builder.Build().Run();

While the above is a little verbose, we now have more primitives that you can build on to make this easier.

Last but not least, and to show off the improvements made to the app model's extensibility, here's a custom resource that encapsulates this behavior.

var builder = DistributedApplication.CreateBuilder(args);

var db = builder.AddSqlServer("sql-svr")
                .AddDatabase("db")
                .AddConnectionStringData($"MultipleActiveResultSets=true");

builder.AddProject<Projects.WebApplication1>("webapplication1")
       .WithReference(db);

builder.Build().Run();


static class Extensions
{
    // Adds a custom resource that appends to another resource's connection string
    public static IResourceBuilder<IResourceWithConnectionString> AddConnectionStringData(this IResourceBuilder<IResourceWithConnectionString> builder, ExpressionInterpolatedStringHandler connectionStringData)
    {
        return builder.ApplicationBuilder.AddResource(new AppendConnectionStringResource(builder.Resource, ReferenceExpression.Create(connectionStringData)))
            .WithInitialState(new CustomResourceSnapshot
            {
                Properties = [],
                ResourceType = "ConnectionStringData",
                State = "Hidden" // Hide from the dashboard
            });
    }

    class AppendConnectionStringResource(IResourceWithConnectionString previous, ReferenceExpression referenceExpression) : 
        Resource($"{previous.Name}-ConnectionString"), IResourceWithConnectionString
    {

        // This is the environment variable used to get the connection string information.
        public string ConnectionStringEnvironmentVariable => $"ConnectionStrings__{previous.Name}";

        // This is used to resolve the connection string
        public ReferenceExpression ConnectionStringExpression => 
            ReferenceExpression.Create($"{previous};{referenceExpression}");
    }
}

[Kralizek]

Thanks for your answer.

I agree, the solution you showed is a really elegant and beautiful one.

[Kralizek]

I added this extension method to your Extensions

    public static IResourceBuilder<IResourceWithConnectionString> AddConnectionStringData(this IResourceBuilder<IResourceWithConnectionString> builder, IReadOnlyDictionary<string, object> data)
    {
        var sb = new StringBuilder();

        foreach (var item in data)
        {
            sb.AppendFormat($"{item.Key}={item.Value};");
        }

        return AddConnectionStringData(builder, $"{sb.ToString()}");
    }

It can be used as follows:

var db = mssql.AddDatabase("Database", "identity-manager")
    .AddConnectionStringData(new Dictionary<string, object>
    {
        ["MultipleActiveResultSets"] = true,
        ["Trusted_Connection"] = false,
        ["Encrypt"] = true
    });