How to auto connect from multiple connection strings in dot net core 3.1

Question

Here is the connection strings in appsettings.json file.

appsettings.json

"ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-11G3852\\SQLEXPRESS;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true",
    "OfficeConnection": "Server=DESKTOP-DTUS54A;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true",
    "LappyConnection": "Server=DESKTOP-J8PN84H;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

Startup.cs

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(
                    Configuration.GetConnectionString("DefaultConnection")));
            .....
        }

Here problem is, each time when I change my machine I have to manually set connection in Sratup.cs file. Here you can see I set "DefaultConnection".

I want solution like I have number of connection strings in appsetings.json file(right now here I have 3 connection strings) and it will check connection is established with first connection string(DefaultConnection) or not.

If not then it will start establish connection with second connection string(OfficeConnection).

If connection establish successfully then my website should continue with second connection string(OfficeConnection).

Extra: If possible my migration also needs to apply with successfully connected connection string.

If you are connecting to the default database instance and not a named one then you could achieve this with just one connection string: "Server=.;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=True" — Rhexis, Aug 15 '20 at 17:47

score 1 · Answer 1 · answered Aug 15 '20 at 10:54

That's not the proper way to do it. The configuration files are there for that exact reason, that is to signify which environment they run on: check this answer here: ASP.NET Core set hosting environment in build process

So the proper solution, would be to change the configuration file, based on the environment you run this on.

If you really want to check connectivity, you can try something like this: What's the best way to test SQL Server connection programmatically?

/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
private static bool IsServerConnected(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
    }
}

For each of your connection strings and choose accordingly.

Truth be told though, you should not do these during configuration as it is something that will take some time and make your application seem non responsive.

Thank you for the solution. I fulfill my requirement with your idea. — Nileksh Dhimer, Aug 15 '20 at 11:57
Glad I could help. Don't forget to upvote/accept helpful answers. — Athanasios Kataras, Aug 15 '20 at 11:58

score 0 · Answer 2 · answered Aug 15 '20 at 12:00

I got the concept with his solution and then I fulfill my requirement.

Here is the solution you want to just paste the code into your file which in

//START

...

//END

block in the below solution:

using System;
using System.Collections.Generic;
using System.Linq;
using MySystem.Data;
using Microsoft.Extensions.Configuration;
using Microsoft.Data.SqlClient;
using System.Configuration;

namespace MySystem
{
    public class Startup
    {       
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
            //START
            ConnectionStrings = Configuration.GetSection("ConnectionStrings").GetChildren().ToDictionary(x => x.Key, x => x.Value);            
            ConnString = "";
            foreach (var item in ConnectionStrings)
            {
                if (IsServerConnected(item.Value))
                {
                    ConnString = item.Value;
                    break;
                }
            }
            //END
        }

        public IConfiguration Configuration { get; }
        //START
        public string ConnString { get; }
        public static Dictionary<string, string> ConnectionStrings { get; private set; }
        //END

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {

            //services.AddDbContext<ApplicationDbContext>(options =>
            //    options.UseSqlServer(
            //        Configuration.GetConnectionString("DefaultConnection")));

            //START
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(
                    ConnString));
            //END
            
            .......
            services.AddRazorPages();           
           
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
           ......
        }

        //START
        private static bool IsServerConnected(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        //END
    }
}

score 0 · Answer 3 · answered May 27 '21 at 13:50

You can create multiple appsetting.json files, and In the lunch settings you specify which of them you'll be using. In the root appsettings put the sections that will not change. For example appsettings.office.json you change the connection only if that's the case. But if you want to change it automatically I think that the right path will be using an environment variable set in each of your environments.

How to auto connect from multiple connection strings in dot net core 3.1

3 Answers3