locked
How to avoid multiple join view - puling data from existing DB RRS feed

  • Question

  • Hi,

    I'm integrating Lightswitch into existing database developing B2B that need to work with current ERP software. Database schema that I have to use is little bit strange and now I'm struggling how to implement one pricing in LS.

    There are standard sets of tables for ordering: Items, Buyers, Order Header, Order Details. But, there is also some tables that are used for calculate price. Price can depend on buyer, on warehouse (from Order Header table), on current date, on type of subject, on type of item etc. There is around 10 different table where the right price can be. There is a hierarchy how the price need to be calculated.

     I don't how to approach to this problem. Whether over SQL view, load all tables in Lightswitch and compute in there or I can somehow use stored procedure which calculate price for each item with passed parameters. If I write one big SQL view to include all tables with multiple joins I'm not sure about speed, with thousands of items and buyers and each and every have own condition, same can be with loading all tables in LS. I already have stored procedure that calculate price for each item under all conditions, and I'm thinking how can I call SP for each and every item when I choose buyer, date and warehouse on my order. 

    I hope that I'm clear where I see the problem. Any suggestions are welcome.

    Thanks,

    Ljuba

    Tuesday, March 18, 2014 9:29 AM

Answers

  • Hi Ljuba

    Welcome to LightSwitch forum.

    According to your description above, you could consider using Stored Procedure on your side. Generally, if it loads too many screens in your LS app, perhaps the speed will be low.

    Moreover, LightSwitch applications can execute stored procedures in a SQL Server database by calling them from the server tier.

    To execute a stored procedure 

    1. Create a table in the intrinsic database that contains the input parameters for the stored procedure.
    2. Add a reference to the ConfigurationManager class.
    3. In the Write Code list, choose the Inserting method (in this case, the UpdateEmployeePersonalInfoOperations_Inserting method).

    hope it helps.

    Regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Angie Xu Friday, March 28, 2014 1:34 AM
    Tuesday, March 25, 2014 8:25 AM