Connection Strings for Entity Framework

I want to share same Database information across multiple entities in Silverlight.. but I want the connection string to be named xyz and have everyone access that connection string from machine.config...

The meta data part of the entities will be different since I didn't name the entities the same..

Can I put multiple entities in that metadata section?

Here is an example.. I want to use this connection string but note that i put multiple entities in the metadata section..

Basically I want to take this Connection String

<add name="XYZ" connectionString="metadata=res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;provider=S开发者_如何学编程ystem.Data.SqlClient;provider connection string=&quot;Data Source=SomeServer;Initial Catalog=SomeCatalog;Persist Security Info=True;User ID=Entity;Password=SomePassword;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

And this Connection String

 <add name="XYZ" connectionString="metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SOMESERVER;Initial Catalog=SOMECATALOG;Persist Security Info=True;User ID=Entity;Password=Entity;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

To make this Connection String

<add name="XYZ" connectionString="metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl|res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SOMESERVER;Initial Catalog=SOMECATALOG;Persist Security Info=True;User ID=Entity;Password=SOMEPASSWORD;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

But it simply doesn't work. Neither project can connect to it.

string encConnection = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
Type contextType = typeof(test_Entities);
object objContext = Activator.CreateInstance(contextType, encConnection);
return objContext as test_Entities; 

Unfortunately, combining multiple entity contexts into a single named connection isn't possible. If you want to use named connection strings from a .config file to define your Entity Framework connections, they will each have to have a different name. By convention, that name is typically the name of the context:

<add name="ModEntity" connectionString="metadata=res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SomeServer;Initial Catalog=SomeCatalog;Persist Security Info=True;User ID=Entity;Password=SomePassword;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
<add name="Entity" connectionString="metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SOMESERVER;Initial Catalog=SOMECATALOG;Persist Security Info=True;User ID=Entity;Password=Entity;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

However, if you end up with namespace conflicts, you can use any name you want and simply pass the correct name to the context when it is generated:

var context = new Entity("EntityV2");

Obviously, this strategy works best if you are using either a factory or dependency injection to produce your contexts.

Another option would be to produce each context's entire connection string programmatically, and then pass the whole string in to the constructor (not just the name).

// Get "Data Source=SomeServer..."
var innerConnectionString = GetInnerConnectionStringFromMachinConfig();
// Build the Entity Framework connection string.
var connectionString = CreateEntityConnectionString("Entity", innerConnectionString);
var context = new EntityContext(connectionString);

How about something like this:

Type contextType = typeof(test_Entities);
string innerConnectionString = ConfigurationManager.ConnectionStrings["Inner"].ConnectionString;
string entConnection = 
    string.Format(
        "metadata=res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl;provider=System.Data.SqlClient;provider connection string=\"{1}\"",
        contextType.Name,
        innerConnectionString);
object objContext = Activator.CreateInstance(contextType, entConnection);
return objContext as test_Entities; 

... with the following in your machine.config:

<add name="Inner" connectionString="Data Source=SomeServer;Initial Catalog=SomeCatalog;Persist Security Info=True;User ID=Entity;Password=SomePassword;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

This way, you can use a single connection string for every context in every project on the machine.

Instead of using config files you can use a configuration database with a scoped systemConfig table and add all your settings there.

CREATE TABLE [dbo].[SystemConfig]  
    (  
      [Id] [int] IDENTITY(1, 1)  
                 NOT NULL ,  
      [AppName] [varchar](128) NULL ,  
      [ScopeName] [varchar](128) NOT NULL ,  
      [Key] [varchar](256) NOT NULL ,  
      [Value] [varchar](MAX) NOT NULL ,  
      CONSTRAINT [PK_SystemConfig_ID] PRIMARY KEY NONCLUSTERED ( [Id] ASC )  
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  
    )  
ON  [PRIMARY]  

GO  

SET ANSI_PADDING OFF  
GO  

ALTER TABLE [dbo].[SystemConfig] ADD  CONSTRAINT [DF_SystemConfig_ScopeName]  DEFAULT ('SystemConfig') FOR [ScopeName]  
GO 

With such configuration table you can create rows like such:

Then from your your application dal(s) wrapping EF you can easily retrieve the scoped configuration.
If you are not using dal(s) and working in the wire directly with EF, you can make an Entity from the SystemConfig table and use the value depending on the application you are on.

First try to understand how Entity Framework Connection string works then you will get idea of what is wrong.

1. You have two different models, Entity and ModEntity
2. This means you have two different contexts, each context has its own Storage Model, Conceptual Model and mapping between both.
3. You have simply combined strings, but how does Entity's context will know that it has to pickup entity.csdl and ModEntity will pickup modentity.csdl? Well someone could write some intelligent code but I dont think that is primary role of EF development team.
4. Also machine.config is bad idea.
5. If web apps are moved to different machine, or to shared hosting environment or for maintenance purpose it can lead to problems.
6. Everybody will be able to access it, you are making it insecure. If anyone can deploy a web app or any .NET app on server, they get full access to your connection string including your sensitive password information.

Another alternative is, you can create your own constructor for your context and pass your own connection string and you can write some if condition etc to load defaults from web.config

Better thing would be to do is, leave connection strings as it is, give your application pool an identity that will have access to your database server and do not include username and password inside connection string.

To enable the same edmx to access multiple databases and database providers and vise versa I use the following technique:

1) Define a ConnectionManager:

public static class ConnectionManager
{
    public static string GetConnectionString(string modelName)
    {
        var resourceAssembly = Assembly.GetCallingAssembly();

        var resources = resourceAssembly.GetManifestResourceNames();

        if (!resources.Contains(modelName + ".csdl")
            || !resources.Contains(modelName + ".ssdl")
            || !resources.Contains(modelName + ".msl"))
        {
            throw new ApplicationException(
                    "Could not find connection resources required by assembly: "
                    + System.Reflection.Assembly.GetCallingAssembly().FullName);
        }

        var provider = System.Configuration.ConfigurationManager.AppSettings.Get(
                        "MyModelUnitOfWorkProvider");

        var providerConnectionString = System.Configuration.ConfigurationManager.AppSettings.Get(
                        "MyModelUnitOfWorkConnectionString");

        string ssdlText;

        using (var ssdlInput = resourceAssembly.GetManifestResourceStream(modelName + ".ssdl"))
        {
            using (var textReader = new StreamReader(ssdlInput))
            {
                ssdlText = textReader.ReadToEnd();
            }
        }

        var token = "Provider=\"";
        var start = ssdlText.IndexOf(token);
        var end = ssdlText.IndexOf('"', start + token.Length);
        var oldProvider = ssdlText.Substring(start, end + 1 - start);

        ssdlText = ssdlText.Replace(oldProvider, "Provider=\"" + provider + "\"");

        var tempDir = Environment.GetEnvironmentVariable("TEMP") + '\\' + resourceAssembly.GetName().Name;
        Directory.CreateDirectory(tempDir);

        var ssdlOutputPath = tempDir + '\\' + Guid.NewGuid() + ".ssdl";

        using (var outputFile = new FileStream(ssdlOutputPath, FileMode.Create))
        {
            using (var outputStream = new StreamWriter(outputFile))
            {
                outputStream.Write(ssdlText);
            }
        }

        var eBuilder = new EntityConnectionStringBuilder
        {
            Provider = provider,

            Metadata = "res://*/" + modelName + ".csdl"
                        + "|" + ssdlOutputPath
                        + "|res://*/" + modelName + ".msl",

            ProviderConnectionString = providerConnectionString
        };

        return eBuilder.ToString();
    }
}

2) Modify the T4 that creates your ObjectContext so that it will use the ConnectionManager:

public partial class MyModelUnitOfWork : ObjectContext
{
    public const string ContainerName = "MyModelUnitOfWork";
    public static readonly string ConnectionString
        = ConnectionManager.GetConnectionString("MyModel");

3) Add the following lines to App.Config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="MyModelUnitOfWork" connectionString=... />
  </connectionStrings>
  <appSettings>
    <add key="MyModelUnitOfWorkConnectionString" value="data source=MyPc\SqlExpress;initial catalog=MyDB;integrated security=True;multipleactiveresultsets=True" />
    <add key="MyModelUnitOfWorkProvider" value="System.Data.SqlClient" />
  </appSettings>
</configuration>

The ConnectionManager will replace the ConnectionString and Provider to what ever is in the App.Config.

You can use the same ConnectionManager for all ObjectContexts (so they all read the same settings from App.Config), or edit the T4 so it creates one ConnectionManager for each (in its own namespace), so that each reads separate settings.

What I understand is you want same connection string with different Metadata in it. So you can use a connectionstring as given below and replace "" part. I have used your given connectionString in same sequence.

connectionString="<METADATA>provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SomeServer;Initial Catalog=SomeCatalog;Persist Security Info=True;User ID=Entity;Password=SomePassword;MultipleActiveResultSets=True&quot;"

For first connectionString replace <METADATA> with "metadata=res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;"

For second connectionString replace <METADATA> with "metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl;"

For third connectionString replace <METADATA> with "metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl|res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;"

Happy coding!

Silverlight applications do not have direct access to machine.config.