Some examples

Hey, do you fellows have an example project / db schema of the use of Access Method:  StoredProcedures and Access Method:  SelectProcedure?  I get the basic idea of them, but I'd like to see a simple implementation to ensure I'm not barking up the wrong tree.

Cheers

After re-reading my last post, I realize it was begun with an absolutely horrible first sentence.  Do you fellows have an example project and/or database which is an example of the StoredProcedures Access method on a entity and the SelectProcedure Access Method on an entity.  Sorry about that.

We don't have a properly packaged example, but in fact I think what you want to understand is not very easy to encapsulate in an example anyway.

The StoredProcedures access method means that CRUD operations for entities of this type will be performed using five stored procedures, whose names you specify on the entity.  It means that you still want to use Find, Add, Remove and SaveChanges to work with the entity, but that these should work in a different way, using sprocs instead of table accesses.  It is intended primarily for scenarios where you are required to use CRUD procedures even though they do not contain useful logic (typically due to enterprisey corporate standards).

The SelectProcedure access method means the entity definition was inferred when you dragged a stored procedure onto the designer.  It is intended for use with stored procedures that return 'entities' that don't back onto tables -- e.g. the sproc performs projections and/or calculations.  It means that you will not (usually) use Find, Add, Remove and SaveChanges to work with the entity, but instead will use the relevant stored procedure(s).

Note that you can still use stored procedures with "normal" (access method = Table) entities.  The main purpose of specifying the SelectProcedure access method is to tell the designer that this entity does not back onto a table and therefore it should not skip it when updating the database schema (because the designer can't update the stored procedure to reflect a change in the result schema!).

Does that help?

Ivan,

Thanks for the fast reply.  Your answer helps clear up some of my thinking, but now what I'm after is how to construct my stored procedures.  For example, do you have some details on what my stored procedures need to look like / return / take in as input / output parameters?  I'm a reasonably seasoned developer, so I can figure out a lot with very little.  I just need to see some documentation or even just stored proc definitions for Sql Server that would get me running in the right direction.

There's some basic information in the docs, and I've attached to this post some example of CRUD procedures (StoredProcedures access method) from our test database.  Let me know if you need examples around non-CRUD (SelectProcedure) procedures.

Thanks for that.  Yeah, any examples of non-crud you've got would be awesome.

Here's an example of something you might use as a SelectProcedure:

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

Here the return schema doesn't map to an underlying table, so you couldn't map the results into a Contribution or Member collection.  Instead, you'd define a ContributionSummary entity (with the SelectProcedure access method) which might look something like this:

  [Table(IdColumnName="ContributionId")]
  public class ContributionSummary : Entity<int>
  {
    [Column("ContributionName")]
    private string _name;

    public string Name
    {
      get { return _name; }
      set { Set(ref _name, value, "Name"); }
    }

    private string _description;

    public string Description
    {
      get { return _description; }
      set { Set(ref _description, value, "Description"); }
    }

    [Column("ContributorName")]
    private string _contributedBy;

    public string ContributedBy
    {
      get { return _contributedBy; }
      set { Set(ref _contributedBy, value, "ContributedBy"); }
    }


    [ValueObject]
    private Coordinates _coordinates;

    public Coordinates Coordinates
    {
      get { return _coordinates; }
      set { Set(ref _coordinates, value, "Coordinates"); }
    }
  }

An important thing to note is that this sproc is still returning (rows that will be materialised into) entities, so it must return an ID column (in this case named ContributionId).

By the way, these examples are for SQL Server.  Most other databases work the same, but because of the way Oracle returns results, there are special conventions that apply there.  See Help Topics > Databases > Oracle in the help file for more info.

Ivan,

Thanks for that, brother.

That really helps.  I shouldn't have any problems now.

Watch this space for my panicked cries for help ;)