Where do you store the database connection string?

Question

Usually I save my connection string in web.config or in the application settings of my Visual Studio project. The application in which I am currently working makes many trips to the database, which means that every time it will look for a connection string. Should I put the connection string in the cache or should I look at saving the entire SqlConnection object in the cache to eliminate the need to constantly open and close them?

Update . The consensus seems to be to keep the connection string in the configuration file and leave the caching in the trusted hand of ADO.NET

Answer 1

I would not cache a connection object that defeats the built-in connection pool. ADO.NET will handle the connections (assuming you instantiate and close them) by itself.

As for the connection string itself, you do not need to cache it if you download it from the connection - the connection manager object in the .NET 2.0 platform loads the configuration into memory the first time you access it, therefore, it does not repeat trips to the file system.

Answer 2

Web.config is cached. But even if this is not the case, do not forget that ado.net supports the connection pool - it does not open a new connection every time you call in db.

Answer 3

I usually cache the connection string in the global configuration object in my application. This value is loaded at the beginning of the program from the place where it is stored: file, encrypted file, configuration file, etc. ADO.NET is very good at caching database connection objects, so I will not cache the SqlConnection object.

Answer 4

Store in configuration file. Use the robust data access strategy provided by tools like NHibernate or Linq to Sql.

Answer 5

From what I can remember, the contents of the .config file are still stored in memory ... I will return to you.

Edit: what HE said

Answer 6

Possible solution: Save the initial encrypted connection string (in Web.Config or App.Config), only one stored procedure is allowed to enter the system for authentication. Then dynamically switch the login from the encrypted values ​​stored in the config table in db.