General approach to databases

[NickThissen]

Hi,

I am often asked to create a very simple database front-end for friends or family, and the structure of the database (and application) is usually very similar.

Right now I am always creating a class that handles all database interactions with some shared functions. For example, there's a function to run a general query and return a table, a function to save/load various items (depending on the application of course). The point is: the code is nearly always the same except for some slight variations.

For example, I was asked to create an application that manages a load of pictures. So, I wrote the class and made a function to load an image (which in turn calls the function to execute a query with the correct SQL query). I made a function to save an image to the database, etc.
A short while later, I was asked to create an application that manages a load of documents. The principle was exactly the same except instead of images there was now documents...

To cut a long story short; I am getting tired of writing (or copy/pasting) the same class every time, I'm sure there must be a way to get this 'automated'.



So I've been trying this for some time, but haven't even gotten close.

Right now, I have a class (Database) that I simply pass the location of an Access database file to. The important part is that it maintains an OleDbConnection object, and has some functions like ExecuteScalar, ExecuteNonQuery, etc, which basically accept a string and run the query.


My goal now is that, for each similar application, I can create a class that is simply a 'container' of all attributes of the corresponding database table, which it exposes through properties.
For example, suppose I have a simple table 'Person' with attributes ID, NAME and AGE, the class would look like this:

Public Class PersonEntity
  
    Private _Id As Integer
    Public Property Id() As Integer
        Get
            Return _Id
        End Get
        Set(ByVal value As Integer)
            _Id = value
        End Set
    End Property
 
    Private _Name As String
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property
 
    Private _Age As Integer
    Public Property Age() As Integer
        Get
            Return _Age
        End Get
        Set(ByVal value As Integer)
            _Age = value
        End Set
    End Property
 
End Class

Each property corresponds to a table attribute. I suppose I need some way to connect the properties with the attributes; perhaps via a custom property Attribute like so?

Private _Name As String
    <DatabaseField("NAME")> _
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property

I created an interface IEntity that requires the class to implement at least an ID property (more might be added later).


Anyway, besides this class (there might be many of these classes of course), I will also need a 'manager' class that manages loading and saving of instances of these classes.

My 'vision' is that I could do this:

Dim db As New Database("file here")   'my Database class
Dim pm As New PersonManager(db)
 
Dim pe As PersonEntity = pm.LoadById(3)
pe.Name = "John"
pe.Age = 35
pm.Save(pe)

what this should do is create a new PersonManager, which manages PersonEntities. Then the LoadById function returns a new PersonEntity object. This object will have its properties populated by running a database query ("select * from Persons where ID = 3" or whatever). Then, the Name and Age property are modified, and the new object is saved back to the database.


Obviously this should work without me having to write the LoadById and Save functions manually for each and every Manager. The goal is that I have a base manager (possibly a generic class?) which would automatically implement the load and save functions.
The base manager would look something like this

Public MustInherit Class TableManager(Of T As IEntity)
 
    Public Sub New(ByVal db As Database)
        _Database = db
    End Sub
 
    Private _Database As Database
    Public ReadOnly Property Database() As Database
        Get
            Return _Database
        End Get
    End Property
 
    Public Function Load(ByVal id As Integer) As T
        '???
    End Function
 
    Public Sub Save(ByVal entity As T)
        '???
    End Sub
 
End Class

(Note that T is constrained, it must implement IEntity)

And the PersonManager class would then simply be

Public Class PersonManager
    Inherits TableManager(Of PersonEntity)
 
    Public Sub New(ByVal db As Database)
        MyBase.New(db)
    End Sub
 
End Class

The obvious problem: what to do with the Load and Save methods?

I am running into a few 'major' problems when trying to implement them and starting to think it may not even be possible*...

This is how I imagined it, but it's obviously not going to work that easy:

Public Function Load(ByVal id As Integer) As T
        Dim t As New T
 
        Dim reader As IDataReader = Me.Database.ExecuteReader("select * from ??? where ID = " & id)
 
        While reader.Read
            t.Id = id
            t.??? = ???
            t.??? = ???
        End While
 
        Return t
    End Function

Problems:
1. I don't know the table name. This could be solved by supplying the table name in the constructor I suppose.
2. a) I don't know which attributes the reader will be reading except the ID
2. b) I don't know which properties T has.
(3. I cannot create an instance of T, but I am sure I did this before; I just need to constrain T so that it must have a constructor I believe?)




Now, for problem 2... The Entity classes (such as PersonEntity) have a list of properties which correspond to database fields. The name of those fields is listed in the Attribute above the property. I am pretty sure I could use reflection or something to retrieve a list of the properties and their attributes. Then, I would have a list of the properties and theoretically I should be able to set their values. I can load the values from the database because I also know the field names.

But how to do this?


I suppose I could also do it without generics, where the Load function returns an IEntity object (and the save function accepts one), but the problem then is even worse: I cannot create an instance of IEntity, but I cannot know which type I should create a new instance of.
I could supply the type in the constructor though, so that would yield something like this

Public MustInherit Class TableManager
 
    Private _t As Type
 
    Public Sub New(ByVal db As Database, ByVal t As Type)
        _Database = db
        _t = t
    End Sub
 
    Private _Database As Database
    Public ReadOnly Property Database() As Database
        Get
            Return _Database
        End Get
    End Property
 
    Public Function Load(ByVal id As Integer) As IEntity
        Dim t As New 'instance of type _t   how???
 
        Dim reader As IDataReader = Me.Database.ExecuteReader("select * from ??? where ID = " & id)
 
        While reader.Read
            t.Id = id
            t.??? = ???
            t.??? = ???
        End While
 
        Return t
    End Function
 
    Public Sub Save(ByVal entity As IEntity)
        '???
    End Sub
 
End Class

and the PersonManager would be

Public Class PersonManager
    Inherits TableManager
 
    Public Sub New(ByVal db As Database)
        MyBase.New(db, GetType(PersonEntity))
    End Sub
 
End Class

I think that's enough for a single post... I'm sure many more problems will arise, but I hope someone can at least give me some ideas.



*However, I am sure it is possible in some way. I used to do a little work for some company and they used this exact 'structure' (that's where I got the idea in the first place). I could ask them how they did it I suppose, but I doubt they would tell me. I could use Reflector to take a peek myself but somehow that feels much more like stealing then asking advice on a forum. If you disagree please let me know. I wouldn't feel comfortable stealing their code and using it as my own. I don't have this problem though if I merely explain the idea and ask others for help, right?

They used the last approach I mentioned though, passing the type to the constructor, without generics. Not sure if that matters.