ADO.NET Entity Connection String for Multiple Projects

Question

I am using multiple layer project where the DataModel hosts the ADo.NET Entity model and DataAccess layer does the validation.

However everytime I get a error like this

The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.

I have tried connection strings

<add name="SalesEntities" connectionString="metadata=res://*/SalesEntities.csdl|res://*/SalesEntities.ssdl|res://*/SalesEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=Phoenix;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

and

<add name="SalesEntities" connectionString="metadata=.\SalesEntities.csdl|.\SalesEntities.ssdl|.\SalesEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=Phoenix;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

also tried other combinations to refer the Root Directory of the Called project directory but no luck.

Any help is highly appreciated. Many Thanks as always :).

Hypethral

Answer 1

You have to put those connection strings in each applications app.config file. If you have a DAL that you generated the model in, and then try to consume the DAL in an EXE the same thing will happen. The EXE does not know the connection string.

The easiest thing I have found is to put an app.config on each project and just copy the connection string from the DAL I generated the models in originally. Then each will have a copy of that same connection string.

Grisette

hm, is it somehow possible to store all connection strings in one place and then 'tell' to all web.config files where to find that connection strings? – Monstrosity

You can put them in the MACHINE.CONFIG (global on the machine), but this requires more privs and deployment is not a good idea in that scenario. – Grisette

Using a partial class you can create your own constructor for the data context generated for your entities and have them set the connection string however you want. You will also have to call that specific constructor where ever you instantiate the data context. – Holton

Answer 2

If you copy your App.Config file into the main project and replace all the " with the normal ' character it should run

Foliate

Answer 3

I suggest a slight variation on the suggestions given above.

It's not a huge improvement, but at least it gives you some separation of concerns.

When the EF wizard creates the .edmx file and its associated .Designer.cs file, the C# code declares a partial class. So you can simply add another.cs file to the project containing the two EDM files.

This new file defines an additional static function for the same namespace and class.

This new static function will return an instance of the desired type (the descendant of ObjectContext).

The new file is a separate file, so it won't be overwritten if you re-create the .edmx and .Designer.cs.

You copy and paste the connection string from the .config of the EDM project, which is kind of a hack, but at least it keeps the connection string hidden in the EDM project.

The new file looks like this:

namespace MyNamespace
{
  public partial class MyEntities : ObjectContext
  {
    public static MyEntities New_MyEntities()
    {
      string connStr;
      MyEntities theContext;

      connStr = "metadata=res://*/MyClass.csdl|res://*/MyClass.ssdl|res://*/MyClass.msl;provider=System.Data.SqlClient;provider connection string=\"Data Source=localhost\\SQLSERVER;Initial Catalog=MyDb;Integrated Security=True;MultipleActiveResultSets=True\"";
      // set the connection string

      theContext = new MyEntities(connStr);
      // allocate it

      return theContext;
      // return it
    }
  }
}

To get a new entities object, you simply call the static function New_MyEntities() from your calling project.

Sequential

Answer 4

I passed the entityconnectionstring to all the instances of the objectContext classes and its working now.

But its a too much overhead, creating a property with connectionstring and passing it as a parameter to each instance

Cryptocrystalline

Answer 5

I add the same problem, trying to unit-test my DAL. I've found that this works :

<add name="SalesEntities" connectionString="metadata=res://*;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=Phoenix;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /> 

Roy

Answer 6

I had a similar problem with a WinForms project and despite trying everything I could find related to it on the web could not resolve my problem..... until I removed the field that I was using for my ObjectContext (private CubEntities _oc = new CubEntities()) from my BaseForm to the actual form using it.

Druce

Answer 7

I got same problem & i tried all the mentioned method. finally i solved it as mentioned. In my case I have separate data layer and presentation layer. in my app.config (data layer) i have connection like this.

 <add name="LibraryMgtSysEntities" connectionString="metadata=res://*/DataLibraryMgtSys.csdl|res://*/DataLibraryMgtSys.ssdl|res://*/DataLibraryMgtSys.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=abc;initial catalog=LibraryMgtSys;Persist Security Info=True;user id=sa;password=123;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

in my web.config i manually configured connection as follows:

<add name="DefaultConnection" providerName="System.Data.SqlClient"
 connectionString="Data Source=abc;
 Initial Catalog=LibraryMgtSys;
 Integrated Security=SSPI;
 user id=sa;password=123;" />

it gives me same exception as mentioned above. so i solved it by adding app.config value in web config file.

my final web.config file as follows:

<connectionStrings>
    <clear />
    <add name="LibraryMgtSysEntities" connectionString="metadata=res://*/DataLibraryMgtSys.csdl|res://*/DataLibraryMgtSys.ssdl|res://*/DataLibraryMgtSys.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=TILANITHOTAMUNE\SQLEXPRESS;initial catalog=LibraryMgtSys;Persist Security Info=True;user id=sa;password=testing;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
    <add name="DefaultConnection" providerName="System.Data.SqlClient"
         connectionString="Data Source=abc;
         Initial Catalog=LibraryMgtSys;
         Integrated Security=SSPI;
         user id=sa;password=123;" />
  </connectionStrings>

Prenotion

Answer 8

I had the issue in one of my projects, as entity framework connection string was required by a job, a web application and a test project. One way to deal with this was the following:

1) Use UnitOfWork (or a similar) pattern. This allows to control data context creation and manipulate connection string

public partial class MyContext
{
    #region Members
    private static readonly object objSync = new object();
    private static readonly string DATACONTEXT_KEY = "MyContext_UserInstance";
    // TODO: read from a place accesible to all deployed projects
    // remove hardcoded database
    private static readonly string DefaultConnectionString = @"metadata=res://*/MyContext.csdl|res://*/MyContext.ssdl|res://*/MyContext.msl;provider=System.Data.SqlClient;provider connection string='data source=Server;initial catalog=MyDatabase;integrated security=True;multipleactiveresultsets=True;App=EntityFramework'";

    private static string connectionString;
    #endregion

    public MyContext(String connectionString) : base(connectionString)
    {
    }

    /// <summary>
    /// Uses a UnitOfWorkStore to return the same instance of MyContext, that is unique
    /// per user, per postback (or CallContext, if not in an HttpContext environment, such as unit tests or console apps)
    /// </summary>
    public static MyContext Instance
    {
        get
        {
            // Dirty (non thread-safe) check
            if (UnitOfWorkStore.GetData(DATACONTEXT_KEY) == null)
            {
                lock (objSync)
                {
                    // Thread-safe check
                    if (UnitOfWorkStore.GetData(DATACONTEXT_KEY) == null)
                    {
                        MyContext context = new MyContext(DefaultConnectionString);
                        connectionString = context.Database.Connection.ConnectionString;
                        UnitOfWorkStore.SetData(DATACONTEXT_KEY, context);
                    }
                }
            }
            return (MyContext)UnitOfWorkStore.GetData(DATACONTEXT_KEY);
        }
    }

}

2. Data context should allow direct connection string input:

   public MyContext(String connectionString) : base(connectionString) { }

Federative