When creating a web site that will (potentially) have MANY users hitting it, what would the most efficient way to connect to the database? (In my mind, MANY will be about 200-400 users during the "busy" time.)
I have always created 1 connection for the user, and keep that connection open for the entire session in any of the applications that I have worked on.
Other people claim that it is better for each page to open the connection, use it, and then close it when they are done.
Is there a way to create 1 connection object that all users can use? Would this be efficient?
I will be writing this in C# (.Net 2005), and I will be hitting a MS SQL database.
As an aside, should I check for unused connections within the web app itself, and attempt to close them, or will IIS and SQL Server clean up for me?