Modelling database tables into classes

Question

I'm designing a personal project which is largely database driven. I'm trying to come up with a good design for how to handle database interop, and I'd like the input of the experienced minds here at StackOverflow.

Is the following good design, or is there a more standard way of handling communication with the database?

EDIT
I was mainly looking for feedback on whether it was usual to break up the connection details into their own base class, with subclasses for logical units/classes.
To that end, I've modified the code below to add a retrieval method to complement the insertion method in the original question. I've also modified it to show that it takes/returns (depending on the method) business objects.
For instance, in the example we use the Employee class throughout the application, but have an EmployeeDb class (inherited from Database) which handles its persistence to/from the database.

I like this because it keeps storage implementation details out of the business objects, but dislike it because it strongly couples the Employee and EmployeeDB classes.

// Abstract Base Class to handle specifics of the database connection
abstract class Database : IDisposable  
{  
    protected OleDbConnection m_Conn;  

    public bool Open()  
    {  
        // Open a connection to the database  
    }  

    public void Dispose()  
    {  
        if (m_Conn != null)  
        {  
           m_Conn.Dispose();  
        }
    }  
}  

// Specific classes for each table, with methods for CRUD functions
class EmployeeDB : Database  
{  
    public bool AddTestData(Employee emp)  
    {  
        // Construct SQL to add Employee class members to the DB, breaking
        // them out into their component tables as needed  
    }  

    public List<Employee> GetEmployeeByProject(string project)  
    {  
        // Retrieve recordset of all employees on the project,
        // breaking them out into instances of the Employee class

        // Add each new Employee object to a list, and return the list
        // to the caller.
    }  
}  

// Specific classes for each table (or logical unit, since obviously 
// most of the time we'll need to join a few tables to get what 
// we want), with methods for CRUD functions
    void AddSomethingToTheDatabase()
    {
        using (TestDataDB td = new TestDataDB())
        {
            td.Open(Application.StartupPath);
            string NewID = td.AddTestData(txtAddMe.Text);
        }
    }

Answer 1

There are lots of ways of doing this, and the vast majority of OO folks will disagree with mine.

I suggest you take a more relational approach, and instead of having classes for tables, have classes for relations. In relational theory, the result of a query is a relation just like the relations that were used in the query; in fact you never see anything but results of queries, since even to get the contents of a table you need to SELECT * FROM x, which is applying the identity function to it.

So do your queries using the relational language you already have at your disposal (building up a framework to write the queries for you, if you need to go that far), and the results of queries should be your classes or, if you're not working in a language where it's easy to create new classes on the fly, some other sort of structure (such as objects). Then you simply use those results directly.

Answer 2

I would spontaneously want to disconnect the database dependency from the objects a bit. You could start by creating an interface that defines the storage methods for your TestData object (in my example there is only the Add method, but this can of course be extended):

public interface ITestDataRepository
{
    void Add(TestData data);
}

Next step is to create your TestData class. One feature of that class could be to accept an ITestDataRepository as a parameter in the constructor, and to expose an Add method:

public class TestData
{
    public TestData(ITestDataRepository repository)
    {
        Repository = repository;
    }

    public ITestDataRepository Repository { get; private set; }

    public string SomeData { get; set; }

    public void Add()
    {
        if (Repository != null)
        {
            Repository.Add(this);
        }
    }
}

Last thing to do is to implement the data mechanism that you want to use. In this case OleDb, but it could just as well be Xml, SqlServer or any other form of data storage:

public class DbTestDataRepository : ITestDataRepository
{
    const string InsertionQuery = @"INSERT INTO TEST (TESTDATA) VALUES (?)";

    public void Add(TestData data)
    {

        using (OleDbConnection conn = DbUtil.GetConnection())
        using (OleDbCommand command = new OleDbCommand(InsertionQuery, conn))
        {
            command.Parameters.Add("@p1", OleDbType.BSTR).Value = data.SomeData;

            using (OleDbDataReader reader = command.ExecuteReader())
            {
                // perhaps log something?
            }
        }
    }

}

The above design provides a couple of different advantages over the design in your question:

• There is no hard relation between your object model and the concrete storage implementation; you can change storage mechanism without altering the TestData object for instance.
• It is testable; since the only requirement on the data storage mechanism is that it implements the repository interface, the data storage can easily be mocked, which makes unit testing easier.
• In this concrete database code, the connection object lives only for a very short moment, which is usually the recommended way of doing it (the DbUtil.GetConnection method is something that I "invented"; it does not exist, but I usually like to centralize the code for setting up the connection, so I don't multiply it all over my code).

These are just some quick ideas (for instance, I did not test the code other than compiling it).

Answer 3

Are you sure you don't want to try an Object Relational Mapper?

Maybe Linq to SQL or nHibernate?