Best practice - database connections and redirecting

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

Maybe this is better - thoughts please!

Code:

---

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

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.

I'll give that a go cheers.

DJ