Switch `ConnectionStrings` between local and remote with Linq to SQL

Question

First, by Remote I mean a central dedicated SQL Server on our Network. By Local I mean a local SQL Express install.

My situation is in house only. No cloud services, external sites, etc. My app allows the user to work disconnected but in order to minimize traffic and a few other issues as well I would like to allow them to connect straight to the central server either automatically whenever it is available and/or when they set a setting choosing Central Server.

Are setup is quite simple. A local connection string for everyone is like so -->

Data Source=.\SQLEXPRESS;Initial Catalog=MemberCenteredPlan;Integrated Security=True

and a Central SQL connection string like so -->

Data Source=CentralSQL;Initial Catalog=MemberCenteredPlan;Integrated Security=True

Also, my Data is in a seperate project from my UI, as such I was having difficulties figuring out how to access the Settings file from the Data layer for the UI layer.

Should I add a parameter to all methods and pass a IsOnline variable to them? Seems repetitive but if I knew a better way I wouldn't be posting in the first place.

Thanks for the help!

This is a very similar post but I wonder if the advice is different when I want to switch between a Local DB and a Remote DB during runtime.

Answer 1

One option I use is to create a method on the DataContext (in a partial class) in this case:

public static DataContext New
{
  get
  {
    var cs = IsConnected ? CentralConnectionString : LocalConnectionString;
    return new DataContext(cs);
  }
}

You could beef up that switch logic however you wanted for the automatic switching. Then to reference in code, just use a format like this:

var DB = DataContext.New;
var result = from a in DB.....

It keeps your datacontext creation logic in one spot, if it suits your needs, I find it simplifies things everywhere.

Answer 2

To answer the question you posed in the comment to Nick's answer, his IsConnected is a property added to the partial class. You can defined it to whatever means to have to determining that state in your program. If it's an AppSetting value, it would be something like this:

public static bool IsConnected 
{ 
  get 
  {
       return ConfigurationManager.AppSettings["Online"] == "true";
  }
}