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
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
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.
public partial class ApplicationDataService
partial void StoredProcedureDefinitions_Inserting(StoredProcedureDefinition entity)
using (SqlConnection connection = new SqlConnection())
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
partial void TestButton_Execute()
DataWorkspace dataWorkspace = new DataWorkspace();
var operation = dataWorkspace.ApplicationData.StoredProcedureDefinitions.AddNew();
operation.Database = "TestDatabaseData";
operation.Procedure = "dbo.TestProcedure";
var param1 = operation.StoredProcedureParameters.AddNew();
param1.ParameterName = "@FirstParameter";
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.