Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

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.

License

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

Share

About the Author

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

Comments and Discussions

 
Questionhow create screen Pinmemberfedison735-May-14 14:25 
GeneralMy vote of 5 PinmemberArash M. Dehghani10-Jul-13 10:47 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 10 Jul 2013
Article Copyright 2013 by thermalkid
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid