How to share a connection string between multiple entity data model

Question

I have a project that has 4 entity data model.for building them I don't want save a connection string in my project and I want store a connection string in app.config files and share it between my models.How I can do this?

thanks

Answer 1

Assuming DbContext and model/database first.

1. Leave the generated EF connection strings in your app.config file intact. As Arthur said, they contain the paths to the EF metadata (csdl/ssdl/msl). They are also used during development by the model designer.
2. Add a store connection string called e.g. "SharedConnection". This is the only connection string which needs to be modified in production.
3. Create a base class which derives from DbContext and derive all your contexts from that class.
4. Create the default EF connection explicitely in the base class. Then modify it to use the shared connection string like that:

public class BaseContext : DbContext
{
    public BaseContext(string nameOrConnectionString)
        : base(CreateConnection(nameOrConnectionString), true)
    {
    }

    private static EntityConnection CreateConnection(string connectionString)
    {
        // Create a (plain old database) connection using the shared connection string.
        DbConnection dbConn = Database.DefaultConnectionFactory.CreateConnection(
            ConfigurationManager.ConnectionStrings["SharedConnection"].ConnectionString);

        // Create a helper EntityConnection object to build a MetadataWorkspace out of the
        // csdl/ssdl/msl parts of the generated EF connection string for this DbContext.
        EntityConnection wsBuilder = new EntityConnection(connectionString);

        // Merge the specific MetadataWorkspace and the shared DbConnection into a new EntityConnection.
        return new EntityConnection(wsBuilder.GetMetadataWorkspace(), dbConn);
    }
}

The code of the derived contexts does not change except that they must inherit from BaseContext. Here is a more robust CreateConnection method. It has error handling and removes the name of the shared connection string from the code at the price of adding an application setting.

private static EntityConnection CreateConnection(string connectionString)
{
    // Find the name of the shared connection string.
    const string appSettingKey = "SharedConnectionStringName";

    string sharedConnectionStringName = ConfigurationManager.AppSettings[appSettingKey];
    if (string.IsNullOrEmpty(sharedConnectionStringName))
    {
        throw new Exception(string.Format(
            "Shared connection not configured. " +
            "Please add a setting called \"{0}\" to the \"appSettings\" " +
            "section of the configuration file.", appSettingKey));
    }

    // Create a (plain old database) connection using the shared connection string.
    ConnectionStringSettings backendSettings =
        ConfigurationManager.ConnectionStrings[sharedConnectionStringName];
    if (backendSettings == null)
    {
        throw new Exception(string.Format(
            "Invalid connection string name \"{0}\" in appSetting \"{1}\"",
            sharedConnectionStringName, appSettingKey));
    }

    System.Data.Common.DbConnection dbConn =
        Database.DefaultConnectionFactory.CreateConnection(
        backendSettings.ConnectionString);

    // Create a helper EntityConnection object to build a MetadataWorkspace out of the
    // csdl/ssdl/msl parts of the generated EF connection string for this DbContext.
    EntityConnection wsBuilder = new EntityConnection(connectionString);

    // Merge the specific MetadataWorkspace and the shared DbConnection into a new EntityConnection.
    return new EntityConnection(wsBuilder.GetMetadataWorkspace(), dbConn);
}

Answer 2

You could use Code First. You would have to write the mappings for each of your models in code, but then it is very easy to use the same connection string--just pass "name=MyConnectionStringName" to the DbContext constructor.

When using Database First with an EDMX the EF connection string contains both:

• The "store connection string" which provides the info on how to connect to the database
• Paths to the EF metadata describing the model

Since the second part is different for each of your models you are going to need to have four different connection strings.

If you want to only have one connection string then you'll need to: - Store the metadata information in some other way - Write code to load the metadata and create a MetadataWorkspace - Read your single store connection string and create a connection from it - Use both the connection and the MetadataWorkspace to create an EntityConnection - Use the EntityConnection to create an ObjectContext - If you're using DbContext, then use the ObjectContext to create a DbContext

It seems very hard to believe that writing all this non-trivial code will better than just having four connection strings in your app.config.

Answer 3

You can pass the EF connection string to the ObjectContext. If you have multiple models you can put each ones connection string in your app.config, give each one a key, and look this up when needed and pass the appropriate string to the context when you instanciate it.