HOW TO: dynamic connection string for entity frame

问题:

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)

回答1:

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.

回答2:

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.