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 :).

Solution 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.

Solution 2

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

Solution 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.

Solution 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

View more solutions