2

What is the best way I can test the database connection using C# and Oracle.ManagedDataAccess.Client?

Should I test before every conn.Open() to see if the server is accessible or do one test on first run?

My main problem is this: if the database server is offline, I get an exception on the webpage, this is not the best way to interact with the user.

How can I display an error page if the connection couldn't be established?

Example Data Access Layer method:

public bool IsEmailAddressExisting(string emailAddr)
{
    using (OracleConnection conn = new OracleConnection(BasicConnection.connectionStringOracle))
    using (OracleCommand command = conn.CreateCommand())
    {
        conn.Open();
        command.CommandText = "SELECT id FROM Users WHERE email=:emailAddr";
        command.Parameters.Add(":emailAddr", OracleDbType.Varchar2).Value = emailAddr;

        var result = command.ExecuteScalar();

        return (result != null);
    }
}

Note: I don't want to put try-catch around EVERY method's connection.

I found this answer really useful.

Community
  • 1
  • 1
szab.kel
  • 2,356
  • 5
  • 40
  • 74
  • Why don't you want to use a `try-catch` because in your catch you could redirect to another page or display a message –  Apr 19 '14 at 19:14
  • It is a bad design to use the try-catch to manage your control flow. – szab.kel Apr 19 '14 at 19:20
  • 1
    If you use `try-catch` to actually _handle exceptions_, like when the database is down, then those are _not_ used for [control flow](https://en.wikipedia.org/wiki/Control_flow). – CodeCaster Apr 19 '14 at 19:21
  • 1
    really bad design? They are there for this case. –  Apr 19 '14 at 19:25
  • There would be to many try-catch block. – szab.kel Apr 19 '14 at 19:35

3 Answers3

3

What is the best way I can test the database connection using C# and Oracle.ManagedDataAccess.Client?

Not to test at all. Unless you really need this. The situation you described doesn't seem to require a test of connection.

Should I test before every conn.Open() to see if the server is accessible or do one test on first run?

No, this will end up in a code full of try-catch blocks. You can have this in some specific situations, but not in general, not for each case.

How can I display an error page if the connection couldn't be established?

Finally, this is what you really need. Exception isn't bad, it's just what it is. Database isn't available what else can you do? Well, from the point of your application you should log all unhandled exceptions and errors. But you don't need to report the exact problem to the user. You can merely display a user-friendly general error message.

Each web technology will have this. You can say something like:

Unfortunately, this functionality is not available just now. An email has been sent to the responsible person. We are looking into the problem. Please try again later.

oleksii
  • 35,458
  • 16
  • 93
  • 163
  • I am using C# ASP.NET MVC4, where should I handle unhandled exceptions? – szab.kel Apr 19 '14 at 19:31
  • Looks like there are multiple ways how you can do this, just check the [first few entries](https://www.google.co.uk/search?q=asp.net+mvc+exception+handling). It also looks like most of them is mater of preference or style. [This answer](http://stackoverflow.com/a/1679904/706456) gives a sample for user friendly error. Whichever approach you take, you shouldn't find yourself repeating `try-catch`, anything else (which does the job) will be fine. – oleksii Apr 19 '14 at 19:52
1

Note: I don't want to put try-catch around EVERY method's connection.

If you've got database connection code in a lot of methods, then that's your problem, not the database being offline now and then (though you should also get that fixed).

You should use try..catch on an high level point in your application anyway, or use the proper error handling of the web or application framework you're using, so you can display a nice "Something went wrong"-screen instead of dumping the exception to the end user.

You don't want to add a "database online"-check before executing a query, because between those two calls the database can go offline, or you can have invalid input which makes the query throw an exception anyway.

CodeCaster
  • 147,647
  • 23
  • 218
  • 272
  • I don't think that is true, if I am using ODP.NET and not an ORM. I used entity framework and it handles it quite well, but now I have to use ODP.NET and write queries everywhere. – szab.kel Apr 19 '14 at 19:18
  • @appl3r what part of my answer don't you think is true? What do you think Entity Framework handles better than ODP.NET? EF throws exceptions when your query is wrong or when your database is down just as well. And you don't have to and don't want to write queries everywhere, those belong for example in a repository pattern, not scattered around in your code. – CodeCaster Apr 19 '14 at 19:19
  • So you say I should write a complete layer to handle every kind of database query, insert, update...etc, with parameters and everything. This would take more than the whole site. – szab.kel Apr 19 '14 at 19:24
  • You should've done that from the beginning. You don't want database connection and query code all over your business logic. But that doesn't matter anymore, the problems you're currently having can be solved by using `try-catch` somewhere close to the entry point of your application (each form, each web page, each controller's action methdo, an error handler, just what is common for the application framework you're using). – CodeCaster Apr 19 '14 at 19:27
  • And the queries are not scattered everywhere. I use MVC pattern without entity framework. So I have Models, ViewModels, Controllers, Views and Data Access Layers <-- These are where the queries are. – szab.kel Apr 19 '14 at 19:27
  • Then use a `try-catch` whenever your business logic calls your data access layer, because that's the place where you can decide what your program should do when the database is unavailable. – CodeCaster Apr 19 '14 at 19:29
0

By default, OracleConnection is a connection pool. If you set the connect string attribute validateconnection to true it will do this work for you, although it will add a round trip.

Christian Shay
  • 2,570
  • 14
  • 24