Dynamically Generating Connection String for Entity Framework

In this blog we will see how to build entity connection string dynamically.

Sometimes, there comes a scenario where you are using certain architecture for your work but due to some issue or for some enhancement in the system you need to use two different connection providers, one for a simple connection and other for entity framework.

In my case I am using LINQ to SQL and entity framework altogether for doing some work.

What is the need for creating connection string dynamically for entity framework?

The real need arises as .edmx entity framework data objects create different connection strings than the linq to sql or we simply use in web.config file that's why we need to generate dynamically by using built in classes in C#.

Simple Connection string:

<add name="ConnectionString9" connectionString="server=ServerName;database=DatabaseName;uid=db_user_id;password=db_user_password; Connect Timeout=0; Max Pool Size=5000"/>

Entity Connection String:

<add name="DBEntities" connectionString="metadata=res://*/DB.csdl|res://*/DB.ssdl|res://*/DB.msl;provider=System.Data.SqlClient;provider connection string="data sourceServerName;initial catalog=DB_Name;persist security info=True;user id=DBUserID;password=DBUserPassword;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

So let's start it to build the connection string dynamically to be used in project. Firstly, we add required namespaces to be used in method.

using System.Configuration;
using System.Data.SqlClient;
using System.Data.Entity.Core.EntityClient;

Secondly, we Create Class and include the method for creating connection string dynamically.

public string GenerateConnectionStringEntity(string connEntity)
{
  // Initialize the SqlConnectionStringBuilder.
    string dbServer = string.Empty;
    string dbName = string.Empty;
    // use it from previously built normal connection string
    string connectString = Convert.ToString(ConfigurationManager.ConnectionStrings[connEntity]);
    var sqlBuilder = new SqlConnectionStringBuilder(connectString);
    // Set the properties for the data source.
    dbServer = sqlBuilder.DataSource;
    dbName = sqlBuilder.InitialCatalog;
    sqlBuilder.UserID = "Database_User_ID";
    sqlBuilder.Password = "Database_User_Password";
    sqlBuilder.IntegratedSecurity = false;
    sqlBuilder.MultipleActiveResultSets = true;
    // Build the SqlConnection connection string.
    string providerString = Convert.ToString(sqlBuilder);
    // Initialize the EntityConnectionStringBuilder.
    var entityBuilder = new EntityConnectionStringBuilder();
    //Set the provider name.
    entityBuilder.Provider = "System.Data.SqlClient";
    // Set the provider-specific connection string.
    entityBuilder.ProviderConnectionString = providerString;
    // Set the Metadata location.
    entityBuilder.Metadata = @ "res://*/EntityDataObjectName.csdl|
    res: //*/EntityDataObjectName.ssdl|
        res: //*/EntityDataObjectName.msl";
        return entityBuilder.ToString();
}

This is the final connection string that returns by this method.

<add name="DBEntities" connectionString="metadata=res://*/DB.csdl|res://*/DB.ssdl|res://*/DB.msl;provider=System.Data.SqlClient;provider connection string="data sourceServerName;initial catalog=DB_Name;persist security info=True;user id=DBUserID;password=DBUserPassword;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

Thirdly, we would use it for whatever we needed by initializing the object.

var entityhelper = new EntityHelper();
var getConnString = entityhelper.GenerateConnectionStringEntity(_connectionString);
var dbentity = new DBName_Entities(getConnString);

The first line has initialization. Second line gets the dynamically generated entity connection string and finally in the third line it is used in the entity data object.

At last after doing our work of inserting or updating query we call entity Save Method like this to transfer our data to the Database server.

dbentity.SaveChanges();

That's  all, hope you found it useful and helpful.

Note: The dynamic connection string generator is not only used for entity connection string builder but you can also create a simple connection string.