0

Hi I am working on ASP.NET MVC project. Currently I am using Model first approach, where i used to add database manually by using ADO.NET model. Currently I have 4 database and I have 4 connection strings in web.config file.

It was fine till now, since I was working on development environment. But now I need to move my code to live and problem is, in live we have like 40 to 50 databases.

So what I should do is, generate connection string dynamically when user wants to connect to particular database.

I have stored procedure for this which returns connection string and database name.

For example if I have 4 database name like db1, db2, db3 and db4, I need to compare this database name with my stored procedure results database name and if both are equal, then generate connection string equal to that database name.

And also I need to put this in session once i generate connection string, so no need to generate connection string again for particular session.

Can someone help me in this??

Ahmed ilyas
  • 5,722
  • 8
  • 44
  • 72
Ajay
  • 317
  • 2
  • 12
  • 25
  • take a look at the SqlConnectionBuilder.... http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(v=vs.110).aspx. Remember, MVC does not technically have a session concept. Also storing a connection string in any form of session is not a good idea for security reasons but also session is not scalable. perhaps you need to think about your approach here and if it is necessary. – Ahmed ilyas Feb 18 '14 at 15:27
  • 1
    May I know what is the problem if you store all those connectionstrings in web.config? – ramiramilu Feb 18 '14 at 15:36
  • @ramiramilu You mean I need to store all 40 connection string in web.config file?? Then I need to add connection string for all database and also since I have stored procedure which returns connection string, I decide to generate dynamically. – Ajay Feb 18 '14 at 15:39
  • @Ajay, yes, I dont see any wrong in adding all those connectionstrings to web.config file. Between it is only going to be one time activity right. – ramiramilu Feb 18 '14 at 15:43
  • @ramiramilu Yeah you are right. I also thought the same, but company requirement is to do like this and I dont have any option!!:(.. If you know can you guide me how can i do this?? Like showing some templates or codes?? – Ajay Feb 18 '14 at 15:45

1 Answers1

0

EF DbContext as a constructor parameter takes a name of a connection string or connection string itself. So there is no problem in generating any kind of connection string and supply this when creating DdContext.

In our application we have many tenants and have a database per tenant. For every request we lookup what tenant it is and from Settings DB provide a connection string to tenant's own database.

I've not worked with Ado.Net, but from what I see in Google, this is very similar (or based on) to Entity Framework. So down to your particular implementation, there must be a way to provide connection string to database context outwith web.config.

trailmax
  • 34,305
  • 22
  • 140
  • 234