OK I'm having a couple of problems with database connections being left open - I'm monitoring them with EXEC SP_WHO in SQL Server.

Originally I had the following:
Code:
SqlConnection dbConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmdSelect = new SqlCommand("IBX_GetPageTemplate", dbConn);
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.Parameters.Add("@CategoryDirectory", splitPath[2]);
cmdSelect.Parameters.Add("@PageUrl", splitPath[3]);

dbConn.Open();
SqlDataReader dtrPage = cmdSelect.ExecuteReader();

if (dtrPage.Read()) {
	Context.RewritePath(String.Format("/admin/{0}?PageID={1}", dtrPage["TemplatePage"], dtrPage["PageID"]));
} else {
	if (splitPath[2] != "default.aspx") {
		Context.Response.Redirect(String.Format("/admin/{0}/default.aspx", splitPath[1]));
	} else {
		Context.Response.Redirect("/admin/default.aspx");
	}
}

dtrPage.Close();
dbConn.Close();
But when the page redirects the DataReader and Database Connection are not closed.

I've read about using a try and finally statement to do this as when the page redirects it throws a ThreadAbortException. But although I can close the database connection I can't close the DataReader.

Code:
SqlConnection dbConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmdSelect = new SqlCommand("IBX_GetPageTemplate", dbConn);
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.Parameters.Add("@CategoryDirectory", splitPath[1]);
cmdSelect.Parameters.Add("@PageUrl", splitPath[2]);

SqlDataReader dtrPage;

try {
	dbConn.Open();
	dtrPage = cmdSelect.ExecuteReader();

	if (dtrPage.Read()) {
		Context.RewritePath(String.Format("/html/{0}?PageID={1}", dtrPage["TemplatePage"], dtrPage["PageID"]));
	} else {
		if (splitPath[2] != "default.aspx") {
			Context.Response.Redirect(String.Format("/{0}/default.aspx", splitPath[1]));
		} else {
			Context.Response.Redirect("/default.aspx");
		}
	}

	dtrPage.Close();
}
finally {
	dbConn.Close();
}
I've also heard about the using statement but I believe this would have the same problem.

I need to be able to close the database connection and data reader when I redirect. Obviously I could just close them in each branch before redirecting but I think this is a bit of a messy method - got to be a better way.

Interested to know which method(s) you out there use.

DJ