Using an existing connection string with Entity Framework

Maybe most of you all using Entity Framework have had my same problem, working on a project with an existing (and undeletable) connection string and a new part of the solution, based on Entity Framework, using same server and instance but requiring a second connection string for the model.

I tried to solve the problem creating and using an instance of the model able to be initialized with the existing connection string.

In this example, we will use a model named MyModel and, in the web.config file, there is a section like this:

<connectionStrings> 
 <add name="MyConnectionString" 
 connectionString="Server=mysrv\myinst;Database=mycatalog;Integrated Security=True" /> 
 <add name="MyModel" 
 connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl; 
 provider=System.Data.SqlClient; 
 provider connection string= 
 &quot;Data Source=mysrv\myinst;Initial Catalog=mycatalog; 
 Integrated Security=True;MultipleActiveResultSets=True&quot;" 
 providerName="System.Data.EntityClient" /> 
</connectionStrings> 

As you can see, the strings are similar but not exactly the same, even if they use same physical server and database, because the model requires additional information about data mapping (csdl, ssdl, msl).

To accomplish the requirement, we need to “extend” the model class, creating the same class as “partial”, giving in this mode additional members to the model itself.
The class will have the same name of the model, so it will be named MyModel.

public partial class MyModel
{ 
 // ...
} 

In this partial of the class we will put a singleton instance of the model itself, implemented as a static property:

// private static instance to create only once
private static MyModel context = null; 
// public static instance of the model
public static MyModel Context 
{ 
 get
    { 
        if (context == null) 
        { 
         context = CreateModelContext("MyConnectionString""MyModel"); 
        } 
 return context; 
    } 
} 

The singleton instance returns the static private instance, having care to initialize it when null (so just in the first occurrence of the usage).

Initialization happens through a method that receives in input the connection string name and the model class name, and returns the instance of the model created with the existing connection string:

/// <summary> 
/// Returns an instance of the model created with an existing connection string from config file 
/// </summary> 
/// <param name="ConnStringName">Name of the connection string</param> 
/// <param name="ModelName">Name of the class that represents the model</param> 
/// <returns>Returns an instance of the model</returns> 
private static MyModel CreateModelContext(string ConnStringName, string ModelName) 
{ 
    // read connection string from config file   
    ConnectionStringSettings sqlconn = ConfigurationManager.ConnectionStrings[ConnStringName]; 
    // use an instance of "EntityConnectionStringBuilder" that will help us to create connection 
 // for the model, using values from existing connection string 
    EntityConnectionStringBuilder ef = new EntityConnectionStringBuilder(); 
    ef.Provider = "System.Data.SqlClient"; 
    ef.ProviderConnectionString = sqlconn.ConnectionString; 
    ef.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl", ModelName); 
    // then returns the instance of the model 
    return new MyModel(ef.ToString()); 
} 

N.B. Italian version of this post can be found here.

Using an existing connection string with Entity Framework

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...