Where do you store your database connectionstring?

Question

I usually store my connectionstring in web.config or in the application settings of my Visual Studio project. The application I'm currently working on makes a lot of trips to the database which means it will look up the connectionstring every time. Should I be putting the connectionstring in the cache or should I be looking at storing the whole SqlConnection object in the cache to eliminate the need to open and close them all the time?

Update: Seems like the consensus is to store the connection string in a configuration file and leave the caching in the trusting hand of ADO.NET

Answer 1

Keep it in a configuration file. Use a robust data access strategy provided by tools like NHibernate or Linq to Sql.

Answer 2

From what I can recall the contents of the .config file are held in memory anyway... I'll get back to you.

Edit: What HE said

Answer 3

I wouldn't cache the connection object, that will defeat the built-in connection pooling -- ADO.NET will handle connections (assuming you instantiate and close them) efficiently by itself.

As far as the connection string itself, you shouldn't need to cache it if you load it from connection -- the connection manager object in the .NET 2.0 framework loads the config into memory when you first access it, so there are no repeat trips to the file system.

Answer 4

I usually cache the connection string in a global configuration object in my application. This value is loaded up at the beginning of program execution from where ever it is stored -- file, encrypted file, config file, etc. ADO.NET is very good at caching connection objects to the database so I would not cache the SqlConnection object.

Answer 5

The web.config is cached. But even if it wasn't, don't forget that ado.net maintains a connection pool - its not opening a new connection every time you make a call to the db.

Answer 6

A possible solution: Store the initial encrypted connection string ( in Web.Config or App.Config) for a login allowed to run only one stored procedure for authentication. Than switch the login dynamically from encrypted values stored in a config table in the db.