invoke Stored Procedures through a generic class

[dennis]

hello,
I am using Oracle Stored Procedures to return and modify data in my
application which is developed using ASP. NET(VB.NET).
How are the SP's invoked from the application
1) Are they directly called from the application like
sql = "{ CALL PROCEDURE_NAME(?,{resultset 0, io_cursor} ) }"
OR
2) Can we have a generic class (VB/C#) which has calls to all the stored
procedures and the client application would call this class
Client Code would be something like
SQLstatment = ClassName.method1( )

The generic Class would contain:
Class Name {
method1( ){
//Connect to the DB
//fetch records from DB using SP's
}
}

I would be glad to hear from inputs on these and if anyone has used a
similar approach like 2)...please share some more info abt the same.

thx,
dennis

 

[Jay B. Harlow [MVP - Outlook]]

Dennis,
Following the OOP lead of layering software (UI Layer, Business Layer, Data
Layer).

I would use a variation of #2, depending on other requirements of the
application.

Remember these are logical layers not physical layers. You can layer your
software within a single project, destined for a single machine.

If you read Martin Folwer's book "Patterns of Enterprise Application
Architecture" from Addison Wesley. He offers a handful of patterns to
address this issue: Data Table Gateway, Row Data Gateway, Active Record,
Data Mapper.

http://www.martinfowler.com/eaaCatalog/

Hope this helps
Jay