Change LINQ Connection String Dynamically with Programming

• Download source code - 10.3 KB

Introduction

It is very common to encounter 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.

Sometimes, needs of the programming projects are on changing data source in LINQ to SQL class. This paper has tried to use Visual Studio features on how to change LINQ to SQL data source at runtime.

Requirements

You will need the following tools before using the class. These tools are needed for development.

• Visual Studio .NET 2008 or higher
• .NET Framework 3.0 or higher

Using the Code

Assume you are creating a function to return a connection string. At first, we create a public static class named Class_MainConStr. We have a method that returns a value of type string. Your function should be like below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqConStr
{
    public static class Class_MainConStr
    {
        public static String UDF_MainCnStr()
        {
            return Properties.Settings.Default.ProjectConnectionString.ToString().Trim();
        }
    }
}

Next, we should find connection string method in LINQ to SQL data class .cs file. (See Figure 1.)

public DataClasses1DataContext() : 
base(global::LinqConStr.Properties.Settings.Default.ProjectConnectionString, mappingSource)
{
    OnCreated();
}

At this time, you should replace LinqConStr.Properties.Settings.Default.ProjectConnectionString with LinqConStr.Class_MainConStr.UDF_MainCnStr().

public DataClasses1DataContext() : 
base(global::LinqConStr.Class_MainConStr.UDF_MainCnStr(), mappingSource)
{
    OnCreated();
}

App.config Setting

Application configuration files contain settings specific to an application. This file contains configuration settings that the common language runtime reads (such as assembly binding policy, remote objects, and so on), and settings that the application can read.

Your project connection string should be placed in app.config file. This file is in XML format. With this file, you can change the connection string. You can create this file automatically with Visual Studio. You can store inside it connection string that you may want to change without having to change code. (See Figure 2.)

If you have remote server, you can change connection string as below:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
    <connectionStrings>
        <add name="LinqConStr.Properties.Settings.ProjectConnectionString"
        connectionString="Data Source=YourRemoteServerIP;Initial Catalog=YourDatabaseName;
        User ID=UserID;Password=YourPassword"providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Conclusion

Now you can use this solution in your projects. If you need help, do not hesitate to contact me.

About the Author

I'm a software engineer living in Iran. I like to work on Windows and Web Application and database programming.

Ali Najafzade