Stop LINQ to SQL dbml from updating connection string when I drag tables into the dbml file

Question

Every time I drag a modified table into my dbml, VS2010 wants me to update the connection string even though I have created a data connection with the exact same information. Every time this happens it changes my connection string to 'DataSourceConnectionString' and I have to delete it from 5 different files and replace it with the name of the connection string I was using previously so it doesn't screw up everyone else using the file through source control. Any Ideas?

Mim

Answer 1

I figured out another away after tinkering with answers to some somewhat related issues.

To solve the problem I was having I needed to delete the Data Connection I created to connect to my database from your server explorer window in VS2010. You then need to open the .dbml designer and right-click in the white space and select properties. A properties window will open and there will be a "Connection" section, click on the small arrow to open it up. If you click on the connection string row a button will appear with dots(...) on it. When you click on this button it will open a Connection Properties window. This window will already have all of the same connection properties your .dbml already has. All you have to do is click 'OK' and it will automatically create a data connection for you in the server explorer that wont cause you to add a new connection string to the .dbml when you drag a new object to it!

Yay! No more deleting extra connection strings every time I update! :D

Mim

This is so strange, but thanks a lot. I hate that MS just stopped working on LINQ to SQL. It's way to much work to rebuild everything with Entity Framework. – Moramorabito

At first I missed the step about deleting the connection I created in the Server Explorer and was thinking that this solution didn't work, but after re-reading your answer and following your steps accurately, I was very pleased to find that it does work. +1 for finding out how to do this as this has bugged me for years! – Quotable

This also worked for me today, but there was an additional step. After clicking OK on the connection window, the Server Explorer didn't immediately show the connection I was interested in. I tried to refresh the Server Explorer connections list, but it kept failing. I first had to remove a few dead/unused connections, then the refresh worked and the missing connection reappeared as prescribed. Thank you, this really saved me today! – Bicorn

Answer 2

When you instantiate your DataContext, use the overload that allows you to specify the connection string. This will overwrite whatever values that were put there by the Designer. For example, instead of just:

DataClasses1DataContext db = new DataClasses1DataContext();

use:

DataClasses1DataContext db = new DataClasses1DataContext("..connection string..");

or

DataClasses1DataContext db = new DataClasses1DataContext(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);

Hope this is what you are looking for.

Hosmer

I didn't think to do this. It would have been a hassle to type that a million times since I have static classes that I call that require a DataContext to be instantiated. I had actually figured it out yesterday, but have had the issue for months so I wanted to post it here for other people. Thank you for the reply – Mim

It seems that VS 2012 has finally gotten it right by not baking the connection string into code. In VS 2012, instantiating the data context with no parameters will call a constructor that reads in a value from AppSettings in web.config. The AppSettings key that is used can be changed/set in the Properties tab of the dbml file. – Hosmer

Answer 3

Use a text editor to open the .dbml and remove the connection string manually. The connection will be something like

<Connection Mode="" >

You can then use your own partial class as follows

public partial class MyDataContext : DataContext
{
    public MyDataContext() : base(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString)
    {
        OnCreated();
    }
}

Huntingdon