This documentation is archived and is not being maintained.

Walkthrough: Updating Records Using Stored Procedures

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.

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

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.

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.

    NoteNote

    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.

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.

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.

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

Show: