I'm trying to create a website using ASP.NET 2.0, where one page during the process of Page_Load has to do multiple table reads from a single database schema. I have a Data Access Layer, which for each select statement returns a DataTable. This however has to open and close the database connection for each retrieval. (I use an ODBC connection to connect to my MySQL database).
I'm currently looking at 4-5 table select statements with open and close connections per page load. I am worried about the overload when the user pool accessing this page grows.
Is this effective programming or would I have to go for stored procs where there could be one open and close.
Thanks in advance,