Dynamically Generating Connection String for Entity Framework

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.

6 Comments

Join the conversation! Your thoughts help the community grow.

Tôm Maư8y ago
Many thanks , but i got a error at EntityHelper ();. Did you miss the class ?
dinesh kumar9y ago
I am getting error . 'EntityHelper' is a namespace but is used like a type.
- Muhammad Aqib Shehzad9y ago
  Kindly share your code
dinesh kumar9y ago
Pls attach source code.
Ammar Shaukat10y ago
I could't understand the example in what cases or case studies we need a dynamic connection string usually ?
- Muhammad Aqib Shehzad10y ago
  i have added a note on the end of article for the purpose of usage. you can simply use to build for fixing issue as i elaborate the case in which i am using linq to sql but linq classes doesnot support multiple updating on single table that's why i have use entity with linq to update the multiple instance of single as entity handles it best