3-tier ... no

[jhermiz]

It's probably my lack of understanding but it is a rather big concern to me...

I've seen people create components (dll's) for say their specific project so that they have some sort of 3-tier solution...

The component actually manipulates a database and its objects. Mostly one can create an object and call methods / functions on this object returning booleans, objects, recordsets, etc.

But I think and this is specifically for SQL Server, that this method is not ideal. For instance, with a three-tier system the client may create an object, then functions of that components are called from the dll...creating a connection and pulling back a recordset or updating a recordset directly. However, my concern is this is inefficient in my opinion. Why do people even bother creating components to represent a database for things such as UPDATES, SELECTS, and DELETES?

Why is there even a need to place SQL in the component when a stored procedure can yield much faster results...It helps to give an example:

Project: Employee
Class: clsEmployee

Dim objEmp As Employee.clsEmployee
Dim rst as ADODB.Recordset
 
Set rst = objEmployee.SelectData
 
'do something with rst
do while not rst.eof
  'blah
  rst.MoveNext
loop

Finally inside of the employee class I have a function called SelectData which returns recordsets/objects or whatever...but I dont see the great performance in implementing SQL on the component / client side. The component is still sitting on the client side processing the code...so an example might be the following:

private sub SelectData() as Object 'can be recordset here
 
'make connection to the database
'code...etc
 
Dim strSQL as String
 
strSQL = "SELECT blah1, blah2, blah3 FROM BlahTable"
 
SelectData = objConn.Execute(strSQL)

Where is the efficiency here ??? I'd rather have a two-tier system which processes recordsets via the ADODB.Command object and a simple stored procedure on the back end. SQL on the component side in my opinion is pointless...why not let the server do the grunt work?

Anyone with some comments ?