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