This is not specific to Oracle and describes an approach to development all DBAs might be interested in. This is an article that describes a technique for creating a clean stored procedure interface without direct table access. It is in a work in progress and an evolution of a system that has been tested in secure performance situations. Any thoughts appreciated.

This is .NET development with SQL Server, however easily applicable and readable by Oracle DBAs. Probably the only change would be the implementation of an OraclePersistence.cs rather than SqlPersistence.cs:


doPersistenceExample.zip

The zip file contains a VS 2010 solution and a SQL script to create a PersistenceTest database. It is intended to describe some basic concepts. doLogic is the business logical layer. doPersistence is the automated mapping layer. TestSite is the UI. Optionally, one might also add a doBusiness layer.

Download doPersistenceExample.zip

Introduction

This article discusses a structured data access methodology which provides a rich and safe business layer that can be customized to suit most developer's needs. The article suggests a technique using carefully designed naming conventions for stored procedures and a data access layer that translates properties to and from the SP parameters/resultsets. From there a business logic layer can be built that allows lazy-loaded objects, a clean database interface with execute-only permissions, and n-layer concepts for MVC-type of implementations. In the example, all persistable classes derive from abstract classes, however it can be easily modified if POCOs are desired. Its current design is not intended to be an academic discussion of design patterns, it is an evolution of a real-life example that can and has been used in production environments requiring security and performance.
Background

Mapping stored procedures directly to objects (entities/items) leverages SQL Server's organization, security, and optimization. Referential integrity is not copied into memory from database structures so the class becomes more than a representation of a table. A list of objects (for lack of a better term) becomes more than a list of rows in a table. Frequently table/object mapping becomes little more than intellisensed database column access. Rather, mapping stored procedures to objects/items/entities allows an abstraction and interface outside of direct table access. Column names can be changed, data types returned differently, joins can be made, summaries for read-only fields, etc. Execute-only permissions for production also increases security at the application level reducing chances of data corruption.

Stored procedures

This section describes the stored procedures. See "doPersistence maps parameters/resultsets to class properties" below for a description of the doPersistence layer and abstract c# classes.

For SQL Server 2005 and higher and assuming a user called 'sp_only' exists:

use SomeDatabase

create role db_spexecutor
grant execute to db_spexecutor

exec sp_addrolemember 'db_spexecutor', 'sp_only'


With the above code, the 'sp_only' database user is granted execute-only permissions to all stored procedures in the database including new ones added later.

The 'sp_only' user cannot access tables directly and the stored procedures are the only interface to the database. FYI, for those who use LINQ or other dynamic SQL creation tool, another user/connection can be deployed on a production server.



Types of stored procedures

Each object has 4 associated types of stored procedures in a structured naming convention:

1. load (prefix_load_ClassName[Action])

2. store (prefix_store_ClassName)

3. delete (prefix_delete_ClassName)

4. list (prefix_list_ClassName[Action])

A 5th type might be a "merge" stored procedure for bulk imports.

Load, store, and delete act upon a single object/entity/item.



Example: ab_list_CustomerInZipCode

'ab_' is the prefix for the application or company.

'list_' is hard-coded which indicates more than one row will be returned.

'ClassName' is the name of the Type of the business logic class

'Action' is an optional string to further define the stored procedure to be called ("InZipCode")

The doLogic layer makes a call to the stored procedure via doPersistence like this:


CustomerList list = new CustomerList();
list.ZipCode = 33812;
list.LoadList("InZipCode");


ZipCode is the parameter to the stored procedure and InZipCode is the optional Action to define which SP to call.

Loading a single object


create procedure ab_load_Customer
@CustomerId int
as
select * from Customer where CustomerId = @CustomerId


The following is a more realistic example with data type conversion, display-only field, and column name changes:



create procedure ab_load_Customer
@CustomerId int
as
select
CustomerId = ID,
CustomerName = Name,
Address,
City,
State,
Zip,
CustomerSince = convert(varchar(10), CustomerSinceDate, 102),
c.CustomerTypeId,
CustomerType = cs.Name
from
Customer c
join CustomerTypes cs
on cs.CustomerTypeId = c.CustomerTypeId
where
CustomerId = @CustomerId

The c# code that executes this via doPersistence:


Customer c = new Customer();
c.CustomerId = 3;
c.Load();


Properties are used for both parameter values and column result sets. CustomerId is mapped to @CustomerId, the stored proc is called, then each column returned is mapped to properties of the object. PThe stored procedure in this sense performs the function of the XML mapping files used in many ORMs.



Storing a single object


create procedure ab_store_Customer
@CustomerId int output,
@Name varchar(50),
@ZipCode int
as
if exists(select 1 from Customer where CustomerId = @CustomerId)
update Customer set
Name = @Name,
ZipCode = @ZipCode
where
CustomerId = @CustomerId
else begin
insert into Customer (
Name, ZipCode
) values (
@Name, @ZipCode
)
set @CustomerId = scope_identity()
end

return @CustomerId

The above is a simple case of the object mapping to a single table, however could be more complex with complex udts or relational information or updating other tables. Example that calls the above SP via doPersistence:


Customer c = new Customer();
c.Name = "Abc Corporation";
c.ZipCode = 33812;
c.Store();

int newIdentityValue = c.CustomerId;




Deleting a single object


create procedure ab_delete_Customer
@CustomerId int
as
delete from Customer
where
CustomerId = @CustomerId

--or an alternative delete implementation:

create procedure ab_delete_Customer
@CustomerId int
as
update Customer set
Active = 0
where
CustomerId = @CustomerId

--cascading deletes could occur here as well

C# code that deletes a single object via doPersistence:


Customer c = new Customer();
c.CustomerId = 3;
c.Delete();


Note the 2nd example where a deletion does not occur and simply sets a flag. In a real-life example, all Load stored procedures might only return Customers where Active = 1. This simplifies and protects the data and is one less thing for the .NET business layer developer to remember.



Listing objects from the database


-- shows all Customers
create procedure ab_list_Customer
as
select * from Customer

-- Active only
create procedure ab_list_CustomerActiveOnly
as
select * from Customer
where
Active = 1

--queried Customers
create procedure ab_list_CustomerInState
@State varchar(50)
as
select * from Customer
where
State = @State


--show child rollup information
create procedure ab_list_CustomerWithSummary
as
;with summary as (
select
CustomerId,
InvoiceCount = count(*),
InvoiceTotal = sum(Amount)
from
Invoice
group by
CustomerId
)
select
c.*,
s.InvoiceCount,
s.InvoiceTotal
from
Customer c
left join summary s
on s.CustomerId = c.CustomerId

The 4th example above retrieves child summary information from related tables for display purposes. This removes the need for loading child objects from the database and leverages SQL Server's unique capabilities for rapid sums and counts.

C# code calling the above SP via doPersistence:


CustomerList list = new CustomerList();
list.LoadList("WithSummary");

foreach (Customer c in list)
{
int numberOfInvoices = c.InvoiceCount;
}


Properties on the CustomerList object correspond to parameters on the various "list" stored procedures. Each stored procedure can have its own custom parameters that must be set on the class. For example:


CustomerList c = new CustomerList();
c.ZipCode = 33812;
c.LoadList("InZipCode");

c.Clear();
c.State = 'FL';
c.LoadList("InState");

c.Clear();
c.LastName = 'white';
c.State = 'OR';
c.LoadList("ByLastNameInState");

See part 2 for C# code describing the Persistence layer.