Yet another n-tier development question

*** Note: I also posted this in the COM/AX forum ***

I'm looking to get some opinions on this. What do think is the best way to get data to the presentation layer (i.e., your VB user interface) from the business object or application layer? For example, let's say you need to load up some combo boxes from various lookup tables.

Of course bear in mind that the presentation layer must not directly access the database. The "GetRows" method seems like it would be most efficient, but I don't particularly like referring to fields by numbers and the reverse row/column structure of the array returned by GetRows can be a little confusing if I haven't had my coffee yet. What is your opinion on the use of UDTs or collections for this kind of processing?

I must admit that although I have been using VB for quite a while, I'm not a heavyweight when it comes to OO development (but I'm working on it). And yes, I have some books that I need to dig into where this is concerned. What I'm looking for here is views from folks who have been down this road before.

Thanks for your attention.

I would create a dll that handled all the business rules that your user interface goes thru to get to the DB.


Is that what you ment?

I know that I would create one or more DLLs that contain the business rules. The classes in these DLLs would talk directly to the database (using ADO or whatever). My question is, when methods of these classes do a SELECT-type statement to pass data back to the presentation layer (i.e., a VB form), what techniques are preferred so that there is no reference to ADO in the form? For example, the business classes must pass back the data in a more of a generic-type structure (such as an array or collection) as opposed to an "ADODB.Recordset". Or alternatively, the business class could pass back one "row's worth" of data at a time, with the VB form calling a "MoveNext" or "GetNext" type method of the business class. (The latter seems less efficient.) So again, I ask your opinions on the best way of constructing a true "n-tier" application ...

Thoughts on this, anyone?

Your form should call Business logic class only. In turn business class would call all DB operations from the DB class.

It's like Serge said you'd use the biz classes in place of lets say the recordset. So if you normally access a table with user info you'd probably have the properties of the CUser class would what was the fields of the table, kind of.

OK, I understand the concepts. Now, how about a practical example: say I want to load up a combo box with data from a lookup table. From my form, I invoke cBizClass.GetDepartmentData. The GetDepartmentData method access the database, using "SELECT DeptCode, DeptName FROM Departments". In that method, the database returns a recordset. I now want to load that recordset data into the form's combo box. What would you say is the best way for cBizClass to transfer that data back to the form?

You have a couple different things you could be there:

You could pass the combo/list control to a function in the class that fills it.

You could call a function in the class that returns an array of the data you want. Or if not an array a collection or whatever you need.

Or if you are working with a collection you can loop through its items and add them to the list.

Ok, here it is. I've created a classic example where you would have everything in classes. So my tiers are CDB, CBusiness and Form1. Also for department I use CDepartments collection class and CDepartment class to hold the data for each Department.

VB Code:

'==========CDepartment Class=================
Private m_lngDeptId As Long
Private m_strDeptName As String
 
Public Property Let DeptId(ByVal p_lngDeptId As Long)
    m_lngDeptId = p_lngDeptId
End Property
 
Public Property Get DeptId() As Long
    DeptId = m_lngDeptId
End Property
 
Public Property Let DeptName(ByVal p_strDeptName As String)
    m_strDeptName = p_strDeptName
End Property
 
Public Property Get DeptName() As String
    DeptName = m_strDeptName
End Property
 
 
 
 
'=============CDepartments Collection==========
Private m_colDept As Collection
 
Public Sub Add(Item As CDepartment, key As Variant)
    m_colDept.Add Item, key
End Sub
Public Property Get Count() As Long
    Count = m_colDept.Count
End Property
 
Public Sub Remove(index As Variant)
    m_colDept.Remove key
End Sub
 
 
Private Sub Class_Initialize()
    Set m_colDept = New Collection
End Sub
 
Public Function Item(index As Variant) As CDepartment
    Set Item = m_colDept.Item(index)
End Function
 
 
'===============CDB========================
Private m_CN As ADODB.Connection
 
Public Function GetDepartmentRS() As ADODB.Recordset
    Dim rs As New ADODB.Recordset
    
    rs.Open "Select * from department", m_CN, adOpenStatic
    Set GetDepartmentRS = rs.Clone
    rs.Close
    Set rs = Nothing
End Function
 
Private Sub Class_Initialize()
    Set m_CN = New ADODB.Connection
    'change this to any other connection
    m_CN.Open "provider=microsoft.jet.oledb.4.0;data source=c:\MyDB.mdb"
End Sub
 
 
Private Sub Class_Terminate()
    If m_CN.State = adStateOpen Then m_CN.Close
    Set m_CN = Nothing
End Sub
 
 
'=============CBusiness Class================
Private m_objDBOper As CDB
 
Private Sub Class_Initialize()
    Set m_objDBOper = New CDB
End Sub
 
Private Sub Class_Terminate()
    Set m_objDBOper = Nothing
End Sub
Public Function GetDepartmentData() As CDepartments
    Dim objDept As CDepartment
    Dim rs As New ADODB.Recordset
    Dim colTemp As New CDepartments
    
    Set rs = m_objDBOper.GetDepartmentRS()
    
    Do Until rs.EOF
        'prepare Department object
        Set objDept = New CDepartment
        With objDept
            .DeptId = rs.Fields.Item("DeptId").Value
            .DeptName = rs.Fields.Item("DeptName").Value
        End With
        
        'add department to the collection
        colTemp.Add objDept, CStr(rs.Fields.Item("DeptId").Value)
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    
    Set GetDepartmentData = colTemp
End Function
 
 
 
 
'===========Form Code==================
Private Sub Command1_Click()
    Dim objDepts As CDepartments
    Dim objBus As New CBusiness
    Dim i As Integer
    
    Set objDepts = objBus.GetDepartmentData()
    
    For i = 1 To objDepts.Count
        List1.AddItem objDepts.Item(CStr(i)).DeptName
    Next
End Sub

I hope this explains enough:)

Thanks, Serge, I'll play with this when I get home.

I'm working on a large N-Tier application as we speak, and all of our data is structured in XML. XML is great and easy to work with. We populate our business objects using it. When our app talks with an outside datastore, we speak via web service calls. The only means of communication is through HTTP requests and sending and receiving XML.

Since I looked at this some more, I realize that by using an object (in Serge's example, the CDepartment class) for the Item property of the Collection class (as Serge used the CDepartment object for the CDepartments' Item), each field that you want to return from a recordset can be coded as a property in the object's class. So what I initially thought was a limitation of the Collection is actually not.

Thank you all for responding; I learned quite a bit from all this.

Great :)