Microsoft LightSwitch: Querying Multiple Entities

(For more resources on this topic, see here.)

Microsoft LightSwitch makes it easy to query multiple entities and with queries you can fine tune the results using multiple parameters.

In the following, we will be considering the Orders and the Shippers tables from the Northwind database shown next:

What we would like to achieve is to fashion a query in LightSwitch which finds orders later than a specified date (OrderDate) carried by a specified shipping company (CompanyName).

In the previous example, we created a single parameter and here we extend it to two parameters, OrderDate and CompanyName. The following stored procedure in SQL Server 2008 would produce the rows that satisfy the above conditions:

Use Northwind
Go
Create Procedure ByDateAndShprName @ordDate datetime, 
@shprName nvarchar(30)
as

SELECT  Orders.OrderID, Orders.CustomerID, Orders.
EmployeeID,Orders.OrderDate,
                 Orders.RequiredDate, Orders.ShippedDate,
Orders.ShipVia, Orders.Freight, Orders.ShipName, 
               Orders.ShipAddress, Shippers.ShipperID,
Shippers.CompanyName, Shippers.Phone
FROM      Orders INNER JOIN
       Shippers ON Orders.ShipVia = Shippers.ShipperID
where Orders.OrderDate > @OrdDate and 
Shippers.CompanyName=@shprName

The stored procedure ByDateAndShprName can be executed by providing the two parameters (variables), @OrdDate and @shprName, as shown below.

Exec ByDateAndShprName '5/1/1998 12:00:00','United Package'

The result returned by the previous command is shown next copied from the SQL Server Management Studio (only first few columns are shown):

The same result can be achieved in LightSwitch using two parameters after attaching these two tables to the LightSwitch application. As the details of creating screens and queries have been described in detail, only some details specific to the present section are described. Note that the mm-dd-yyyy appears in the result reversed yyyy-mm-dd.

1. Create a Microsoft LightSwitch application (VB or C#).
   Here project Using Combo6 was created.
2. Attach a database using SQL Server 2008 Express and bring the two tables, Orders and Shippers, to create two entities, Order and Shipper, as shown in the next screenshot:

   Create a query as shown in the next image:

   Here the query is called ByDate. Note that the CompanyName in the Shippers table is distinct.
   The completed query with two parameters appears as shown:

3. Create a new screen (click on Add Screen in the query designer shown in the previous screenshot) and choose the Editable Grid Screen template.
   Here the screen created is named EditableGridByDate.
4. Click on Add Data Item… and add the query NorthwindData.ByDate.
   The designer changes as shown next:

5. Click on OrderDate parameter on the left-hand side navigation of the screen and drag and drop it just below the Screen Command Bar as shown.

6. In a similar manner, drag and drop the query parameter CompanyName below the OrderDate of the earlier step.
   This will display as two controls for two parameters on the screen.
7. Hold with mouse, drag and drop ByDate below the CompanyName you added in the previous step.
   The completed screen design should appear as shown (some fields are not shown in the display):

   The previous image shows two parameters. The DataGrid rows show the rows returned by the query. As is, this screen would return no data if the parameters were not specified. The OrderDate defaults to Current Date.

8. Click on F5 to display the screen as shown:

9. Enter the date 5/1/1998 directly.
10. Enter United Package in the CompanyName textbox and click on the Refresh button on the previous screen.

The screen is displayed as shown here:

The above screen is an editable screen and you should be able to add, delete, and edit the fields and they should update the fields in the backend database when you save the data. Also note that the LightSwitch application returned 11 rows of data while the stored procedure in SQL Server returned 10 rows. This may look weird but SQL Server date time refers to PM but Microsoft LightSwitch order date is datetime data type with AM. Entering PM instead of AM returns the correct number of rows.