Setup Entity Framework For Dynamic Connection String

Question

I am working on an app that will use the same database schema across multiple databases. For this reason, I've created a database called MyTemplate. When a new user is created, they will have their own instance of the database. So, a database called something like MyTemplate_[UserName] will be created. When a user logs in, I need to point their queries to their database. For this reason, I know that I need to set the connection string at runtime. My problem is, I also want to use the Entity Framework.

Currently, I created a new .edmx using MyTemplate as the source. I thought I would be able to update the code and set the connection string there. Unfortunately, I can't figure out how to set it. The constructor to TemplateEntities does not have an overload that allows me to pass in a connection string. I noticed that TemplateEntities derived from DbContext, I don't think this would be the problem.

string connectionString = GetUsersConnectionString();
using (TemplateEntities entities = new TemplateEntities())
{
  TemplateEntity entity = new TemplateEntity();

  // Save to the database
  entities.TemplateEntity.Add(entity);
  entities.SaveChanges();
}

Am I creating the .edmx incorrectly? Or am I missing something entirely? Everything I Google shows an overload that should allow a connection string passed in. However, I don't have that overload available.

Answer 1

The generated TemplateEntities class is marked as partial.

All you have to do is add another file with another part of the partial class definition that exposes the constructor you want to use:

partial class TemplateEntities
{
  public TemplateEntities( string nameOrConnectionString )
    : base( nameOrConnectionString )
  {
  }
}

Then pass your connection string in to this constructor.

You want to put this code in a different file so it doesn't get over-written when you update your edmx model.

Answer 2

Nicholas Butler's answer is quite correct. In addition to what he said, I was faced with the problem of taking an existing connection string for entity framework and just pointing it at a different database that had the same structure. I used the following code to change only the data source of the existing string:

var originalConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CSName"].ConnectionString;
var ecsBuilder = new EntityConnectionStringBuilder(originalConnectionString);
var sqlCsBuilder = new SqlConnectionStringBuilder(ecsBuilder.ProviderConnectionString)
{
    DataSource = "newDBHost"
};
var providerConnectionString = sqlCsBuilder.ToString();
ecsBuilder.ProviderConnectionString = providerConnectionString;

string contextConnectionString = ecsBuilder.ToString();
using (var db = new SMSContext(contextConnectionString))
{
    ...
}

Answer 3

This is the step by step I have used while building my solutions:

1. On your desired project, make sure Entity Framework was installed using "Manage Nuget Packages..." options menu.
2. On your desired project, right click, then Add->New Item, go to Data and select ADO.NET Entity Data Model.
3. Type the name of the model, let's say "ExampleModel". Click Add.
4. Four choices will appear to choose Model contents, I usually select the first one, in order to build the model from existing objects from the database. Click Next.
5. Set your data connection. Once done, type the name of your model entity, let's say "ExampleModelEntities", click Next.
6. Select the objects from the database that will be present on your EF model. On Model Namespace input box type the same model name from Step 3 ("ExampleModel"). Click Finish.

At this point a new .edmx file has been created and added to the project, containing all of your objects ready-to-work. Only non-desired detail is, so far the connection string has been specified and saved into the Web.config file of our project.

To remove this, just go to <connectionStrings></connectionStrings> section block of your Web.config and delete the details from there. We will now work on making the connection string dynamically readable from other sources.

As pointed out by Nicholas Butler, next thing will be to create a "version" of the original partial entity class created (ExampleModelEntities), that will allow us to pass the dynamic connection string. This is possible since the original entity class created inherits from DBContext which is the one that contains the constructor to pass such connection.

To do the aforementioned, add a new empty class to your project. Make sure to type the same name provided on Step 5, following our case-study "ExampleModelEntities". Below the code to implement:

C#

public partial class ExampleModelEntities
{
public ExampleModelEntities(string connString) : base(connString)
{

}
}

VB.Net:

Partial Public Class ExampleModelEntities

Public Sub New(ByVal connString As String)
    MyBase.New(connString)
End Sub
End Class

At this moment your code is ready to work with dynamic connection strings coming from other sources. One of these sources could be passing a connection string coming from another field stored on a different database or using EntityConnectionStringBuilder class.

The following example is implemented in VB.Net, but please use some tool like Telerik to translate. Let's say we are getting a list of objects from a certain database, only we want to pass dynamically the connection string coming from another field stored on a different database. To accomplish this, the code would look as follows:

Public Shared Function Get_List(ByVal Param1 As String) As List(Of Stored_Procedure_Code_Result)

Try

Dim Object_List_Result As List(Of Stored_Procedure_Code_Result) = Nothing

Using dbContext As New ExampleModelEntities(Configuration.CONNECTION_STRING)

Object_List_Result = dbContext.Stored_Procedure_Code(Param1).ToList

dbContext.Dispose()
End Using
Return Object_List_Result

Catch ex As Exception
Throw ex
End Try

End Function

Where Configuration.CONNECTION_STRING is the value of the dynamic connection string, expressed using a Module called "Configuration" and a function which retrieves such value.

In order to avoid format inaccuracies, the value should be stored using the following format:

For Windows authentication using Entity Framework:

UPDATE [DBConnections].[dbo].[ListOfConnectionsTable]
SET ConnValue = 'metadata=res://*/ExampleModel.csdl|res://*/ExampleModel.ssdl|res://*/ExampleModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=ServerName;Initial Catalog=DBName;Integrated Security=True"'

For SQL authentication using Entity Framework:

UPDATE [DBConnections].[dbo].[ListOfConnectionsTable]
SET ConnValue = 'metadata=res://*/ExampleModel.csdl|res://*/ExampleModel.ssdl|res://*/ExampleModel.msl;provider=System.Data.SqlClient;provider connection string="Persist Security Info=False;User ID=XXXXXX;Password=XXXXXXX;Initial Catalog=DBName;Data Source=ServerName;App=YourAppName;Network Library=dbmssocn"'

Finally, extending the answer provided by Mark, at Microsoft there is a detailed explanation on how to work with EntityConnectionStringBuilder class, which can also be used to build dynamic connection strings and then pass this value on demand.