none
LightSwitch and Stored Procedure Calls RRS feed

  • Question

  • Hi, LightSwitch fans,  

    In Eric's post,

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

    he showed how to make a stored procedure call from LightSwitch.

    As Eric explained, if we are writing code in a button on the screen, the issue is that if we hit the button, the code will execute on the client tier.  But there is no way to access the database from the client.  Instead we need to make a call from the client to get onto the middle tier.  A simple way to do this is to take advantage of the save pipeline built into LightSwitch. 

    Whenever a record is inserted, updated or deleted, LightSwitch will invoke methods (Inserting, Updating, Deleting, etc.) on the middle tier.  This allows you to write business logic that is ensured to run, no matter what client is making the request.  We will use this functionality to invoke the stored procedure. 

    I tried and it works fine. But I have a little bit more complicated business case:

    Here it is: I have a simple table called MyWorkSteps

    Columns:

    WS_ID

    Step1_Status

    Step2_Status

    Step3_Status

    I first create a new data and want to call 3 stored procedures with 3 buttons

    Button “Start Step1” calls “stored_procedure_1”

    Button “Start Step2” calls “stored_procedure_2”

    Button “Start Step3” calls “stored_procedure_3”

    The stored_procedure_1 does some work and change the Step1_Status = Done

    The stored_procedure_2 does some work and change the Step2_Status = Done

    The stored_procedure_3 does some work and change the Step3_Status = Done

    My questions:

    Q1: The middle tier cannot distinguish the 3 different buttons from the same screen. Is there any way that I can inform the middle tier that Button 1 or Button 2 is pressed? Or it is just impossible?

    Belong is the code to catch the Insert:

    Private Sub MyWorkSteps_Inserting(entity As MyWorkSteps)

                Using connection = New SqlConnection

                            Dim connectionStringName = Me.DataWorkspace.RptcockpitData.Details.Name

                            connection.ConnectionString =

                                        ConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString

                  

                            Dim procedure = "dbo.stored_procedure_1"

                            Using command = New SqlCommand(procedure, connection)

                                        command.CommandType = CommandType.StoredProcedure

                                        command.Parameters.Add(

                                                    New SqlParameter("@WS_ID", entity. WS_ID))

                                        ' set up the timeout for stored procedure in second

                                        command.CommandTimeout = 3000

                                        connection.Open()

                                        command.ExecuteNonQuery()

                            End Using

                End Using

                Me.Details.DiscardChanges()

    End Sub

    Q2: The stored procedure needs the WS_ID to update the status,  but how can I pass the WS_ID to stored procedure?

    Because WS_ID is the primary ID and will be generated.

    The line of code got the error message that WS_ID is Read-Only.

      operation.WS_ID = CurrentWorkbench.WS_ID

    Namespace LightSwitchApplication

        Public Class MyWorkStepsListDetail

            Private Sub Step1_Execute()

                ' Write your code here.

                Dim dataWorkspace = New DataWorkspace

                Dim CurrentWorkbench = Me. MyWorkSteps.SelectedItem

                Dim operation = dataWorkspace.Data. MyWorkSteps.AddNew()

              operation.WS_ID = CurrentWorkbench.WS_ID --> this line gets the complier error

                dataWorkspace.ApplicationData.SaveChanges()

            End Sub

        End Class

    End Namespace

    Thanks a lot for your help.

    Andy

    Friday, February 6, 2015 3:51 PM

Answers

  • Hi, Josh,

    just a short update. Now I found a solution, instead of updating the Status at the middle tier, just after the stored procedure is called. I use the screen (client) method _Saved

    Me.MyWorkstep.SelectedItem.Status = "Done"

    This will update the status correctly without any deadlock.

    thanks again for your help.

    Andy

    • Marked as answer by Andy_LS Tuesday, February 10, 2015 1:49 PM
    Tuesday, February 10, 2015 1:49 PM

All replies

  • Hi Andy,

    1)  You're right the middle tier doesn't know which button is pressed.  You would use columns in your MyWorkSteps table to send that info to the server.  Perhaps add an 'Action' colums and set it to Button1,2,3 respectively.

    2)  If you're sproc needs the id  of the entity being inserted then that's not available in the Inserting method.  You should use the 'MyWorkSteps_Inserted' method instead.

    HTH,

    Josh

    Sunday, February 8, 2015 3:34 PM
  • Hi, Josh,

    thanks a lot for your tips.

    1) I came to the same idea that I use additional column say Action and use

    entity.Action to decide which stored procedure that I should call.

    2) perfect, I can get the id now in the method 'MyWorkSteps_Inserted', but my problem remains unsolved, because I want to use id to update the status = Done in my stored procedure when the stored procedure is finished, like

    UPDATE Table_MyWorkSteps

    set Status = "Done"

    where id = @Current_id

    But  'MyWorkSteps_Inserted' also want to change the current entity, so there is a deadlock.

    Lightswitch application is blocked.

    Any idea on how to solve my problem?  

    Tuesday, February 10, 2015 10:14 AM
  • Hi Andy,

    I cannot recall for sure, but does the deadlock go away if you update in only one place? - either Inserted method or stored proc - not both. 

    In other words, if you refrain from changing entity in the Updated Method, does SET Status = "Done" work in sproc? 

    or if you refrain from doing that in sproc does entity.Status = "Done" work in Inserted method?

    HTH,

    Josh

    Tuesday, February 10, 2015 12:09 PM
  • I tried both, but both did not work for me.

    1. update in stored procedure only: the problem here is that the Save Pipeline save the record with the same Id as stored procedure as well.

    2. in _Inserted method only and not in stored procedure: 

    After the Stored procedure call, there is a Me.Details.DiscardChanges(). Now I want to change the entity status: entity.Status = "Done", but a timeout error comes.  

    Tuesday, February 10, 2015 12:47 PM
  • I stopped using this pattern and switched to WebApi a while back, it is much cleaner.

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Tuesday, February 10, 2015 1:04 PM
  • Dave,

    what are the main reasons that makes you decided not to use save pipeline but web API?  

    I checked one link from Beth Massi, but it seems to be not intuitive to me.

    http://blogs.msdn.com/b/bethmassi/archive/2013/07/31/calling-stored-procs-in-your-lightswitch-databases-using-web-api.aspx

    Do you have any other links as a good introduction to WebAPI?

    Andy

    BTW: I have tight deadline. For now, I do not want to try new way if the pattern "save pipeline" works.  

    Tuesday, February 10, 2015 1:17 PM
  • Well, once you get introduced to WebApi, you can use its full power to call the server and offload lots of processing. It is more 'future proof'.

    Like Beth stated:

    There are other ways to call stored procedures in LightSwitch, but using Web API with LightSwitch gives you the flexibility of creating custom web methods that can take advantage of all the data and business logic in your LightSwitch middle-tier via the ServerApplicationContext. If you have LightSwitch version 3 or higher (VS2012 Update 2+ or VS2013) then you are ready to unlock the possibilities.

    For more possibilities, like creating reporting dashboards, see: Create Dashboard Reports with LightSwitch, WebAPI and ServerApplicationContext


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Tuesday, February 10, 2015 1:27 PM
  • Hi, Dave,

    thanks for the insight. I will definitively follow that "future proof" solution after I finished the current release.

    Andy

    Tuesday, February 10, 2015 1:43 PM
  • Hi, Josh,

    just a short update. Now I found a solution, instead of updating the Status at the middle tier, just after the stored procedure is called. I use the screen (client) method _Saved

    Me.MyWorkstep.SelectedItem.Status = "Done"

    This will update the status correctly without any deadlock.

    thanks again for your help.

    Andy

    • Marked as answer by Andy_LS Tuesday, February 10, 2015 1:49 PM
    Tuesday, February 10, 2015 1:49 PM