Problem Statement:

There will be a situation where we need to connect and run some queries parallelly to our n number of clients with same database structure. Following is the approach to handling such situation using Entity Framework.

When we thought of this problem, we could think theoretically as creating database connections and executing respective statements on it. Yes, we are correct but we need to learn what is the best way to generate a connection using Entity Framework.

Assuming you are aware of adding the ADO.NET Entity Data Model in a selected project as I don’t want to go in Dep’t of creating an application and adding it as I want to focus more on our subject.

Our first task is to generate the connection object of type DBConnection as follows.

public DbConnection GetConnectionString(string dataSource, string databaseName)
            string defaultDataSource = "localhost";
            DbConnection conn;

            SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
            sqlConnectionStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? defaultDataSource : dataSource;
            sqlConnectionStringBuilder.IntegratedSecurity = true;
            sqlConnectionStringBuilder.MultipleActiveResultSets = true;
            SqlConnectionFactory sqlConnectionFactory = new SqlConnectionFactory(sqlConnectionStringBuilder.ConnectionString);

            conn = sqlConnectionFactory.CreateConnection(databaseName);
            return conn;

In the above code snippet, we are trying to create the connection object using given client data source and database name. Once this information is sent, a SQL connection string can be built using SqlConnectionStringBuilder. Using SqlConnectionFactory we can create a DBConnection object.

Now we are ready with generating DB connection with selected client details. Where do we need to call this method? Yes, you need to call this method before creating the DBContext object as shown below.

DbConnection dbConnection = GetConnectionString("myclientservername", "adventureDB"); 
ProjectManagementEntities projectManagementEntities = new ProjectManagementEntities(dbConnection, true);

Here, ProjectManagementEntities is a class inherited from  DBContext. Once we created we can use this projectManagementEntities object to run any queries on this database. Wait! By default DBContext class accepts only connection string name as its constructor. Yes correct, DBContext also have another parameterized constructor which accepts DBConnection object and a Boolean value asking whether the user will take care of disposing of this DBContext object (set False value) or runtime should take care (set True value).

For this, we need to extend a partial class of created/generated DBContext class and add with this new parameterized constructor as follows.

public partial class  ProjectManagementEntities : DbContext
    public ProjectManagementEntities(DbConnection dbConnection, bool contextOwnsConntection)
        :base(dbConnection, contextOwnsConntection)
    { }

That’s it… Now you can configure all your client-server information and database information somewhere in configuration or any XML based or any way and loop through it to create the connections and execute the required queries on each.

Well, it is short but just sharing my solutions here. Hope you enjoyed !!!