locked
Multiple "Update" Stored Procedures in WCF Service RRS feed

  • Question

  • Having read the materials referenced in the answer to http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/ba520d22-5340-4115-ba3b-8c697f400054, I noticed Eric's solution REQUIRES the use of the "intrinsic ApplicationData database".  Not really wanting to add another database, is there any solution that will enable me to have a set of 4 update-style SP's available to a back-office LS App WITHOUT that database?

    I've also read http://blogs.msdn.com/b/eric_erhardt/archive/2012/04/19/updating-records-in-lightswitch-using-stored-procedures.aspx and noted that it appears that one is limited to ONE update-style SP.  Is this purely because the example is simple, or is the "U" in CRUD only implementable by a single update-style SP per table?

    I have a table that comprises 22 columns.  I don't really want to have to limit myself to a ridiculous "what changed?" UPDATE SP just because there's only one UPDATE I can point to...

    I sincerely hope I'm wrong about the surmised limitation, being a newb to the Entity Framework Model and WCF RIA Services.  I'm trying to do a proof of concept to automate our back-office scripts, and this is an easy starter, or so I thought...

    Even a "tough, you're out of luck" will help as I'll stop wasting time heading a brick wall and wait until LS properly supports Stored Procedures.

    Thanks in advance.

    Friday, July 20, 2012 11:08 PM

All replies

  • A RIA WCF domain service can only have one update method per entity, but can have multiple query methods as long as only one of those are marked as the default query method.

    Typically your entity would contain all 22 fields and when you update that from the UI to the backend you will just update all 22 fields, without caring which field had changed, unless there is specific business logic that needs to be triggered when specific fields are updated.

    One way to have more information available in the backend service as to what was changed and/or what triggered the update call, is to add additional transient properties onto the entity (that are not stored in the database, but trigger specific business processes). An example of that might be on a Customer entity where you want to update the customer details in the UI and as part of the save in the backend you also want to optionally send an email to that customer. Adding a boolean SendEmail property to the Customer entity allows the user to select a checkbox over that SendEmail property and the backend update method can then be coded to trigger the email if that SendEmail == True.

    If you create your own RIA domain service then you can of course update the entities in any way you want - using an SP, EF, ADO, whatever.

    Not sure whether this answers your question?

    Regards


    Xander

    • Edited by novascape Saturday, July 21, 2012 12:01 AM
    Friday, July 20, 2012 11:59 PM
  • Many thanks, Xander.  Your explanation makes sense.

    As I understand Eric's solution, which is extremely close to what I had in mind, despite having to create fake tables for every SPs parameters, I will try to implement it and bite the bullet on the extra database.

    This is the first of many wide tables that are silly to treat as a whole when there are specific business processes that affect one or a few of the attributes.  We're already encumbered by some "work out the differences and derive what business process(es) the User is performing" in the form of other overly complex Stored Procedures that many ostensibly support the business but don't adequately reveal the business processes and rules at the data level.  Our tables aren't the kind where simplistic business rules encapsulated in Lightswitch will be a good fit, hence the SP route.  We want to leverage Lightswitch by using it to expose our SPs, not by duplicating logic from within those SPs into the LS "middle tier/business logic layer".

    If I happen to use wrong terminology, please accept my apologies - it's been quite a few months since my first foray into LS-1 and with so many changes for LS-2 there's a deal of re-learning.

    Thanks again.

    Stephen

    http://blogs.msdn.com/b/eric_erhardt/archive/2012/04/17/executing-a-simple-stored-procedure-in-lightswitch.aspx

    Saturday, July 21, 2012 12:42 AM
  • I'm a bit flat out with a big delivery over the next few days, so don't have too much time at present unfortunately, but will respond when I can.

    You typically only need one domain service. My advice will be to expose POCO entities out of that domain service, rather than exposing native EF entities, for example. That way you can have as many entities as you wish and if it makes business sense, you can even split that single 22 field table into multiple smaller entities. You can even combine multiple database tables into a single entity if it makes sense. In other words, POCO entities allow you to shape your data so it matches your LS UI - that makes it far more simple.

    I use a convention of lean Digest entities for populating grids that contain only the columns you see in the grids plus the PK (they are typically read-only) and Model objects for doing the CUD stuff. So with Customer for example, I will have a CustomerDigest POCO and another CustomerModel POCO. CustomerDigest is used where I populate a grid of Customers and search/filter on Customers and CustomerModel is used on the editing screen where I create and update Customers and where you need all the fields.

    As a general pattern I've also started to move away from declaring Associations between POCO entities inside the domain service as LS appears to always eagerly load those. I still need to do more testing to confirm the exact behavior here, but I've found that hooking up master-detail queries on the LS screens seem to work better. Do you own testing to confirm, but take this as a heads up.

    Regards


    Xander

    Saturday, July 21, 2012 12:43 AM
  • You can also read this thread that covers RIA where I show an example towards the bottom on how to create query method with EF and POCO How to Refresh a Single Row from the Database to Handle Triggers

    Regards


    Xander

    Saturday, July 21, 2012 12:46 AM