n-tier & COM confuse

[buddu]

i hv functional process (business logic ) as follows


sql="select * from ...."

rs.open "sql"

do while not rs.eof()
calculateAmt=calculateQamt(rs("field1"),rs("field10"))

If caculateamt>0 then
myval=do some events
else
myval=do some Events
endIf
rs.movenext
end loop

if myVal>0 then
runStoredProcedure for Insert
else
runStoredPrcodure for Update
endif

------------------------

for this
1.do i need to write everything in StoredProcedre?
2.write everything in COM?
if i write rs.movenext,rs.open is this good approach

Which is the best approch for n-tier arch.
Help me

[alex_read]

Hi Buddu!

1) do i need to write everything in StoredProcedre?
Not neccessarily - you could place the code in there below & use ado/dao as you are using with the recordset & select statement at the top of your code - it's entirely down to you. I myself haven't learnt stored procedure code/syntax & still code it this way ('bout time I got round to that...).

For the reasoning why it's preferred to use stored procedures instead of SQL statements, I've pinched a quote from the MSDN helpfiles:

You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:

* You can execute a series of SQL statements in a single stored procedure.


* You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.


* The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements.


* The compiled stored procedure can be cached in memory as well, for faster execution.

[alex_read]

2.write everything in COM?
if i write rs.movenext,rs.open is this good approach...

Which is the best approch for n-tier arch.
Really awkward to answer that one as I haven't a clue about the project you're writing - what it has to do, who it's used by etc. If you can put a bit more down about your setup, I can be of more help here!

[yrwyddfa]

You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:

* You can execute a series of SQL statements in a single stored procedure.


* You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.


* The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements.


* The compiled stored procedure can be cached in memory as well, for faster execution.

All but the last two can be done in VB, though. So the only advantage in using stored procedures is speed (according to M$)

However using ADO Command objects caches SP's

All SQL statements sent to SQL Server (2K) are parameterised and stored in the Query cache so the query plans are reused anyway (depending on RAM constaints)

[yrwyddfa]

As for write everything in COM. Everything you natively do in VB6 is COM!

Most of the runtime libraries lever the COM libraries that are already on your machine.

The caveat, of course, is when calling WinAPI's direct.

VB6 really is the worlds most flexible COM component.