Wednesday, July 18, 2007
Milos and O/R Mapping: Stored Procedure calls

It's been a while, but let's continue with the series on Milos and O/R Mapping. This time around, discussing Stored Procedure calls.

On the last installment of this series we've seen how to execute queries by running individual SQL statements (dynamic SQL). Executing Stored Procedures is not that much different: the main difference is that instead of using the ExecuteQuery method, we use the ExecuteStoredProcedureQuery method. For example, the following method in a business object would execute a stored procedure named GtPaymentsList:

public DataSet GetPaymentsList()
{
   IDbCommand command = this.NewDbCommand("GetPaymentsList");
   command.CommandType = CommandType.StoredProcedure;
   return this.ExecuteStoredProcedureQuery(command);
}

In case the stored procedure only processes some data and does not return anything to the caller, a method such as the one below will do the job of calling the stored procedure:

public void ProcessPayments()
{
   IDbCommand command = this.NewDbCommand("ProcessPayments");
   command.CommandType = CommandType.StoredProcedure;
   this.ExecuteNonQuery(command);
}

Another aspect of using stored procedures is that we may want all data access in our business objects to go through stored procedures. By default, when we use business objects and entities, Milos uses "individual commands" (dynamic SQL), which means it creates the SELECT, UPDATE, INSERT, and DELETE commands on the fly, depending on what the business object needs. Alternatively, the object can be set so that instead of creating SQL on the fly, Milos can look for Stored Procedures that follow a naming convention (we have code generators that create the necessary stored procedures). In order for that to work, all the developer needs to work is to code-generate the stored procedures, and configure the business object class, by adding the following line to the Configure method:

this.SetDataAccessMethod(EPS.Data.DataRowProcessMethod.StoredProcedures);

Milos can also be set so that the only data access method permitted is through stored procedures (which means that anything going through the data access layer has to be a Stored Procedure call). This can be set by adding the following configuration setting:

<add key="AllowedDataMethod" value="StoredProcedures"/>

That is it for now.  :)