Using multiple database connection string in web application web confing and changing the connection string base on the user in app_code file
I am working on a asp.net c# web appliaction where we have multiple connection strings defined in web config with differnet names and differnet databases, where the database name is selected based on the user at login, I have defined a public static sting 'db_name' in app code file (cs) and a public connection string in the same app code and passed the 'db_name' in the name proprty of connection string.
I am facing a issue where when mltiple users login at the same time ,all the data is stored in the database of the latest user logined.
I wanted to know wether the app code file complies evry time a new user login and that complied file is used for other user
I doubt you can achieve this in a production envorment.
App_code will likely be re-compiled if you have a conneciton string class inside of app_code.
In fact, I don't use app_code anymore, since IIS will attempt to re-compile that code EVEN WHEN using a web site application that deploys compiled code.
So, I create a folder called MyCode, and place my class(s) and code inside of that.
But, your case gets MUCH worse. Since if you modify web.config, then IIS will detect this, and not only (often) trigger a re-compile of app_code, but will also do a WHOLE app_pool re-start, and that will blow out all users current session.
This idea of having each user their own database?
Nope, not even close to practial.
What happen after 50 users a and 50 databases, and you NOW want to change a database. Maybe a simple addition of a new view, or stored procedure. What now, you going to modify 50 databases?
You REALLY can't design ANY software, let alone web based software to work this way. I suppose on a desktop, ONE user can open ONE excel file, then close, and then open another.
However, in datbase land, and "espeically" web based?
You already dealing with a so called "state-less" system, and attempts to use mutliple different datbases created for each user? Nope, not even close to ANY kind of practial design, and it NOT done, and thus even worse, the tools, the software, the system, the design approach?
ALL of your designs will have built around the assumption of a ONE database, and attempts to shoe-horn in some design that out of the blue is going to change the connection strings for one user?
You can't really do this. Each user does not have their "own" copy of the web server. You have ONE web server, and ANY user can post back a single page, and the web server's job is to process that ONE page. That use then might close their laptop, or start browsing amazon to shop. The web server has no clue that user does not exist anymore.
All you have, and ALL your designs must then assume the web server can and will process a incomming page from any and all users.
Boatloads, but beyond boatloads of designs and assumptions will have to work on this concept, and that includes things like user sessions, session management, code management (static class values are actually SHARED among all connected usres!!! - (which is why you can use static class(s) but you can NOT use static classes with variables scoped to that class!!!
As noted, you can and its often for a web site to use and connect to several databases, but NOT on per user bases - it will not work.
You can do this on a desktop, since EACH user has their own computer, own memory, and own software running on each desktop.
on a web server?
You have one computer, one system, and it has to serve "all" users, and things like connections etc. as a general rule are NOT to be "per user", but per "system".
A correctly normalized and correct database design will MORE then take care of each user needs like creating a new project, new tours or whatever.