Updating Records in LightSwitch using Stored Procedures
Since stored procedures provide benefits over directly using TSQL commands, a lot of database administrators like to completely hide database tables from applications. Instead, they will expose a View to read data from the table, and then create stored procedures to allow inserting, updating and deleting records in the table.
The problem is, Visual Studio LightSwitch doesn’t understand how to call stored procedures to insert, update and delete records out of the box. Thus, any database that only allows stored procedures to update records seems like it is unusable in LightSwitch.
However, LightSwitch can still support these databases, but it just needs a little more work to get it hooked up. I’ll show you a simple way to manipulate these records in LightSwitch.
General Approach
To work with stored procedures in our data model, we are going to use one of my favorite features in LightSwitch: attaching to a custom WCF RIA Service. The reasons this is one of my favorite features is because it provides you the flexibility you need to bring any data into your application. If you have data stored in a text file, a spreadsheet, out on the web, or wherever, you can write a WCF RIA Service to expose that data and LightSwitch will seamlessly work with that data.
Overall, the approach will be:
- Create an Entity Framework .edmx model that can write using the database’s stored procedures
- Create a WCF RIA Service that talks to the Entity Framework model
- Import the WCF RIA Service into LightSwitch and use it as a normal data source
Prerequisites
The stand alone VS LightSwitch 2011 doesn’t have an Entity Framework designer in it. In order to build the Entity Framework .edmx model, you will need one of the following:
- Visual Studio 11 Beta or later
- Visual Studio 2010 Professional or higher
- Microsoft Visual Studio 2010 Express (I used Visual Web Developer 2010 Express)
I will use the standard Northwind database for this article. However, I will add 3 standard stored procedures to it: InsertCustomer, UpdateCustomer, and DeleteCustomer. These will be simple stored procedures that call INSERT, UPDATE, and DELETE TSQL commands respectively. See the appendix at the end of this article or the MSDN Code Gallery: Updating Records in LightSwitch using Stored Procedures sample for this article for the stored procedures’ definitions.
Entity Framework Model
First start by creating a new Class Library in Visual Studio (not the stand alone Visual Studio LightSwitch, but Visual Studio Pro or Visual Studio Express), name it “NorthwindService”. In the Solution Explorer, right-click the “NorthwindService” project and select “Add” –> “New Item…”. Select “ADO.NET Entity Data Model” and name it “Northwind.edmx”.
Select “Generate from database” and enter the connection information to your Northwind database and click “Next”. Expand the “Tables” node and select “Customers”. (Note: you could have selected a view if the table isn’t exposed.) Also expand the “Stored Procedures” node and select “InsertCustomer”, “UpdateCustomer”, and “DeleteCustomer”. (Note these stored procedures don’t exist in a default Northwind database. They need to be added. See the gallery sample for the commands to create them.)
Click Finish. This generates you an entity model that contains the Customer entity in it. We want to insert, update and delete through our stored procedures. So we need to tell the Entity Framework how to do that. Right-click the Customer entity and select “Stored Procedure Mapping”. At the bottom of the screen, you will see a windows with three grid rows:
Click on each of these dropdowns and select the corresponding stored procedure for each action. The Entity Framework will automatically hook up the parameter mapping if the names match. If the names don’t match, you can easily map each property to the corresponding parameter. When you are finished your mapping screen should look like:
You now have an Entity Framework data model that will insert, update and delete records using your stored procedures.
Creating the WCF RIA Service
The next thing we need is a way for LightSwitch to consume this Entity Framework model. That is where WCF RIA Services fits into the picture. There are two ways to create a WCF RIA Service. If you have a Visual Studio Professional installation, you can right-click the project in the Solution Explorer and “Add” –> “New Item…” and select “Web” –> “Domain Service Class”.
Name the file “NorthwindService.cs” (or .vb) and click “Add”. In the “Add New Domain Service Class” dialog, accept the defaults and click OK.
If you are working with Visual Studio Express, you can do all the steps the above Add New Item template does by hand:
- Add References to the following .NET assemblies:
- System.ComponentModel.DataAnnotations
- System.ServiceModel.DomainServices.EntityFramework (Look in %ProgramFiles(x86)%\Microsoft SDKs\RIA Services\v1.0\Libraries\Server if it isn’t under the .Net tab)
- System.ServiceModel.DomainServices.Server (Look in %ProgramFiles(x86)%\Microsoft SDKs\RIA Services\v1.0\Libraries\Server if it isn’t under the .Net tab)
- Create a new Code File by “Add” –> “New Item…” and select “Code File” named “NorthwindService”
No matter which route you take, copy and paste the following code into your NorthwindService.cs file:
C#:
using System.Data;
using System.Linq;
using System.ServiceModel.DomainServices.EntityFramework;
using System.ServiceModel.DomainServices.Server;
namespace NorthwindService
{
public class NorthwindService : LinqToEntitiesDomainService<NorthwindEntities>
{
[Query(IsDefault = true)]
public IQueryable<Customer> GetCustomers()
{
return this.ObjectContext.Customers;
}
public void InsertCustomer(Customer customer)
{
if ((customer.EntityState != EntityState.Detached))
{
this.ObjectContext.ObjectStateManager.
ChangeObjectState(customer, EntityState.Added);
}
else
{
this.ObjectContext.Customers.AddObject(customer);
}
}
public void UpdateCustomer(Customer currentCustomer)
{
this.ObjectContext.Customers.AttachAsModified(currentCustomer,
this.ChangeSet.GetOriginal(currentCustomer));
}
public void DeleteCustomer(Customer customer)
{
if ((customer.EntityState != EntityState.Detached))
{
this.ObjectContext.ObjectStateManager.
ChangeObjectState(customer, EntityState.Deleted);
}
else
{
this.ObjectContext.Customers.Attach(customer);
this.ObjectContext.Customers.DeleteObject(customer);
}
}
}
}
VB:
Imports System.ComponentModel.DataAnnotations
Imports System.ServiceModel.DomainServices.EntityFramework
Imports System.ServiceModel.DomainServices.Server
Public Class NorthwindService
Inherits LinqToEntitiesDomainService(Of NorthwindEntities)
<Query(IsDefault:=True)>
Public Function GetCustomers() As IQueryable(Of Customer)
Return Me.ObjectContext.Customers
End Function
Public Sub InsertCustomer(ByVal customer As Customer)
If ((customer.EntityState = EntityState.Detached) = False) Then
Me.ObjectContext.ObjectStateManager.
ChangeObjectState(customer, EntityState.Added)
Else
Me.ObjectContext.Customers.AddObject(customer)
End If
End Sub
Public Sub UpdateCustomer(ByVal currentCustomer As Customer)
Me.ObjectContext.Customers.AttachAsModified(currentCustomer,
Me.ChangeSet.GetOriginal(currentCustomer))
End Sub
Public Sub DeleteCustomer(ByVal customer As Customer)
If ((customer.EntityState = EntityState.Detached) = False) Then
Me.ObjectContext.ObjectStateManager.
ChangeObjectState(customer, EntityState.Deleted)
Else
Me.ObjectContext.Customers.Attach(customer)
Me.ObjectContext.Customers.DeleteObject(customer)
End If
End Sub
End Class
You now are ready to import this WCF RIA Service into a LightSwitch application.
LightSwitch Application
Open Visual Studio LightSwitch and create a new LightSwitch application. Name it “Northwind”. Make sure the solution is being shown by opening Tools –> Options. Under “Projects and Solutions” ensure that “Always show solution” is checked. Then, in the Solution Explorer, right-click on the solution and say “Add –> Existing Project”. Navigate to the Class Library you created above. (The Open File Dialog may filter only *.lsproj files. Either typing the full path into the dialog or navigating to the folder, typing * and pressing “Enter” into the File name text box will allow you to select the NorthwindService.csproj/vbproj project.)
Now click the “Attach to external Data Source” link in LightSwitch and select “WCF RIA Service”.
Click Next. Since the new LightSwitch application doesn’t have any references, no classes are shown. Click the “Add Reference” button and select the “Projects” tab and double-click the NorthwindService project.
Select the “NorthwindService.NorthwindService” class and click Next.
Select all the Entities check box and click “Finish”.
One last thing needs to happen in order for you to use this data source. You need to copy the “NorthwindEntities” connection string from the NorthwindService class library’s App.Config into your application’s web.config. Open the App.config file and copy the line starting with “<add name=”NorthwindEntities”…”. Paste this line under the <connectionStrings> node in the LightSwitch web.config. To open the LightSwitch web.config:
Switch your Solution Explorer to “File View”.
Then click on the “Show All Files” tool bar button.
Under the “ServerGenerated” project (or just “Server” project if you are using Visual Studio 11) you will find the “Web.config” file.
That’s it. Now you have a fully functioning data source that can insert, update and delete records using stored procedures. You can create your LightSwitch screens and business logic as usual, and quickly and easily make a functioning application using stored procedures.
I have uploaded a sample application in both C# and VB to the MSDN Code Gallery at https://code.msdn.microsoft.com/Updating-Records-in-c88c7997.
Eric
p.s. For more information on what the Entity Framework can do with views and stored procedures, see Julie Lerman’s excellent article: Stored Procedures in the Entity Framework
Stored Procedures Used in This Article
CREATE Procedure [dbo].[InsertCustomer]
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
AS
INSERT INTO [Northwind].[dbo].[Customers]
([CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[Phone]
,[Fax])
VALUES
(@CustomerID
,@CompanyName
,@ContactName
,@ContactTitle
,@Address
,@City
,@Region
,@PostalCode
,@Country
,@Phone
,@Fax)
CREATE PROCEDURE [dbo].[UpdateCustomer]
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
AS
UPDATE [Northwind].[dbo].[Customers]
SET [CompanyName] = @CompanyName
,[ContactName] = @ContactName
,[ContactTitle] = @ContactTitle
,[Address] = @Address
,[City] = @City
,[Region] = @Region
,[PostalCode] = @PostalCode
,[Country] = @Country
,[Phone] = @Phone
,[Fax] = @Fax
WHERE CustomerID=@CustomerID
CREATE PROCEDURE [dbo].[DeleteCustomer]
@CustomerID nchar(5)
AS
DELETE FROM [Northwind].[dbo].[Customers]
WHERE CustomerID=@CustomerID