object mapping

[nswan]

Hi all,
I am currently discussing a project with someone and I am putting forward the idea of using a datalayer, business layer, and UI layer(s). This is how I usually approach work as I see it as a best practise. The business layer contains classes that directly map to tables in the databases, so instead of always passing datasets around, I am passing custom collections of my objects.

As I said I use this approach usually, but when trying to explain the benefits to someone else I really struggled. I need to sumarize the benefits/drawbacks in the following areas:
1, Development Time
2, Security
3, RunTime speed
4, Maintainability
5, Scalability
6, Cost

I've tried searching on Google but only really get OR mapping tools returned. Any body know any good articles, or can add info to the above points for Object Relational Mapping?

Cheers
Nick

[Dave Sell]

This is extremely wierd of you to post this... I was just having this discussion with a Java architect about this. My suggestion was to do exactly what you describe; to create an Object to represent each database concept!

I mean, we all love to deal with Objects, why not encapsulate the database itself with Objects? Each Object could be designed with internal intelligence to auto-generate the appropriate SQL statements based on its own internal structure.

Anyway, I believe it is the right thing to do, but he insists that this approach is naiive and will not work.

If you are willing to pursue your idea I will discuss the development with you.

[janis]

I have one practical question: suppose we have table named 'employee' in db, and same class employee (represent one employee), where you put method e.g. GetAllEmployees() ? This method need to return all employees from table employee. Do you put this method in employee class or make new class?

regard j

[nswan]

Sorry meant to reply to your post before Dave,
I generate objects to map to the DB, but I still use stored procedures to do the CRUD stuff. The things to generate the SQL are very damm clever but I wonder how much processing time they require? You might as well use the SQLDataAdapter mighten you and just do an update?
Sometimes my objects overlap the database tables as well so the select SP has a join in it, and fills the object with necessary data from 2 tables. This isn't the norm though.

janis, I generally have 2 classes per object. For your example, an employee object, and an employeeCollection object. The employee object can fill itself and return it to wherever it needs to go. The employeeCollection object has a GetEmployees(parameters....) function, that returns an array list of employee objects.

This is only my view on stuff though and may be completely wrong! I hope someone can put me on the right track if it is!

Cheers
Nick

[Dave Sell]

I have one practical question: suppose we have table named 'employee' in db, and same class employee (represent one employee), where you put method e.g. GetAllEmployees() ? This method need to return all employees from table employee. Do you put this method in employee class or make new class?

regard j

In my suggested paradigm, the Class will have a GetEmployees or Retrieve method. The Class will inheret the appropriate intelligence from a root Class of intelligent design, which will "just know" how to get the Employees, based upon Filter (WHERE clause) inputs.

[Dave Sell]

Sorry meant to reply to your post before Dave,
I generate objects to map to the DB, but I still use stored procedures to do the CRUD stuff.

I am suggesting to completely do away with Stored Procedures as the rule. The exception can be extremely poor performance, but I would hope it would never come to that. Again, I may be being overly-naiive.

The things to generate the SQL are very damm clever but I wonder how much processing time they require? You might as well use the SQLDataAdapter mighten you and just do an update?

No, not really. The proposed intelligence I refer to can do very much more. I want to add a field to the Object itself, and have it "just know" how to update the Table definition for me. Likewise remove a Field from the Object. I never again want to use Enterprise Manager, rather some special Object manager for the middle tier. The Back-end database becomes an invisible after-thought, that only needs addressing in exceptional circumstances.

Sometimes my objects overlap the database tables as well so the select SP has a join in it, and fills the object with necessary data from 2 tables. This isn't the norm though.

As is expected, but again I think an Object can be instructed how to "just know" about such things, as in 2 Table Obejcts, and 1 Relationship Object, which knows about the 2 Tables. This is a very rough sketch as you can tell.

This is only my view on stuff though and may be completely wrong! I hope someone can put me on the right track if it is!
Cheers
Nick

I think we left right vs. wrong about 2 miles back.

[Dave Sell]

In the hopes of rekindling this idea I am posting an example of what I am talking about. It's not glamorous, but if you have time I'd like you to try it out. Its a generic and intelligent Record. Since it's VB6 we use aggregation as opposed to inheritance.

A table Object would be the consumer of the Generic Record Class and use it at Initialization to define itself (hint: at run-time).

Here is the Record Class. Notice it auto-generates a SQL segment of the INSERT statement, to be completed by its "parent" Table.

Option Explicit
'
Private m_colFieldNames As Collection
Private m_colFieldValues As Collection
'
 
Public Sub Add(strName As String, strValue As Variant)
    m_colFieldNames.Add strName, strName
    m_colFieldValues.Add strValue, strName
End Sub
 
Private Sub Class_Initialize()
    Set m_colFieldNames = New Collection
    Set m_colFieldValues = New Collection
End Sub
 
Public Property Get Names_Values() As Variant
    '
    Dim varFieldName As Variant
    Dim varFieldValue As Variant
    '
    Names_Values = "("
    '
    For Each varFieldName In m_colFieldNames
        Names_Values = Names_Values & varFieldName & ", "
    Next
    '
    Names_Values = Left(Names_Values, Len(Names_Values) - 2) ' Remove extra comma + space
    Names_Values = Names_Values & ") VALUES ("
    '
    For Each varFieldValue In m_colFieldValues
        Names_Values = Names_Values & "'" & varFieldValue & "', "
    Next
    '
    Names_Values = Left(Names_Values, Len(Names_Values) - 2) ' Remove extra comma + space
    Names_Values = Names_Values & ")"
    '
End Property
 
Public Property Get Size() As Variant
    Size = m_colFieldNames.Count
End Property

Here is a sample Table. It uses the Record Class to define itself at run-time.

Public Sub NewIssue( _
    intIssueNumber As Integer, _
    strDescription As String, _
    intStatus As Integer, _
    intUrgency As Integer, _
    strDueDate As String, _
    intValue As Integer, _
    intTimeEstimate As Integer, _
    strContact1 As String, _
    strContact2 As String, _
    strContact3 As String, _
    strAssignee As String, _
    strAssignee2 As String, _
    strDateAdded As String, _
    strDateAssigned As String, _
    strDateOpened As String, _
    strDateResolved As String, _
    strDateClosed As String, _
    strDateChanged As String, _
    intParentIssue As Integer, _
    strGroup As String, _
    strCategory As String _
    )
    '
    m_Record.Add "IssueNbr", intIssueNumber
    m_Record.Add "Description", strDescription
    m_Record.Add "Status", intStatus
    m_Record.Add "Urgency", intUrgency
    m_Record.Add "DueDate", strDueDate
    m_Record.Add "BusValue", intValue
    m_Record.Add "TimeEst", intTimeEstimate
    m_Record.Add "Contact1", strContact1
    m_Record.Add "Contact2", strContact2
    m_Record.Add "Contact3", strContact3
    m_Record.Add "AssignedTo", strAssignee
    m_Record.Add "SuperUser", strAssignee2
    m_Record.Add "AddDate", strDateAdded
    m_Record.Add "AssignDate", strDateAssigned
    m_Record.Add "OpenDate", strDateOpened
    m_Record.Add "ResolveDate", strDateResolved
    m_Record.Add "CloseDate", strDateClosed
    m_Record.Add "ChgDate", strDateChanged
    m_Record.Add "ParentIssueNbr", intParentIssue
    m_Record.Add "GroupID", strGroup
    m_Record.Add "Category", strCategory
    '
End Sub
 
Private Sub Class_Initialize()
    Set m_Record = New clsRecord
    Set m_Conn = New Connection
    Set m_Command = New Command
    '
    m_Conn.Open "Provider=sqloledb;" & _
               "Data Source=SQLSERVER;" & _
               "Initial Catalog=DATABASE;" & _
               "Integrated Security=SSPI"
    '
    m_Command.ActiveConnection = m_Conn
    '
End Sub
 
Public Property Get Insert_Satement() As Variant
    Insert_Satement = "INSERT INTO " & TABLE_NAME & " " & m_Record.Names_Values
End Property

[RobDog888]

Wouldnt the performance suffer using collections?

[Dave Sell]

Run-time, or design time performance? Run-time - who cares if its10% slower than arrays (I'm being generous). Design-time - this is where I am most concerned.

[nswan]

Hiya,

have you lot ever looked at Code Smith? We had to create a load of business object type things the other week, and this just made it a breeze! There are some good templates out there already that you can just hack together and learn off.

I was told about it ages ago but only just downloaded it the other week. Boy do i wish i got it ages ago!

Cheers
Nick

ps i know that sounds like a sales speech, but it really is good!

[Dave Sell]

Code Smith - looks interesting. I'm not sure by looking at it how it would be useful. I guess it's something you have to try it out in order to feel the benefit. This is also true for the Record Class I posted. It really simplified my life.

[Edneeis]

Here is a very nice, effective and cheap OR Mapper tool.
http://www.ormapper.net/News/

I have used it before and it is pretty nice. The GUI for doing the mapping could use some work but the core of the deal is pretty cool especially for only $50. I came across it while making my own OR type tool, but mine used attributes to do the mapping and I kind of like how this one uses external xml files.

Anyway I just figured I'd bring it up.

I could probably big up the code to mine if someone is interested, too.