Best practice - database connections and redirecting

[dj4uk]

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:

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.

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

[dj4uk]

Maybe this is better - thoughts please!

using (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]);

	dbConn.Open();

	using (SqlDataReader 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");
			}
		}
	}
}

DJ

[HoraShadow]

response.redirect is overloaded.

Ex: response.redirect("http://vbforums.com", False)

will let the whole page finish processing and THEN redirect. If you put it on True, it will cut it right there.

Is this what you were looking for?

*edit* I would use the second example (the one with the Try/Catch), and put the response.redirect in False.

[dj4uk]

I'll give that a go cheers.

DJ