Click here to Skip to main content
12,950,618 members (63,438 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


7 bookmarked
Posted 10 Jul 2013

Executing Stored Procedures in Lightswitch

, 10 Jul 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Removing the need for one table per stored procedure


I was recently asked to create a new Lightswitch application. There were two aspects to this - a basic forms-over-data app (which Lightswitch is very good at), and a number of processes to analyse the newly created data (which it is not). The second part went well beyond what's viable to do using LINQ, and the sensible choice was to write a series of stored procedures to do the heavy lifting.


Last year, Eric Erhardt published an excellent article on how to execute a stored procedure in Lightswitch. His article focuses on how to create a new entity, but my requirements were to simply execute a number of stored procedures without a return type.

I was wondering how I could get around having to create a table in the ApplicationData for each stored procedure and came up with the below.

Using the Code

Create two new tables in ApplicationData, and create a one-to-many relationship between them.

Add the code for the _Inserting method. You will need to add a reference to System.Configuration to the server.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.LightSwitch;
using Microsoft.LightSwitch.Security.Server;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace LightSwitchApplication
    public partial class ApplicationDataService
        partial void StoredProcedureDefinitions_Inserting(StoredProcedureDefinition entity)
            using (SqlConnection connection = new SqlConnection())
                connection.ConnectionString =

                string procedure = entity.Procedure;
                using (SqlCommand command = new SqlCommand(procedure, connection))
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (var item in entity.StoredProcedureParameters)
                            new SqlParameter(item.ParameterName, item.ParameterValue));   



Finally, add a button and write code for the _Execute method.

partial void TestButton_Execute()
    DataWorkspace dataWorkspace = new DataWorkspace();
    var operation = dataWorkspace.ApplicationData.StoredProcedureDefinitions.AddNew();
    //The database name as it's shown in the Solution Explorer
    operation.Database = "TestDatabaseData";
    operation.Procedure = "dbo.TestProcedure";

    //add the parameter(s)
    var param1 = operation.StoredProcedureParameters.AddNew();
    //the param name as the stored procedure expects it.
    param1.ParameterName = "@FirstParameter";            
    //each parameter must be passed in as a string.
    param1.ParameterValue = this.SampleData.SelectedItem.Property1.ToString();

    var param2 = operation.StoredProcedureParameters.AddNew();            
    param2.ParameterName = "@SecondParameter";            
    param2.ParameterValue = this.SampleData.SelectedItem.Property2.ToString();



Of course, there are a few things that aren't perfect - it's using a hard-coded database name, and the parameters must be cast to a string but hopefully this will save you some time and effort.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer
United Kingdom United Kingdom
He's just this guy, you know?

You may also be interested in...

Comments and Discussions

QuestionWhy New Dataworkspace Pin
AIBofMass24-Mar-15 4:12
memberAIBofMass24-Mar-15 4:12 
Questionhow create screen Pin
fedison735-May-14 14:25
memberfedison735-May-14 14:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170525.1 | Last Updated 10 Jul 2013
Article Copyright 2013 by thermalkid
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid