Pass Connection String At Run Time to Entity Framework

Sourav Kayal

Entity Frameworkexpose data using Entity Frameworkpass connection string at run time

13 Comments

Join the conversation! Your thoughts help the community grow.

mojtaba toghyani6y ago
Thanks a lot Sourav.
Mohamad Helaly7y ago
Thanx alooooooooooooooooooooooooooooooooooooooot
Sajid Ali8y ago
Sir what is DB context . i am new to Entity framework and wants to change username and password at runtime in connection string. so please suggest me the way. actually i am working with oracle and wants to connect with forms authentication. for example when user will enter login and password that login id and password will be used as a db user name and password.
Turino Sastrawijaya9y ago
5* Excellent article!
Mohammed Arfath9y ago
Hi Sourav, This happens to be life saver for me. Had been struggling to achieve this one for days. we followed a DB first approach and required to login using native SQL membership for audit trail purposes. I hope its not late to ask. Have you been able to find a solution for the problem which Robert K mentioned?
kuldeep Sharma9y ago
This is not a good solution, it is just an alternate of same process done by EF
Hardik Bhavsar11y ago
How to add connection timeout in connection string using entity framework in app.config file? <add name="DataEntities2" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.4.0;provider connection string="data source=|DataDirectory|\Data.sdf"" providerName="System.Data.EntityClient" />
Robert K.12y ago
Dear Sourav Kayal, I see you changed Context.cs in the edmx. But this file is auto-generated. What if you Update your Model from Database? The class efDBEntities will be overwritten...
Lakshmanan Sethu Sankaranarayan12y ago
Thank you. THis is the first time I m hearing that. Hence raised it.
Sourav Kayal12y ago
Dear Lakshmanan, It it is policy of Organisation , We have our services which will return connection string with username and password. Just i have explain scenario from my end. In reality or depending on business it might change.
Sourav Kayal12y ago
Dear Sachin, Just modify the Context class of your Entity Framework like example, Everything will remain same, no need to change anything.
Lakshmanan Sethu Sankaranarayan12y ago
Saurav. why are we storing connection string in service?
Sachin Kalia12y ago
Hi Saurav.Could you add sample application?

A few days ago I was tasked to implement a Web API application to fetch and expose data using the Entity Framework, Cool!! It's the latest technology but there was a little tweak in that. The connection string to fetch the data from the database will come from another service.

Hmm, It was DB first approach application because our database was already built and we just needed to write code to process the data. We know that Entity Framework automatically generates a connection string at the time of model creation , if you closely look at the connection string you will find that there is a little more information within the connection string, the string is not a straight-forward connection string as we see generally for the ADO.NET environment.

Here is one example for your better understanding.

<add name="efDBEntities"
connectionString="metadata=res://*/testModel.csdl|res://*/testModel.ssdl|res://*/testModel.msl;
provider=System.Data.SqlClient;
provider connection string="
data source=SOURAV-PC;initial catalog=efDB;
user id=sourav;password=password;
MultipleActiveResultSets=True;
App=EntityFramework""
providerName="System.Data.EntityClient" />

Just have a look at that metadata property and all the other stuff in there along with username, password, initialCatalog and data source name.

If you want to build your own connection string by setting all those properties at run time then here is the solution. Just open the context file of Entity Framework and modify the code as in the following. In this example we have implemented a Singleton class to supply the connection string. The reason is, when the first request hits a controller to fetch data, the connection string will be formed and it will be used by every subsequent request.

namespace WebAPI
{
using System;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
public class SingleConnection
{
private SingleConnection(){}
private static SingleConnection _ConsString = null;
private String _String = null;
public static string ConString
{
get
{
if (_ConsString == null)
{
_ConsString = new SingleConnection { _String = SingleConnection.Connect() };
return _ConsString._String;
}
else
return _ConsString._String;
}
}
public static string Connect()
{
//Build an SQL connection string
SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
{
DataSource = "SOURAV-PC", // Server name
InitialCatalog = "efDB", //Database
UserID = "sourav", //Username
Password = "mypassword", //Password
};
//Build an Entity Framework connection string
EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
{
Provider = "System.Data.SqlClient",
Metadata = "res://*/testModel.csdl|res://*/testModel.ssdl|res://*/testModel.msl",
ProviderConnectionString = sqlString.ToString()
};
return entityString.ConnectionString;
}
}
public partial class efDBEntities : DbContext
{
public efDBEntities() : base(SingleConnection.ConString)
{
//"name=efDBEntities"
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
}
}

In the example we have used the Connect() function to populate all the properties, if needed then the credentials can be pulled from another application.

Thanks, Happy learning.