Catch database connection error

Is 开发者_如何学Pythonthere 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

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:

• SqlConnection's constructor.
• SqlConnection.Open.

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.

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

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