Multiple open close database connections in one page

[mrfreeze81]

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,

[Old Pedant]

Modify the data access layer so it doesn't close the connection each time??

[mrfreeze81]

Thank you. I don't want to change the DAL unless its really necessary. I have tried to write a SP to club a few of the insert/select statements which help me in eliminating some of the open and close statements from my code.

I'll probably have to come up with a better way eventually, as I will have to start integrating transactions and much more to that page.