How to change database name in dbcontext connection string at runtime

Question

My Asp.Net MVC application is setup as follows. There are 4 projects in solution.

ge.Web
ge.BLL
ge.Core
ge.Entities

Controller in ge.Web initializes a repository object present in ge.Core

 public class MapsController : Controller
 {
      private AssessmentRepository repAssessments = new AssessmentRepository("name=GEContext", schoolCode);

      public ActionResult DisplaySearchResults()
      {
        .....
      }
  }

Assessments Repository

  public class AssessmentRepository : Repository<Assessment>, IAssessmentRepository
{
    public AssessmentRepository(string connString, string schoolCode)
        :base(connString, schoolCode)
    { }
 }

Repository

  public class Repository<TEntity> : IRepository<TEntity> where TEntity:class
{
    protected readonly GEContext context;


    public Repository(string connString, string schoolCode) {
        context = new GEContext(connString);
    }
 }

GEContext

 public class GEContext : DbContext
 {
    public GEContext(string connString):base(connString) 
    {
        this.Configuration.LazyLoadingEnabled = false;
        Database.SetInitializer(new MySqlInitializer());
    }
  }

DbContext

public class DbContext : IDisposable, IObjectContextAdapter
{
    public DbContext(string nameOrConnectionString);
}

Web.Config

 <add name="GEContext" connectionString="server=localhost;port=4040;uid=root;pwd=xxx;database=ge" providerName="MySql.Data.MySqlClient" />

now i want to replace "database=ge" present in web.config with database=ge_[schoolCode]. at runtime How can i go about it?

UPDATE My solution did not work. so i am stating the problem once again.

Web.Config

I have changed My config file to the following (previously GEContext was the only connection string)

<connectionStrings>
<add name="GEContext_sc001" connectionString="server=localhost;port=4040;uid=root;pwd=blabla;database=db_sc001" providerName="MySql.Data.MySqlClient" />
<add name="GEContext_sc002" connectionString="server=localhost;port=4040;uid=root;pwd=blabla;database=db" providerName="MySql.Data.MySqlClient" />

<appSettings>
     <add key="SchoolCodes" value="sc001,sc002"/>

these are the allowed schoolCodes

Now when the user enters schoolcode at login screen, it is validated against the codes present in SchoolCodes key. and if yes, then it should try to connect to the connectionString for that particular connection. Now when my code comes to

UserManager.FindAsync

in Login function of AccountController, it crashes trying to find GEContext. Where is that set? and how can i change it?

I have changed the repository calling in controller as follows

private static string schoolCode = (string)System.Web.HttpContext.Current.Session["SchoolCode"];

    private AssessmentRepository repAssessments = new AssessmentRepository("name=GEContext_" + schoolCode);

UPDATE-2 Following is present in ge.Web

IdentityConfig.cs

  public class ApplicationUserManager : UserManager<ApplicationUser, int>
{
    public ApplicationUserManager(IUserStore<ApplicationUser, int> store)
        : base(store)
    {
    }

     public static ApplicationUserManager Create(IdentityFactoryOptions<ApplicationUserManager> options, IOwinContext context) 
    {
        var manager = new ApplicationUserManager(new UserStore<ApplicationUser, Role, int, UserLogin, UserRole, UserClaim>(context.Get<ApplicationDbContext>()));
  ...........
 }

The following is present in ge.Core

ApplicationDbContext

 public class ApplicationDbContext : IdentityDbContext<ApplicationUser, Role, int, UserLogin, UserRole, UserClaim>
{

     public ApplicationDbContext(string connString)
        : base(connString)
    {
        Database.SetInitializer(new MySqlInitializer());
    }

    public static ApplicationDbContext Create()
    {

        return new ApplicationDbContext("name=GEContext_");
    }
}

How can i pass schoolCode from ge.web to ge.Core (answer should be straight forward but currently i cant get my head around it)

UPDATE-3

As told by itikhomi and taking help from this post I have changed my code as follows

in ApplicationDbContext class added the following

public static ApplicationDbContext Create(string scCode){ return new ApplicationDbContext("name=GEContext_" + scCode); }

in AccountController Login

var appDbContext = ApplicationDbContext.Create(model.SchoolCode);

            Request.GetOwinContext().Set<ApplicationDbContext>(appDbContext);

it still does not hit the correct database

You just alter the web.config. You can also add multiple connection string values "GEContext", "GEContext2", "GEContext3" then have your program select one of these connections dynamically. — Jasen
– Jasen, Commented Jul 26, 2017 at 1:18
my database name would be db_[schoolCode]..so i basically need to validate the schoolcode entered at login.. i try to connect with the entered schoolCode by renaming database name in connection string — Samra
– Samra, Commented Jul 26, 2017 at 1:28
What you're asking is quite broad. Here's some guidance stackoverflow.com/questions/35956345/… and benfoster.io/blog/… — Jasen
– Jasen, Commented Jul 26, 2017 at 2:33

Lakmal · Accepted Answer · 2020-05-12 01:51:44Z

2

You can change the database for an open connection

context.Database.GetDbConnection().ChangeDatabase("");

answered May 12, 2020 at 1:51

Lakmal

9382 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

itikhomi · Accepted Answer · 2017-07-27 05:55:10Z

1

You have two ways

1)

    using System.Data.SqlClient;

         public class Repository<TEntity> : IRepository<TEntity> where TEntity:class
        {
            protected readonly GEContext context;


            public Repository(string connString, string schoolCode) {
                context = new GEContext(connString);
                var connection = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);
                connection.InitialCatalog = "YOUR_PREFIX_FROMSOMEWHERE"+schoolCode;
                context.Database.Connection.ConnectionString = connection.ConnectionString;
            }
         }

2) if you wants to switch connection when it opened before use ChangeDatabase:

//open connection if it close
    context.Database.Connection.ChangeDatabase("DATABASE-NAME");

NOTE: if use ChangeDatabase connection should be already opened

FOR UPDATE3:

You need to do somethink like this:

 public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {

        }

        public ApplicationDbContext(string schoolCode)
            : base(schoolCode)
        {
            var connection = new SqlConnectionStringBuilder(this.Database.Connection.ConnectionString);
            connection.InitialCatalog = "YOUR_PREFIX_FROMSOMEWHERE" + schoolCode;
            this.Database.Connection.ConnectionString = connection.ConnectionString;
        }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
    }

in account controller:

public ApplicationSignInManager SignInManager
        {
            get
            {

                if (_signInManager == null)
                {
                    var code = HttpContext.Request.Form.Get("SchoolCode");//Get from FORM\QueryString\Session whatever you wants
                    if (code != null)
                    {
                        HttpContext.GetOwinContext().Set<ApplicationSignInManager>(new ApplicationSignInManager(_userManager, HttpContext.GetOwinContext().Authentication));
                    }
                    _signInManager = HttpContext.GetOwinContext().Get<ApplicationSignInManager>();
                }

                return _signInManager;
            }
            private set
            {
                _signInManager = value;
            }
        }

 public ApplicationUserManager UserManager
        {
            get
            {
                if (_userManager == null)
                {
                    var code = HttpContext.Request.Form.Get("SchoolCode");//Get from FORM\QueryString\Session whatever you wants
                    if (code != null)
                    {
                        var appDbContext = new ApplicationDbContext(code);

                        HttpContext.GetOwinContext().Set<ApplicationDbContext>(appDbContext);
                        HttpContext.GetOwinContext().Set<ApplicationUserManager>(new ApplicationUserManager(new UserStore<ApplicationUser>(appDbContext))); //OR USE your specified create Method
                    }
                    _userManager = HttpContext.GetOwinContext().GetUserManager<ApplicationUserManager>();
                }
                return _userManager;
            }
            private set
            {
                _userManager = value;
            }
        }

Your problem is in Store of UserManager is created before you change your OWIN context, in this case better to use DI like here

edited Jul 27, 2017 at 5:55

answered Jul 26, 2017 at 4:47

itikhomi

1,57013 silver badges13 bronze badges

13 Comments

Samra Over a year ago

Thanks.. i have edited my application a bit can you please check the updates above?

itikhomi Over a year ago

@Samra, try to debug and provide me schoolCode value in AccountController, i think it will be null and it's why you get the error

Samra Over a year ago

the problem is not with school code it is present but when it goes to Login function in accountController but crashes at var user = await UserManager.FindAsync(model.Username, model.Password);

Samra Over a year ago

It is trying to find GEContext here

itikhomi Over a year ago

@Samra, please provide Identity configuration, there are manulaly set your old GEContext, or just find it by searching "GEContext"

|

Samra · Accepted Answer · 2017-07-28 00:39:33Z

I resolved it with the help of itikhomi..Posting the final code..

ApplicationDbContext

public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext("name=GEContext");
    }

AccountController

 public ApplicationUserManager UserManager {
        get
        {
            if (System.Web.HttpContext.Current.Session["SchoolCode"] == null)
                return _userManager ?? HttpContext.GetOwinContext().GetUserManager<ApplicationUserManager>();
            else
            {
                var appDbContext = ApplicationDbContext.Create(System.Web.HttpContext.Current.Session["SchoolCode"].ToString());//new ApplicationDbContext("name=GEContext", System.Web.HttpContext.Current.Session["SchoolCode"].ToString());

                HttpContext.GetOwinContext().Set<ApplicationDbContext>(appDbContext);
                HttpContext.GetOwinContext().Set<ApplicationUserManager>(new ApplicationUserManager(new UserStore<ApplicationUser, Role, int, UserLogin, UserRole, UserClaim>(appDbContext)));

                return HttpContext.GetOwinContext().GetUserManager<ApplicationUserManager>();
            }
        }
        private set
        {
            _userManager = value;
        }
    }

Collectives™ on Stack Overflow

How to change database name in dbcontext connection string at runtime

3 Answers 3

Comments

13 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related