dynamic connection string in 3 tier architecture

Question

Hi I am working on MVC4 application with three-tier architecture. I have a scenario where i am stuck by one week... In this application we have dynamic connection string for each logged-in user I can get the dynamic connection string in Presentation layer but getting issue to pass it in data access layer I tried with one static Utility class but it has problem to override value for another user session. I want to avoid to pass in each method from Presentation layer to data access layer or business layer. So please give me some idea how to cover this scenario. Thanks VD.

Suthish Nair · Answer

BL is just a class library then by using HttpContext you can get the session information. The other way is passing the user name as constructor parameter to BL which in turns retrieve the connection string info from Main DB based on logged in user. There are many ways..

Vd Tripathi · Answer

Thanks Suthish I got it as you have told and it solved my issue. I just added Reference of System.Web in Business Layer and can get data by HttpContext.Current.Session[variableName]. Thanks again Suthish.

Vd Tripathi · Answer

Actually here user means an organization so each organization has their own server so when an organization will get registered system will save all records of organization including Sql Server information also (like server name username password). Now these data will be saved in a Main database(it's common for all). Now when user will login based on user id it fetch all server detail from Main Database and now we need to use this data in whole application until session goes end. It's Sql only not windows authentication.

Suthish Nair · Answer

Not sure I clearly understood your requirement. During registration each user will have a dynamic connection string get saved? Which Authentication you are using? SQL or Windows?

Vd Tripathi · Answer

Hi Suthish I can do it in business layer actually application has a common database where some organizations will be registered and their connection string will be saved in common database. After user logged in I get connection string based on logged-in user id from common database (and these session creation is done in presentation layer) now I have issue with how to pass connection string in business layer for each method of business layer whenever it is called by presentation layer. I think Session variable couldn't be used in business layer. Thanks VD

Suthish Nair · Answer

Move the dynamic connection string creation logic to Business Layer to DbL instead of Presentation Layer.