C# with Database code simplication

Question

I am working on a project centered around an Oracle database (though honestly I don't expect that to matter) and I find myself have a fair amount of duplicate code, specifically exceptions. The best way I've seen so far is from this question https://stackoverflow.com/a/1554/865868, which suggests the use of delegates. This looked like the perfect solution until I tried to implement it in my project. I found that I had one case where it was not practical.

Let me describe my program a bit. I have two sections of code that deal with database operations. The idea is that I call a function that returns a DataTable, called LoadDataTable(). I then have a function that deals inserting items from a list into a table.

private void AddToList(List<string> itemList) {
    try {         
        using (OracleConnection connection = new OracleConnection(connectionString)) {
        connection.Open();
        foreach (string item in itemList) {
            using (OracleCommand command = new OracleCommand()) {
                command.Connection = connection;
                //Insert operation here
                //......
                command.ExecuteNonQuery();
            }
        }

    } catch (OracleException ex) {
        string messageboxtitle = "Database Exception";
        switch (ex.Number) {
            case 00001:
                MessageBox.Show("Constraint Violation Error", messageboxtitle, MessageBoxButtons.OK);
                break;
            case 12154:
                MessageBox.Show(string.Format("Connection Error: {0}", ex.Message), messageboxtitle);
            break;
            default:
                MessageBox.Show(ex.ToString());
            break;
        }
    }
}

private DataTable LoadDataTable() {
    DataTable dataTable = new DataTable();
    try {
        using (OracleConnection connection = new OracleConnection(connectionString)) {
            connection.Open();
            using (OracleCommand command = new OracleCommand()) {
                command.Connection = connection;
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                using (OracleDataAdapter oda = new OracleDataAdapter(command)) {
                    oda.Fill(dataTable);
                }
            }
        }
    } catch (OracleException ex) {
        string messageboxtitle = "Database Exception";
        switch (ex.Number) {
            case 12154:
                MessageBox.Show(string.Format("Connection Error: {0}", ex.Message), messageboxtitle); //Duplicate Exception
                break;
            default:
                MessageBox.Show(ex.ToString());
                break;
            }
        }
    return dataTable;
}

Keep in mind I had to rewrite and simplify that code so I could discuss it. Anyway, looking at the delegate example I quickly realized that paramaters are an issue. You can't use params for List<string> types, but there is no doubt in the usefulness of the delegates as I would be able to one one centralized section for exceptions that are not duplicated.

private delegate void DatabaseOperation(List<string> itemList);
private void PerformDatabaseOperation(DatabaseOperation operation, List<string> itemList){
    try {
        operation(itemList);
    } catch (OracleException ex) {
        string messageboxtitle = "Database Exception";
        switch (ex.Number) {
            case 00001:
                MessageBox.Show("Constraint Violation Error", messageboxtitle, MessageBoxButtons.OK);
                break;
            case 12154:
                MessageBox.Show(string.Format("Connection Error: {0}", ex.Message), messageboxtitle);
            break;
            default:
                MessageBox.Show(ex.ToString());
            break;
        }
    }
}

private void AddToList(List<string> itemList) {              
    using (OracleConnection connection = new OracleConnection(connectionString)) {
    connection.Open();
    foreach (string item in itemList) {
        using (OracleCommand command = new OracleCommand()) {
            command.Connection = connection;
            //Insert operation here
            //......
            command.ExecuteNonQuery();
        }
    }
}

How to use:

List<string> itemList = new List<string>();
//code to fill list
PerformDatabaseOperation(AddToList, itemList);

Problem is now that I am unable to implement LoadDataTable() with this delegate as it does not have any parameters. The use of params on the delegate does not work since List is incompatible. I'm looking to improve my coding techniques for re usability and readability but I find myself spinning my wheels reading various threads on the subject, mostly since they do not go in depth enough beyond a simple example that doesn't really catch the problem I find myself having now. To ensure this gets answered let me pose a final question. How could I write code which would avoid duplicating exceptions?

UPDATE

For anyone looking to solve a similar problem, see below. Keep in my there is plenty more that can be done to improve the code. Also, anyone interested in the discussion around the var keyword discussed here, go here. I hope this helps:

private delegate void DatabaseOperation();

private void PerformDatabaseOperation(DatabaseOperation operation) {
    try {
        operation();
    } catch (OracleException ex) {
        string messageboxtitle = "Database Exception";
        switch (ex.Number) {
            case 00001:
                MessageBox.Show("Constraint Violation Error", messageboxtitle, MessageBoxButtons.OK);
                break;
            case 12154:
                MessageBox.Show(string.Format("Connection Error: {0}", ex.Message), messageboxtitle);
            break;
            default:
                MessageBox.Show(ex.ToString());
            break;
        }
    }
}

private void AddToList(List<string> itemList) {              
    using (OracleConnection connection = new OracleConnection(connectionString)) {
    connection.Open();
    foreach (string item in itemList) {
        using (OracleCommand command = new OracleCommand()) {
            command.Connection = connection;
            //Insert operation here
            //......
            command.ExecuteNonQuery();
        }
    }
}

private DataTable LoadDataTable() {
    DataTable dataTable = new DataTable();

    using (OracleConnection connection = new OracleConnection(connectionString)) {
        connection.Open();
        using (OracleCommand command = new OracleCommand()) {
            command.Connection = connection;
            command.CommandText = sql;
            command.CommandType = CommandType.Text;

            using (OracleDataAdapter oda = new OracleDataAdapter(command)) {
                oda.Fill(dataTable);
            }
        }
    } 
    return dataTable;
}

Answer 1

I would suggest that you use a micro ORM such as PetaPoco which has Oracle support. Why write all that code if it can be auto generated? If you want to write your own data access layer I suggest you create CRUD methods for your classes such as

 public bool Insert(Person p)
 {...} 

You can also look into using generic methods to do this such as:

public bool Insert<T>(T item)
{...} 

Answer 2

Keep in mind that delegates capture variables from the parent method's body. This is also called a closure. Therefore it is often not necessary for the delegate to have a parameter list.

var itemList = new List<string>();
PerformDatabaseOperation(
    () => {
        ...
        itemList.Add(...);
        ...
    }
);

---

UPDATE

You would call it like this:

List<string> itemList = new List<string>();
PerformDatabaseOperation(() => AddToList(itemList));

The trick is to pass a lambda expression to the PerformDatabaseOperation that has no parameters (the empty braces ()); also PerformDatabaseOperation has no itemList parameter. The body of the lambda expression uses itemList declared and initialized just before the call to PerformDatabaseOperation. C# compiler magic does the rest.

Answer 3

One very often used pattern of passing varying parameters is to pass them to a constructor. The advantage is that constructors are not part of interfaces. However, it requires you to work with objects instead of delegates.

public interface IDatabaseOperation
{
    void Execute();
}

public class LoadListDatabaseOperation : IDatabaseOperation
{
    private List<string> _itemList;

    public LoadListDatabaseOperation(List<string> itemList)
    {
        _itemList = itemList;
    }

    public void Execute()
    {
        ...
        // Fill the list here
        ...
    }
}

Passing objects is a bit more costly but has also advantages, like being able to create hierarchies of operations having different degrees of specialization. You might have generic base operations and derive operations specialized for specific types. You can also use properties for scalar returns values etc.