Managing different developer's connection strings under LINQ to SQL

Question

With my source in Subversion, I am having issues when 2 different computers have different connection strings.

The LINQ to SQL designer seems to only like having the same connection string.

Is it possible for the designer to use a connection string that varies since developers have different local configurations, but the actual usage in the web application pulls from the web.config?

Answer 1

Unfortunately, this is a huge source of pain with the LINQ to SQL designer. I do not know of a way to force visual studio to never add a default connection string when you drag tables or stored procedures onto the design surface.

We work around the issue thusly:

1. we never save our dev passwords
2. we never use the default connection string in code when newing up a DataContext
3. we can therefore "safely" ignore the multiple connection strings during sprints that touch the data layer
4. when things die down/become more stable, we delete the connection strings from the data context, either using properties of the designer surface itself or by editing the XML. At that point, it's up to the modifier of the data context to keep up with deleting the default connection strings.

Alas, this is not an ideal situation. Hopefully VS2010 will "fix" this issue.

Answer 2

I ran into this problem and found your question. Here's the solution we're now using:

1. Use a centralised Configuration class for retrieving config values from a particular location on the file system. This allows every machine where the code is running to use its own config values.

2. Create a partial class for the LINQ to SQL data context. Add a custom constructor that takes no parameters and retrieves the database connection string from the Configuration class described above.

For example:

public partial class MyCustomDBDataContext
{
    public MyCustomDBDataContext() :
                base(Configuration.GetDatabaseConnectionString())
    {
    }
}

This should now solve the problem both for developers and when deployed to test and production.

Answer 3

By following the guidelines on How Do I? Change Connection String of datacontext default constructor from web.config my application now uses different connectionstrings depending on if HttpContext.Current.Request is local or not.

//using System.Web.Configuration;

partial void OnCreated()
    {
        //Change this condition to your needs
        var isLocal = HttpContext.Current.Request.IsLocal;
        this.Connection.ConnectionString = WebConfigurationManager.ConnectionStrings[isLocal ? "localConnectionstring" : "otherConnectionstring"].ToString();
    }