Dynamically Set Connection String in Entity Data Model in C#

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?

spajce · Accepted Answer · 2013-01-16 14:33:58Z

1

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();
}

answered Jan 16, 2013 at 14:33

spajce

7,0905 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bill Jones Over a year ago

MyEntities doesn't have an overload that allows for a string though. What would cause that?

Bill Jones Over a year ago

Based on my web.config file, it looks like 5.0. I see the following in my web.config: <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

spajce Over a year ago

okay, try to add a string parameter from your MyEntities(string con) then rebuild your project and try to apply my answer.

Bill Jones Over a year ago

unfortunately, that did not work. I got a compile time error that said: 'MyEntities' does not contain a constructor that takes 1 arguments.

VoidMain · Accepted Answer · 2013-01-16 14:11:23Z

0

Use:

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

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

answered Jan 16, 2013 at 14:11

VoidMain

2,04721 silver badges22 bronze badges

2 Comments

Bill Jones Over a year ago

MyEntities doesn't have an overload that allows for an EntityConnection though. What would cause that?

VoidMain Over a year ago

Interesting, what is the base class? Is not ObjectContext?

William Smith · Accepted Answer · 2013-08-13 01:36:13Z

0

In your Model.Designer.cs file you have to Initialize a new Entities object that takes the required number of arguments. For example:

namespace:MyModel
{
#region Contexts 
public partial class MyEntities : ObjectContext
{
    #region Constructors

public MyEntities(EntityConnection connection) //add the required arguments
        : base(connection, "Entities")
    {
        this.ContextOptions.LazyLoadingEnabled = true;
        OnContextCreated();
    }
....

Then use your new Entity to manipulate whatever data the connection string retrieves e.g

 MyModel.MyEntities db = new MyModel.Entities(connectionString);
 var query = from rows in db.Table_Name orderby rows.ID select rows;

answered Aug 13, 2013 at 1:36

William Smith

5831 gold badge5 silver badges11 bronze badges

Collectives™ on Stack Overflow

Dynamically Set Connection String in Entity Data Model in C#

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related