EF6 DBContext Dynamic Connection String

Question

public partial class ProcessContext : DbContext
{
    static ProcessContext()
    {
        Database.SetInitializer<ProcessContext>(null);
    }

    public ProcessContext()
        : base("Name=ProcessCS") //Comes from Config File
    {
    }

    --DBSets 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       --Code
    }
}

This is a Multi Tenent DB where we have 3 Different DB's. Centralized DB is in common location and would not be changed. This is where rest of the DB details will be stored. I need to create the Connection string @ runtime where the details will be coming from this centralized DB. Can some one please let me know how to go about it?

I tried with the following code, but it is not working. This Method will be called here

public ProcessContext()
    : base(nameOrConnectionString: ConnectionString())
{
}

private static string ConnectionString()
{
    SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
    sqlBuilder.DataSource = "XXX";
    sqlBuilder.InitialCatalog = "YYY";
    sqlBuilder.PersistSecurityInfo = true;
    sqlBuilder.IntegratedSecurity = true;
    sqlBuilder.MultipleActiveResultSets = true;

    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
    entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
    entityBuilder.Metadata = "res://*/";
    entityBuilder.Provider = "System.Data.SqlClient";

    return entityBuilder.ToString();
}

"But it is not working" - what doesn't work about it? Do you get an error message? — Steven V
– Steven V, Commented Jan 16, 2014 at 15:22
solution on how to use dynamic contexts stackoverflow.com/a/16133150/1347784 — phil soady
– phil soady, Commented Mar 13, 2015 at 8:03
@StevenV When I tried above code I am getting an error saying "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication". What could be the possible solution for it? — Sarthak Shah
– Sarthak Shah, Commented Dec 19, 2017 at 4:37
Thanks a lot, It is working fine. I found a little bit mistake in my code. — Sarthak Shah
– Sarthak Shah, Commented Dec 19, 2017 at 5:54

albert · Accepted Answer · 2014-04-22 18:12:11Z

24

For SQL Server connection, override the entity container class: (works in EF6, SQL server 2012 express, VS2013)

public partial class PxxxxEntities
{
    private PxxxxEntities(string connectionString)
        : base(connectionString)
    {
    }

    public static PxxxxEntities ConnectToSqlServer(string host, string catalog, string user, string pass, bool winAuth)
    {
        SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder
        {
            DataSource = host,
            InitialCatalog = catalog,
            PersistSecurityInfo = true,
            IntegratedSecurity = winAuth,
            MultipleActiveResultSets = true,

            UserID = user,
            Password = pass,
        };

        // assumes a connectionString name in .config of MyDbEntities
        var entityConnectionStringBuilder = new EntityConnectionStringBuilder
        {
            Provider = "System.Data.SqlClient",
            ProviderConnectionString = sqlBuilder.ConnectionString,
            Metadata = "res://*/DbModel.csdl|res://*/DbModel.ssdl|res://*/DbModel.msl",
        };

        return new PxxxxEntities(entityConnectionStringBuilder.ConnectionString);
    }
}

answered Apr 22, 2014 at 18:12

albert

2412 silver badges2 bronze badges

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

2 Comments

Tim Butterfield Over a year ago

The above certainly worked for me and saved me a lot of pain.

Harvey Darvey Over a year ago

This is a nice solution. However, just wondering, the "MultipleActiveResultSets = true" part, is it advisable for EF6? msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx

luksan · Accepted Answer · 2014-01-16 16:39:03Z

15

You should pass an ordinary connection string into the the DbContext constructor, not an entity connection string. So try changing your code as follows:

public ProcessContext()
    : base(ConnectionString())
{
}

private static string ConnectionString()
{
    SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
    sqlBuilder.DataSource = "XXX";
    sqlBuilder.InitialCatalog = "YYY";
    sqlBuilder.PersistSecurityInfo = true;
    sqlBuilder.IntegratedSecurity = true;
    sqlBuilder.MultipleActiveResultSets = true;

    return sqlBuilder.ToString();
}

answered Jan 16, 2014 at 16:39

luksan

7,8173 gold badges40 silver badges38 bronze badges

1 Comment

Gautham Nayak Over a year ago

@luskan can you please help me out with this question as well.. stackoverflow.com/questions/21181253/…

Mehul Akabari · Accepted Answer · 2016-12-22 11:26:10Z

You have to change Web.config

<connectionStrings>
<add name="DefaultConnection" connectionString="data source=mydb;initial catalog=mydatabase;persist security info=True;user id=sa;password=password;multipleactiveresultsets=True;application name=EntityFramework" providerName="System.Data.SqlClient" />
<add name="DataContext" connectionString="data source=mydb;initial catalog=mydatabase;persist security info=True;user id=sa;password=password;multipleactiveresultsets=True;application name=EntityFramework" providerName="System.Data.SqlClient" />
<!-- Here Add multiple database connection string  -->

after you have to modify ProcessContext.cs file one constructor to create default connection

public ProcessContext()
      :base("name=DefaultConnection"){
        //here code
       }

other parametrise constructor dynamic connection to database

public ProcessContext(string DynamicConnectionString)
       :base(DynamicConnectionString){
// herer code }

here to default connection is "DefaultConnection" but you have chnage connection string given code any controller

ProcessContext db=new ProcessContext();//this is default connection

ProcessContext db=new ProcessContext("DataContext");//dynamic change connection string

try this code

Celso Jr · Accepted Answer · 2016-03-31 13:17:34Z

1

Try to use an app.config file. Then call the desired connection string by its name:

using System.Configuration;

public ProcessContext()
    : base(ConnectionString("foo"))
{
}

private static string ConnectionString(string connKey)
{
    var conn = ConfigurationManager.ConnectionStrings[connKey].ConnectionString;
    return conn;
}

answered Mar 31, 2016 at 13:17

Celso Jr

2951 silver badge11 bronze badges

Comments

dodgy_coder · Accepted Answer · 2015-03-13 07:57:01Z

0

This method worked well for me. I just added another named connection string to my app.config and then passed the name into the below GetDbContext() static method as shown below.

Example of usage:

var dbAlternate = PxxxxEntities.GetDbContext("PxxxxEntitiesAlternate")

Add the following to your project:

public partial class PxxxxEntities
{
    private PxxxxEntities(string name)
        : base("name=" + name)
    {
    }

    public static PxxxxEntities GetDbContext(string name)
    {
        return new PxxxxEntities(name);
    }
}

answered Mar 13, 2015 at 7:57

dodgy_coder

13.1k12 gold badges61 silver badges70 bronze badges

Comments

halfer · Accepted Answer · 2020-01-12 22:45:49Z

0

Dynamic Connection String EF

Use this code:

    public Entities(): base(ConnectToSqlServer())
    {
    }



    public static string ConnectToSqlServer()
    {
        SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder
        {
            DataSource = "ServerName",
            InitialCatalog = "DatabaseName",
            PersistSecurityInfo = true,
            IntegratedSecurity = false,
            MultipleActiveResultSets = true,

            UserID = "Username",
            Password = "Password",
        };
        var entityConnectionStringBuilder = new EntityConnectionStringBuilder
        {
            Provider = "System.Data.SqlClient",
            ProviderConnectionString = sqlBuilder.ConnectionString,
            Metadata = "res://*/Data.Database.csdl|res://*/Data.Database.ssdl|res://*/Data.Database.msl",
        };

        return entityConnectionStringBuilder.ConnectionString;
    }

edited Jan 12, 2020 at 22:45

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Jan 11, 2020 at 12:26

Amin Evin

1

1 Comment

E. Zeytinci Over a year ago

While this code may provide a solution to OP's problem, it is highly recommended that you provide additional context regarding why and/or how this code answers the question. Code only answers typically become useless in the long-run because future viewers experiencing similar problems cannot understand the reasoning behind the solution.

Collectives™ on Stack Overflow

EF6 DBContext Dynamic Connection String

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related