Executing a Simple Stored Procedure in LightSwitch
Note: This article applies to LightSwitch in both Visual Studio 2010 and Visual Studio 11.
Many existing databases make use of stored procedures (or stored procs). There are advantages that stored procedures have over other types of database interaction as listed in MSDN at https://msdn.microsoft.com/en-us/library/aa214299(SQL.80).aspx. This makes them very popular with database administrators. So it is very common that existing databases already have stored procedures in them that you will want to take advantage of in your LightSwitch application.
This article is the first in a two part series that will show you how to execute a stored procedure in your LightSwitch application. The second part will describe displaying and editing data from a stored procedure in your LightSwitch screens.
Business Scenario
Our customer wants an application that can track employee information. One of the pieces of information is to track if the employee is married or not, which is used for tax purposes. The application should also allow the user to update the marital status (going from single to married, and married to single).
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;
This is a very simple stored procedure that takes in five parameters and updates the Employee record with the matching EmployeeID with the other 4 parameters’ values. The specific parameter we are interested in is @MaritalStatus.
The Application
I’m going to use Visual Studio LightSwitch 2011 for this post, but these same steps will work with Visual Studio 11 Beta or later.
Start by creating a new LightSwitch application named AdventureWorks and click “Attach to external Data Source”. Select Database and enter the connection information to your AdventureWorks database. Select all tables and hit Finish.
Next create a new Search Data Screen and select “AdventureWorksData.Employees” as the data source:
We should now have a functioning Employee Search screen. Hit F5 just to make sure everything is hooked up correctly. Your app should look like this:
Notice the far right column “Marital Status” has values ‘M’ and ‘S’. This isn’t the best UI, because my user would like to see “Married” and “Single”. There is a super simple way to do this in LightSwitch. Open the Employees table, and select the MaritalStatus property. Notice in the Properties sheet, there is a link to Choice List… Click that link.
This presents you with a Choice List dialog that allows you to map values in the database with values to display on the screen. Fill out the dialog as shown below. (Note that Choice List can be used for other data types, like boolean, integers, etc.)
Now you can run your application again and see that it correctly displays marital status as a user would expect.
Invoking the stored procedure
Now we are ready to write the code to update the employee’s marital status. However, if we are writing code in a button on the screen, the issue we hit is the button code will execute on the client tier. But there is no way to access the database from the client. Instead we need to make a call from the client to get onto the middle tier. A simple way to do this is to take advantage of the save pipeline built into LightSwitch. Whenever a record is inserted, updated or deleted, LightSwitch will invoke methods (Inserting, Updating, Deleting, etc.) on the middle tier. This allows you to write business logic that is ensured to run, no matter what client is making the request. We will use this functionality to invoke our stored procedure. To start, create a new Table and name it “UpdatePersonalInfoOperation”. Add a property for each input parameter of the stored procedure. (Keep in mind that adding a new table will create an intrinsic ApplicationData database that will need to be deployed along with your application.)
To invoke this ‘operation’, we will create a new UpdatePersonalInfoOperation record, set all the properties and save the record. This will execute the UpdatePersonalInfoOperations_Inserting method on the middle tier. So let’s write our inserting logic by dropping down the Write Code button at the top right of the designer and selecting “UpdatePersonalInfoOperations_Inserting”. Here is the code:
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
You will have to add a reference from your Server project to the System.Configuration assembly in order to use the ConfigurationManager class. (In the Solution Explorer toolbar, drop down the View toggle and select “File View”. Then right-click the Server project and say “Add Reference”. In the .NET tab, select System.Configuration.)
This code creates a SqlConnection and gets the connection string from the web.config. Note that since we are attached to an external database, the name of the connection string setting is the name of the data source. If we needed to get the connection string of the intrinsic “ApplicationData” database, the name of the setting would be hard-coded to “_IntrinsicData”.
Then, the code creates a SqlCommand object with the required command text, adds the parameter values, and executes the command. Notice the “Using” statements. These statements are important because if an exception occurs while processing this code, we are guaranteed that the connection to the database will get cleaned up in a timely manner.
Finally, the code calls DiscardChanges(). This call is needed because we don’t really want to insert an UpdatePersonalInfoOperation record in the intrinsic database. We created and saved the record only as a mechanism to execute code on the middle tier. Saving a record for every call to this operation isn’t necessary. On the other hand, if you want an easy way to log whenever the operation is invoked, then remove the call to DiscardChanges() and the record will be inserted, recording the parameter values to the operation.
Note that you can use any of your favorite frameworks for invoking stored procedures (Entity Framework, Linq To SQL, etc.). I just showed SqlClient since it has been around since .Net 1.0 and pretty simple with nothing else to setup.
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.
Advanced topics
Refreshing your screen
After invoking the UpdatePersonalInfoOperation, you will notice that your screen is not updated with the latest data – the selected employee still says “Single”. You can fix this by issuing a query in your button code to read the updated results. However, note that this only works if the records being refreshed don’t already have changes made to them. If a record is currently modified, it is not possible to refresh its values by executing a query.
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() will refresh all the records where Employees_Single will just refresh the selected item.
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
For more information on securing your LightSwitch applications, be sure to check out this excellent MSDN Magazine article: Securing Access to LightSwitch Applications.
Passing Result Parameters from a Stored Procedure
There are plenty of cases where the stored procedure will return values (rows modified, success/failure info, the result of the procedure, etc.). There is a very simple way to pass these values back to the client button code so it can be relayed to the user. Start by adding an extra property to the UpdatePersonalInfoOperation entity. Be sure to uncheck “Required” as this will cause validation to fail because the value won’t be set before calling SaveChanges() to invoke the operation. This extra property will be used as the “result” of the operation.
Inside the UpdatePersonalInfoOperations_Inserting middle tier method, set the record’s “result” property to the value you wish to pass back to the button code.
In the button code, after the call to SaveChanges(), you can get the “result” property off of the UpdatePersonalInfoOperation record, which will now be set to result of the operation.
Note that you can add multiple “result” properties to the UpdatePersonalInfoOperation entity to suit your needs.
Passing result parameters works even if you are calling DiscardChanges() in your UpdatePersonalInfoOperations_Inserting method. The result properties are still serialized to the client on the record.
Conclusion
So you can see, even though LightSwitch doesn’t support executing stored procedures out of the box, there are plenty of hooks that allow you to invoke the stored procedures you need to create your application.
I have uploaded this example application in both VB and C# to the MSDN Code Gallery: https://code.msdn.microsoft.com/Executing-a-Simple-Stored-b43fe7e5.
Eric