Dynamicly Change Database MVC3 and EntityFramework

问题:

I am working on an MVC3 application database first approach . I would like to use one connection string to connect to database, based on some string (company name). Example: I have in my MSSQL Express 2012 this db: my_database_microsoft, my_database_oracle and so on..(those databases have same structure). On login page I have 3 input fields: username,password,company. I know how to build connection string dynamic with SqlConnectionStringBuilder and then use it on EntityConnectionStringBuilder

string providerName = "System.Data.SqlClient";
string serverName = "MY-PC\\SQL2012";
string databaseName = "my_database_"+form[company].toString();
.....
.....

  entityBuilder.Provider = providerName;

// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;

// Set the Metadata location.
entityBuilder.Metadata =@"res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl";

using (EntityConnection conn =
    new EntityConnection(entityBuilder.ToString()))
{
    conn.Open();

   // Console.WriteLine("Just testing the connection.");

    conn.Close();
}


obracun_placEntities1.nameOrConnectionString = entityBuilder.ToString();
obracun_placEntities1 o = new obracun_placEntities1(entityBuilder.ToString());

I have made a partial class of my entety and give a constructor that take a nameOrConnectionString string as a parameter.

public partial class obracun_placEntities1
{
   public string nameOrConnectionString { get; set; }

    public obracun_placEntities1(string nameOrConnectionString)
        : base(nameOrConnectionString ?? "obracun_placEntities1") { }

}

This works only in loginController but how can I use this in UsersController and all other controllers where I using obracun_placEntities1 db = new obracun_placEntities1(); > this take the default database from web.config. I would not like to save connection string to session or cookie and than pass it in every controler as a parameter. private obracun_placEntities1 db = new obracun_placEntities1();

How can i achieve that i pass connection string in login controller and using this database in entire project.

One more problem occured when i want to use public static string nameOrConnectionString and pass it to constructor. The problem is when I open application in Chrome and login as user1 I get all infromation from user1 database, but then I login in MS Explorere as user2 and get all data from user2 database. When i refresh chrome I get information from the user2 database not user1.

Model1.context.cs

  public partial class obracun_placEntities1 : DbContext
{
    public static string nameOrConnectionString { get; set; }
   // public static string connection;
    public obracun_placEntities1()
        : base(nameOrConnectionString ?? "obracun_placEntities1")

    {

    }

回答1:

Connecting to different Databases is best done using the DBconnection constructor on DBCOntext. If you look at the DBContext class you will see multiple constructor overloads. One allows the DBConnection to be supplied. So no entry in WEB.Config/App.Config is required.

See this post with sample code Same Context accessing different databases.

EDIT sample added:

 public partial class obracun_placEntities1 : DbContext
{
    // use THIS CONSTRUCTOR
    protected obracun_placEntities1(DbConnection dbConnection, bool contextOwnsConnection)
        : base(dbConnection, contextOwnsConnection)
    {
    }

     }
}

// DONT USE THIS
//    obracun_placEntities1.nameOrConnectionString = entityBuilder.ToString();
 //        obracun_placEntities1 o = new obracun_placEntities1(entityBuilder.ToString());`

// build the connection - note: it is NOT a connection string. it is a DBConnection!

   conn = getDBConnection4SQLServer(DatabaseServer,Databasename)
   obracun_placEntities1 o = new obracun_placEntities1(conn,true);

 //====================================================================

 public const string DefaultDataSource = "localhost";

 public DbConnection getDBConnection4SQLServer(string dataSource, string dbName) {
        var sqlConnStringBuilder = new SqlConnectionStringBuilder();
        sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource;
        sqlConnStringBuilder.IntegratedSecurity = true;
        sqlConnStringBuilder.MultipleActiveResultSets = true;

        var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString);
        var sqlConn = sqlConnFact.CreateConnection(dbName);
        return sqlConn;
    }

回答2:

I finnaly getting it to work with this code.

My LoginController

    [HttpPost]
    public ActionResult Index(UPORABNIK model, FormCollection form)
    {....}

    public obracun_placEntities1(EntityConnection entityConnection)
        : base(entityConnection, false)
    {
    }

I call EntityConnection conn = GetEntityConnDbName("ServerName", "FirmName"). _entities = new obracun_placEntities1(conn, false);

var uporabniki = from r in _entities.UPORABNIK.Where(r => r.ime == uporabnik && r.geslo == geslo && danes <= r.veljavnost).ToList() select r;

I get the firmName from the post form so this work only in LoginController.But how can I use this constructor in all other Controllers? I get firm name only once in LoginControler, I tried to save it as cookie but then i can not read it in the constructor. In my other Controller I use the default controller again. How can I tranfer conn to other Controllers?

private obracun_placEntities1 db = new obracun_placEntities1();

I would like to call like this

private obracun_placEntities1 db = new obracun_placEntities1(conn);

Or is there some better way?