Stored Procedures

 

From version 4.0 Eloquera DB supports stored procedures – or better to call them “stored methods”? The notion of the stored procedures (SP) is well-known to those with experience in the relational databases: SPs are blocks of code, which can be invoked on the database side, usually to perform multiple data operations or to validate input data against existing data.

[1] Benefits of using stored procedures

Applications that use stored procedures have the following advantages:

Reduced network usage between clients and servers

A client application passes control to a stored procedure on the database server. The stored procedure performs intermediate processing on the database server, without transmitting unnecessary data across the network. Only the records that are actually required by the client application are transmitted. Using a stored procedure can result in reduced network usage and better overall performance.

Applications that execute SQL statements one at a time typically cross the network twice for each SQL statement. A stored procedure can group SQL statements together, making it necessary to only cross the network twice for each group of SQL statements. The more SQL statements that you group together in a stored procedure, the more you reduce network usage and the time that database locks are held. Reducing network usage and the length of database locks improves overall network performance and reduces lock contention problems.

Applications that process large amounts of SQL-generated data, but present only a subset of the data to the user, can generate excessive network usage because all of the data is returned to the client before final processing. A stored procedure can do the processing on the server, and transmit only the required data to the client, which reduces network usage.

Enhanced hardware and software capabilities

Applications that use stored procedures have access to increased memory and disk space on the server computer. These applications also have access to software that is installed only on the database server. You can distribute the executable business logic across machines that have sufficient memory and processors.

Improved security

By including database privileges with stored procedures, the database administrator (DBA) can improve security. The DBA or developer who builds the stored procedure must have the database privileges that the stored procedure requires. Users of the client applications that call the stored procedure do not need such privileges. This can reduce the number of users who require privileges.

Reduced development cost and increased reliability

In a database application environment, many tasks are repeated. Repeated tasks might include returning a fixed set of data, or performing the same set of multiple requests to a database. By reusing one common procedure, a stored procedure can provide a highly efficient way to address these recurrent situations.

Centralized security, administration, and maintenance for common routines

By managing shared logic in one place at the server, you can simplify security, administration, and maintenance. Client applications can call stored procedures that run queries with little or no additional processing.

 

What is the difference between the classical SPs and the Eloquera SPs? Here is a quick summary:

·         Eloquera SPs are written in C#.

·         Eloquera SPs support any number parameters, including array and object parameters.

·         Eloquera SPs can return objects from the database and also new objects including arrays.

·         The objects originated from the database and returned by the SP, still keep their binding to the database, so you can update or delete them, or use them as the object parameter for the query.

 

The Eloquera stored procedures can be thought as .NET extension methods added to the Eloquera database server. These SPs are written on C# (no new cryptic or crippled languages!), and you can use the full power of the .NET framework object library on the server side.

It looks like this.

First, you define an interface that must be present on both the server and the client, and which contains the methods you would like to call on the server.

Define the interface:

public interface ICityStatisticSP

{

    CityCars GetCityCars(string cityName);

}

 

Create class:

 

   public class CityStatisticSP: StoredProcedure, ICityStatisticSP

    {

        public CityCars GetCityCars(string cityName)

        {

            Parameters parms = db.CreateParameters();

            parms["city"] = cityName;

 

            List<Car> cars =

                db.ExecuteQuery("SELECT Car FROM Car JOIN Seller ON Car.SellerID = Seller.ID WHERE Seller.City = @city", parms)

                .OfType<Car>()

                .ToList();

 

              …

 

        }

    }

 

The client side call:

 

ICityStatisticSP procedures = db.GetStoredProcedureClass<ICityStatisticSP>();

string cityName = "Chicago";

CityCars cars = procedures.GetCityCars(cityName);

 

 

StoredProcedure provides public “db” property that you can use to work with database.

 

And here magic doesn’t end! Stored procedures allow you to return any custom types you ever dreamed. No mystic class and field attributes and kilometers of configuration files.

 

For this to work don’t forget to copy your libraries to server folder. Note that you don’t have to restart server to update your libraries. Just drop new version and it will work!

 

The dll library that contains public class CityStatisticSP shall be copied to the Lib folder of your Eloquera Database installation, or alternatively copy to any place and update “StoredProceduresPath” in Eloquera.config