CRUD Stored Procedures

Some environments mandate that all database access be through stored procedures.  The reasons vary, but usually centre around either performance – the architects believe that stored procedures are faster than table accesses – or security – denying application code access to tables prevents SQL injection.  The result of such policies is that every table comes with a set of stored procedures which do nothing more than perform CRUD actions.

The best way to handle such an environment is to overthrow the ‘stored procedures only’ policy.  In most modern large‑scale databases, the query planner is so efficient that the performance argument no longer holds water.  And LightSpeed always uses parameters to pass values in SQL statements, preventing SQL injection through unsanitised data.  The inconvenience, limitations and inefficiency of CRUD procedures dwarf whatever marginal benefits they may have.

If it is not possible to get direct table access, you can force LightSpeed to use stored procedures for its CRUD operations.  To do this, select each entity and change its Access Method to StoredProcedures.  The designer displays a new section in the property grid named Access Procedures: fill this section out with the names of the CRUD procedures.

In addition, you will need to tell LightSpeed to use stored procedures to load child collections in associations.  To do this, select the association arrow and fill in the Select Procedure option.

CRUD Procedure Conventions

You can define five access procedures for entity CRUD operations.

·         Select Procedure: Selects all entities of this type.

·         Select By Id Procedure: Selects a single entity by Id.  The procedure should take a single parameter, whose name is the Identity Column Name for the entity, and whose value is the entity Id to select.

·         Insert Procedure: Inserts a single entity.  The procedure should take a parameter for each column, with the same name as that column.

·         Update Procedure: Updates a single entity.  The procedure should take a parameter for each column, with the same name as that column.

·         Delete Procedure: Deletes a single entity.  The procedure should take a single parameter, whose name is the Identity Column Name for the entity, and whose value is the entity Id to delete.

You can also define an access procedure on an association, for looking up child collections:

·         Select Procedure: Selects all entities of the child type whose foreign key is equal to the parameter value.  The procedure should take a single parameter, whose name is the column name of the foreign key of the association, and whose value is the Id of the parent.

CRUD procedures must also adhere to any database-specific conventions for LightSpeed stored procedures.  This specifically affects Oracle databases.  See Working with Database Providers for details.

CRUD Procedure Limitations

CRUD procedures prevent you from using many aspects of LightSpeed.  For example, you cannot use ad hoc queries or eager loading with entities that are loaded through CRUD procedures.  You will need to create and invoke specific stored procedures for any queries you want to use.