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.

[Shaggy Hiker]

This is a problem that people have wrassled with for a long time: Interactions with databases tend to fall into repetitive patterns of boringly similar code.

This realization has prompted numerous efforts to automate the functionality. I am currently working with a template based system written with a guy I work with. It's a pretty impressive piece of work, to be sure, but it has strengths and weaknesses in its design. I have felt for quite some time that the DAL and BO layers (overkill for a simple front end, but with this system you can build them in a matter of only a few minutes) were solid and functional. They did have a tendency to be overwrought, and not as efficient as I would like, but since they got the job done with no noticeable delay, does it really matter that I could have written the few hundred lines of code so that they performed 50-80% faster? It would have taken me hours to write those lines, whereas I generated them in less than fifteen minutes (and maybe less than 2 minutes) of effort, and if I got the business objects (essentially a wrapper around a datatable, in this case) wrong, I could change and re-generate in seconds. Can't do that by hand.

The drawback is that he went a bit too far. The program generates the entire program including the interface. Whenever you generate an interface, you constrain the design and layout in some way, and that was the greatest weakness of this program. Fortunately, it could handle custom code, and I wrote lots of it to make the interface work in the most useful way. The other problem was more nebulous...and irrelevant, so I think I get off this tangent by summing up with: The program generated a good database interaction layer, but it took a fair amount of effort to build that generator.

Recently, I have started into the Entity Framework, and that is something you should really look into before you go too far down this road. The EF is something MS has put out to solve pretty much the same problem. Building an entity model using the EF is quick to learn, and quick to accomplish. Doing something with that can take a bit more effort. However, it appears (I haven't gotten into it much, and keep getting drawn away to other things) that for simple apps it might give you a nice, relatively generic, means of interacting with the database through the entity objects.

[brin351]

It sounds to me that because you want to be able to do this

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)

You need to create a class for every table, storedProc etc in the database which seams ideal for a code generator to do for you even if you setup the generation templates yourself.

OR it's sort of asking for intelligence / intellisence about your database from your development environment which would be a great addin to VS.

On the other hand I sometimes prefer not to go down the "typed" data classes path because with simple DAL's like you use and a simple database like you often have it's not difficult to remember the table name and queries etc to pass to your DAL and I like the flexibility when the client says can you change that to this.

This is just my limited opinion. Here's a link by jmcilhinney about the DbProviderFactory class but you probably already know about that

http://jmcilhinney.blogspot.com/2009...ess-layer.html

[NickThissen]

It sounds to me that because you want to be able to do this

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)

You need to create a class for every table, storedProc etc in the database which seams ideal for a code generator to do for you even if you setup the generation templates yourself.

OR it's sort of asking for intelligence / intellisence about your database from your development environment which would be a great addin to VS.

On the other hand I sometimes prefer not to go down the "typed" data classes path because with simple DAL's like you use and a simple database like you often have it's not difficult to remember the table name and queries etc to pass to your DAL and I like the flexibility when the client says can you change that to this.

This is just my limited opinion. Here's a link by jmcilhinney about the DbProviderFactory class but you probably already know about that

http://jmcilhinney.blogspot.com/2009...ess-layer.html

Yes, I would need a class for every table in the database, and that class is basically an 'enumeration' of the attributes. It could be generated entirely automatically by looking at the attribute names and types, but that is a different story. Writing the entity classes is not so much work (and probably, writing a generator would mean more work).

I did not know about the DbProviderFactory class, I'll look into it, thanks.

This is a problem that people have wrassled with for a long time: Interactions with databases tend to fall into repetitive patterns of boringly similar code.

This realization has prompted numerous efforts to automate the functionality. I am currently working with a template based system written with a guy I work with. It's a pretty impressive piece of work, to be sure, but it has strengths and weaknesses in its design. I have felt for quite some time that the DAL and BO layers (overkill for a simple front end, but with this system you can build them in a matter of only a few minutes) were solid and functional. They did have a tendency to be overwrought, and not as efficient as I would like, but since they got the job done with no noticeable delay, does it really matter that I could have written the few hundred lines of code so that they performed 50-80% faster? It would have taken me hours to write those lines, whereas I generated them in less than fifteen minutes (and maybe less than 2 minutes) of effort, and if I got the business objects (essentially a wrapper around a datatable, in this case) wrong, I could change and re-generate in seconds. Can't do that by hand.

The drawback is that he went a bit too far. The program generates the entire program including the interface. Whenever you generate an interface, you constrain the design and layout in some way, and that was the greatest weakness of this program. Fortunately, it could handle custom code, and I wrote lots of it to make the interface work in the most useful way. The other problem was more nebulous...and irrelevant, so I think I get off this tangent by summing up with: The program generated a good database interaction layer, but it took a fair amount of effort to build that generator.

That sounds a lot like what I am trying to do (except the interface, I want to do that manually). As said above, the classes could be generated entirely by simply looking at all attributes.

I do realize that performance may suffer slightly, because I'd probably need reflection to pull apart the classes and iterate through their properties or whatever. But performance is no issue in my case, the applications and databases are so simple that it simply doesn't matter. Since the applications are not meant to be complicated, it is much more important to me that I can spent all my time on designing the interface (to make it as clear and easy as possible), instead of having to spent 80% on writing the same code over and over except with 'picture' replaced by 'document'.

Recently, I have started into the Entity Framework, and that is something you should really look into before you go too far down this road. The EF is something MS has put out to solve pretty much the same problem. Building an entity model using the EF is quick to learn, and quick to accomplish. Doing something with that can take a bit more effort. However, it appears (I haven't gotten into it much, and keep getting drawn away to other things) that for simple apps it might give you a nice, relatively generic, means of interacting with the database through the entity objects.

Looks promising, I'll look into this more when I get some more time.



I don't want to make this too complicated, if possible. As said, the applications are just for friends and family, and while I enjoy helping them out, I'm getting tired of it being so much 'dull work'. If my 'vision' would work, then it would be much, MUCH faster. All I need to do is design the database (it being so simple usually, that will take 2 minutes), then write the entity classes (another 2 minutes) and finally the manager class (3 seconds, as it's just the same every time only a different generic type). Then I could start concentrating on the interface already, and even that would be simplified. Loading and saving records would be a few lines of code, instead of writing queries and sending them to my functions, etc.

For example, when I want to let the user edit a record, I would show a dialog form and pass the ID to its constructor. In that constructor, i write a query that retrieves the data and then populate the form. When OK is pressed, I write a new query to save the new data.

Now, I could just do this

Dim id As Integer = GetSelectedId()  'returns some id
Dim pe As PersonEntity = personManager.LoadById(id)
Using frm As New EditForm(pe)
   If frm.ShowDialog = DialogResult.OK Then
      personManager.Save(frm.PersonEntity)
   End If
End Using

Now, the constructor takes a PersonEntity object, and it can simply populate its textboxes etc using the properties from that object. When OK is pressed it saves the new data back to the PersonEntity object and exposes it via a property, so I can save it using the same Manager object I used to load it.

I can do all this already, obviously, but then I would have to write the PersonManager and PersonEntity classes completely by scratch every single time, for every application, and that kinda defeats the purpose. What I'm looking for is a way to create base classes I can inherit which already implement most of the functionality, like loading and saving. A base class for the PersonEntity class wouldn't really do much good (right now I think an interface with a property ID and Delete or something would be enough), but a base class for the PersonManager would have to do all the loading and saving, and that's where I'm having trouble, as this basically goes against OOP design I guess. The base class would have to know what kind of Entity object it is dealing with. Right now I can only tell it that it implements IEntity, but that isn't any help. I can pass the type it needs via the constructor or via generics, but then I still have problems. For example, I would need to be able to create an instance of that type, but I will only know it at run-time...

EDIT
I just found I can create an instance at run-time by using

Dim inst As T = CType(Activator.CreateInstance(GetType(T)), T)

but then I still have further problems, because all I know is that T implements IEntity, while I need to know all of its properties...

[NickThissen]

EDIT: Updated.

Well I've gotten a tiny bit further I think. I could remember from a very different project that I once had to get a property by its name to set its value. So I looked it up and this is what I came up with:

Public Function Load(ByVal id As Integer) As T
        Dim type As Type = GetType(T)
        Dim inst As T = Nothing
 
        'Get name of table from type attributes
        Dim tableName As String = Me.GetTableName(type)
        If tableName <> String.Empty Then
 
            'Create instance
            inst = CType(Activator.CreateInstance(type), T)
 
            'Get Data Reader with data from correct table
            Dim params As New Dictionary(Of String, Object)
            params.Add("@ID", id)
            Dim reader As IDataReader = Me.Database.ExecuteReader("select * from " & tableName & " where ID = @ID", params)
 
            If reader.Read Then
                inst.Id = id
                inst.Deleted = (Me.Database.ToInt(reader("DELETED_CD")) = 1)
 
                'Loop through all properties of T
                Dim fieldName As String
                For Each prop As PropertyInfo In type.GetProperties(BindingFlags.DeclaredOnly) '
                    'Get field name from property attributes
                    fieldName = Me.GetFieldName(prop)
                    If fieldName IsNot Nothing Then
                        'Set its value to the value in the data reader
                        prop.SetValue(inst, reader(fieldName), Nothing)
                    Else
                        Me.Database.LogError("Missing FieldNameAttribute on property '" & prop.Name & "'.")
                    End If
                Next
            End If
        Else
            Me.Database.LogError("Missing TableNameAttribute on type '" & type.Name & "'.")
        End If
 
        Return inst
    End Function
 
    Private Function GetTableName(ByVal type As Type) As String
        Dim tableName As String = String.Empty
        Dim tableNameAttr As TableNameAttribute
        For Each attr As Attribute In type.GetCustomAttributes(False)
            tableNameAttr = TryCast(attr, TableNameAttribute)
            If tableNameAttr IsNot Nothing Then
                tableName = tableNameAttr.TableName
            End If
        Next
        Return tableName
    End Function
 
    Private Function GetFieldName(ByVal prop As PropertyInfo) As String
        Dim fieldName As String = String.Empty
        Dim fieldNameAttr As FieldNameAttribute
        For Each attr As Attribute In prop.GetCustomAttributes(False)
            fieldNameAttr = TryCast(attr, FieldNameAttribute)
            If fieldNameAttr IsNot Nothing Then
                fieldName = fieldNameAttr.FieldName
            End If
        Next
        Return fieldName
    End Function

It creates an instance of T by using the Activator.CreateInstance method. Then it reads the data (still need to find table name) into a data reader.

Then, I use the Type.GetProperties method to get a collection of all the properties of T. I found that the PropertyInfo objects have a SetValue method, which I suppose would set the value...? That's what I am assuming anyway. I am retrieving the field names for each property from their custom attributes, and I'm also retrieving the table name from the class attributes.
Now the Entity class looks like so

<TableName("PERSONS")> _
Public Class PersonEntity
    Implements IEntity
 
    Private _Id As Integer
    Public Property Id() As Integer Implements IEntity.Id
        Get
            Return _Id
        End Get
        Set(ByVal value As Integer)
            _Id = value
        End Set
    End Property
 
    Private _Deleted As Boolean
    Public Property Deleted() As Boolean Implements IEntity.Deleted
        Get
            Return _Deleted
        End Get
        Set(ByVal value As Boolean)
            _Deleted = value
        End Set
    End Property
 
    Private _Name As String
    <FieldName("NAME")> _
    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
    <FieldName("AGE")> _
    Public Property Age() As Integer
        Get
            Return _Age
        End Get
        Set(ByVal value As Integer)
            _Age = value
        End Set
    End Property
 
End Class

I am guessing that I'm getting closer, but since I don't really have any way to test this yet I really have no idea whether it works or not.

[NickThissen]

Well, I just managed to test it quickly, and, after some minor modifications, to my utter amazement it actually works!
Well, just the LoadById function. The save function would probably bring a whole new load of problems, because I'd have to generate an sql string out of nothing, but let's deal with that once the time comes...