Home » Blog

Stored procedures and LightSpeed

Tagged as LightSpeed

We’ve had a lot of requests for stored procedure support in LightSpeed, and now that 2.1 is out the door we’ve started to work on this functionality. So we’re pleased to announce that the latest nightly builds now contain a first cut of support for stored procedures.

The main use of procedures in this drop is to load entities. This is accomplished by another overload of IUnitOfWork.Find. This overload takes a ProcedureQuery object, which specifies the name of the procedure and a set of parameters to pass to the query. (We plan to offer more convenient versions of this API once we have gathered some initial user feedback.) It returns a list of entities, just like the existing Find methods. For example:

// Simple stored procedure
IList<ContributionSummary> summaries = 
  _unitOfWork.Find<ContributionSummary>(new ProcedureQuery("SummariseContributions"));
 
// With parameters
ProcedureQuery query = new ProcedureQuery(
  "GetContributions", 
  new ProcedureParameter("maxid", 10));
IList<Contribution> contributions = _unitOfWork.Find<Contribution>(query);

The stored procedure is expected to perform a SELECT of the required entity data. On SQL Server, this is done in the familiar way:

-- Somewhat artificial example - this could have been done as a view
CREATE PROCEDURE SummariseContributions
AS
  SELECT
    Contributions.Id ContributionId,
    Contributions.Title ContributionName,
    Contributions.Description,
    Contributions.CoordinatesGeoX,
    Contributions.CoordinatesGeoY,
    Members.UserName ContributorName
  FROM
    Contributions
    INNER JOIN Members ON Contributions.ContributorId = Members.Id
  ORDER BY
    Contributions.Id

The procedure may of course return records directly from a table (e.g. SELECT * FROM Contributions), but as the example above demonstrates, it can also define its own schema provided that you also define an entity class to match that schema. (That entity class is a normal LightSpeed entity; you don’t need to do anything special in the entity class to support stored procedures.)

Like anything that wants to become a LightSpeed entity, the schema for the returned recordset must have an identity column, and the values in this column must be unique. If the identity column is not named “Id”, then the entity class must have a TableAttribute specifying the IdColumnName. Other attributes also apply as normal, just as if the entity were loading from a table:

[Table(IdColumnName="ContributionId")]
public class ContributionSummary : Entity<int>
{
  [Column("ContributionName")]
  private string _name;
 
  private string _description;
 
  [Column("ContributorName")]
  private string _contributedBy;
 
  [ValueObject]
  private Coordinates _coordinates;
}

One attribute that doesn’t work with stored procedures is EagerLoad. If an entity with associations is loaded through a ProcedureQuery, its associations are always lazily loaded (and no warning is given). This is not usually an issue because a stored procedure is usually designed to return a complete and self-contained set of data.

We also support stored procedures on Oracle, as long as you are using the Oracle9 provider (not the Oracle9Odp provider). However, because Oracle procedures don’t return recordsets directly in the way that SQL Server does, we require the Oracle procedure to follow a defined LightSpeed convention: namely, that the recordset be returned through an out parameter of ref cursor type, named “results”. (We welcome feedback on this convention.) In Oracle, the SummariseContributions procedure therefore looks like this:

CREATE PROCEDURE SummariseContributions
(
  results  OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN results FOR
  SELECT
    Contributions.Id ContributionId,
    Contributions.Title ContributionName,
    Contributions.Description,
    Contributions.CoordinatesGeoX,
    Contributions.CoordinatesGeoY,
    Members.UserName ContributorName
  FROM
    Contributions
    INNER JOIN Members ON Contributions.ContributorId = Members.Id
  ORDER BY
    Contributions.Id;
END;

The “results” convention is required only at the database end of things. At the LightSpeed end, you still just call _unitOfWork.Find < ContributionSummary > (“SummariseContributions”) as if the “results” parameter didn’t exist.

Stored procedures can return additional data via out parameters. In this case you must specify the data type of the parameter and, for strings, the size. You must also specify whether the parameter is an out parameter, an in-out parameter or the return value.

CREATE PROCEDURE GetMostRecentComment
(
  @postedBy NVARCHAR(50) OUT,
  @subject NVARCHAR(128) OUT,
  @postedOn DATETIME OUT
)
AS
BEGIN
  SELECT TOP(1)
    @postedOn = Comments.PostedOn, 
    @subject = Comments.Subject, 
    @postedBy = Members.UserName
  FROM
    Comments
    INNER JOIN Members ON Comments.MemberId = Members.Id
  ORDER BY 
    Comments.PostedOn DESC, Members.UserName;
  SELECT TOP(1) * FROM Comments;
END

ProcedureParameter postedOn = new ProcedureParameter("postedOn", null, 
  ParameterDirection.Output, DbType.DateTime, null);
ProcedureParameter postedBy = new ProcedureParameter("postedBy", null, 
  ParameterDirection.Output, DbType.String, 50);
ProcedureParameter subject = new ProcedureParameter("subject", null, 
  ParameterDirection.Output, DbType.String, 128);
 
ProcedureQuery query = new ProcedureQuery("GetMostRecentComment", 
  postedBy, subject, postedOn);
_unitOfWork.Find<Comment>(query);
 
// out-parameters now available via postedOn.Value, postedBy.Value
// and subject.Value

We welcome feedback on how widely you use stored procedure out-parameters so that we can determine whether to put effort into streamlining this API.

We do not yet support stored procedures that do not return recordsets, except for ExecuteScalar-type (e.g. SELECT COUNT) procedures on SQL Server (accessed via IUnitOfWork.Calculate(ProcedureQuery)). We will soon be adding support for ExecuteNonQuery-type procedures on both SQL Server and Oracle, and looking at support for MySQL. Again, your feedback will help us prioritise this work.

Finally, what I’ve described here is the low-level raw API. We plan to add designer support for stored procedures, so that we can generate friendly wrapper functions — for example:

public class SampleUnitOfWork : UnitOfWork
{
  public IList<ContributionSummary> SummariseContributions() { ... }
  public IList<Contribution> GetContributions(int maxid) { ... }
  public IList<Comment> GetMostRecentComment(out DateTime postedOn, out string postedBy, out string subject) { ... }
}

We’re at an early stage with this work and the earlier you give us your feedback the easier it will be for us to adapt our designs and priorities. So download the latest nightly — Express Edition from the download site or retail editions from the store — and take it for a spin today!

Add your comments
Posted by Ivan Towlson on 20 October 2008

3 Responses to “Stored procedures and LightSpeed”

1. Awesome. Just what I was looking for!

   James Newton-King Oct 21st, 2008

2. [...] The Mindscape guys explain Stored procedures and LightSpeed [...]

   2008 October 22 - Links for today « My (almost) Daily Links Oct 22nd, 2008

3. I read the complete article and come to know that this is based in stored procedure used in database to execute pre compile code. really helpful for programmer. Thanks for giving this information.

   Software Development Company Dec 23rd, 2008

Leave a Reply