locked
ASP.NET modify connectionstring at runtime RRS feed

  • Question

  • User1604699451 posted

    I need to change dataset connectionstrings to point to different DBs at run time.

    I've looked at a number of solutions however they all seem to be related to WinForms or web application projects or other technology slightly different than what I'm using, so I haven't figured out how apply them.

    The application is like a discussion. It's a web site project based on code originally written under VS2005, and there's no budget (or personal talent!) for major changes at this time. The app is written in vb.net; I can understand answers in c#. I'm working in VS2013.

    For example, the following post has lots of solutions, however, they seem to focus on web application projects, that have a project file with settings, which this web site project does not. 

    https://social.msdn.microsoft.com/Forums/en-US/7483b816-be7a-4204-a4d3-cfb14b2aae26/how-to-dynamically-change-connection-string-in-generated-dataset-class?forum=adodotnetdataset

    The app has three typed datasets pointing to one MDF, call it "MainDB.mdf". There are dozens of tableadapters among the three datasets.

    I'm going to deploy the app it as an "alpha/demo" version. I would like to use the same code base for all users, and a separate physical version of MainDB for each user, to reduce chances that the users crash each other.

    The initial demo access URL will contain query string information that I can use to connect the user with the right physical database file. I should be able to identify the database name and thus the connection string parameters from the query string information (probably using replace on a generic connection string). If necessary I could use appsettings to store fully formed connection strings, however, I would like to avoid that.

    I would like to be able to change the connection strings for all the datasets at the time that the entry point pages for the app are accessed.

    Changing the tableadapter connection strings at each instantiation of the tableapters would (maybe) require too much code change (at least a couple of hundred instantiations); I'dmaybe just make complete separate sites instead of doing that. That's the fall back position if I can't dynamically change the connectionstrings at runtime (or learn some other way to make this general scheme work).

    Below is a sample instantiation of tableadapter (admittedly not great code, however, it works for the demo version).

    Any suggestions on how to approach this would be appreciated.

    Thanks!

    Public Shared Sub ClearOperCntrlIfHasThisStaff( _
        varSesnID As Integer, varWrkprID As Integer)
    
        Dim TA As GSD_DataSetTableAdapters.OPER_CNTRLTableAdapter
    
        Dim DR As GSD_DataSet.OPER_CNTRLRow
        DR = DB.GetOperCntrlRowBySesnID(varSesnID)
    
        If IsNothing(DR) Then
            Exit Sub
        End If
    
        If DR.AField = varWrkprID Then
            DR.AField = -1
            TA.Update(DR)
            DR.AcceptChanges()
        End If
    
    End Sub
    Monday, September 7, 2015 8:39 PM

Answers

  • User1604699451 posted

    I got the approach in this link to work for a single adapter, so I can make it work for others.

    http://blogs.msdn.com/b/marcelolr/archive/2010/04/06/changing-the-connection-string-for-typed-datasets.aspx

    Since it's a web site project, there's no project file, so I would have to do that every time I instantiate a tableadapter (~200 code locations), and then finagle the object data sources on top of that.

    I think this is the technical answer, unfortunately it's something I can not implement in the current target release (alpha test / demo).   So, back to multiple physical sites.  Not so bad;  there are ways to automate the distribution of the code base.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 9, 2015 9:56 PM

All replies

  • User753101303 posted

    Hi,

    Never used much strongly typed datasets but more likely the connection string is stored inside the web.config( not sure if this is what you call the "project file" but you should have one even for web site projects).

    You could then deploy the same app to 3 locations and change the web.config. As a sanity check, I would check at startup that the web address of the web site matches the connection string that should be used for this site based on the host name (ie  app-cust1.mycompany.com, app-cust2.my company.com etc...)

    Tuesday, September 8, 2015 5:16 AM
  • User-84896714 posted

    Hi codequest,

    codequest

    I need to change dataset connectionstrings to point to different DBs at run time.

    You need to store all your connection strings at one place, for example config file. You use VS Find and Replace(CTRL+F) to fix it. By search your current connection string and replace it with below string.

    System.Configuration.ConfigurationManager.ConnectionStrings["CnnString"].ConnectionString

    Then you only need to change the config file when you want to change the database.

    Best Regards,
    Wang Li

    Tuesday, September 8, 2015 5:17 AM
  • User1604699451 posted

    Thanks for the inputs.   In my long winded explanation, I should have more clear on a couple of key points:

    > I'd like to use the same code base in one physical website , and select different databases from that single website.  So I would like to not make multiple copies of the website.

    > I need to make the connection string change at run time, for each user, based on the initial URL query string parameter.  So I can't change the physical web.config file.

    Hope that helps in defining the problem.

    Tuesday, September 8, 2015 10:56 AM
  • User-84896714 posted

    Hi codequest,

    I think after you get connection string from query string, you could store it in Session.

    if (Request.QueryString["connectionstring"] != null)
    {
        Session["connectionstring"] = Request.QueryString["connectionstring"];
    }

    Then you could use it's session value in all the scenes where you need to use connection string. Use ctrl + F to find current connection string and change it to below string.

    HttpContext.Current.Session["connectionstring"].ToString()

    Best Regards,
    Wang Li

    Wednesday, September 9, 2015 9:30 PM
  • User1604699451 posted

    I got the approach in this link to work for a single adapter, so I can make it work for others.

    http://blogs.msdn.com/b/marcelolr/archive/2010/04/06/changing-the-connection-string-for-typed-datasets.aspx

    Since it's a web site project, there's no project file, so I would have to do that every time I instantiate a tableadapter (~200 code locations), and then finagle the object data sources on top of that.

    I think this is the technical answer, unfortunately it's something I can not implement in the current target release (alpha test / demo).   So, back to multiple physical sites.  Not so bad;  there are ways to automate the distribution of the code base.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 9, 2015 9:56 PM