I have an ASP.NET MVC application for accounting in which I want to use a single application a different database in respect to each user.
What I have done for this-
I have created an common database which handle each users's login detail and users database connection string and one another database for all the rest work.
I store connection string in catch with user's user id and each time when call an action I get this catch in the constructor and replace default connection string parameter with this.
This process is working fine but 2 things I have found which are;
This is very unprofessional
This gets down the speed of my application(I mean if i use single application for each user with static connection string and no catch for connection string the application works pretty cool and faster but when i use this process speed gets too slower like speed decreased to 200%).
Here Is the Code for which i used to save the Connection string in Cookie
var userCookie = new HttpCookie("ConnectionString", sxs.clinicConnectionString);
userCookie.Expires.AddDays(365);
HttpContext.Response.SetCookie(userCookie);
Connection string in web.config which i use.
<add name="AppConnection"
connectionString="Data Source=Server-Name;
Initial Catalog=#hss#;
User ID=username; Password='password'; MultipleActiveResultSets=true;"
providerName="System.Data.SqlClient"/>
Now Finally i use this code in Controller's Constructor to replace the database name in web.config connection string so i can get the database access for the respected user.
string ConnectionString = System.Web.HttpContext.Current.Request.Cookies["ConnectionString"].Value.ToString();
var conString = System.Configuration.ConfigurationManager.ConnectionStrings["AppConnection"];
string conssss = conString.ConnectionString.ToString();
string fx = conssss.Replace("#hss#", ConnectionString.ToString());
context = new AppContext(fx);
I also cookied the username and queried In all constructor to get database name from the Master Database. But the main point is it's slowing down my application speed. using both approaches my application speed went worst... So how can i improve that. Before Tenanting or using single database and single application also for each respective user the application speed was great