Share via

Walkthrough: Updating Records Using Stored Procedures

  • Article

You can’t call a stored procedure in a SQL Server database directly from LightSwitch, but you can create a Windows Communication Foundation (WCF) Rich Internet Application (RIA) service to expose stored procedures and then access the service from your LightSwitch app. This walkthrough shows how to insert, update, and delete customer records in a LightSwitch app by using stored procedures and a WCF RIA service.

Many database administrators don’t allow direct access to tables, instead exposing read-only views and providing stored procedures to insert, update, and delete records. LightSwitch doesn’t recognize stored procedures, so any database that relies on stored procedures to update records might at first appear to be unusable. If you do a little extra work, you can indeed use these databases from LightSwitch.

Prerequisites

This walkthrough uses the Northwind database from the LightSwitch Guided Tour tutorial. For more information, see Installing the Northwind Sample Database.

Create stored procedures

In most cases, you’ll access stored procedures that already exist in a database. The Northwind database doesn’t have the necessary stored procedures to insert, update, and delete customer records, so you must add them.

To add stored procedures

  1. On the menu bar, choose View, SQL Server Object Explorer.

  2. In the SQL Server Object Explorer window, expand the NORTHWIND database node, and then choose Programmability.

  3. Expand the Programmability node, and then choose Stored Procedures.

  4. Open the shortcut menu for Stored Procedures, and then choose Add New Stored Procedure.

  5. In the Code Editor, replace the contents with the following Transact-SQL code, and then choose the Update button.

    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 [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)

  6. In the Preview Database Updates dialog box, choose the Update Database button.

    The InsertCustomer stored procedure is added to the database.

  7. Open the shortcut menu for Stored Procedures, and then choose Add New Stored Procedure.

  8. In the Code Editor, replace the contents with the following Transact-SQL code, and then choose the Update button.

    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 [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

  9. In the Preview Database Updates dialog box, choose the Update Database button.

    The UpdateCustomer stored procedure is added to the database.

  10. Open the shortcut menu for Stored Procedures, and then choose Add New Stored Procedure.

  11. In the Code Editor, replace the contents with the following Transact-SQL code, and then choose the Update button.

    CREATE PROCEDURE [dbo].[DeleteCustomer]
    @CustomerID nchar(5)
AS

DELETE FROM [dbo].[Customers]
 WHERE CustomerID=@CustomerID

  12. In the Preview Database Updates dialog box, choose the Update Database button.

    The DeleteCustomer stored procedure is added to the database.

Create an entity data model

The next step is to create an Entity Framework data model that will insert, update, and delete records by using the stored procedures that you just added.

To create a data model

  1. On the menu bar, choose File, New, Project.

  2. In the New Project dialog box, expand either the Visual Basic or Visual C# node, and then choose the Class Library template.

  3. In the Name text box, enter NorthwindService, and then choose the OK button.

  4. In Solution Explorer, open the shortcut menu for the MyProject node, and then choose Open.

  5. In the Target framework list, choose .NET Framework 4.

  6. In Solution Explorer, open the shortcut menu for NorthwindService, choose Add, and then choose New Item.

  7. In the Add New Item dialog box, choose the ADO.NET Entity Data Model template.

  8. In the Name text box, enter Northwind.edmx, and then choose the Add button.

  9. In the Entity Data Model Wizard, choose the Generate from database icon, and then choose the Next button.

  10. On the Choose Your Data Connection page, choose the New Connection button.

  11. In the Connection Properties dialog box, in the Server name text box, enter the name of the server where the Northwind database is installed.

    J# noteJ# Note

    You can find the name in SQL Server Object Explorer.

  12. In the Select or enter a database name text box, enter Northwind.

  13. Choose the OK button, and then choose the Next button.

  14. On the Choose Your Database Objects and Settings page, expand the Tables node, and then select the Customers check box.

  15. Expand the Stored Procedures and Functions node, select the DeleteCustomer, InsertCustomer, and UpdateCustomer check boxes, and then choose the Finish button.

    The entity model is generated, and the Northwind.edmx database diagram appears.

    Note

    If a Security Warning dialog box appears, choose the OK button to run the template.

  16. Open the shortcut menu for the Customer entity, and then choose Stored Procedure Mapping.

  17. In the Mapping Details window, in the <Select Insert Function> list, choose InsertCustomer.

  18. In the <Select Update Function> list, choose UpdateCustomer.

  19. In the <Select Delete Function> list, choose DeleteCustomer.

  20. Close the Northwind.edmx designer.

  21. In Solution Explorer, choose the Show All Files toolbar button.

  22. Expand the node for Northwind.edmx, and then delete the Northwind.tt and Northwind.Context.tt files.

  23. Open the Northwind.edmx file, and highlight the white space outside of the Customer entity.

  24. In the Properties window, change the value of the Code Generation Strategy property to Default.

  25. On the menu bar, choose Build, Build NorthwindService.

    You now have an Entity Framework data model that will insert, update, and delete records by using your stored procedures.

Create a WCF RIA Service

If you create a WCF RIA service, your LightSwitch app can consume the Entity Framework data model as a data source.

To create a service

  1. In Solution Explorer, open the shortcut menu for the NorthwindService project, choose Add, and then choose New Item.

  2. In the Add New Item dialog box, choose the Domain Service Class template.

  3. In the Name text box, enter NorthwindService, and then choose the Add button.

  4. In the Add New Domain Service Class dialog box, choose the OK button.

  5. In Solution Explorer, open the shortcut menu for Northwind Service, and then choose Add Reference.

  6. Choose the Extensions node, select the System.ServiceModel.DomainServices.EntityFramework check box, and then choose the OK button.

  7. Add the following code to the NorthwindService class:

    Imports System.ServiceModel.DomainServices.EntityFramework

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

    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);
            }
        }
    }
}

  8. On the menu bar, choose Build, Build NorthwindService.

  9. On the menu bar, choose File, Save All.

Create a LightSwitch App

The final step is to create a LightSwitch app that will consume the WCF RIA service that you just created.

To create the application

  1. On the menu bar, choose File, New, Project.

  2. In the New Project dialog box, choose the LightSwitch category, and then choose either the LightSwitch Application (Visual Basic) or LightSwitch Application (Visual C#) project template.

  3. In the Name text box, enter Northwind, and then choose the OK button.

  4. On the menu bar, choose File, Add, Existing Project.

  5. Choose the NorthwindService project, and then choose Open.

  6. In Solution Explorer, in the Northwind project, open the shortcut menu for the Data Sources node, and then choose Add Data Source.

  7. In the Attach Data Source Wizard, choose the WCF RIA Service icon.

  8. Choose the Next button, and then choose the Add Reference button.

  9. In the Reference Manager dialog box, choose the Browse button.

  10. In the Bin\Release folder for your NorthWindService project, choose the NorthwindService.dll file, and then choose the Add button.

  11. Choose the OK button to close the dialog box, and then choose the Next button.

  12. On the Select Data Source Objects page, select the Entities check box, and then choose the Finish button.

  13. In Solution Explorer, in the NorthwindService project, open the App.Config file.

  14. In the ConnectionStrings section, copy the connection string that starts with <add name’”NorthwindEntities”.

  15. In Solution Explorer, choose the Northwind project, and then choose File View from the list on the toolbar.

  16. Expand the Server node, and then choose Web.config.

  17. In the ConnectionStrings section, paste the connection string that you copied.

    You now have a fully functioning data source that can insert, update, and delete records by using stored procedures.

Next Steps

You can create your LightSwitch screens and business logic as usual and quickly and easily make a functioning application by using stored procedures.

See Also

Tasks

How to: Execute a Stored Procedure in LightSwitch

Other Resources

Data: The Information Behind Your Application