HOW TO: dynamic connection string for entity framework

Question

Like the title. How can I do it?

I tried something, but it doesn't work like I was expecting.

I'm using an Entity Framework model. I need to pass my connection string like parameter, so, in another file, I've written

namespace MyNamespace.Model
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;

    public partial class MyEntities: DbContext
    {
        public MyEntities(string nameOrConnectionString) : base(nameOrConnectionString)
        {

        }
    }
}

When I startup the app, I call this constructor in this way, so I can refer to this from anyway in the app:

public static MyEntities dbContext = new MyEntities(mdlImpostazioni.SetConnectionString());

where mdlImpostazioni.SetConnectionString() returns a string (the data are correct):

server=192.168.1.100\SVILUPPO;database=MyDB;uid=myName;pwd=111111;

When I execute this code, it seems to be all ok, but when I try to make a query like:

var query = (from r in MainWindow.dbContext.TabTipoSistema select r);

it throws an exception from here:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    throw new UnintentionalCodeFirstException(); //exception here
}

So, this is a bad way... which is the right one? (using only code C#, not from xaml)

Is connection string proper? I've never seen such a conn string before. — , Sep 30 '14 at 11:34
I think so. Further, the data inside the string are correct... I don't know where I'm getting wrong! — Piero Alberto, Sep 30 '14 at 11:36
There's nothing wrong with this way, it's just incomplete. Nearly there... — reckface, Sep 30 '14 at 12:25

reckface · Accepted Answer · 2014-09-30T12:44:35.710

Your approach is correct, but you need to remember the connection string for EF requires the metadata and so on. So use the EntityConnectionStringBuilder. For example:

// the model name in the app.config connection string (any model name - Model1?)
private static string GetConnectionString(string model, YourSettings settings)
{
    // Build the provider connection string with configurable settings
    var providerSB = new SqlConnectionStringBuilder
    {
        // You can also pass the sql connection string as a parameter instead of settings
        InitialCatalog = settings.InitialCatalog,
        DataSource = settings.DataSource,
        UserID = settings.User,
        Password = settings.Password
    };

    var efConnection = new EntityConnectionStringBuilder();
    // or the config file based connection without provider connection string
    // var efConnection = new EntityConnectionStringBuilder(@"metadata=res://*/model1.csdl|res://*/model1.ssdl|res://*/model1.msl;provider=System.Data.SqlClient;");
    efConnection.Provider = "System.Data.SqlClient";
    efConnection.ProviderConnectionString = providerSB.ConnectionString;
    // based on whether you choose to supply the app.config connection string to the constructor
    efConnection.Metadata = string.Format("res://*/Model.{0}.csdl|res://*/Model.{0}.ssdl|res://*/Model.{0}.msl", model); ;
    return efConnection.ToString();

}
// Or just pass the connection string
private static string GetConnectionString(string model, string providerConnectionString)
{

    var efConnection = new EntityConnectionStringBuilder();
    // or the config file based connection without provider connection string
    // var efConnection = new EntityConnectionStringBuilder(@"metadata=res://*/model1.csdl|res://*/model1.ssdl|res://*/model1.msl;provider=System.Data.SqlClient;");
    efConnection.Provider = "System.Data.SqlClient";
    efConnection.ProviderConnectionString = providerConnectionString;
    // based on whether you choose to supply the app.config connection string to the constructor
    efConnection.Metadata = string.Format("res://*/Model.{0}.csdl|res://*/Model.{0}.ssdl|res://*/Model.{0}.msl", model);
    // Make sure the "res://*/..." matches what's already in your config file.
    return efConnection.ToString();

}

EDIT

The exception you get is because when you pass a pure SQL connection string, it assumes you are working with Code first, so it calls the OnModelCreation event. When you include the MetaData section as shown above, that tells EF it's a complete EF connection string.

and how have I to call this GetConnectionString? what I put as parameters for model and settings? — Piero Alberto, Sep 30 '14 at 12:39
You can build it with the provider (SQL) connection string or your own settings file — reckface, Sep 30 '14 at 12:45
I have delete the "settings" paramenter and passed the name of my model... and it works!!!!! Thanks a lot!!!!! :) — Piero Alberto, Sep 30 '14 at 12:46

apomene · Answer 2 · 2014-09-30T11:49:58.187

0

I believe the problem lies on the Datasource you specify. You need to add the port of the connection, e.g if your SQL Server is configured on Port 1433, try:

server=192.168.1.100,1433\SVILUPPO;database=MyDB;uid=myName;pwd=111111;

more details about connection strings you can find Here

Also I am not sure if uid and pwd are valid, better try User ID and Password:

 Server=192.168.1.100,1433\SVILUPPO;Database=MyDB;User ID=myName;Password=111111;

Finally mind the case sensitivity.

edited Sep 30 '14 at 11:49

answered Sep 30 '14 at 11:42

apomene

14,282
9
46
72

and what is the param name for the port? simply "port"? – Piero Alberto Sep 30 '14 at 11:43
you just add the port after the IP seperated by a comma (,) as shown in my answer – apomene Sep 30 '14 at 11:45
Unluckyly not... I used the same syntax of your answer, but it throws the same error as before... – Piero Alberto Sep 30 '14 at 11:52

HOW TO: dynamic connection string for entity framework

2 Answers2

Linked