I have 3 databases, live, test and dev. Could I easily swap the links between these in a DBML? - 【StackMirror】|asp.net|sql|ado.net|linq-to-xml

I have a need to swap between different DB instances of the same design. Is there a way to repoint the DBML to this different DB. What would be ideal would be to specify in code a different web.config connection string. However one would not want to change the Datacontext names in the codebase. If one was doing this in ADO.NET then one could choose the connection string.

Thanks,

2012-04-05 01:37
by EdB

When you instantiate a DBML the appropriate connection string is retrieved from a configuration file. (app.config or web.config). In the case that no setting is found, the initial connection string (which exists as a property on the DBML, and can be viewed through the designer) is used.

You will only need to use the connection string constructor if you are using multiple connection strings through a single instance, and need to specify the database to which you should connect.

In your case you simply need to include the appropriate connection string in your executing settings file. The connection string must be named correctly - see the setting which is placed in the project containing the DBML to check the name.

2012-04-05 02:27
by Kirk Broadhurst

Thanks. "You will only need to use the connection string constructor if you are using multiple connection strings through a single instance, and need to specify the database to which you should connect." may always be true since one may not want to change the C# code to use some other DataContext or perhaps one would??? Perhaps one could set up a Live, Test and Dev Data context and then link to the appropriate one via some config setting???? 2 options here: 3 datacontexts with unique connection string or 1 datacontext with interchangeable connection string. Best approach?? Thanks, E - EdB 2012-04-05 10:05

@EdB You want a single DataContext which retrieves a named connection string from your configuration file. But you want three configuration files. Configuration settings - such as target database - shouldn't need to be switched in C# code. If you re-read my answer you will see that I am suggesting that you specify the connection string in configuration file, and implying that you use a configuration file for each environment - Kirk Broadhurst 2012-04-10 00:09

Thanks, really appreciate the help - EdB 2012-04-11 14:44

You can use web.config transformations and easily swap needed configurations, here are the some links :

Common Web.Config transformations with Visual Studio 2010

Web.config Transformation Syntax for Web Application Project Deployment

2012-04-05 01:51
by Antonio Bakula

You should able to do that. When you generate DBML usually it writes the connection string to a configuration file (web.config or app.config). We should just change the database location in the configuration file. However, you have to make sure all your database are in Sync.

2012-04-05 01:48
by Tanvir Huda

Thanks folks, I have also come across this from "Ronny": Yes it's possible.

MYDataContext mycontext = new MYDataContext("Your Connection String");

There is a Constructor where you can chage the Connectionstring - EdB 2012-04-05 02:04