C# - What is good SQLException Error Handling for Time-outs and failed connections

Question

I use EntitySpaces for all of my Database related coding. As a sole developer I don't have time to sit and write the SQL for each and every application I write, so using a generator such as ES Suites me down to the ground and saves me days, if not weeks, of time.

I generally write a series of helper classes for each of the tables that my application utilises. The issue I have is that I don't really know the best way to handle any SQL Timeouts or failed connections, apart from stick a Try-catch around each method that deals with returning data.

In EntitySpaces the SQL Connection is built and executed only when I run any kind of CRUD command.

For example:

    public TblUserCollection GetCollection()
    {

        TblUserCollection collection = new TblUserCollection();
        collection.Query.Where(collection.Query.CompanyId == CompanyId);
        collection.Query.OrderBy(collection.Query.FullName, esOrderByDirection.Ascending);
        collection.Query.Load();

        return collection;

    }

This method is called when my helper class is told to assign the user list of a certain company to a ComboBox. The method then calls this and I assign the data to the list. I've got about 30 of these dotted around, all called GetCollection() in table specific helper classes.

Aside from just writing the method as:

    public TblUserCollection GetCollection()
    {

        try
        {

            TblUserCollection collection = new TblUserCollection();
            collection.Query.Where(collection.Query.CompanyId == CompanyId);
            collection.Query.OrderBy(collection.Query.FullName, esOrderByDirection.Ascending);
            collection.Query.Load();

            return collection;
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            //
        }

    }

What else can I do?

Answer 1

This doesn't really answer your question, but you might try learning LINQ. It could make this code:

public TblUserCollection GetCollection()
{

    TblUserCollection collection = new TblUserCollection();
    collection.Query.Where(collection.Query.CompanyId == CompanyId);
    collection.Query.OrderBy(collection.Query.FullName, esOrderByDirection.Ascending);
    collection.Query.Load();

    return collection;

}

alot more readable in my mind.

Answer 2

Define a base class to handle your SQL error handling and let all your classes ex. TblUserCollection implement that base class. So when your child classes throw sql exceptions, you can have the base class handle them for you with graceful exits

Have you tried Subsonic (free version) and LLBLGen(commercial version). Both will generate the DAL and some web components for you. Also are you happy with Entityspaces? Your answer will help me with my own projects.

Answer 3

The conventional approach would be to just let the exception propagate from your GetCollection method. You usually don't want to return what may be an incomplete collection.

Then maybe in the top-level of your presentation tier, you could catch the exception and e.g. offer the user the opportunity to retry.