How to: Execute a Stored Procedure in LightSwitch

 

For the latest documentation on Visual Studio 2017, see Visual Studio 2017 Documentation.

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.

      System_CAPS_ICON_note.jpg 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:

      NameTypeRequired
      EmployeeIDIntegerYes
      NationalIDNumberStringYes
      BirthDateDate TimeYes
      MaritalStatusStringYes
      GenderStringYes
      System_CAPS_ICON_note.jpg 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:

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

    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.

    System_CAPS_ICON_note.jpg 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.

    System_CAPS_ICON_note.jpg 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:

    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.

    System_CAPS_ICON_tip.jpg 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.

    System_CAPS_ICON_note.jpg 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:

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

Walkthrough: Using Stored Procedures
Working with Data-Related Objects in Code
How to: Enable Authentication in a Silverlight Client App

Show: