none
How to Change the DataBase Reference in a SqlServer Project 2008? RRS feed

  • Question

  • Hi there,

    I have what I think should be a simple question. I want to deploy my solution, that contains among other projects, several SqlServer Projects to the production environment. One of the things that must changed is the database server and database name to which these projects must be deployed.

    I googled "Change database connection in SqlServer Projects" but only came up with database references for a Database Project. There should be a Database Reference in the Solution Explorer. I cannot find any way to add or modify a database reference in the solution explorer.

    Before I tried: In the "My Project", database tab, I have altered to connection string to the production database. However the assemblies are still deployed to the test server, instead of the production server. Also I have replaced all "Data Source=TestServer" to "Data Source=ProdServer" and all "Initial Catalog=TestDb" to "Initial Catalog=ProdDb". Still deployment is to the TestServer.

    Only when I took the TestServer.TestDb offline I was prompted to alter the database connection. Then it finaly deployed to the right Server/Database.
    So my simple question is, how can I change this connection in Visual Studio in a less crude way?

    Hope you can help

    Jos


    I'll be back

    Tuesday, February 14, 2012 9:09 AM

All replies

  • This is where the Application configuration files come handy. Just store your connection string in App.Config file instead of hardcoding it in the code. Whenever you want to change the database, get the correct connection string and add (or replace) it into app.config file so that your application immidiately starts using new database. This not only saves time, but also avoids rebuilding of entire project.


    Please mark this post as answer if it solved your problem. Happy Programming!


    • Edited by Adavesh Tuesday, February 14, 2012 9:16 AM
    • Marked as answer by Paul Zhou Thursday, February 23, 2012 9:14 AM
    • Unmarked as answer by Josje Tuesday, February 28, 2012 8:54 AM
    Tuesday, February 14, 2012 9:15 AM
  • Hi Adavesh,

    I have tried to find out what Application configuration file and with what content I am supposed to create, but I don't seem to get the proper documentation on it. Can you elaborate on app.config files or point me to the proper documentation?

    As said my question seems simple enough. I develop the solution on server ONTW01 using database Baanstede_Test. In order to promote changes I need to deploy the solution to server APP04 using database Baanstede_Prod.
    Ideally I should only have to change the CONFIG parameter in the Configuration Manager from [CLR_]Debug to Production. It is unclear to me how to connect this CONFIG parameter to app.config????

    If you please, can you tell me how?

    Jos


    I'll be back

    Tuesday, February 28, 2012 9:21 AM
  • Hi Josje,

    This is how, you can add connectionstring to app.config:

    <?xml version='1.0' encoding='utf-8'?>
      <configuration>
        <connectionStrings>
          <clear />
          <add name="connectionName" 
           providerName="System.Data.ProviderName" 
           connectionString="Valid Connection String;" />
        </connectionStrings>
      </configuration>

    And you can read connection string as:

     const string connectionSting = ConfigurationManager.ConnectionStrings["connectionName"].ConnectionString;

    Check out his for more understanding on app.config:

    http://msdn.microsoft.com/en-us/library/ms254494(v=vs.80).aspx


    Regards, http://shwetamannjain.blogspot.com

    Tuesday, April 10, 2012 3:10 AM
  • Hi Adavesh,

    Oké I have:

    Dim ConnectionSting As String = "" #IF CONFIG = "Debug" Then ConnectionSting = ConfigurationManager.ConnectionString("Baanstede.My.Settings.Baanstede_TestSettings").ConnetionString

    #ElseIf CONFIG = "Production" Then

    ConnectionSting = ConfigurationManager.ConnectionString("Baanstede.My.Settings.Baanstede_ProdSettings").ConnetionString

    #EndIf

    However it still escapes me, how this can affect the the deployment process, as what I asked: how can I "Change database connection in SqlServer Projects"

    so the deploy is to this connection and not to the defaulting connection?


    I'll be back

    Thursday, April 12, 2012 2:48 PM
  • Hi,

    Just Change CONFIG Variable.

    when you want to Deploy Change CONFIG Variable = "Production" and if you want to Local Development then Change CONFIG = "Debug"

    this variable automatic change your Database Location as per value of variable.

    Regards

    Gautam

    Thursday, April 12, 2012 5:28 PM
  • Hi Shweta, Adavesh,

    I have done all you proposed:

    - add a app.config file to all projects in my solution on the TestServer
    - copy my solution from TestServer (ONTW01) to ProdServer (APP04)
    - replaced all occurences of "Data Source=" & TestServer to "DataSource=" & ProdServer
    - replaced all occurences of "Initial Catalog=" & TestDbName(Baanstede_Test) to "Initial Catalog=" & ProdDbName(Baanstede_Prod)
    - Changed "Active solution configuration" in the configuration manager from "Debug" to "Production"

    - Started "Deploy Solution" from the build menu.

    In the Database Tab of each project, sure enough it says "Data Source=APP04;Initial Catalog=Baanstede_Prod;Integrated Security=True"
    However, still the assemblies are created in Sql database Baanstede_Test on ONTW01, instead of Sql database Baanstede_Prod on APP04.

    What am I overlooking?
    Regards Jos


    I'll be back

    PS: Only after taking offline of Baanstede_Test on ONTW01 I get this message:

    After which I can alter the connection wrong connection string from ONTW01.Baanstede.Test to APP04.Baanstede_Prod.
    What I am looking for is to alter this connection string, as I cannot find it anywhere in the solution, since I already replaced all ONTW01 to APP04 and Baanstede_Test to Baanstede_Prod. So I try to figure out where this connection string can be coming from?????

    Regards Jos

    • Edited by Josje Monday, June 4, 2012 10:22 AM
    Monday, June 4, 2012 10:00 AM
  • Hi josje, 

    As you stated have connection string entry for debug and release mode in config file then.

    I guess the above code you mentioned , have wrong pre-processor statements. change to 

    string ConnectionString;
    
    #if DEBUG
       ConnectionString = ConfigurationManager.ConnectionStrings["Baanstede.My.Settings.Baanstede_TestSettings"].ConnectionString;
    #else
       ConnectionString = ConfigurationManager.ConnectionStrings["Baanstede.My.Settings.Baanstede_ProdSettings"].ConnetionString
    #endif
    Hope this helps you...

    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

    Wednesday, June 6, 2012 4:03 PM
  • Hi Kris444,

    I have done all that twice and ten times over again. Still the assemblies and CLR-routines are created on the wrong server in the wrong database that is the testserver in the testdatabse instead of the production server in the production database.

    Why cannot you give a simple answer to a simple question: where is the test connection string comming from, after I replaced all test connection string values with production connection strings?????

    Regards, Jos


    I'll be back

    Monday, June 18, 2012 11:39 AM