Executing Stored Procedures in Lightswitch

Introduction

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.

Background

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 =
                ConfigurationManager.ConnectionStrings[entity.Database].ConnectionString;

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

                    foreach (var item in entity.StoredProcedureParameters)
                    {
                        command.Parameters.Add(
                            new SqlParameter(item.ParameterName, item.ParameterValue));   
                    }

                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            
            this.Details.DiscardChanges();

        }
    }
}

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();

    dataWorkspace.ApplicationData.SaveChanges();

    this.Refresh();
}

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.