Dynamically Set Connection String in Entity Data Model in C#

Question

Dynamically Set Connection String in Entity Data Model in C#

c# entity-framework

Question

I'm working on a project that relies on the ADO.NET Entity Data Model. I have 4 identical database schemas, one for each department in my company (Marketing, Finanace, Administrative, and HR). I currently know which which department each of my users resides in. I would like to use the department to determine which database the user can connect to.

In C# code, how do I set the connection string at runtime? Currently, I have

string connectionString = GetUsersConnectionString();
using (MyEntities entities = new MyEntities())
{
  MyDataEntity myDataEntity = new MyDataEntity();

  // Save to the database
  entities.MyDataEntity.Add(myDataEntity);
  entities.SaveChanges();
}

What am I missing here? How do I set the connection string of MyEntities to connectionString?

Bill Jones

View more on Stack Overflow

spajce · Accepted Answer · 1/16/2013 2:33:58 PM

Here's a good way approach.

public static class ConnectionManager
    {
        public static string GetSqlConnectionString()
        {

            var serverName = @"" + ConfigurationManager.AppSettings["ServerName"];
            var databaseName = ConfigurationManager.AppSettings["DatabaseName"];
            var username = ConfigurationManager.AppSettings["Username"];
            var password = ConfigurationManager.AppSettings["Password"];

            SqlConnectionStringBuilder providerCs = new SqlConnectionStringBuilder();

            providerCs.DataSource = serverName;
            providerCs.InitialCatalog = databaseName;
            //providerCs.IntegratedSecurity = true;
            //providerCs.UserInstance = true;
            providerCs.UserID = username;
            providerCs.Password = password;
            var csBuilder = new EntityConnectionStringBuilder();

            csBuilder.Provider = "System.Data.SqlClient";
            csBuilder.ProviderConnectionString = providerCs.ToString();

            csBuilder.Metadata = string.Format("res://{0}/yourDataBase.csdl|res://{0}/yourDataBase.ssdl|res://{0}/yourDataBase.msl",
                typeof(yourDataBaseEntities).Assembly.FullName);

            return csBuilder.ToString();
        }
    }

Usage:

In your app.config place this code

<appSettings>
    <add key="ServerName" value="UNKNOWN01-PC\sampleServer"/>
    <add key="DatabaseName" value="samplDatabase"/>
    <add key="Username" value="sampleUser"/>
    <add key="Password" value="sampPass"/>
</appSettings>

Then,

string connectionString = GetSqlConnectionString();
using (MyEntities entities = new MyEntities(connectionString))
{
  MyDataEntity myDataEntity = new MyDataEntity();

  // Save to the database
  entities.MyDataEntity.Add(myDataEntity);
  entities.SaveChanges();
}

Dynamically Set Connection String in Entity Data Model in C#

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related