locked
Change database connection string at runtime RRS feed

  • Question

  • User-809753952 posted

    Is it possible to add  new connectionStrings during runtime?

    For example, I have 5 different databases named as DB1014, DB2015, DB2016, DB2017, DB2018.

    I have a drop down list filled with  these database connection names. If I select an item DB2016 , then the application should use the  DB2016 database connection. 

    Please note these connectionstrings are not created in the web.config gile. If so, how can I achieve this?

    Monday, September 17, 2018 2:12 PM

Answers

All replies

  • User475983607 posted

    If you want a different connection string for each user then store the connection name in a cookie.  If you want to set the connection when the app starts then add a web.config node that has the name to use.   Other than that the question is unclear...

    Can you explain the problem you are trying to solve?

    Monday, September 17, 2018 2:20 PM
  • User753101303 posted

    Hi,

    It seems to be historical data ? You could perhaps,include that as part of the url wiht ASP.NET Web Forms routing to have site.com/2014/data.aspx, site.com/2016/data.aspx and maybe the user could not even realize he is actually directed to another db...

    Edit: https://msdn.microsoft.com/en-us/library/cc668201.aspx and scroll down to Adding Routing to ASP.NET Web Forms applications.

    Monday, September 17, 2018 2:29 PM
  • User-809753952 posted

    Hi mgebhard

    I want to use  one asp.net application with different database connections. 

    These database connections must be dynamic. There are more than 20 databases. So I don't want to configurate all these connections in my application. Instead I want to use the dropdownlist to select a database name and then create a connection string for it.

    Hope you can understand my problem.

     

    Monday, September 17, 2018 2:32 PM
  • User475983607 posted

    Hi mgebhard

    I want to use  one asp.net application with different database connections. 

    These database connections must be dynamic. There are more than 20 databases. So I don't want to configurate all these connections in my application. Instead I want to use the dropdownlist to select a database name and then create a connection string for it.

    Hope you can understand my problem.

    I still do not understand the problem you are trying to solve.  

    Do you want to change the DB connection for all users or each user can select their own connection? 

    If the user determines the connection string then you can store the user's selection in a cookie or as PatriceSc recommend the URL.  Basically, your question is related to how to persist user data in ASP.NET. This is a well cover subject and very common.

    If an admin is changing the connection string and it affects all the application users then use cache, a central DB, or the file system. 

    Monday, September 17, 2018 3:05 PM
  • User-809753952 posted

    My question is how to add Connection Strings to web.config file on runtime.

    Tuesday, September 18, 2018 5:56 AM
  • User-1320437544 posted

    Hello,

    Andddd here you go Session["SQLConnectionString"] = dropdownlist1.SelectedValue.Trim();

    Now you use the connection string:

    SqlConnection conn = new SqlConnection(Session["SQLConnectionString"].ToString());

    SqlCommand comm = new SqlCommand("Select * From dbo.Users", conn);

    comm.Paramters.AddWithValue("@UserName", textBox1.Text.Trim());

    try

    {

    conn.Open();

    SqlDataReader dr = comm.ExecuteReader();

    if(dr.HasRows)

    {

    while(dr.Read())

    {

    // DO THINGS HERE

    }

    }

    dr.Close();

    ]

    catch(Exception ex)

    {

    }

    finally

    {
    conn.Close();

    }

    Hope you get the idea... and hopefully it helps you to solve your issue.

    Let me know.

    Tuesday, September 18, 2018 7:25 AM
  • User-1716253493 posted

    You can strore multiple connectionstrings in web.config

      <connectionStrings>
        <add name="DB1014" />
        <add name="DB2015" />
        <add name="DB2016" />
      </connectionStrings>
    string cs = System.Configuration.ConfigurationManager.ConnectionStrings[DropDownList1.Text].ConnectionString;

    Tuesday, September 18, 2018 7:48 AM
  • User-809753952 posted

    Hi oned_gk

    Is it possible to add the connectionstrings on run time to the web.config file?

    Let us say, we have 3 connection strings in the web.config file as 

    <connectionStrings>
        <add name="DB1014" />
        <add name="DB2015" />
        <add name="DB2016" />
      </connectionStrings>

    If there is a new connectionstring "DB4016" in the drop down list, can you add it after selecting the item?

    Tuesday, September 18, 2018 8:56 AM
  • User753101303 posted

    Or you could use a single connection string and just change the "database" or "initial catalog" to what you need (possibly using https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder?redirectedfrom=MSDN&view=netframework-4.7.2)

    Seems the db list could comes from the server or from a central catalog. It's a bit unclear if you have a base database and you can select what seems historical data or if it is  selected from the start and then the user just works always inside this db.

    My personal preference is to keep web.config for what is really part of the "technical configuration" rather than related to data.

    Tuesday, September 18, 2018 9:11 AM
  • User632428103 posted

    Hello all,

    @mnmhemaj => web config is a file try to open it and add it as this sample 

    https://www.codeproject.com/Questions/217366/dynamically-set-connection-string-in-web-config

    Tuesday, September 18, 2018 10:21 AM
  • User475983607 posted

    mnmhemaj

    Is it possible to add the connectionstrings on run time to the web.config file?

    Let us say, we have 3 connection strings in the web.config file as 

    <connectionStrings>
        <add name="DB1014" />
        <add name="DB2015" />
        <add name="DB2016" />
      </connectionStrings>

    If there is a new connectionstring "DB4016" in the drop down list, can you add it after selecting the item?

    It seems that you are missing ASP.NET basics. 

    The web.config used is for application configuration.  Modifying the web.config causes the application to restart and load the new configuration into memory.  oned_gk's point is you can have several connection string in the web.config and can use any one of the connection strings at any time.  If you need to add a connection string then the app is stopped and restarted.

    In the case of an ADO.NET connection, the connection string is used right before making a database connection.  Usually the connection string is read from the web.config but it can be read from a central DB if needed.  You can load whatever connection string you like with the understanding that only the current user is affected. 

    It seems the main issue you are having is not related to connection strings but maintaining state in an ASP.NET application.  

    Can you explain the problem you are trying to solve at a high level?  Why do you have several databases are they backups?  Can you explain the application design and why you are unable to connect to different databases?

    Tuesday, September 18, 2018 11:11 AM
  • User-809753952 posted

    I have a database from which I am creating some reports for sales and other data every month using an ASP.NET application.

    When I compared  two reports from Feb 2018 and Aug 2018, I can see that the sales data figure has changed a lot.

    My database is updated daily using some SSIS.

    The change in the figure  should have happened as I added some changes in my business logic. ( I have to change it again.)

    So I want to create back up for my database every day and use the same ASP.NET application to create reports on different days.

    This will also help me as a log to explain the difference in the reports.

    I

    Tuesday, September 18, 2018 11:56 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2018 2:20 PM
  • User-1716253493 posted

    The sample is not creating connection strings, but choosing which connection you want use in your code.

    You can switch connectionstring based dropdown selection

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 19, 2018 1:07 AM