Re: Insert a new row into a talbe using Addnew method via Oracle OLEDB Provider

rooty_hill2002_at_yahoo.com.au (Gary) wrote

> I am writing a Oracle database connection layer (a VB class using
> Oracle OLEDB provider) for my vb developers. I have a little problem
> here, see whether you have some idea about it.
<snipped>

Gary, I would chuck VB out of the window as the layer to use to provide that type of data class abstraction. Instead, I would select PL/SQL. The most simplistic of implementations : create a PL/SQL package per table. Treat the package as a persistant class. Define "methods" for this class as stored procs and functions within the package. AS PL/SQL supports polymorphisism, it works pretty well as you can "overload" methods within a package. You may also want to have a look at methods (aka functions) that can return reference cursors (similar to a Transact SQL stored proc that does a SELECT and returns a result set).

Advantages are numerous even with this simplistic approach. To mention a few:
- network traffic is reduced (up to 80% from personal experiece) - no SQL in the client (your VB programmers can do the job without being Oracle SQL experts)
- exposing an API of sorts to your programmers (of which the implementation is hidden) instead of Oracle SQL and physical db structures & issues
- allowing you to refactor (and finetune) the API implementation mercilessly without having to change a single line of VB code and redeploying the client app
- business logic moved to the database (where it should be) etc.

Ideally, the VB code should deal with information presentation logic, basic validation, and process flow. Anything more than that can be done better on the database side.

--
Billy