In my previous post (see Using Both Remote and Local Data in a LightSwitch Application), I started building a demo application that a training company’s sales rep can use to manage customers and the courses they order. The application includes both remote data and local data. The remote data consists of the Courses, Customers and Orders tables in the TrainingCourses SQL Server database. The local data consists of the Visits and CustomerNotes tables in a local SQL Server Express database.

March 27, 2011: The original version of this was posted on January 10, 2011 and was based on Beta 1. I have updated this for Beta 2. I reshot all the screens and have made some minor changes to both the text and the narrative. The primary difference is that I have moved my data code into the screen’s InitializeDataWorkspace event handler, rather than using the screen’s Created handler (which was known as Loaded in Beta 1). 

Figure1

The application currently contains four screens:

  • CustomerList is the application’s main screen and shows the list of customers.
  • NewCustomer is used for adding customers.
  • CustomerDetail is used for viewing and editing customers.
  • CourseList is used for viewing courses. I am using the default LightSwitch generated screens for adding and editing courses.
  • NewOrder is used to add a course order for a customer.

In this post, I want to show you some of the code I have written for this application and I want to discuss where the code goes. I’m not going to do a deep-dive on all the events in LightSwitch and when they execute. For that, check out Prem Ramanathan’s Overview of Data Validation in LightSwitch Applications. I’m also not going to do not a deep-dive on data validation. Rather, I want to do more of an introduction to the types of things you will think about when you write code to perform specific tasks.

We are going to review the following data-related tasks:

  • Generate default values for customer region and country.
  • Calculate total price for a course order based on attendees and price.
  • Show year to date order totals for each customer.
  • Validate that courses can’t be scheduled less than 7 days in advance.
  • Retrieve the price when the user selects a course for a new order.
  • Validate that the sales rep doesn’t discount courses by more than 20%.
  • Prompt the user to confirm deletion of a customer.

 

Generate default values for customer region and country

When the user creates a new customer, I want to default the Region to “WA” and the Country to “USA”. I could do this at the screen level with the following code:

' VB
Private Sub CustomerDetail_InitializeDataWorkspace(
  saveChangesTo As System.Collections.Generic.List(Of Microsoft.LightSwitch.IDataService))

  ' Execute this code to make both data sources editable
  saveChangesTo.Add(Me.DataWorkspace.TrainingCoursesData)
  saveChangesTo.Add(Me.DataWorkspace.ApplicationData)

  If Me.CustomerId <> 0 Then
    Me.Customer = Me.CustomerQuery
    Me.Customer.Region = "WA"
    Me.Customer.Country = "USA"
    If Me.Customer.CustomerNote Is Nothing Then
      Me.Customer.CustomerNote = New CustomerNote()
      Me.Customer.CustomerNote.Note = "Missing note"
      Me.Customer.CustomerNote.Customer = Me.Customer
    End If
  Else
    Me.Customer = New Customer
  End If
End Sub

// C#
partial void CustomerDetail_InitializeDataWorkspace(List<IDataService> saveChangesTo)
{
  // Execute this code to make both data sources editable
  saveChangesTo.Add(this.DataWorkspace.TrainingCoursesData);
  saveChangesTo.Add(this.DataWorkspace.ApplicationData);

  if (this.CustomerId != 0)
  {
    this.Customer = this.CustomerQuery;
    this.Customer.Region = "WA";
    this.Customer.Country = "USA";
    if (this.Customer.CustomerNote == null)
    {
      this.Customer.CustomerNote = new CustomerNote();
      this.Customer.CustomerNote.Note = "Missing note";
      this.Customer.CustomerNote.Customer = this.Customer;
    }
  }
  else
  {
    this.Customer = new Customer();
  }
}

To do this, open the screen and select CustomerDetail_InitializeDataWorkspace from the Write Code button’s drop-down list. This method runs before the screen data is retrieved.

Figure2

Note: In the Beta 1 version of this post, I put this code in the screen’s Loaded event handler. That event has been renamed Created. This code code go in the Created event handler, but data code should really go in the data related InitializeDataWorkspace event handler. Code that relates to the screen can go in the Created handler.

This works fine if the only place the user ever adds a customer is the CustomerDetail screen. But if customers can get added in other screens or in code, and you want the region and country to default to WA and USA, then this code should be at the entity-level. Of course, it is bad form to put this code in the screen because it is an entity rule and it belongs in the entity not the screen. So even if this is the only time this code gets run it belongs in the Customer class.

' VB
Private Sub Customer_Created()
  Me.Region = "WA"
  Me.Country = "USA"
  Me.CustomerNote = New CustomerNote()
  Me.CustomerNote.Note = "Missing note"
  Me.CustomerNote.Customer = Me
End Sub
// C#
partial void Customer_Created()
{
  this.Region = "WA";
  this.Country = "USA";
  this.CustomerNote = new CustomerNote();
  this.CustomerNote.Note = "Missing note";
  this.CustomerNote.Customer = this;
}

To do this, open the Customer entity and select Customer_Created from the Write Code button’s drop-down list. This method runs after the after the item is created and runs on the tier where the item was created.

Figure3

Calculate total price for a course order based on attendees and price

To add a new order, you specify the customer and the course they are purchasing. You then specify the number of attendees and the price of the course. The total price of the order is number of attendees times the price. This calls for a computed field. I added a TotalPrice property to the Orders table and made it a Money type. I clicked Edit Method in the Properties window and added the following code:

' VB
Public Class Order
  Private Sub TotalPrice_Compute(ByRef result As Decimal)
    result = Me.Attendees * Me.Price
  End Sub
End Class

// C#
public partial class Order
{
  partial void TotalPrice_Compute(ref decimal result)
  {
    result = this.Attendees * this.Price;
  }
}

TotalPrice_Computed is a method in the Order class, so it is attached to the data. That means that whether you add a new order by hand in the UI or in code, the total price will always be calculated based on the attendees and price. This is clearly the right place for this code.

Show year to date order totals for each customer

This application is used by a sales rep, so it would be very useful to easily see year-to-date revenue for each customer. So I added a YearToDateRevenue computed property to the Customers table and used a LINQ query to retrieve the data. (If you are new to LINQ, check out the LINQ topic in the online Visual Studio documentation.)

' VB
Public Class Customer
  Private Sub YearToDateRevenue_Compute(ByRef result As Decimal)
    result = Aggregate order In Me.Orders
             Where order.OrderDate.Year = Date.Today.Year
             Into Sum(order.Attendees * order.Price)
  End Sub
End Class
// C#
public partial class Customer
{
  partial void YearToDateRevenue_Compute(ref decimal result)
  {
    result = (from order in this.Orders
              where order.OrderDate.Year == DateTime.Today.Year
              select (decimal)order.Attendees * order.Price).Sum();
  }
}

YearToDateRevenue is a property of the Order entity, so I can simply add it to the CustomerList screen, as well as the CustomerDetail screen. And I know that any time I access a customer, I automatically retrieve the year to date revenue.

Figure4

When I run this application, I notice that the customers appear first and then the revenue figures appear, one at a time, as the query and looping occurs for each customer. This does not seem to have sped up much since Beta 1, so I am not convinced this is the way to do this. I may have to rethink this and not use a computed property. I could just move the query into the CustomerDetail screen so the query runs once each time I view a customer. But I really want the year to date revenue to show up on the customer search screen. I will have to think about this. All ideas welcome.

Validate that courses can’t be scheduled less than 7 days in advance

In our scenario, customers buy courses for a particular date. So an order has an order date and a course date, which is when the course is scheduled. I want to enforce a rule that the sales rep can’t schedule a course less than 7 days ahead of time. There are two ways to do this: at the property level and at the entity level.

To validate this at the property level, I can write code to check the course date.

' VB
Public Class Order
  Private Sub CourseDate_Validate(ByVal results As EntityValidationResultsBuilder)
    If Me.CourseDate < Me.OrderDate.AddDays(7) Then
      results.AddPropertyError(
        "Courses can’t be scheduled less than 7 days in advance")
    End If
  End Sub
End Class
// C#
public partial class Order
{
  partial void CourseDate_Validate(EntityValidationResultsBuilder results)
  {
    if (this.CourseDate < this.OrderDate.AddDays(7))
    {
      results.AddPropertyError(
        "Courses can’t be scheduled less than 7 days in advance");
    }
  }
}

Tip: I can get to CourseDate_Validate in a number of ways:

  • Open Orders in the Entity Designer. Select CourseDate and click Custom Validation in the Properties window.
  • Open Orders in the Entity Designer. Click the Write Code button. Then select CourseDate_Validate from the members drop-down list.
  • Right-click on Orders in the Solution Explorer and select View Table Code. Then select CourseDate_Validate from the members drop-down list.

As soon as I wrote that code, it occurred to me that I need the same code in the OrderDate_Validate method. Rather than duplicate this code, I can make this an entity level validation by moving this code to the Orders_Validate method.

' VB
Public Class TrainingCoursesDataService
  Private Sub Orders_Validate(entity As Order, results As EntitySetValidationResultsBuilder)
    If entity.CourseDate < entity.OrderDate.AddDays(7) Then
      results.AddEntityError(
        "Courses can’t be scheduled less than 7 days in advance")
    End If
  End Sub
End Class

// C#
public partial class
TrainingCoursesDataService
{
   partial voidOrders_Validate(Orderentity, EntitySetValidationResultsBuilder results)
  {
    if (entity.CourseDate < entity.OrderDate.AddDays(7))
    {
      results.AddEntityError(
        "Courses can't be scheduled less than 7 days in advance");
    }
  }
}

Notice that this method is in the TrainingCoursesDataService class, not the Order class. That is why I use entity instead of Me and AddEntityError instead of AddPropertyError.

To get to this method, open the Orders table in the Entity Designer and select Orders_Validate from the Write Code button’s drop-down list.

Figure6

What are the pros and cons of property level validations vs. entity level? Property level validations occur on the client as soon as the user moves off a control. When this code is in the CourseDate_Validate and OrderData_Validate methods of the Order class, I see the error as soon as I tab out of the course date control.

Figure7

Entity-level validations run on the server and don’t run until the user clicks Save. When this code is in the Orders_Validate and method of the TrainingCoursesDataService class, I don’t see the error as it occurs. I have to wait until I click Save.

Figure8

So there is a UI tradeoff. But there is also a scalability tradeoff. If I use entity level validation, the validation occurs on the middle tier. Do I really want to involve the middle tier for a simple comparison of two dates? Yes, there is a little duplication of code, but it is more efficient at runtime to have the validation occur on the client.

Retrieve the price when the user selects a course for a new order

When the user enters an order, he or she selects a course and then enters the course date, number of attendees and the price. The user should not have to enter the price. The application should retrieve the price from the course record and display it on the screen.

If you are not well versed in the multi-tier application arts, you might think about putting the code to get the price in the screen. So you go into the NewOrder screen’s code behind and start looking for events tied to the various UI elements. You won’t find them. The only thing you will find is events related to the screen and the queries it uses.

Figure9

LightSwitch applications are built on the classic three-tier architecture. See The Anatomy of a LightSwitch Application Series on the LightSwitch team blog for the full story. The presentation tier handles data entry and visualization. Queries and validation occur in the logic tier. So the code to retrieve the course price won’t be in the screen. It will be part of the entity.

clip_image010

To get the price of the course, I add one line of code to the Order class’s Course_Changed method. Every time the course changes for an order, the order’s price is immediately updated.

' VB
Public Class Order
  Private Sub Course_Changed()
    Me.Price = Me.Course.Price
  End Sub
End Class
// C#
public partial class Order
{
  partial void Course_Changed()
  {
    this.Price = this.Course.Price;
  }
}

Validate that the sales rep doesn’t discount courses by more than 20%

When placing an order, the sales rep can apply a discount and offer a course for less than its list price. I want to make sure the discount doesn’t exceed 20%. In the Order class’s Price_Validate method I will check if the price is less than the lowest discounted price.

The first thing I need to do is calculate the lowest acceptable price. I am going to do that right after I retrieve the course’s price. So I create a lowestPrice field and calculate the lowest price after retrieving the course’s price. I then validate the price by using the Order class’ Price_Changed method:

' VB
Public Class Order
  Private lowestPrice As Decimal = 0

  Private Sub Course_Changed()
    Me.Price = Me.Course.Price
    lowestPrice = Me.Course.Price * 0.8
  End Sub

  Private Sub Price_Validate(ByVal results As Microsoft.LightSwitch.EntityValidationResultsBuilder)
    ' Users can set a price before selecting a course. 
    ' If so, don't validate the discount
    If lowestPrice > 0 AndAlso Me.Price < lowestPrice Then
      results.AddEntityError(String.Format(
        "No discounts of more than 20%. Price can't be less than {0:C}", lowestPrice))
    End If
  End Sub
End Class
// C#
public partial class Order
{
  private decimal lowestPrice = 0;

  partial void Course_Changed()
  {
    this.Price = this.Course.Price;
    lowestPrice = this.Course.Price * .8M;
  }

  partial void Price_Validate(EntityValidationResultsBuilder results)
  {
    // Users can set a price before selecting a course. 
    // if (so, don't validate the discount
    if (lowestPrice > 0 && this.Price < lowestPrice)
    {
      results.AddEntityError(String.Format(
        "No discounts of more than 20%. Price can't be less than {0:C}", lowestPrice));
    }
  }
}

The user could enter a price before selecting a course, so the code first checks if lowestPrice has a positive value. It will if the user selected a course. Then the code checks for the amount of the discount.

Prompt the user to confirm deletion of a customer

The last thing I want to look at in this post is deleting. When you create a detail screen, there is no delete button by default. You can easily add one by selecting Delete from the Command Bar’s Add button’s drop-down list.

Figure10

However, you can’t write your own code to run when the user presses that Delete button. Which means you can’t prompt to see if the user really wants to delete the customer or whatever. So I added my own Delete button by selecting New Button instead. I named it DeleteCustomer and set its Display Name to Delete. Then I right-clicked and selected Edit Execute Code. Then I wrote code to prompt and only delete if confirmed.

Figure11

' VB
Public Class CustomerDetail
  Private Sub DeleteCustomer_Execute()
    If ShowMessageBox("Do you want to delete this customer?", "Customer", 
MessageBoxOption.YesNo) = Windows.MessageBoxResult.Yes
Then Me.Customer.Delete() Me.Save() Me.Close(False) End If End Sub End Class
// C#
public partial class CustomerDetail
{
  partial void DeleteCustomer_Execute()
  {
    if (this.ShowMessageBox(
      "Do you want to delete this customer?", Customer", 
MessageBoxOption.YesNo) == System.Windows.MessageBoxResult.Yes) { this.Customer.Delete(); this.Save(); this.Close(false); } } }

To delete the customer, I call the Delete method of the Customer object. That deletes the customer locally. To make this permanent I call the Save method of the screen. Then I close the CustomerDetail form. I pass False to Close indicating I do not want the user prompted to save changes.

Summary

This post explored how to accomplish in LightSwitch some of the standard things we have all done in every data application we have ever written. I love doing things like this because this is how I learn. I am a data guy and always have been. Whenever I learn a new technology like LightSwitch, I want to know how I add, edit, delete and query data. I know what I want to do. The only questions are what code do I write and where do I put it? Hopefully, the examples here will help you answer those questions in your own LightSwitch applications.

Check out Performing Data-Related Tasks by Using Code for examples of how to do many of these same tasks. Note that the examples there are purely code focused whereas the examples here are more driven from the UI.