Connection String in LINQ

When at run time you need to change the database server, you need to use the connection string in your LINQ.

It is very common to encouner a scenario in which during execution you need to change the database server and so the connection string is used in your LINQ. This might be a requirement, if you are moving your application from a development server to staging and from staging to production.

There are two ways you can change a connection string on the fly:

1. Edit config file
2. Build a connection string to pass to the constructor of the DataContext.

Edit config file

You can do it in two ways. Open the configuration file and edit the connection string here.



In another way, you can change the connection string is open DBML file and right-click then select Properties:


In the properties tab create on Connection and click on Connection String:



In the connection properties dialog box you can change the connection so the connection string.


Editing Connection String in code

Assuming you are creating a function to return a connection string. Create a function that takes a server name as an input parameter. Your function should be like below.



You can pass a server name to this function to create a connection string and use the output of this function in the constructor of the DataContext.



So here you can pass any server name to create the DataContext for that server.

For reference the code is as below:

static string GetConnectionString(string serverName)
{
    System.Data.SqlClient.SqlConnectionStringBuilder builder =
    new System.Data.SqlClient.SqlConnectionStringBuilder();
    builder["Data Source"] = serverName;
    builder["integrated Security"] = true;
    builder["Initial Catalog"] = "Sample2";
    Console.WriteLine(builder.ConnectionString);
    Console.ReadKey(true);
    return builder.ConnectionString;
}

I hope this post was useful. Thanks for reading.