Connection Strings for Entity Framework

Question

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   lt add name  XYZ  connectionString  metadata res     ModEntity csdl res     ModEntity ssdl res     ModEntity msl provider System Data SqlClient provider connection string  amp quot Data Source SomeServer Initial Catalog SomeCatalog Persist Security Info True User ID Entity Password SomePassword MultipleActiveResultSets True amp quot   providerName  System Data EntityClient    gt    And this Connection String    lt add name  XYZ  connectionString  metadata res     Entity csdl res     Entity ssdl res     Entity msl provider System Data SqlClient provider connection string  amp quot Data Source SOMESERVER Initial Catalog SOMECATALOG Persist Security Info True User ID Entity Password Entity MultipleActiveResultSets True amp quot   providerName  System Data EntityClient    gt    To make this Connection String   lt 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  amp quot Data Source SOMESERVER Initial Catalog SOMECATALOG Persist Security Info True User ID Entity Password SOMEPASSWORD MultipleActiveResultSets True amp quot   providerName  System Data EntityClient    gt    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

User · Answer

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

You have two different models, Entity and ModEntity
This means you have two different contexts, each context has its own Storage Model, Conceptual Model and mapping between both.
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.
Also machine.config is bad idea.
If web apps are moved to different machine, or to shared hosting environment or for maintenance purpose it can lead to problems.
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.

User · Answer

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     lt  xml version  1 0  encoding  utf-8   gt   lt configuration gt     lt connectionStrings gt       lt add name  MyModelUnitOfWork  connectionString           lt  connectionStrings gt     lt appSettings gt       lt add key  MyModelUnitOfWorkConnectionString  value  data source MyPc SqlExpress initial catalog MyDB integrated security True multipleactiveresultsets True    gt       lt add key  MyModelUnitOfWorkProvider  value  System Data SqlClient    gt     lt  appSettings gt   lt  configuration gt    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

User · Answer

Silverlight applications do not have direct access to machine config

User · Answer

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    lt add name  ModEntity  connectionString  metadata res     ModEntity csdl res     ModEntity ssdl res     ModEntity msl provider System Data SqlClient provider connection string  amp quot Data Source SomeServer Initial Catalog SomeCatalog Persist Security Info True User ID Entity Password SomePassword MultipleActiveResultSets True amp quot   providerName  System Data EntityClient    gt   lt add name  Entity  connectionString  metadata res     Entity csdl res     Entity ssdl res     Entity msl provider System Data SqlClient provider connection string  amp quot Data Source SOMESERVER Initial Catalog SOMECATALOG Persist Security Info True User ID Entity Password Entity MultipleActiveResultSets True amp quot   providerName  System Data EntityClient    gt    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    lt 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    gt    This way  you can use a single connection string for every context in every project on the machine

User · Answer

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   lt METADATA gt provider System Data SqlClient provider connection string  amp quot Data Source SomeServer Initial Catalog SomeCatalog Persist Security Info True User ID Entity Password SomePassword MultipleActiveResultSets True amp quot     For first connectionString replace  lt METADATA gt  with  metadata res     ModEntity csdl res     ModEntity ssdl res     ModEntity msl    For second connectionString replace  lt METADATA gt  with  metadata res     Entity csdl res     Entity ssdl res     Entity msl    For third connectionString replace  lt METADATA gt  with  metadata res     Entity csdl res     Entity ssdl res     Entity msl res     ModEntity csdl res     ModEntity ssdl res     ModEntity msl    Happy coding

User · Answer

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

[c#] Connection Strings for Entity Framework

Examples related to c#

Examples related to entity-framework

Examples related to machine.config