Catch database connection error

Question

Is there a way to catch a database exception and then redirect to an error page? I have a data access class that I use to make a sql connection and then functions that call it to execute my SQL commands. My problem is that if my database is not available I cannot catch that error. Here is the code I am using in my class:

Protected Function GetConnection() As SqlConnection
    Dim ret_conn As SqlConnection
    ret_conn = New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("EUCNET00617").ToString())
    ret_conn.Open()
    GetConnection = ret_conn
End Function

did you ever read about "try..catch" blocks?!? msdn.microsoft.com/en-us/library/fk6t46tz%28v=VS.90%29.aspx — BertuPG
– BertuPG, Commented Feb 11, 2011 at 12:58
You shouldn't really open your connection until you need it, and then you should close it immediately when you're done with it. So your consuming code would be conn = GetConnection() ... conn.Open() ... PerformDataPull() ... conn.Close(). Opening the connection where you have it is too easy to lead to the possibility of orphaned connections. — Joel Etherton
– Joel Etherton, Commented Feb 11, 2011 at 13:21

Rob · Accepted Answer · 2011-02-11 13:16:47Z

What you're looking for is the try/catch/finally construct. This allows you to capture an exception (error) and react to it.

Protected Function GetConnection() As SqlConnection
    Dim ret_conn As SqlConnection
    Try
        ret_conn = New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("EUCNET00617").ToString())
        ret_conn.Open()
        GetConnection = ret_conn
    Catch exceptionThatICaught as System.Exception
       ' You could also perform logging of details from exceptionThatICaught here
        GetConnection = Null
    End Try
End Function

Now, your GetConnection function will return Null when it's unable to create and open a connection, which means that the code that calls it can react to the connection returned being null, rather than crashing.

You might have noticed that the Exception that I put in the Catch block is System.Exception, ordinarily catching such a generic (all exceptions are derived from System.Exception) would be considered bad form, as it means you're trying to cater for anything that happens without being sure of what is happening. This is just an example to show you though =)

It's always worth reviewing the msdn documentation pages for the functions you're wrapping your try/catch around as some of them list "expected" (but not all) exceptions that may be thrown which you could consider catching, if you have a way of handling that failure case. In this instance, the pages to look at would be:

The documentation for SqlConnection.Open lists two exceptions that it can throw, InvalidOperationException and SqlException. I'd strongly recommend you review that documentation to make a decision as to "what to do" and "catch" whichever of those exception types you decide it's appropriate for you to do so.

NakedBrunch · Accepted Answer · 2011-02-11 13:20:17Z

0

Use a Try/Catch Block (MSDN Link)

Protected Function GetConnection() As SqlConnection
    Try
       Dim ret_conn As SqlConnection
       ret_conn = New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("EUCNET00617").ToString())
       ret_conn.Open()
       GetConnection = ret_conn
    Catch Ex As Exception
       'Write code to log error and then redirect to your error page
    Finally
       'Something you always want to happen
    End Try
End Function

edited Feb 11, 2011 at 13:20

answered Feb 11, 2011 at 13:09

NakedBrunch

49.5k14 gold badges75 silver badges98 bronze badges

Comments

Crippledsmurf · Accepted Answer · 2011-02-11 13:21:45Z

The first thing that comes to mind upon reading your code is that SqlConnection.Open throws a SqlClientException if something goes awry while opening the connection. It's possible to catch this exception and respond to it by having ASP.NET redirect to an error page like so.

    Try

    ret_con.Open();

Catch Exception ex As SqlClientException

    //logging and other things ommitted
   Response.Redirect("/errors/page");
   End Try

One of the negitives of this particular solution is that it causes your data access class to have to know about the HttpResponse object which might not be acceptable depending upon how much you value seperation of concerns.

Another optionon would be to catch the exception, log it and rethrow it so that it can bubble up causing a redirect to a custom error page configured through web.config for the site. More info on setting up custom errors can be found here.

Appoligies for the potentially invalid VB, it isn't my native language I'm afraid

Collectives™ on Stack Overflow

Catch database connection error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related