First, you need to insure your application session can survive restarts and app pool recycles. See this
Second, you need to inject the connection string for your DBContext based on the authenticated user request.
I assume you’ve got a database full of users so what you need to do is save a list of possible connection strings in a SQL table and relate them back to their associated user accounts. Once you’ve authenticated the user you need to retrieve the connection string associated with the user account. You don't want to store your connection string in a session or any other mechanism that could potentially expose sensitive data to a web client. So in summary this what you need to do.
- You will want to retrieve your connection string for each request base on the authenticated user.
- Inject the connection string into your DBContext.
- Make your database calls as necessary.
- Money!
Injecting strings into entity is easy.
If you're using EF 4.1 Code first your DBContext would look something like this.
EF 4.1 accepts normal ADO.NET connection strings.
public class ExampleProvider : DbContext, IExampleProvider
{
private readonly string _nameOrStringConnection;
public ExampleProvider()
{
}
public ExampleProvider(string nameOrStringConnection)
: base(nameOrStringConnection)
{
_nameOrStringConnection = nameOrStringConnection;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Example>().ToTable("example");
modelBuilder.Entity<Example>().HasKey(x => x.ExampleId);
}
public DbSet<Example> Examples { get; set; }
}
If you're using EF.edmx you will need to make sure that your injected connection string includes the edmx metadata files info like this...
..."metadata=res:///ExampleModel.csdl|res:///ExampleModel.ssdl|res://*/ExampleModel.msl;...
If you look in the edmx designer file you will see your DBContext has several constructor overloads. Use the second or third overload per your needs.
#region Contexts
/// <summary>
/// No Metadata Documentation available.
/// </summary>
public partial class Entities : ObjectContext
{
#region Constructors
/// <summary>
/// Initializes a new Entities object using the connection string found in the 'Entities' section of the application configuration file.
/// </summary>
public Entities() : base("name=Entities", "Entities")
{
this.ContextOptions.LazyLoadingEnabled = true;
OnContextCreated();
}
/// <summary>
/// Initialize a new Entities object.
/// </summary>
public Entities(string connectionString) : base(connectionString, "Entities")
{
this.ContextOptions.LazyLoadingEnabled = true;
OnContextCreated();
}
/// <summary>
/// Initialize a new Entities object.
/// </summary>
public Entities(EntityConnection connection) : base(connection, "Entities")
{
this.ContextOptions.LazyLoadingEnabled = true;
OnContextCreated();
}
#endregion
/// incomplete file
Good luck!
