Stored procedures and object oriented programming..

[ashish Hosalkar]

What is the practical way of using stored procedures / ADO in a multi-tiered
app. especially for a reporting app.
Should business logic(some ?) be there within the stored procedures; or as
the purists would have it ; should there be cut and dry partitioning.

[ranga raghunathan]

"ashish Hosalkar" <ashish.hosalkar@centresolutions.com> wrote:
>
>What is the practical way of using stored procedures / ADO in a multi-tiered
>app. especially for a reporting app.
>Should business logic(some ?) be there within the stored procedures; or
as
>the purists would have it ; should there be cut and dry partitioning.
>

Use stored-procedures.. pass form information entered by user to a class
method that mirrors the stored procedure's input parameters .. in the class
method, construct a sql string such as strSQL=sp_SomeStoredProc param1,param2
and use cn.execute to execure the stored proc .. this is "practical" because
to some extent, you can make changes to the stored procedures code without
having to recompile your component. if the stored proc returns a result-set
(select statement), then catch it in a static-readonly ado cursor (or other
types as appropriate) and disconnect the recordset from the connection..


another approach, which, IMHO is NOT "practical" is to use the sql statements
in the vb-classes themselves .. such as
strSQL= "UPDATE table1 set field1=value1" .. the problem with this is that
if any change needs to be made to this statement, you need to recompile the
vb-class. Also, it would be a pain to
construct the sql string.

since in the MS DNA model, transactions are taken care of at the component
level, there is no need to do BEGIN TRANS in the stored procs .. should you
include "Some" business logic in stored procs .. again, IMHO, it is ok to
do so, particularly if you get SQL Server to do most of the crunching and
not burden your components.

hope this will help you a bit
ranga