DataBase Connection Efficiency (C#)

[BryanHood]

This will probably start a large debate, but here goes: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?Thanks, Bryan Clauss

[aspnetguy]

I open and close the connection with each query (potentially many ope/close per page) and enclose each query in try/catch and on error I close the connection to be sure none get left open.I don't know if this is the most efficient way but I don't think that have 1 connection per session open all the time is a good idea.You will use up your connections quickly if you will have 300-400 users at a one time.You are asking for connection errors with that, IMO