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