locked
Expert Review Wanted: LightSwitch Power SQL: Dynamic SQL using SQL Server. RRS feed

  • Question

  • Maybe someone has offered this solution before, but here is a very easy way to run DYNAMIC SQL withing lightswitch using SQL Server 2012

    Anyway, I have not seen anything like this in my search of the forums as a solution to run dynamic SQL.

    As a note, I have a more complex version of this to return result data to a pre-defined strucutre, etc, allowing the SQL server to run a long process in another thread, etc.  But I am wanting to see if any of you have done this before and/or get your review.

    If this is OLD hat, I appologize for taking your time.  However, my feeling is that this is a very simple way to do powerful things with SQL and LightSwitch that may be being overlooked.

    So here we GO:

    Overview:

    1. Create a table with a VarChar(max) field for SQL to be run
    2. Create a trigger that uses the given SQL and runs it using the EXEC() command.
    3. Create a lightswitch app that inserts the desired SQL into given table and saves the record.
    4. That is it.  Simple as pie.

    Here we go with how to create a sample to demonstrate running Dynamic SQL from LightSwitch !

    Create an SQL POWER Run database

    create database SampleForSQLPOWER;
    GO
    USE SampleForSQLPOWER;
    GO


    Create an SQL POWER run table and its status:

    Create Table LigthSwitchPowerSQL(
    Create Table LightSwitchPowerSQL(
    LightSwitchPowerSQLID Int Identity(1,1) Primary Key,
    LightSwitchPowerSQLSQLToRun VarChar(max),
    LightSwitchPowerSQLRunStatusFK int,
    LightSwitchPowerSQLStatusText VarChar(max)
    );
    GO
    Create Table LightSwitchPowerSQLRunStatus(
    LightSwitchPowerSQLRunStatusID Int Identity(1,1) Primary Key,
    LightSwitchPowerSQLRunStatusDescription VarChar(25)
    );
    GO
    -- data for status
    Insert Into LightSwitchPowerSQLRunStatus
    (LightSwitchPowerSQLRunStatusDescription)
    Values
    ('Submitted');
    Insert Into LightSwitchPowerSQLRunStatus
    (LightSwitchPowerSQLRunStatusDescription)
    Values
    ('Running');
    Insert Into LightSwitchPowerSQLRunStatus
    (LightSwitchPowerSQLRunStatusDescription)
    Values
    ('Completed');
    Insert Into LightSwitchPowerSQLRunStatus
    (LightSwitchPowerSQLRunStatusDescription)
    Values
    ('Error');
    GO


    Just for Cool Automatic Linking in LIGHTSWITCH Run this:

    ALTER TABLE dbo.LightSwitchPowerSQL WITH NOCHECK ADD CONSTRAINT
    	FK_LightSwitchPowerSQL_LightSwitchPowerSQLRunStatus FOREIGN KEY
    	(
    	LightSwitchPowerSQLRunStatusFK
    	) REFERENCES dbo.LightSwitchPowerSQLRunStatus
    	(
    	LightSwitchPowerSQLRunStatusID
    	) ON UPDATE  NO ACTION 
    	 ON DELETE  NO ACTION 
    	 NOT FOR REPLICATION;
    
    GO
    ALTER TABLE dbo.LightSwitchPowerSQL
    	NOCHECK CONSTRAINT FK_LightSwitchPowerSQL_LightSwitchPowerSQLRunStatus;
    GO

    Add the SQL POWER Trigger

    Create Trigger LightSwitchPowerSQL_Update
    On LightSwitchPowerSQL 
    For Update
    AS
    
    Declare @sSQL VarChar(max),
    	@_RecordCount Int,
    	@_LightSwitchPowerSQLRunStatus Int,
    	@_LightSwitchPowerSQLID Int;
    
    Select @_LightSwitchPowerSQLRunStatus = max(LightSwitchPowerSQLRunStatusFK),
    		@_RecordCount = count(*),
    		@_LightSwitchPowerSQLID = max(LightSwitchPowerSQLID),
    		@sSQL = max(LightSwitchPowerSQLSQLToRun)
    	from inserted ;
    
    if @_RecordCount = 1 and @_LightSwitchPowerSQLRunStatus = 1 -- Only updating one record and this is now submitted.
    Begin
    
    	Update LightSwitchPowerSQL
    		set LightSwitchPowerSQLRunStatusFK = 2 -- Running
    		Where LightSwitchPowerSQLID = @_LightSwitchPowerSQLID;
    
    	Begin Try
    
    		Execute(@sSQL); -- do the damage
    
    		Update LightSwitchPowerSQL
    		set LightSwitchPowerSQLRunStatusFK = 3 -- Sucess!
    			Where LightSwitchPowerSQLID = @_LightSwitchPowerSQLID;
    
    	End Try
    	Begin Catch
    
    		Update LightSwitchPowerSQL
    		Set LightSwitchPowerSQLRunStatusFK = 4, -- Error
    			LightSwitchPowerSQLStatusText = 'User: ' + user_name() + '  Error: ' + ERROR_MESSAGE() 
    			Where LightSwitchPowerSQLID = @_LightSwitchPowerSQLID ;
    
    	End Catch
    
    
    End
    GO
    

    Create PowerSQLExample LightSwitch VB.Net application

    1. Create New Project named PowerSQLExample
    2. Add Datasource for SampleForSQLPOWERData
    3. Add Screen.  Choose New Data Screen.  Name it SamplePower. Choose the LightSwitchPowerSQLs Data Source.new
    4. Under Screen Command Bar add New Button.  Use the New Method option.  Name the button cmdPowerSQL
    5. DoubleClick the cmd Power SQL button.
    6. Add the following code to the cmdPowerSQL_EXECUTE()
            Private Sub cmdPowerSQL_Execute()
    
                Dim oAdd As LightSwitchPowerSQL
    
                'just get a new record, forget doing anything else.
                oAdd = LightSwitchPowerSQLs.AddNew()
                Me.Save() ' allows us to get the LightSwitchPowerSQLID if it were desired 
    
                '  Prepare SQL
                ' I like to use Carriage Returns so things format in case I test
                Dim CR As String
                CR = ChrW(13) + ChrW(10)
    
                Dim sSQL As String
                sSQL = "Create Table Delme_OK (dummycolumn Char(10))" + CR _
                    + "insert into Delme_OK (dummycolumn) values('Yup!!!')"
    
                ' fix single quote problem with SQL quotes
                'sSQL = Replace(sSQL, "'", "''")
    
                'put the sql code in and the status
                oAdd.LightSwitchPowerSQLSQLToRun = sSQL
                oAdd.LightSwitchPowerSQLRunStatu1 = (From os In Me.DataWorkspace.SampleForSQLPOWERData.LightSwitchPowerSQLRunStatus _
                                                     Where os.LightSwitchPowerSQLRunStatusID = 1).FirstOrDefault
    
                Try
                    Me.Save() 'saves it so the report will run
                    '' it runs correctly, so add the web link
                Catch ex As Exception
    
                    'set it as an error
                    oAdd.LightSwitchPowerSQLRunStatu1 = (From os In Me.DataWorkspace.SampleForSQLPOWERData.LightSwitchPowerSQLRunStatus _
                                                     Where os.LightSwitchPowerSQLRunStatusID = 4).FirstOrDefault
                    oAdd.LightSwitchPowerSQLStatusText _
                        = "Error: " & ex.Message.ToString
    
                    Me.Save()
    
                End Try
    
                ' this is a sample, just show the status results
                Dim strText As String
    
                LightSwitchPowerSQLs.Refresh()
    
                strText = oAdd.LightSwitchPowerSQLRunStatu1.LightSwitchPowerSQLRunStatusDescription & " " & oAdd.LightSwitchPowerSQLStatusText
    
    
    
                Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(
                    Sub()
    
                        MessageBox.Show(strText)
    
                    End Sub
                )
    
                'delete the record if desired
                'oAdd.Delete()
                'Me.Save()
    
    
            End Sub

    Run that, and assuming the user you used to create the DataSource has writes to do the create table, you will then have created the Delme_OK table with one record.

    Note, that this method will work with operations that take up-to the timeout in LightSwitch that I've observed is about 30 seconds for an update operation.

    If you want to get results, use a work table.  That is, create a work table in the exact structure of a query you have in your dynamic SQL.  The syntax is something like this:

    create MyWorkTable(
    column1 Varchar(max),
    column2 Varchar(max)
    )
    
    Declare @sSQL VarChar(max)
    
    set @sSQL = 'Select column1, column2 from mytable'
    
    insert into vWorkTable
    EXEC(@sSQL)

    Of course, for a better solution you need to have your work table contain the LightSwitchPowerSQLFK so that you can run multiple instances of your app.

    Let me know your Thoughts!

    JoeB


    • Edited by JoeB_LS103 Wednesday, February 13, 2013 8:29 PM minor change
    Wednesday, February 13, 2013 8:27 PM

Answers

  • Looks interesting, but for me a bit overwhelming.

    I don't like too much db triggers etc. I even don't user stored procedures, because I want that business logic is in one place (and written in one "technology").  When I have really heavy db processing I would tend to use Sql Server Integration Services.

    But... this is a matter of personal taste and which technologies you like. You are clearly very db oriented and the most important is that the above approach works for you.

    Thank you very much for sharing.


    paul van bladel

    • Marked as answer by JoeB_LS103 Monday, February 18, 2013 2:48 PM
    Saturday, February 16, 2013 8:37 AM
  • Hi Joe,

    I also think this is quite an interesting approach, specifically if you are DB orientated, as Paul mentioned above. I also prefer putting the business logic in C# code wherever I can, but do acknowledge that some tasks are indeed better suited to implement in T-SQL.

    Using a trigger to kick off an asynchronous process is particularly interesting!

    Without having studied every detail of your approach above (current time limitation sorry), one slight modification I might make is to insert only the name (and optional parameters) of a stored procedure into your main table driving the trigger. DBAs typically do not like "dynamic SQL" for a variety of reasons, including performance and security, if you were only inserting the name of a stored procedure to execute they may be a bit more lenient on you. The meat of your process is then coded in a stored procedure and can be approved by the DBAs.

    If it makes sense you could even provide a drop-down list of stored procedures to choose from in Lightswitch with the user then providing the parameters.

    Regards 


    Xander

    • Marked as answer by JoeB_LS103 Monday, February 18, 2013 2:47 PM
    • Unmarked as answer by JoeB_LS103 Monday, February 18, 2013 2:47 PM
    • Marked as answer by JoeB_LS103 Monday, February 18, 2013 2:47 PM
    Saturday, February 16, 2013 8:19 PM

All replies

  • To me, the LightSwitch side of what you're doing is using what we've come to call the "Command Table Pattern", then in the code that gets executed on the server, you're doing the rest in a SQL Server trigger.

    Is that a fair summing up of what you're doing?


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Thursday, February 14, 2013 3:05 AM
    Moderator
  • Yann,

    Yes, exactly.

    I saw a reference to the Command Table Pattern that my friend Goole introduced me to, but I never found an example, so I came up with this.

    I actually am using similar code to run queries that populate working tables used for reporting.  I'm probably re-inventing the wheel for some of this... but it is largely a training excercise, though I'm planning on using what I have done.

    Here is what my real report app does so that I can run LONG SQL scripts (the kind that take minutes)... the kind that would time-out this method above.  On top of that, the queries running are pulling data from many servers through the use of a linked server.

    So what I'm hoping for is if I'm on the right track, or if there is easier ways to do this.

    1. Using what I have shown I add the record to the SQL table.
    2. Instead of running the SQL, I Call a Service Broker with Activatation
    3. The Service Broker calls a stored procedure that executes the SQL
    4. On the LightSwitch side I do some timer events to detect when it gets done then refresh the status.

    I use this to populate working tables that I am then using from Report Builder to give the users a printable/exportable view of the data.

    I've seen the posts about using a web service to run SQL code.  That is all good and fine, and honestly I'm not up to the point where I'm doing that.  But what I think I have found is a simple way to run SQL on the server without any extra coding except setting up the service broker.  So a completely SQL Server solution for running any SQL code, stored procedures or otherwise.

    So my curiousity is if I've simply stumbled onto something that others have tried and found that there are major issues with it, or it I have found something that others can use.

    I am really loving LightSwitch, and if this is OLD hat, that is fine... for good or bad I'm learning.  Otherwise, I want to share.  If the old-timers such as yourself (My friend Google has introduced you to me several times) tell me that I've got another alternative, I'll take the time to make an Example out of it so I can give back to many of you that have helped me via the history of posts.

    Thanks again!

    Thursday, February 14, 2013 4:18 AM
  • This is interesting.  A sort of extension of the command table pattern whereby sql server can handle the long running processing of big data.  I could see this being useful for using LS to spark a big legacy process in an accounting system or some such thing that nobody in the company dares to touch :) +1.  Makes me wonder if Web API could also be used for this... Paul?
    Thursday, February 14, 2013 5:13 AM
  • Euh, maybe :)

    Joe, check following article:

    Simple commanding with web-api


    paul van bladel

    Thursday, February 14, 2013 11:48 AM
  • Yes, thanks Paul.  I want to give the Web API method a try too.  What I have pieced together almost does the same thing, except all with SQL.  I'll create an example and post it here for your guys to review.
    Thursday, February 14, 2013 2:08 PM
  • That's great.

    I'm looking forward to your post !


    paul van bladel

    Thursday, February 14, 2013 2:10 PM
  • Here it is the (drum roll please)

    The LightSwitch Power Broker

    I want to return the results of a stored procedure or other dynamic SQL script to LightSwitch only using SQL server.  Here is how I cam up to do this with NO limits on the time it takes for the query to run.

    As a note, this code will be able to run code against linked servers with the enabling of one commented out code as described below.

    So here we go:

    May as well throw the result table out into our database (contuning with the SampleForSQLPOWER database)

    Create Table WRKSampleResults(
    	WRKSampleResults_LightSwitchPowerSQLFK int, -- forein key into our SQL run table
    	WRKSampleResultsMySampleData varchar(max),
    	WRKSampleResultsMyRating int,
    	WRKSampleResultsID int identity(1,1) primary key
    );
    GO
    -- make the relations
    ALTER TABLE dbo.WRKSampleResults WITH NOCHECK ADD CONSTRAINT
    	FK_WRKSampleResults_LightSwitchPowerSQL FOREIGN KEY
    	(
    	WRKSampleResults_LightSwitchPowerSQLFK
    	) REFERENCES dbo.LightSwitchPowerSQL
    	(
    	LightSwitchPowerSQLID
    	) ON UPDATE  NO ACTION 
    	 ON DELETE  NO ACTION 
    	 NOT FOR REPLICATION;
    
    GO
    ALTER TABLE dbo.WRKSampleResults
    	NOCHECK CONSTRAINT FK_WRKSampleResults_LightSwitchPowerSQL;


    So the goal is to fill this table with data so we can easily view/manipulate its results in LightSwitch.  Note the WRKSampleResults_LightSwitchPowerSQLFK field so we can filter the results by what run of the query it came from.

    NOW FOR THE MEAT OF THIS:

    In Microsoft SQL Server Management Studio right-click the SampleForSQLPOWER database and click properties. Under the Options tab, go near the bottom to the Service Broker.  Set Broker Enabled to True.

    Set the following… I found that when the dynamic code includes an Exec(@sql) on NAMEOFREMOTESERVER it will error if this is not enabled.

    ALTER DATABASE [SampleForSQLPOWER] SET TRUSTWORTHY ON;
    GO

    Do this to set up the Service Broker Parts (I don’t understand this well, just took what my friend Google helped my find, and pieced it together by trial and error).

    Create Message Type
    	[//SQLPower/Broker/RunSQL]
    	VALIDATION = None;
    GO
    
    Create Contract [//SQLPower/Broker/Contract]
    	(
    	[//SQLPower/Broker/RunSQL]
    	Sent By Initiator
    	) ;
    GO
    
    Create Queue LightSwitchPowerBrokerQueue
    GO 
    Create Service
    	[//SQLPower/Broker/InitiatorService]
    	On Queue LightSwitchPowerBrokerQueue
    		([//SQLPower/Broker/Contract]);
    
    GO
    
    Create Service
    	[//SQLPower/Broker/TargetService]
    	On Queue LightSwitchPowerBrokerQueue
    		([//SQLPower/Broker/Contract]);
    GO

    If you have done this correctly you can check on the status of the queue with the following:

    select * from LightSwitchPowerBrokerQueue;

    Create the stored procedure to handle the SQL requests.  It is pretty simple, but with the Begin Try, Begin Catch code block it makes it fairly easy to debug code.

    Create Procedure LightSwitchPowerBrokerRunSQL
    AS
    
      Declare @RecvReqDlgHandle UNIQUEIDENTIFIER;
      Declare @RecvReqMsg NVARCHAR(100);
      Declare @RecvReqMsgName sysname;
      
      WHILE (1=1)
      BEGIN
    
        WAITFOR
        ( RECEIVE TOP(1)
            @RecvReqDlgHandle = conversation_handle,
            @RecvReqMsg = message_body,
            @RecvReqMsgName = message_type_name
          FROM LightSwitchPowerBrokerQueue
        ), TIMEOUT 5000;
    
        IF (@@ROWCOUNT = 0)
        BEGIN
          BREAK;
        END
    
        IF @RecvReqMsgName = N'//SQLPower/Broker/RunSQL'
    	   and ISNUMERIC(@RecvReqMsg) = 1
        BEGIN
    
    		Begin Try
    
    			Declare @sSQL varchar(max);
    
    			Select @sSQL = LightSwitchPowerSQLSQLToRun
    				from LightSwitchPowerSQL
    				Where  LightSwitchPowerSQLID = cast(@RecvReqMsg as int);
    	
    			Update LightSwitchPowerSQL
    				Set LightSwitchPowerSQLRunStatusFK = 2 --processing
    			from LightSwitchPowerSQL a
    			Where a.LightSwitchPowerSQLID = cast(@RecvReqMsg as int)
    
    			execute(@sSQL) -- do the damage
    		
    			Update LightSwitchPowerSQL
    				Set LightSwitchPowerSQLRunStatusFK = 3 --complete
    			from LightSwitchPowerSQL a
    			Where a.LightSwitchPowerSQLID = cast(@RecvReqMsg as int)
    
    
    		End Try
    		Begin Catch
    			Update [LightSwitchPowerSQL]
    			Set LightSwitchPowerSQLRunStatusFK = 4,
    				[LightSwitchPowerSQLStatusText] = 'User: ' + user_name() + '  Error: ' + ERROR_MESSAGE() 
    			Where LightSwitchPowerSQLID = cast(@RecvReqMsg as int)			
    		End Catch
    
        END;
    
        END CONVERSATION @RecvReqDlgHandle ;
          
      END;
    
      GO

    Now, activate the Queue.  It is the ‘With Activation’ that allows us to not use a web service to call our SQL code.

      ALTER QUEUE LightSwitchPowerBrokerQueue
        WITH ACTIVATION
        ( STATUS = ON,
          PROCEDURE_NAME = LightSwitchPowerBrokerRunSQL,
          MAX_QUEUE_READERS = 10,
          EXECUTE AS Owner
        );
    GO

    Note that I did mess with the Execute As section during my debug days, but I think this will work fine.

    Now we will alter the trigger we used above to call the LightSwitch Power Broker:

    ALTER TRIGGER LightSwitchPowerSQL_Update 
       ON  LightSwitchPowerSQL
       For Update
    AS 
    BEGIN
    
    Declare @sSQL varchar(max),
    	@_RecordCount int,
    	@_LightSwitchPowerSQLRunStatusFK int,
    	@_LightSwitchPowerSQLID int ;
    
    Select @_LightSwitchPowerSQLRunStatusFK = max(LightSwitchPowerSQLRunStatusFK),
    		@_RecordCount = count(*),
    		@_LightSwitchPowerSQLID = max(LightSwitchPowerSQLID)
    	from inserted ;
    
    if @_RecordCount = 1 and @_LightSwitchPowerSQLRunStatusFK = 1 -- submitted
    Begin
    
    	-- request that the SQL be run!
    	Declare @InitDlgHandle UNIQUEIDENTIFIER;
    	Declare @RequestMsg NVARCHAR(100);
    
    	BEGIN DIALOG @InitDlgHandle
    		 FROM SERVICE
    		  [//SQLPower/Broker/InitiatorService]
    		 TO SERVICE
    		  N'//SQLPower/Broker/TargetService'
    		 ON CONTRACT
    		  [//SQLPower/Broker/Contract]
    		 WITH
    			 ENCRYPTION = OFF;
    
    	-- Send a message on the conversation
    	Select @RequestMsg = @_LightSwitchPowerSQLID;
    
    	SEND ON CONVERSATION @InitDlgHandle
    		 MESSAGE TYPE 
    		 [//SQLPower/Broker/RunSQL]
    		 (@RequestMsg);
    
    END
    END

    Notice this is an ALTER command because it replaces the above Create.


    That ends the SQL coding for this.

    Here we go with the LightSwitch Coding:

    I’ll start from scratch, but this is mostly just restating what I stated above:

    1. Create New Project named PowerSQLExample
    2. Add Datasource for SampleForSQLPOWERData. Include all tables.
    3. Add Screen.  Choose New Data Screen.  Name it SamplePower. Choose the LightSwitchPowerSQLs Data Source.new
    4. Click on the Add WRKSSampleResults
    5. Click on the Row Layout – Sample Power Bar, then in ADD data grids to view the LightSwitchPower SQL and the WRKSampleResults.
    6. Under Screen Command Bar add New Button.  Use the New Method option.  Name the button cmdPowerSQL
    7. DoubleClick the cmd Power SQL button… it will open the write code window.  Add the code as described below.


            Private Sub cmdPowerSQL_Execute()
    
                Dim oAdd As LightSwitchPowerSQL
    
                'just get a new record, forget doing anything else.
                oAdd = LightSwitchPowerSQLs.AddNew()
                Me.Save() ' allows us to get the LightSwitchPowerSQLID if it were desired 
    
                '  Prepare SQL
                ' I like to use Carriage Returns so things format in case I test
                Dim CR As String
                CR = ChrW(13) + ChrW(10)
    
                Dim sSQL As String
                Dim sNestedSQL As String
    
                'this is very much sample code, the SQL will demonstrate how to add data to a table.  This gives you the ID to use in the procedures so that the table is unique
                sNestedSQL _
                    = "Declare @_LightSwitchPowerSQLID int" & CR _
                    & "set @_LightSwitchPowerSQLID = " & oAdd.LightSwitchPowerSQLID.ToString & CR _
                    & "" & CR _
                    & "-- for production this is where major long query data goes, but doing a select union for sample data." & CR _
                    & "-- in my production example this code comes from a separate table so I can copy and past my code into it from SQL" & CR _
                    & "Select WRKSampleResults_LightSwitchPowerSQLFK = @_LightSwitchPowerSQLID," & CR _
                    & "	WRKSampleResultsMySampleData = 'LightSwitch Programmers love to spin,'," & CR _
                    & "	WRKSampleResultsMyRating = 7" & CR _
                    & "Union" & CR _
                    & "Select WRKSampleResults_LightSwitchPowerSQLFK = @_LightSwitchPowerSQLID," & CR _
                    & "	WRKSampleResultsMySampleData = 'Useful nonsense and just grin.'," & CR _
                    & "	WRKSampleResultsMyRating = 8" & CR _
                    & "Union" & CR _
                    & "Select WRKSampleResults_LightSwitchPowerSQLFK = @_LightSwitchPowerSQLID," & CR _
                    & "	WRKSampleResultsMySampleData = 'But if you listen you might win:'," & CR _
                    & "	WRKSampleResultsMyRating = 9" & CR _
                    & "Union" & CR _
                    & "Select WRKSampleResults_LightSwitchPowerSQLFK = @_LightSwitchPowerSQLID," & CR _
                    & "	WRKSampleResultsMySampleData = 'A brand new Coding Life'," & CR _
                    & "	WRKSampleResultsMyRating = 10" & CR _
                    & "order by 3;" & CR _
                    & "" & CR _
                    & "-- simulate a long query" & CR _
                    & "WAITFOR DELAY '00:00:10'" & CR
    
                'Remember that above usually comes from a table, so that is why I do this next part:
                'Add the nexted SQL formating so we can do an insert.  Also handle the single quote issue
                sNestedSQL = Replace(Replace(Replace(sNestedSQL, ChrW(10), ""), "'", "''"), ChrW(13), "' + @CR" & CR & "+ '")
    
                ' Now we format the SQL that calls the Execute (nested EXEC()'s)  It does the following
                ' 1. Sets up the @CR variable for the Dymanic SQL
                ' 2. Removes data from work table that is of the same id
                ' 3. Inserts the results of the query into the work table with the given ID
    
                sSQL _
                = "declare @CR char(2)" & CR _
                & "set @CR = char(13) + char(10)" & CR _
                & "delete from WRKSampleResults" & CR _
                & "where WRKSampleResults_LightSwitchPowerSQLFK = " & oAdd.LightSwitchPowerSQLID.ToString & CR _
                & "insert into WRKSampleResults" & CR _
                & "exec('" & sNestedSQL & "'" & CR _
                & ")" & CR
    
                ' optional code for running the nexted SQL on linked server... that is the magic of this method.  You could concevably pull in data from any server you can link to and run an EXEC() at SQLSERVERNAME
                If 1 = 2 Then
                    sSQL = sSQL _
                        & "At NameOfLinkedServer " & CR
                End If
    
                oAdd.LightSwitchPowerSQLSQLToRun = sSQL
    
                oAdd.LightSwitchPowerSQLRunStatu1 = (From os In Me.DataWorkspace.SampleForSQLPOWERData.LightSwitchPowerSQLRunStatus _
                                                     Where os.LightSwitchPowerSQLRunStatusID = 1).FirstOrDefault
    
                Try
    
                    Me.Save() 'saves it so the code is submitted
    
                Catch ex As Exception
    
                    'if save fails
                    oAdd.LightSwitchPowerSQLRunStatu1 = (From os In Me.DataWorkspace.SampleForSQLPOWERData.LightSwitchPowerSQLRunStatus _
                                                     Where os.LightSwitchPowerSQLRunStatusID = 4).FirstOrDefault ' the type that is an error
                    oAdd.LightSwitchPowerSQLStatusText _
                        = "Error: " & ex.Message.ToString
    
                    Me.Save()
                End Try
    
                ' Now we wait for the results.
    
                Dispatchers.Main.BeginInvoke(
                    Sub()
                        oTimer.Interval = TimeSpan.FromSeconds(5)
    
                        oTimer.Start()
                    End Sub
                )
            End Sub

    If you wanted to manually refresh the data, or you didn’t care about waiting for the process to run, you could leave off the Dispatchers.Main.BeginInvoke section above and you are through.  But for cool display, I’ve included a refresh time.

    Now for the rest of the timer code so your screen will refresh if the user waits:

    Add this above the Namespace LightSwitchApplication:

    Imports Microsoft.LightSwitch.Threading
    Imports System.Windows.Threading

    The methods to add for the refresh timer:

            Private Shared oTimer As New DispatcherTimer()
    
            Private Sub SamplePower_InitializeDataWorkspace(saveChangesTo As List(Of IDataService))
    
                Dispatchers.Main.BeginInvoke(
                    Sub()
                        AddHandler oTimer.Tick, AddressOf Each_Tick
                    End Sub
                )
            End Sub
    
    
            Private Sub Each_Tick()
                Me.Details.Dispatcher.BeginInvoke(
                    Sub()
    
                        Dim oRec As IDataServiceQueryable(Of LightSwitchPowerSQL)
    
                        oRec = From oSQLRec In Me.DataWorkspace.SampleForSQLPOWERData.LightSwitchPowerSQLs
                               Where oSQLRec.LightSwitchPowerSQLID = LightSwitchPowerSQLs.SelectedItem.LightSwitchPowerSQLID
                               Select oSQLRec
    
                        ' This is where I'm not 100% sure on if this is the best way to do this part... but it works in my tests.
                        If Not IsNothing(oRec.SingleOrDefault) Then
                            If oRec.SingleOrDefault.LightSwitchPowerSQLRunStatu1.LightSwitchPowerSQLRunStatusID = 2 Then
                                LightSwitchPowerSQLs.Refresh()
                            ElseIf oRec.SingleOrDefault.LightSwitchPowerSQLRunStatu1.LightSwitchPowerSQLRunStatusID > 2 Then
    
                                LightSwitchPowerSQLs.Refresh()
    
                                Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(
                                    Sub()
                                        oTimer.Stop()
                                    End Sub
                                )
                            End If
                        End If
                    End Sub
                )
            End Sub

    So, what you should have when you get done is a screen that has a command button, cmd Power SQL, when it is run, you see the record added to the top grid, and then it updates in 5 seconds, it shows, Running.  Then in another 5 seconds it refreshes with the status of ‘Complete’.

    I look forward to your review and any tips on how to make this better!

    -JoeB





    • Edited by JoeB_LS103 Thursday, February 28, 2013 2:27 PM Database Name Error
    Thursday, February 14, 2013 6:06 PM
  • Paul, Yann, Anybody, had a chance to look this over?

    Thanks!

    Friday, February 15, 2013 4:43 PM
  • Looks interesting, but for me a bit overwhelming.

    I don't like too much db triggers etc. I even don't user stored procedures, because I want that business logic is in one place (and written in one "technology").  When I have really heavy db processing I would tend to use Sql Server Integration Services.

    But... this is a matter of personal taste and which technologies you like. You are clearly very db oriented and the most important is that the above approach works for you.

    Thank you very much for sharing.


    paul van bladel

    • Marked as answer by JoeB_LS103 Monday, February 18, 2013 2:48 PM
    Saturday, February 16, 2013 8:37 AM
  • Hi Joe,

    I also think this is quite an interesting approach, specifically if you are DB orientated, as Paul mentioned above. I also prefer putting the business logic in C# code wherever I can, but do acknowledge that some tasks are indeed better suited to implement in T-SQL.

    Using a trigger to kick off an asynchronous process is particularly interesting!

    Without having studied every detail of your approach above (current time limitation sorry), one slight modification I might make is to insert only the name (and optional parameters) of a stored procedure into your main table driving the trigger. DBAs typically do not like "dynamic SQL" for a variety of reasons, including performance and security, if you were only inserting the name of a stored procedure to execute they may be a bit more lenient on you. The meat of your process is then coded in a stored procedure and can be approved by the DBAs.

    If it makes sense you could even provide a drop-down list of stored procedures to choose from in Lightswitch with the user then providing the parameters.

    Regards 


    Xander

    • Marked as answer by JoeB_LS103 Monday, February 18, 2013 2:47 PM
    • Unmarked as answer by JoeB_LS103 Monday, February 18, 2013 2:47 PM
    • Marked as answer by JoeB_LS103 Monday, February 18, 2013 2:47 PM
    Saturday, February 16, 2013 8:19 PM
  • Novascape, yes, a store procedure would be a perfect implentation.

    My particular implementation is in a corporate environment where ony approved users would even have access to it, so the dynamic SQL restrictions are not as big of an issue.

    Thanks!

    Monday, February 18, 2013 2:47 PM
  • Novascape, yes, a store procedure would be a perfect implentation.

    My particular implementation is in a corporate environment where ony approved users would even have access to it, so the dynamic SQL restrictions are not as big of an issue.

    Thanks!

    Hi Joe,

    Note that the dynamic sql is "injected" from the silverlight client in your case. So, you could implement a CanExecute() method on your button, but even that would not prevent any authenticated user to connect directly over the odata feed (so without silveright client) and inject any sql, so also "not so innocent sql". My apologies if I overlooked something in your implementation which would be preventing this. A stored procedure would indeed make things more secure.


    paul van bladel

    Monday, February 18, 2013 4:11 PM
  • I think you are right about the above example.

    In the end-user app so far, the only option they have is to run code that is already on the SQL Server.  So the dynamic sql code has already been created, sitting in a single vChar(max) field.  So they can update the status to run.

    But I will take this into consideration as I implmement enhanced options.

    This is exactly why I wanted to share what I am doing so that not only can others benefit, but so I can get the opinion of those experienced so I don't make stupid mistakes as I implement things past the 2 users that use my apps so far.

    It sounds like I should separate things a little further though from the front-end app for good measure such as having the status table and the SQL code table separate so it is never exposed to the oData part of this.

    So many thanks!

    Monday, February 18, 2013 4:29 PM
  • Thank you for sharing Joe.

    I know exactly what you mean, from own experience, when you write that currently the app has only 2 users and  thus... the security risk is very low. The point is that one day the user base of your app will be growing and you will see that the risky part of the app will never but really never be updated. That's how security vulnerabilities are programmed:).

    Indeed, you should do kind of refactoring in such a way the client can initiate the execution of a command via the client, but the actual command implementation details should be server side. This allows you to implement also permission based security server side. (e.g. only an admin may execute command abc). This permission check will also be executed when the command is triggered via something else than the silverlight client.


    paul van bladel

    Monday, February 18, 2013 4:39 PM
  • Yes, and thanks!

    Monday, February 18, 2013 4:42 PM