Business Apps Example for Silverlight 3 RTM and .NET RIA Services July Update: Part 24: Stored Procedures


Still updating my Mix 09 Silverlight 3 + RIA Services talk with more fun stuff.    While many of the RIA Services examples we have shown thus far do CRUD directly against database tables, I certainly recognize that many scenarios require using stored procedures in the database to encapsulate all data access. 

You can see the full series here.

The demo requires (all 100% free and always free):

  1. VS2008 SP1
  2. Silverlight 3 RTM
  3. .NET RIA Services July ’09 Preview

Check out the live site Also, download the full demo files

In this example, I am going to encapsulate all our data access in stored procedures. I will continue to use Entity Framework to access these stored procs, but you could of course use LinqToSql or ADO.NET directly.

 

Add Stored Procedures

The first step is to create a set of stored procedures in the database.  

In Server Explorer open up the Database, select the Stored Procedures node and “Create New Stored Procedure”

image

These are very, very basic.  My goal here is to show you how to get started, you can then add additional logic as needed while following the same basic overall pattern. 

Let’s start by adding some query stored procedures:

ALTER PROCEDURE dbo.CoolSuperEmployees
AS
SELECT  SuperEmployees.Name, SuperEmployees.EmployeeID,SuperEmployees.Gender,SuperEmployees.Origin,SuperEmployees.Issues,SuperEmployees.Publishers,SuperEmployees.LastEdit,SuperEmployees.Sites
FROM SuperEmployees 
WHERE SuperEmployees.Issues Between 10 And 99999
 
RETURN

Notice this stored proc does not directly support paging.  You could take a paging information if that is required.

and to get a particular employee

 
ALTER PROCEDURE dbo.GetSuperEmployee
    (
    @EmployeeID int 
    )
AS
    SELECT  SuperEmployees.Name, SuperEmployees.EmployeeID,SuperEmployees.Gender,SuperEmployees.Origin,SuperEmployees.Issues,SuperEmployees.Publishers,SuperEmployees.LastEdit,SuperEmployees.Sites
FROM SuperEmployees 
WHERE SuperEmployees.EmployeeID = @EmployeeID
 
RETURN

Update:

 

ALTER PROCEDURE dbo.UpdateSuperEmployee
    (
    @EmployeeID int,
    @Name nvarchar(MAX),
    @Gender nvarchar(50),
    @Origin nvarchar(10),
    @Issues int,
    @Publishers nvarchar(10),
    @LastEdit datetime,
    @Sites nvarchar(MAX)
    )
 
AS
Update SuperEmployees
Set
  Name = @Name,
  Gender = @Gender,
  Origin = @Origin,
  Issues = @Issues,
  Publishers = @Publishers,
  LastEdit = @LastEdit,
  Sites = @Sites
Where 
 EmployeeID = @EmployeeID 

Insert:

 

ALTER PROCEDURE dbo.InsertSuperEmployee
    @Name nvarchar(MAX),
    @Gender nvarchar(50),
    @Origin nvarchar(10),
    @Issues int = 0,
    @Publishers nvarchar(10),
    @LastEdit datetime = null,
    @Sites nvarchar(MAX)
AS
Insert into SuperEmployees
(
 Name,
 Gender,
 Origin,
 Issues,
 Publishers,
 LastEdit,
 Sites
)
Values
(
  @Name,
  @Gender,
  @Origin,
  @Issues,
  @Publishers,
  @LastEdit,
  @Sites
 )
Select SCOPE_IDENTITY() as Id

And finally delete:

 

ALTER PROCEDURE dbo.DeleteSuperEmployee
    (
    @EmployeeID int 
    )
 
AS
Delete From
   SuperEmployees
   
Where 
   EmployeeID = @EmployeeID

 

Update the Entity Framework Model

Now let’s create an Entity Framework model that knows how to access the data via these Stored Procs.   Let’s start by creating a new Entity Framework Model

image

Then we select the SuperEmployees table and all of the storedprocs we created above

image

Next, I like to set the properties on the SuperEmployee entity to make the naming more clear in the .NET world.

image

 

Next, we wire up the CUD (create, update, delete) operations for this table to go through the storedprocs we just wrote. 

First we setup the Insert function to map to the “InsertSuperEmployees” storedproc

image

 

Visual Studio automatically sets up the mapping, if you need to tweak this based on your stored procs, you certainly can. 

image

Repeat this for Update and Delete, such that they are all mapped.

 

image

 

Now we need to do the same thing for the query methods.   Open the Model Browser and find the stored proc for “CoolSuperEmployee”. Right click and select “Create Function Import”.

image

 

Then we set up a mapping to return SuperEmployees

image 

And repeat for the GetSuperEmployee (int employeeID)…

 

Now we have our model all setup, let’s go back to our DomainService and update it to use these settings.

 

 

Update the Domain Service

The DomainService allows you to create custom business logic and easily expose this data to the Silverlight client.    The good news is this looks almost exactly like the pervious examples. 

First, let’s look at the query methods. 

   1: [EnableClientAccess()]
   2: public class SuperEmployeeDomainService : 
   3:     LinqToEntitiesDomainService<NORTHWNDEntities>
   4: {       
   5:     public IList<SuperEmployee> GetSuperEmployees()
   6:     {
   7:         var q = Context.CoolSuperEmployees();
   8:         return q.ToList();
   9:     }
  10:  
  11:     public SuperEmployee GetSuperEmployee(int employeeID)
  12:     {
  13:         return Context.GetSuperEmployee(employeeID).FirstOrDefault();
  14:         
  15:     }

Notice in line 5, we return an IList rather than an IQueryable.. this means that query composition from the client composes at the stored proc level, rather than all the way into the database.  This is goodness because we funnel all requests through that stored proc, but it has the costs of maybe returning more data to the mid-tier than the client really needs.  You can of course add paging to the stored proc or you can do direct table access for read only scenarios, but still use stored procs for CUD.

Then we have Insert and Update..

   1: public void InsertSuperEmployee(SuperEmployee superEmployee)
   2: {
   3:    Context.AddToSuperEmployees(superEmployee);
   4: }
   5:  
   6: public void UpdateSuperEmployee(SuperEmployee currentSuperEmployee)
   7: {
   8:     this.Context.AttachAsModified(currentSuperEmployee, this.ChangeSet.GetOriginal(currentSuperEmployee));
   9: }

Notice they look just like our previous example, but now these methods eventually call into our stored procs rather than direct table access.

 

We run it and it works great!  Exactly like pervious examples, but this time all data access is via stored procs. 

 

 image

 

For more information on working with stored procedures with Entity Framework or LinqToSql see:

http://blogs.msdn.com/bags/archive/2009/03/12/entity-framework-modeling-action-stored-procedures.aspx

http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/17/ado-net-entity-framework-tools-stored-procedures.aspx

http://msdn.microsoft.com/en-us/library/bb384469.aspx

Comments (7)

  1. Hi,

    Very nice article. Can all these be done in code. Why are you automating these things ?

    Thanks,

    Thani

  2. Sean says:

    Thanks Brad.  Great post.  Question question:  I’m still trying to come up to speed on incorporating stored procs like you have demonstrated.  Why do I need to pull the "table" into the model if the CRUD operations are being done by the stored procs.  Is that so the appropriate meta data is created?  It would seem like I would need to pull ALL the tables in the from the DB.  Am I on the right path?  

    Thanks,

    Sean

  3. slyi says:

    You seen to skim over the issue, that you need to explictly create sprocs a against superemployee object that already exists as a table.

    We need to see samples for read only operations for join tables, could you please provide this?

  4. Andrej says:

    Hello Brad,

    many thanks for your superb blog, wow ;o)! Such good reading, better than TV or anything..

    Now, one problem, among the others :).. All the nice articles deal with pretty ideal (simple) data and filter scenarios. My situation is: I’ve a lot of related db tables on the server, which I’ve managed to be sent to the client with .Include() and [Include].

    Now my main entity has got a 1-many relation, which I would like to filter on. Concrete: the SessionDetails record (the main business entity) posses a Media collection.

    Showing it in DataGrid should be possible with a custom converter (e.g. 1 SessionDetails row will contain a column with all the media names concatenated as a string).

    Now the filter in the GUI will show things like "User name" (who created the session), from-to date (when the session was created). This is easy with the FilterDescriptors collection, applying a logical "AND" to all the required (simple) props. But now, there should be also a group of checkboxes, one for every possible media involved in (e.g. related to) the SessionDetails record. I mean a group of "OR" criteria, which as such is another "AND" criterion in the final FilterDescriptors.

    I’ve tried to append all the filter "where" expressions in DomainDataSource_LodingData, modifying the original LoadingDataEventArgs.Query, which works fine – but looses the sort order :-(. I’m not sure, how the stuff works low-level, I even didn’t find any [IsComposable] docs etc.

    So my question is: what about complicated data sources (e.g. 1-many relations, lot of tables) and tricky filtering (e.g. groups of "AND" and groups of "OR")?

    I would love to see some "how to" / "best practice" in situations, where you have such data sources (should one take DTO, but what about related entities and server-side filtering then?), how to apply custom filtering, what about complicated custom expression columns (e.g. "if the session response code was 200 AND the media used was voiceOverIp, THEN set the column "duration" to "endTime – answerTime") – how to filter and sort on such custom columns would be really a nirvana article for me ;-).

    Thank you so much for your blog,

    Andrej

  5. Andrej says:

    Hi Brad,

    I’ve analyzed even more the DomainDataSource problem with custom querying – it should be possible:

    1. to have some custom code hookups in generating the query, as doing this in LoadingData appends more ".Where" to the expression tree _after_ the .OrderBy methods, which renders in lost sorting (and paging may be as well). I’ve decompiled the DomainDataSource.LoadData(), calling .GetFilterDescriptorsQuery(), which calls static (!) LinqHelper.BuildFilterExpression(). So by this design, it’s impossible to change anything, IMHO.

    – or –

    2. to have the possibility to provide an own IQueryable<TEntity>, instead of doing this by reflection by "QueryName", as one can’t append own modifiers to the .Where

    – or –

    3. appending the .Where wherever one likes (e.g. into the _LoadingData() event) and some component should be enough smart to reorder it: e.g. when SortXXX is found before any .Where, it should be put onto the end. As is, the EF SQL provider (?) ignores it simply – I’ve sniffed the executed query and point is, if there is an .OrderBy before any .Where, it gets lost.

    Thank you,

    Andrej

  6. FredrickMw says:

    I have been using this method even before this post and I seem to prefer this way as compared to the normal way…

    I encountered several problems…. but I was able to get around it.. by manually modifying the xml file… then I will have to save a copy of the xml because when ever I "update from the database" everything I have typed gets erased…

    Anyways… I still prefer this way…

  7. Patrick Hennig says:

    Hi,

    thank you for this tutorial.

    It works fine, but now I have a Entity with Including some attributes as a foreign key.

    And now if I want to filter hte process crashes if i type in a filter.

    Did anyone know, what I have to do now?

    Thanks