Modelling database tables into classes

Answer 1

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

Maybe Linq to SQL or nHibernate?

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

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.