How to: Execute a Stored Procedure in LightSwitch

The Database

For this example, we are going to use the AdventureWorks sample database. The AdventureWorks database has a “HumanResources” schema that stores information about your company’s organization, employees, and job candidates. The AdventureWorks database has a stored procedure that allows an application to update an Employee’s personal information. One of these pieces of information is the employee’s marital status. Here is what the stored procedure looks like:

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
@EmployeeID [int], 
@NationalIDNumber [nvarchar](15), 
@BirthDate [datetime], 
@MaritalStatus [nchar](1), 
@Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
UPDATE [HumanResources].[Employee] 
SET [NationalIDNumber] = @NationalIDNumber 
,[BirthDate] = @BirthDate 
,[MaritalStatus] = @MaritalStatus 
,[Gender] = @Gender 
WHERE [EmployeeID] = @EmployeeID;
END;

Table view

C#:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
    
public partial class ApplicationDataService
{
    partial void UpdatePersonalInfoOperations_Inserting(UpdatePersonalInfoOperation entity)
    {
        using (SqlConnection connection = new SqlConnection())
        {
            string connectionStringName = this.DataWorkspace.AdventureWorksData.Details.Name;
            connection.ConnectionString =
                ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

            string procedure = "HumanResources.uspUpdateEmployeePersonalInfo";
            using (SqlCommand command = new SqlCommand(procedure, connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(
                    new SqlParameter("@EmployeeID", entity.EmployeeID));
                command.Parameters.Add(
                    new SqlParameter("@NationalIDNumber", entity.NationalIDNumber));
                command.Parameters.Add(
                    new SqlParameter("@BirthDate", entity.BirthDate));
                command.Parameters.Add(
                    new SqlParameter("@MaritalStatus", entity.MaritalStatus));
                command.Parameters.Add(
                    new SqlParameter("@Gender", entity.Gender));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        this.Details.DiscardChanges();
    }
}

VB:

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Namespace LightSwitchApplication
    Public Class ApplicationDataService
        Private Sub UpdatePersonalInfoOperations_Inserting(entity As UpdatePersonalInfoOperation)
            Using connection = New SqlConnection
                Dim connectionStringName = Me.DataWorkspace.AdventureWorksData.Details.Name
                connection.ConnectionString =
                    ConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString

                Dim procedure = "HumanResources.uspUpdateEmployeePersonalInfo"
                Using command = New SqlCommand(procedure, connection)
                    command.CommandType = CommandType.StoredProcedure

                    command.Parameters.Add(
                        New SqlParameter("@EmployeeID", entity.EmployeeID))
                    command.Parameters.Add(
                        New SqlParameter("@NationalIDNumber", entity.NationalIDNumber))
                    command.Parameters.Add(
                        New SqlParameter("@BirthDate", entity.BirthDate))
                    command.Parameters.Add(
                        New SqlParameter("@MaritalStatus", entity.MaritalStatus))
                    command.Parameters.Add(
                        New SqlParameter("@Gender", entity.Gender))

                    connection.Open()
                    command.ExecuteNonQuery()
                End Using
            End Using

            Me.Details.DiscardChanges()
        End Sub
    End Class
End Namespace 

Creating a Button to Call Our Operation

Now let’s invoke this middle tier operation.  We wish to add a button to our application that will mark a Single employee as Married. First, open the SearchEmployees screen and add a new button to the ribbon. At the top of the screen designer, right-click the “Screen Command Bar” content item and select “Add Button…”.

Name the button “MarkAsMarried”. We only want this button to be enabled for single employees, so right-click the button and select “Edit CanExecute Code”. Enter in the following code:

C#:

partial void MarkAsMarried_CanExecute(ref bool result)
{
    result = this.Employees.SelectedItem != null && 
        this.Employees.SelectedItem.MaritalStatus == "S";
}

VB:

Private Sub MarkAsMarried_CanExecute(ByRef result As Boolean)
    result = Me.Employees.SelectedItem IsNot Nothing And
        Me.Employees.SelectedItem.MaritalStatus = "S"
End Sub

Back in the screen designer, right-click on the button again and select “Edit Execute Code”.  Enter the following code:
C#:

partial void MarkAsMarried_Execute()
{
    DataWorkspace dataWorkspace = new DataWorkspace();
    Employee employee = this.Employees.SelectedItem;

    UpdatePersonalInfoOperation operation = 
        dataWorkspace.ApplicationData.UpdatePersonalInfoOperations.AddNew();
    operation.EmployeeID = employee.EmployeeID;
    operation.NationalIDNumber = employee.NationalIDNumber;
    operation.BirthDate = employee.BirthDate;
    operation.MaritalStatus = "M";
    operation.Gender = employee.Gender;

    dataWorkspace.ApplicationData.SaveChanges();
}

VB:

Private Sub MarkAsMarried_Execute()
    Dim dataWorkspace = New DataWorkspace
    Dim employee = Me.Employees.SelectedItem

    Dim operation =
        dataWorkspace.ApplicationData.UpdatePersonalInfoOperations.AddNew()
    operation.EmployeeID = employee.EmployeeID
    operation.NationalIDNumber = employee.NationalIDNumber
    operation.BirthDate = employee.BirthDate
    operation.MaritalStatus = "M"
    operation.Gender = employee.Gender

    dataWorkspace.ApplicationData.SaveChanges()
End Sub

This code first creates a new DataWorkspace.  This is needed because we don’t want to modify the current change set on the screen.  Instead, we will make an independent change set that only contains the operation entity we wish to invoke.  When it is complete, we discard the DataWorkspace and operation entity.

Next, we create a new UpdatePersonalInfoOperation making sure to use our newly created DataWorkspace’s ApplicationData.  We grab the currently selected employee’s information and set it on the operation, but we tell the operation to change the MaritalStatus to “M”.  Finally, we invoke the operation by calling SaveChanges.

That’s it.  Now you can run your application, select a single employee, and click on the Mark As Married button.  The stored procedure will be invoked.

 

Second Method to use the store procedure

1. Create a class file in server folder under UserCode

2. Write the following code in that file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace LightSwitchApplication.UserCode
{
   public class SqlHelper
    {
        public static SqlConnection conn(string Connection)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection].ConnectionString);
            return con;
        }
        public static void InsInFeeDeatil(Int64 StuID, string CreatedBy,string ConnectionString)
        {
            string SP = "spInsFeeStructure";
            SqlCommand cmd = new SqlCommand();
            SqlConnection con = conn(ConnectionString);
            cmd.Connection = con;
            cmd.CommandText = SP;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@StuID",StuID ));
            cmd.Parameters.Add(new SqlParameter("@CreatedBy",CreatedBy));
            con.Open();
            cmd.ExecuteNonQuery();
          
        }
    }
}

Now write the following code under mail class file under inserting function

partial void tbStudentMasters_Inserting(tbStudentMaster entity)
        {
            SqlHelper.InsInFeeDeatil(StuId+1, this.Application.User.Name,this.DataWorkspace.SMData.Details.Name);
        }

The above code automatically fire when you are trying to insert the new entry in the data base

Allowing certain users to update personal information

A lot of times, you only want specific people to be able to invoke the stored procedure.  Using the pattern above, it is very simple to enable this in your application.  First, start by creating a Permission by navigating to the Application’s Properties (right-click on the application in the Solution Explorer and select “Properties” or double click the “Properties” item under the application).  On the “Access Control” tab, enable authentication and enter a new Permission in the grid.

Now, to only allow users with the UpdatePersonalInfo permission to invoke the stored procedure, we just need to say that only these users are allowed to “insert” the UpdatePersonalInfoOperation records.  So go back to the UpdatePersonalInfoOperation entity and drop down “Write Code” –> UpdatePersonalInfoOperations_CanInsert and enter the following code:

C#:

partial void UpdatePersonalInfoOperations_CanInsert(ref bool result)
{
    result = this.Application.User.HasPermission(Permissions.UpdatePersonalInfo);
}

VB:

Private Sub UpdatePersonalInfoOperations_CanInsert(ByRef result As Boolean)
    result = Me.Application.User.HasPermission(Permissions.UpdatePersonalInfo)
End Sub

Now if a user isn’t granted the UpdatePersonalInfo permission, and they try invoking this operation, it will throw a PermissionException and not allow the user to perform the operation.  This isn’t a great user experience, so the last thing to change in the application is the button’s CanExecute code to check if the current user has permission to insert UpdatePeronalInfoOperation records.  Change your button’s CanExecute method as follows:

C#:

partial void MarkAsMarried_CanExecute(ref bool result)
{
    result = this.Employees.SelectedItem != null &&
        this.Employees.SelectedItem.MaritalStatus == "S" &&
        this.DataWorkspace.ApplicationData.UpdatePersonalInfoOperations.CanInsert;
}

VB:

Private Sub MarkAsMarried_CanExecute(ByRef result As Boolean)
    result = Me.Employees.SelectedItem IsNot Nothing And
        Me.Employees.SelectedItem.MaritalStatus = "S" And
        Me.DataWorkspace.ApplicationData.UpdatePersonalInfoOperations.CanInsert
End Sub

Thanks Eric Erhardt