Share via


How to: Execute a Stored Procedure in LightSwitch

LightSwitch applications can execute stored procedures in a SQL Server database by calling them from the server tier. You can create commands on the client tier to trigger the execution, and you can define permissions to control who has access to the stored procedures.

Many existing databases use stored procedures because they have advantages over other types of database interaction such as modular programming, faster execution, and reduced network traffic. These same advantages apply to executing stored procedures from your LightSwitch application.

The following procedures use the uspUpdateEmployeePersonalInfo stored procedure from the AdventureWorks sample database:

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;

To execute a different stored procedure, you must change the names and parameters to match it.

To execute a stored procedure

  1. Create a table in the intrinsic database that contains the input parameters for the stored procedure.

    1. In Solution Explorer, open the shortcut menu for the Data Sources node, and then choose Add Table.

    2. In the Properties window, in the Name text box, enter UpdateEmployeePersonalInfoOperation.

      Note

      You will typically replace UpdateEmployeePersonalInfoOperation with the name of the stored procedure that you want to execute plus an identifier such as “Operation” to signify that this table is used for a stored procedure.

    3. Add the following fields to the table:

      Name

      Type

      Required

      EmployeeID

      Integer

      Yes

      NationalIDNumber

      String

      Yes

      BirthDate

      Date Time

      Yes

      MaritalStatus

      String

      Yes

      Gender

      String

      Yes

      Note

      In the Name and Type fields, replace the values with the names and data types for each input parameter in your stored procedure, and ensure that all fields are marked as required.

  2. Add a reference to the ConfigurationManager class.

    1. In Solution Explorer, open the shortcut menu for the Server node, and then choose Add Reference.

    2. In the Reference Manager dialog box, expand the Assemblies node, choose the Framework node, and then select the System.Configuration check box.

  3. In the Write Code list, choose the Inserting method (in this case, the UpdateEmployeePersonalInfoOperations_Inserting method).

  4. Add code that resembles the following example:

    Imports System.Configuration
    Imports System.Data
    Imports System.Data.SqlClient
    
    Namespace LightSwitchApplication
        Public Class ApplicationDataService
            Private Sub UpdateEmployeePersonalInfoOperations_Inserting(entity As UpdateEmployeePersonalInfoOperation)
                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
            End Sub
        End Class
    End Namespace
    
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    
    public partial class ApplicationDataService
    {
        partial void UpdateEmployeePersonalInfoOperations_Inserting(UpdateEmployeePersonalInfoOperation 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 code creates a SqlConnection object and gets the connection string from the web.config file. The code then creates a SqlCommand object with the required command text, adds the parameter values, and executes the command. Finally DiscardChanges is called; you don’t need to save the changes in the UpdateEmployeePersonalInfoOperation table.

    Note

    You must replace the DataWorkspace name and the parameter names and values with those for your own stored procedure.

To invoke the stored procedure from a screen

  1. In the Screen Designer, open the shortcut menu for the Screen Command Bar node, and then choose Add Button.

  2. In the Add Button dialog box, choose the New Method option button.

  3. In the Name text box, enter UpdateEmployeeInfo.

    Note

    You can substitute a name that describes what your stored procedure does.

  4. In the Screen Designer, open the shortcut menu for the button node, and then choose Edit Execute Code.

  5. Add code that resembles the following example:

    Private Sub UpdateEmployeeInfo_Execute()
        Dim dataWorkspace = New DataWorkspace
        Dim employee = Me.Employees.SelectedItem
    
        Dim operation =
            dataWorkspace.ApplicationData.UpdateEmployeePersonalInfoOperations.AddNew()
        operation.EmployeeID = employee.EmployeeID
        operation.NationalIDNumber = employee.NationalIDNumber
        operation.BirthDate = employee.BirthDate
        operation.MaritalStatus = employee.MaritalStatus
        operation.Gender = employee.Gender
    
        dataWorkspace.ApplicationData.SaveChanges()
    End Sub
    
    partial void UpdateEmployeeInfo_Execute()
    {
        DataWorkspace dataWorkspace = new DataWorkspace();
        Employee employee = this.Employees.SelectedItem;
    
        UpdatePersonalInfoOperation operation = 
            dataWorkspace.ApplicationData.UpdateEmployeePersonalInfoOperations.AddNew();
        operation.EmployeeID = employee.EmployeeID;
        operation.NationalIDNumber = employee.NationalIDNumber;
        operation.BirthDate = employee.BirthDate;
        operation.MaritalStatus = employee.MaritalStatus;
        operation.Gender = employee.Gender;
    
        dataWorkspace.ApplicationData.SaveChanges();
    }
    

    This code first creates a DataWorkspace and then creates an UpdateEmployeePersonalInfoOperation by using the newly created DataWorkspace’s ApplicationData. The values for the operation are assigned, and operation is invoked by calling SaveChanges. This step triggers the middle tier UpdateEmployeePersonalInfoOperations_Inserting method that executes the stored procedure in the database.

    Tip

    The screen data isn’t refreshed to reflect the changes when you call the stored procedure. To refresh the screen, you can either call Employees.Refresh() or DataWorkspace.AdventureWorksData.Employees_Single(operation.EmployeeID) at the end of the button’s Execute method. Note that Employees.Refresh() refreshes all the records, and Employees_Single refreshes only the item that you specify.

To define permissions for a stored procedure

  1. In Solution Explorer, open the shortcut menu for the Properties node, and then choose Open.

  2. On the Access Control tab, choose the type of authentication to use if it’s not already specified.

  3. Choose the <Add New Permission> link, and then and enter UpdatePersonalInfo.

    Note

    You can substitute a name that describes what your stored procedure does.

  4. In Solution Explorer, open the shortcut menu for your stored procedure table, and then choose Open.

  5. In the Write Code list, choose the CanInsert method.

  6. Add code that resembles the following example:

    Private Sub UpdateEmployeePersonalInfoOperations_CanInsert(ByRef result As Boolean)
        result = Me.Application.User.HasPermission(Permissions.UpdatePersonalInfo)
    End Sub
    
    partial void UpdateEmployeePersonalInfoOperations_CanInsert(ref bool result)
    {
        result = this.Application.User.HasPermission(Permissions.UpdatePersonalInfo);
    }
    

See Also

Tasks

Walkthrough: Using Stored Procedures

How to: Enable Authentication in a Silverlight Client App

Concepts

Working with Data-Related Objects in Code