What is the most DRY way to get data out of my database?

I have to write an ASP.NET application that connects to our legacy IBM Universe Database and we are using a product called mv.net which allows us to connect, read, write, select, run server side programs, etc.

I want as little code repetition as possible but I also want as little data transfer as possible as well.

In order to open a connection we must first get a reference to the account using code such as:

mvAccount myAccount = new mvAccount(serverName, login);

Then we can read an item:

mvFile myInvoiceFile = myAccount.FileOpen("INVOICE");
mvItem myInvoiceRecord = myInvoiceFile.Read(invoiceID);

Then we we're done:

myAccount.Logout();

I have a Class for each module, so I may have INVOICE, PURCHASE_ORDER, RMA, REQ, SHIPMENT, and so on. Within INVOICE, I may need to access multiple tables such as CUSTOMER, INVOICE, TERMS, SHIPVIA, etc.

What I planned to do was create a class called TechDB which is the name of our database and put the code in there so in my INVOICE class I can just say:

TechDB connection = new TechDB();
mvItem myInvoiceRecord = connection.Read("INVOICE", invoiceID)

When I do this my TechDB class would open the connection, read the record, and then logout all in one step.

I think I'm heading down the right path but please let me know if not. Here are my problems with this:

1. How do I return errors my INVOICE class? For example, errors could occur if we are unable to connect to the database, unable to open the file, unable to read the record.

2. What if I then need to take some data from my INVOICE and then read the TERMS table. I'd hate to have to open a new connection to the database when I just opened one.

3. Should I call the Dispose method on all classes that have this? For example, the mvAccount has a Dispose method. None of the documentation says to call it, but should I after the Logout()?

4. Could I create a Dispose method on the TechDB class that does the myAccount.Logout()? That way the connection would remain open and I could close it from my INVOICE class when I was completely done with it?

Give me some opinions on the best way to handle this? My goal is a robust application that is easy to modify and as little code repitition as possible.

From stackoverflow

• I would use closures, I think for C# you have Delegates. So something like:

  MyAccount.loginAndDo(servername, login, delegate(account){
     invoice = account.read("INVOICE");
     .
     .
     .
  });
  

  In loginAndDo, you would login, call the delegate, and then close the account.

• 1. Custom exception classes
  2. One idea would be to construct batch requests, (a list of delegates).

  as per 3/4. In my case, all of my data access objects inherit from a class that holds a static reference to a connection. I'm hesitant to implement disconnect logic in Dispose because of the possibility that there is a power out or system crash or something and that connection isn't released.

• You may want to take a look at an OleDB / ODBC connection. This is what we are using to connect to Universe.

  See: http://www-01.ibm.com/software/data/u2/middleware/