locked
Change Connection String whilst App is running RRS feed

  • Question

  • User439975351 posted

    Hi all,

    I have a requirement to select different databases from a drop down list in an app which would then populate the active database connection string. Is this possible in Asp Core? If so does anyone have any suggestions how I might achieve this?

    I have thought about writing the updated conn string to a "dynamic" appsettings file but I think I would need to restart the app for this to be read.

    Thanks,

    Jus

    Tuesday, April 7, 2020 8:14 AM

Answers

All replies

  • User753101303 posted

    Hi,

    Regardless of the user? What is the purpose of updating the connection string while it is live? It is not a multi tenant app?

    Tuesday, April 7, 2020 8:21 AM
  • User439975351 posted

    Its for an administration tool. Almost like a Web UI for SQL Server Management Studio but for "users" not "server admins"

    Each "user" would have their own list of servers that they might want to connect to to review data.

    Tuesday, April 7, 2020 8:26 AM
  • User753101303 posted

    So it should be likely stored as part of the user profile so that each user can use its own database. You already have user profile information stored in a general database? Seems an unusual design (or this is data source for a reporting tool ???)

    For now my understanding is that not all users are using the same db even though this is a single application.

    Tuesday, April 7, 2020 8:46 AM
  • User439975351 posted

    Exactly, each user profile can have any number of selectable database connections. The profiles are persisted in json files so to allow database switching. Yes the design is very specific and there are underlying business requirements that dictate it that we're working to.

    Your understanding is absolutely correct, do you think what we are trying to achieve is not possible in a single app?

    Tuesday, April 7, 2020 9:18 AM
  • User-474980206 posted

    its fairly trivial. the main decision is which state technology you will use to store the selected connect string (say the key in a cookie). if you use DI for database connectivity, you will want the connection object constructor to to expect a session scope object with the connection string, and use middleware to update this object.

    Tuesday, April 7, 2020 3:15 PM
  • User439975351 posted

    Thanks Bruce. With this scenario would multiple users being able to work with different databases at the same time? The more I think about the problem the more I think that a DI based solution wont allow for this example but I'm probably wrong ;-) ?

    Tuesday, April 7, 2020 3:49 PM
  • User1120430333 posted

    Thanks Bruce. With this scenario would multiple users being able to work with different databases at the same time? The more I think about the problem the more I think that a DI based solution wont allow for this example but I'm probably wrong ;-) ?

    Each Web user using the Web program is running in their own independent thread, and the Web program is running independently in each thread, becuase a Web server is a multiple thread solution that can be used concurrently.  :)

    Tuesday, April 7, 2020 7:50 PM
  • User-474980206 posted

    you just use a transient scoped DI object, which means its just of the life of the request. each user can have their own database.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 7, 2020 8:41 PM
  • User-854763662 posted

    Hi 1jus ,

    You could refer to the below similar threads to use the implementationfactory overload of the IServiceCollection in the ConfigureServices method of the Startup class.

    https://stackoverflow.com/questions/36816215/dynamically-change-connection-string-in-asp-net-core

    https://stackoverflow.com/questions/40836102/asp-net-core-change-ef-connection-string-when-user-logs-in

    Best Regards,

    Sherry

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 8, 2020 3:02 AM
  • User439975351 posted

    @bruce (sqlwork) & @Sherry Chen - Thanks for this, both your suggestions look like they will provide the solution we're looking for. 

    Have a great day and #staysafe :)

    Wednesday, April 8, 2020 7:46 AM