ASP.net best practise - where do I connect to database from?

Question

cn = new SqlConnection(
         ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
cn.Open();

Where should this code go assuming I have content pages and master pages?

Should I put it in the master pages Page_Init? Will it then be fully accessible from every stage of execution on my content pages?

I'm used to classic ASP, so I would usually do it:

Declare variables

Open connection

process code

Close connection

Render HTML

But there are lots of stages of the page lifecycle in .net so I am wondering where it is best to place this code?

And does this connection need closing, or will garbage handling take care of it for me?

Answer 1

For me id create a DataAccess Layer so that I can remove the database connectivity in the codebehind files. My webapp will then reference the Dal and expose a public method that will allow Dal interaction from the front end

in terms of opening and closing Dal connections - wrap it in a using statement - that takes care of opening and closing the connection when required.

more information on this can be found here - http://davidhayden.com/blog/dave/archive/2005/01/13/773.aspx