C# change custom connection string at runtime

Question

Bit new on EF. I'm, creating application where user selects database from computer. and now i want to change connection string to match location of the database for example : this is current connection string that points to database location somewhere on disk (C:\Users\student\Documents\TestData.md) :

add name="test" connectionString="metadata=res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl;provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\v11.0;attachdbfilename=&quot;C:\Users\student\Documents\TestData.mdf&quot;;integrated security=True;connect timeout=30;MultipleActiveResultSets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />

now when user selects new database from disk the connection sting needs to change to location where new database is located (C:\Users\student\Desktop\NewSelectedDatabase.mdf) :

add name="test" connectionString="metadata=res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl;provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\v11.0;attachdbfilename=&quot;C:\Users\student\Desktop\NewSelectedDatabase.mdf&quot;;integrated security=True;connect timeout=30;MultipleActiveResultSets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />

now i've created filedialog so user can select database and to get its adress . i've also changed my edmax to to recive custom connection string :

    public partial class Tester : DbContext
{
    public Tester()
        : base("name=Test")
    {
    }

    public Tester(string customcs)
        : base(customcs)
    {

    }

now my problem is what do i pass to constructor as custom connection string ? i hope you understood me because i'm realy bad at english and explainig things

Found the solution : i copy pasted my old connection string to strinng then just changed attachdbfilename to openfile dialog location then passed to constructor thanks :D — user3764527
– user3764527, Commented Dec 9, 2014 at 13:31
@user3764527 -- I added to my answer below, it offers another more dynamic way (using partial classes). — Jason
– Jason, Commented Dec 10, 2014 at 16:29

Jason · Accepted Answer · 2014-12-10 20:49:27Z

1

When you have the EF designer up, on the properties window is a connectionstring setting. Once you have everything set as you like, clear that setting to none. It rewrites the generated code to accept a connection string passed in on instantiating.

var mything= new dbcontext (connstring)

Another option would be to just create a new class (.cs) file giving it the same namespace that your Tester EF context belongs to, and paste this in there:

public partial class Tester : DbContext {
    public Tester(string _connectionString) : base(ConnectionString(_connectionString)) {
        this.Configuration.ProxyCreationEnabled = false;
        this.Configuration.AutoDetectChangesEnabled = false;
    }
    private static string ConnectionString(string _connectionString) {
        EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
        entityBuilder.ProviderConnectionString = _connectionString;
        entityBuilder.Metadata = "res://*/Models.Tester.csdl|res://*/Models.Tester.ssdl|res://*/Models.Tester.msl";
        entityBuilder.Provider = "System.Data.SqlClient";
        return entityBuilder.ToString();
    }   
}

Notice it's a partial class (just like the auto-generated ones for Tester are) -- and so you're adding to the auto-generated class made by EF (again, make sure they're in the same namespaces, so it really is an addition to the partial class, not just you off making your own little one).

This way, you're adding a new construction instantiation (that's passing a connection string) that gets modified into the right entity-connection-string builder (via the private static ConnectionString method).

var myThing = new Tester(ConfigurationManager.ConnectionStrings["db_DBName"].ToString());

I have one line in the web.config for the connection:

<add name="db_DBName" connectionString="Data Source=DBSERVER;initial Catalog=DBNAME;" providerName="System.Data.SqlClient" />

the build target defines its transformantion, and I just pass the same string into the code all the time.

edited Dec 10, 2014 at 20:49

answered Dec 9, 2014 at 14:14

Jason

3,0322 gold badges27 silver badges25 bronze badges

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

2 Comments

user3764527 Over a year ago

Nice :D , var mything= new dbcontext (connstring) is pretty good but this gives me more options. is this how models are made by using code first ?

Jason Over a year ago

Maybe, probably? I've yet to tackle a Code-First approach, each time I've done it, I'm scaffolding off an existing database (DB First, designer). I remember coming up with that second option when I needed to target an Oracle database and using the managed driver they have.

Collectives™ on Stack Overflow

C# change custom connection string at runtime

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related