Stored Procedure Support in Domain Model

[Kurt]

I was doing a few experiments with OpenAccess and the various Services that can be produced with the data service wizard. I stared with a simple database, and the Astoria data service wizard template. This all worked pretty well and I was able to use the service in Silver-light. Now, I wanted to add a stored procedure that returned a collection of entities, and I am not seeing how to do that :(.

The stored procedure is present in the Model Schema Explorer, but there does not appear to be any way to create a Function mapping using this designer, is there?

I assume its possible to create and expose method interfaces as well as the entity linq interfaces using the Astora services, eg say I wanted to expose a GetSomeEntitySet(int param1, int param2) as a method accessible from the Silverlight client using the Astora model. I did attempt a few things to make this work, but have not been successful so far. Anyway, this would be important, as I am trying to create some plumbing to expose a stored procedure call that returns entities to a Silverlight client.

Any ideas would be appreciated.

I am using OA Version: 2010.1.527.2

[PetarP]

Hello Kurt,

The stored procedures indeed do not have an option to be mapped or unmapped, however they all get generated as methods by default. If you look closely in your domain context file (EntityDiagrams1.cs by default) you will see that there is a method that execute each of your stored procedures there. You can use those methods and expose them to your clients. Unfortunately currently we don't have examples showing exactly that but we are actively working on improving our services integration documentation. If you have any further difficulties please do not hesitate to contact us.

Sincerely yours,
Petar
the Telerik team

[cheekl]

If all my operations with my database are via stored procedure only, is there any benefit for me to use OpenAccess?

[Kurt]

My Question was specifically about exposing methods in the "Astoria" DataServices Implementation, as its direct table access has some benefits, although not enough to give up the ability to expose custom methods that connect to stored procedures. However, if all actions on a database are controlled through stored procedures, then WCF is probably a better approach.

As far a exposing stored procedures in an OpenAccess data-model, that could be improved but I was able to see the code that was generated and ultimately use the OA DataContaxt class to call my stored procedures. When open access imports a stored procedure, it creates a public method in the OpenAccessContext class with the stored procedure name, its parameters that returns an object[]. What would be cool is to have a mapping dialog for the stored procedures that allowed the return type, access modifiers, and method name to be specified from within the designer of a Domain Model.

[PetarP]

Hi Kurt,

You should expose the method to the service via the WebGet attribute. You can place a method below the initialization of your service that will expose your stored procedure. The method should look something similar to this:

[WebGet]
        public List<Product> ProductsViaStoredprocedure()
        {
            NorthwindEntityDiagrams db = new NorthwindEntityDiagrams();
            return db.TenMostExpensiveProducts(null);
        }

Best wishes,
Petar
the Telerik team