LightSwitch includes .NET and JavaScript APIs (application programming interfaces) that you can use to access the data in your project. By using these APIs, you can write code to carry out tasks such as adding, deleting, or updating records. In this chapter, you will learn how to

• write JavaScript and .NET code to access data on the client;
• attach custom code that executes during the server-side save process; and
• understand transactions and concurrency.

This chapter will show you how to build features that you can create only through code. Through the context of the Help Desk application, you’ll discover how to allow administrators to configure the system for first-time use by creating priority codes and issuing statuses. You’ll also find out how to allow managers to bulk update the target completion date on multiple issues, and bulk delete issues that are greater than 12 months old.

When a user saves a record, the previous and updated values of any modified fields can be determined via code you’ve written. To demonstrate this coding practice, I’ll show you how to maintain a history of status changes by recording the old status, the new status, and the date that the change occurred.

To help maintain the consistency of your data, I’ll describe the use of transactions. In particular, I’ll show you how to transaction-bind operations that occur across multiple data sources. To illustrate this technique, I’ll show you how to audit data changes by creating records in a separate SQL Server data source. This process will happen inside a transaction. If the auditing fails, all data modifications that the user made within the LightSwitch application will be rolled back.

LightSwitch Data API

To begin this chapter, I’ll describe the parts that make up the LightSwich API. I’ll then provide some examples to give you ideas of how to use data-access code in your applications.

The LightSwitch API exposes the object model shown in Figure 6-1. You can use these objects to access data sources, tables, queries, records, and much more. You can access this data API in both server- and client-side code.

The topmost object is the data workspace object. This object provides access to all the data sources in your application—the API doesn’t limit you to just the data in your Intrinsic database. The API exposes data sources by name. Therefore, the ApplicationData object provides access to the data in your Intrinsic database, and objects named similarly provide access to the other data sources in your application. In code, the name of the data-source objects match the data-source names that you see in Solution Explorer, as shown in Figure 6-2.

Through this API, you can access table data through objects known as entity sets. The name of each entity-set object matches the table name. Entity sets expose a collection of entity objects. These objects represent an individual record, and you can use this object to view and set the value of individual properties.

Entity-set objects contain methods you can call to create records. When you make changes to data in code, you must call the SaveChanges method on the data-source object in order for your changes to take effect.

Retrieving Single Records by ID Value

Each data-source object exposes methods you can call to retrieve records by ID value. For each table in your data source, LightSwitch provides a method called <TableName>_Single. This method requires you to supply the unique numeric ID value, and returns an entity object that represents a single record.

The API also exposes a method called <TableName>_SingleOrDefault for each table in your data source (Figure 6-3).

The difference between Single and SingleOrDefault is that Single throws an exception if the record doesn’t exist. SingleOrDefault returns a null rather than throwing an exception.

For HTML client applications, the client-side JavaScript API exposes only the SingleOrDefault methods. The Single methods don’t exist in the JavaScript API.

Working with Records and Nullable Data Types

The API allows you to work with individual records through entity objects. These expose each field in your record as a property, and you can use these properties to get and set the value of each field in a record. The data type of each property matches the data type that’s specified in the table designer.

An important characteristic about properties in .NET code is that the data type of a property depends on the “required” checkbox setting for the property in the table designer. If you define a property as “not required,” the API exposes a nullable property. The two exceptions to this rule are the String and Binary types. This is because String and Binary are reference types and can implicitly accept a null value.

The nullable data type enables you to set the value of property to null. From a code point of view, nullable data types expose different property members. The additional property members include Value, HasValue, and GetValueOrDefault (Figure 6-4). The difference between the Value and GetValueOrDefault methods is important. This is because if you try to access the value of a nullable property that’s set to null, LightSwitch throws an exception. In contrast, the GetValueOrDefault method does not throw an exception. Instead, it returns the default value that a new instance of the property would provide. For example, this would be 0 for an integer or false for a Boolean. By using the GetValueOrDefault method appropriately, you can help avoid runtime errors in your application.

Using JavaScript Promises

Before I describe how to carry out data access with JavaScript, I’ll explain an important coding practice you need to be aware of.

JavaScript is a single-threaded language. Because of this, long-running tasks can block your UI and leave your application unresponsive. A typical example of a long-running task is data retrieval. To make your application more responsive, LightSwitch supports asynchronous code execution through the use of promises. Here’s how this process works. When you call a data-retrieval method through the JavaScript API, the method won’t immediately return data. Instead, the method returns a promise object to the calling code and retrieves the data in the background. This enables the calling code to continue execution, and prevents your application from locking up while it waits for the slow-running data-retrieval task to complete. The promise object is a JavaScript object that provides access to a method called then. This method accepts two arguments: a function to call when the promise succeeds, and a function to call when the promise fails with an error.

The then method also returns a promise, and a common coding technique is to chain multiple then functions together. Figure 6-5 illustrates this technique, and you’ll see how this works in practice later in this chapter.

LightSwitch reuses the WinJS promise implementation, and because of this you can provide a third argument to the then method that in theory enables you to supply progress information. The promise object also provides a method called done that you can use to run code when a promise completes. In practice, the LightSwitch runtime does not support the progress parameter, and in most cases you should avoid using the done method due to exception-handling issues. The LightSwitch team avoided the use of the done method within the actual LightSwitch runtime itself. The reason I mention these non-supported items is because IntelliSense exposes these items in the code editor. By understanding these items, you can avoid writing less-optimal code.

Walkthrough 6-1: Client-Side Data-Access Code

Rather than describe the data APIs in detail, this walkthrough will provide some practical examples of why you would choose to write data-access code, and highlights the syntax you would use to add, delete, and update records.

Adding Records

To start this section, this first example shows you how to add new records. Figure 6-6 illustrates the type of screen you can create by writing data-access code. This screen provides a button to populate an empty database with a set of initial data. In the code that follows, I’ll describe how to add records to a table called IssueStatus.

Here are the steps to recreate this example. Note that the instructions here apply both to HTML and Desktop client applications.

1. Create a table called IssueStatus (Figure 6-7). Add a one-to-many relationship between the Issue and IssueStatus tables.

   

   Figure 6-7. Create a new table called IssueStatus

2. Add a new screen that isn’t bound to any data. To do this for HTML client applications, choose the “Browse Data Screen” template. For desktop clients, choose the “New Data Screen” template. Leave the screen data drop-down set to “None” and name your screen SystemSetup.
3. Create the button that adds the new records. To do this, create a Rows Layout group and choose the option to add a new button (Figure 6-8). When the Add Button dialog opens, it prompts you to “write your own method” or “choose an existing method.” Select the option to write your own method and name your method CreateRecords.

   

   Figure 6-8. Adding a button to a screen

4. Double click the CreateRecords method and enter the code that’s shown in Listing 6-1.

To complete this screen, you can optionally add labels and other content to tidy up the layout. At this point, you can run your screen. When you click the button, the code adds two issue-status records with the status descriptions “Open” and “Closed.”

Here’s how this code works. The first line creates a new record by calling the AddNew method on the IssueStatusSet entity-set object . If you wonder why the API uses the name IssueStatusSet, the answer is that this name comes from the “Plural Name” setting of the IssueStatus table in the table designer. The AddNew method returns an entity object you can use to retrieve and set the value of each field in the new record. In this case, the code sets the StatusDescription property . An important part of working with code is to call the SaveChanges method when you’re done. LightSwitch won’t commit your changes to the database unless you call this method.

With the JavaScript API, the SaveChanges method returns a promise. You can return promises from methods to make your application wait for the method for complete. If you were to call the SaveChanges method and not return the promise, the runtime would have no idea that an asyncronous operation was in progress. Therefore, it would not place the application into a wait state to indicate to the user that an operation is in progress.

The DataWorkspace object in this code refers to your application’s default data workspace. HTML applications have a single data workspace, whereas desktop applications include separate data workspaces for each screen in your application. Because of this, an important point is that when you call the SaveChanges method in code, LightSwitch commits all other data changes that may exist. If you want to avoid this behavior, you can create a new instance of a data workspace object and carry out your data changes through the new instance.

Updating Records

A useful task you can accomplish with data-access code is to update multiple records. By writing code that modifies multiple records, you can make it easier for your users to carry out bulk tasks.

To demonstrate this technique, this walkthrough will show you how to write code that closes all the old issues in your database. The screen in this walkthough displays all issue records with a target end date earlier than the start of the current year, and includes a button that closes all the issue records that are shown on the screen (Figure 6-9).

To close an issue, the code sets the IssueStatus property of the record to “Closed.” Issue status is a related record. As you’re aware, a one-to-many relationship exists between the Issue and IssueStatus tables. Therefore, this example does more than just show you how to modify records in code. Importantly, it also highlights how to write code to set the value of properties that a user would normally set using a drop-down control or an auto-complete box on a screen.

To carry out this example, you can reuse the SystemSetup screen from the previous example. Just like before, the instructions here work both with HTML and desktop applications. Here are the steps to carry out.

1. Create a new query on the Issue table to return all records with a target end date prior to the start of the year. Figure 6-10 shows the filter condition. Name your query IssuesForLastYear.

   

   Figure 6-10. Add a query to return issues with a target end date of last year

2. Create a query to return the issue-status record that represents a closed issue. This is necessary because we’ll use this to set the status on the issue records that are shown on the screen. To do this, create a query on the IssueStatus table that returns the issue-status record with the status description “Closed.” Figure 6-11 shows the filter condition. Because this query returns a single record, set the “Number of Results Returned” setting to “One.” Name your query IssueStatusClosed.

   

   Figure 6-11. Add a query to return the issue-status record with the status description “Closed”

3. Open the SystemSetup screen and add the queries to your screen. To do this, click the Add Data Item button in the toolbar and select the IssuesForLastYear query (Figure 6-12). Click the Add Data Item button again and select the IssueStatusClosed query.

   

   Figure 6-12. Use the Add Data Item dialog to add the IssuesForLastYear and IssueStatusClosed queries

4. Add the button that updates the records. When the Add Button dialog opens, choose the option to write your own method. Name your method CloseOldIssues.
5. Create a control to display the records that the user is about to update. To do this, drag the IssuesForLastYear query from the Screen Member List onto your screen. You can tidy the appearance of your screen by using group controls and rearranging the items on your screen. Figure 6-13 shows an HTML client layout that uses the tab control.

   

   Figure 6-13. Designing the screen

6. Write the code that updates the records. To do this, double click the CloseOldIssues method and enter the code that’s shown in Listing 6-2.

At this point, you can run your application. The system setup screen will show the old issue records in the database. When you click the Close Issues button, the code sets the status of these issue records to “closed.”

Here’s an explanation of how the VB and C# code works. In code, you can access the IssuesForLastYear query by name. The query object contains a collection of entity objects that you can enumerate. This code uses a for each loop to loop through each issue object .

Within the loop, the code sets the IssueStatus property for each record to “Closed.” With this type of code, you can’t use syntax that looks like this: issue.IssueStatus = "Closed". This is because issue.IssueStatus is a property of data type IssueStatus, and you can’t assign a string value to an IssueStatus property. This is the reason why we add a query called IssueStatusClosed to return a single record. This enables you to assign the IssueStatus to the Issue object . Finally, the code ends with a call to SaveChanges method .

The structure of the JavaScript code follows the promise pattern that I described earlier and therefore looks quite different than the .NET code. Unlike the Silverlight client, the HTML client doesn’t auto-execute queries when a screen loads—this option doesn’t appear in the properties sheet (Figure 6-14).

LightSwitch only executes queries that are bound to visible controls on a screen. So, to work with a query not bound to screen controls, you must manually call code to load the query. To do this, the LightSwitch screen object provides “get” methods to load screen queries, as shown in Figure 6-15.

The “get” method loads the query asynchronously and returns a promise. The first part of the code calls getIssueStatusClosed to load the singleton IssueStatusClosed query . This method returns a promise. The code in the then method runs when the first query succeeds. If this query succeeds, the next part calls the getIssuesForLastYear method to load the query that returns issue records with a target end date earlier than this year . This method also returns a promise, which illustrates the technique of chaining promises. The code in the second then method loops through the promise and assigns the value of the issues status to the property that represents a closed value. The final part of the code calls the screen’s saveChanges method to commit the changes to the database.

Deleting Records

At this stage of the walkthrough, I’ll show you how to delete records. Writing code to delete records can be very useful. This is because it allows you to delete records based on conditions, and you can also use this technique to write code that deletes multiple records.

Another reason why this type of code is important is because it enables you to add record-deletion capabilities to HTML client applications. None of the screen templates in the HTML client designer includes a delete button, or any mechanism for a user to delete a record.

To demonstrate how to delete records in code, this walkthrough will reuse the IssuesForLastYear query from the previous exercise. But rather than amend the status on issue records with a target end date earlier than this year, it will delete those records. Here are the steps to carry out this example:

1. Follow the instructions from the previous exercise to create the IssuesForLastYear query. Add this to query to your SystemSetup screen through the Add Data Item dialog if you’ve not done so already.
2. In the screen designer for the SystemSetup screen, select the command-bar node and choose the option to add a new button. When the Add Button dialog appears, select the option to write your own method, and name your method DeleteIssues. Add the code for the method as shown in Listing 6-3.

You can now run your screen. When you click the Delete Issues button, the code deletes all issue records with a target end date earlier than the start of the year. In the same way as in the previous example, this code loops through the query results . The key difference is the part that deletes the issue. As this code illustrates, the entity object itself includes a method called Delete , and this is the method you call to delete a record. In the case of the HTML client, this method is called deleteEntity. As with the previous examples, LightSwitch doesn’t actually delete the record until you call the data-source object’s SaveChanges method .

An interesting snippet of information that Stephen (my technical reviewer) told me is that the LightSwitch team wanted to name the deleteEntity method Delete, in order to make the naming consistent with the .NET API. However, they were unable to do this because delete is a reserved keyword in JavaScript.

Reading Records

In the final example in this walkthrough, I’ll show you how to read records in code. You’ve already seen some examples of how to call queries in code, so rather than repeat what you already know, this section demonstrates a useful feature you can add by writing data access code—the ability to perform data aggregations. Examples of such aggregate operations include sum, count, min, and max.

This walkthrough will show you how to add a label to an engineer detail screen that shows a count of the number of issues that are assigned to the engineer with a create date greater than the start of the current month. Figure 6-16 shows a screenshot of this example.

To recreate this example, follow these steps:

1. For an HTML client application, open a view-data screen that’s based on the engineer table. In the case of a desktop application, open an engineer-details screen.
2. Add a local string property to your screen and name it IssueCountLabel. Create a label by dragging the IssueCountLabel property from the screen member list onto your screen.
3. Add the code that’s shown in Listing 6-4.

You can now run your application, and the engineer-detail screen will show a count of the issues that have been assigned to the engineer since the start of the month.

Let’s examine the .NET code in this listing. This code uses LINQ syntax to query the Issues navigation property . The code applies criteria to return only issue records with a create date greater than the start of the current month. The results of this query are accessible via the currentIssues collection, and the count method returns a count of the number of results .

The JavaScript version of this code works in a similar way. It calls the getIssues member on the Engineer entity to asynchronously return the issues that are associated with the engineer. The code then queries the results and uses the where method to return only those records with a create date greater than the start of the month. To use the where method, you need to supply a predicate function. This is a function that returns a Boolean value to indicate whether the item should be included in the final result set.

Executing Queries Remotely

By using the LightSwitch API, you can intuitively write code to query data by simply using the IntelliSense suggestions in the code window. What may not be clear is exactly where these queries run, and this can have a big impact on the performance of your application.

In the previous section, I showed you how to query a navigation property to return issues with a create date greater than the start of the month. Navigation properties are local collections of data, so this provides an example of a query that executes locally. Here’s how such a query performs: Let’s suppose in total, 1,000 issues are assigned to an engineer, 20 of which have a create date greater than the start of the month. By using the syntax I showed you in the previous section, LightSwitch retrieves all 1,000 issue records onto the client and then filters out the unnecessary 980 records to give the resulting 20 records. This process isn’t efficient, and a more effective technique is to construct queries so that they execute on the server. Fortunately, you can accomplish this by making some relatively small syntax changes. Listing 6-5 shows how to modify the previous walkthrough so that the server executes the query, not the client.

Looking first at the .NET code, the main difference is that rather than query the Issues navigation property, the code queries an object called IssuesQuery . For each navigation property, LightSwitch exposes objects with the suffix Query. These objects represent the underlying query, which is of type IDataServiceQueryable (Figure 6-17). The navigation property on the other hand, is on object of type EntityCollection<T>. The difference between these two types is that any query you perform against an IDataServiceQueryable object will take place remotely. With IDataServiceQueryable objects, you must call the Execute method to carry out the query.

With the JavaScript example, the code carries out remote query execution by calling the filter method . This method requires you to supply an OData query string. The basic query operators you can use are eq for equals, gt for greater than, and lt for less than. The query string in this example illustrates a couple of useful techniques. The first is the ability to filter by navigation properties by using the syntax that looks like this: AssignedEngineer/Id. The second is the syntax you use to supply a date value—you need to prefix the string dates you supply with the keyword datetime. The comment illustrates the syntax of an OData query string.

Returning to the .NET examples, to prove that LightSwitch actually carries out remote query execution against IDataServiceQueryable objects, Figure 6-18 illustrates the result of running SQL Server profiler against the code samples in this section.

The first screenshot shows that when you query a navigation property, SQL Server returns issues records that are filtered only by engineer. Therefore, it follows that the client must be filtering out the issue records with a create date less than the start of the month. In the second screenshot, you can see that when you query an IDataServiceQueryable object, SQL Server applies a where clause that includes both the engineer and create date. Therefore, in this instance, SQL Server returns only the exact data that’s required.

You can use this knowledge to identify code that queries data inefficiently and then improve the performance of your application by refactoring your code so that it executes the query remotely. Other examples where you see this type of code may be in invoicing systems where there’s code at the invoice-header level that sums the total cost of invoice line items.

Although it often makes sense to execute queries on the server when possible, there are scenarios where it makes sense to use navigation properties to query data locally. For example, tasks that require you to manipulate, merge, or group data are good candidates for local execution.

Working with the Save Pipeline

In this section, we’ll focus on code that runs on the server. The key component that allows you to do this is the Save pipeline. The Save pipeline defines the process that LightSwitch follows when it commits data changes to a data source. The pipeline includes various phases in which you can inject custom .NET code.

When a user clicks the Save button on a screen, the client serializes the change set and passes it to the entity set’s save method. The change set enters the Save pipeline; Figure 6-19 shows you the full process.

Some of the reasons why you may want to write code in the Save pipeline include:

• performing permission checks and optionally preventing changes from being saved
• carrying out validation
• carrying out additional tasks after a successful save (e.g., auditing)

To access the methods in the Save pipeline, open a table in the table designer and click on the Write Code button. As you hover the mouse over the methods that are shown (Figure 6-20), the tooltip shows you where the code runs. The events that run on the server belong to the Save pipeline.

As you can see in Figure 6-20, you can execute methods when an entity is created, inserted, deleted, or validated.

If an error occurs during the save operation, LightSwitch raises the SaveChanges_ExecuteFailed event. You can capture this exception and handle it on the client if necessary.

If you change any entities inside the Save pipeline, execution returns to the start of the pipeline, and LightSwitch reruns the validation methods.

If the processing succeeds, any changes that are made to entities in the original change set are serialized and returned to the client. This allows the client to get the ID values or any other changes that have been made during processing.

It’s important to note that any entities that you add in the Save pipeline are not returned to the client. To retrieve items that have been added, you need to handle the Screen_Saved event and manually call code to refresh the data.

You can perform a refresh by calling the refresh method on the screen’s visual collection; for example, Issues.Refresh() on an issues grid screen. An alternative is to refresh the entire screen by calling the screen’s refresh method (Me.Refresh() in VB.NET or this.Refresh() in C#). In HTML client applications, you can refresh a screen by navigating to the same screen by calling the show method in the msls.application namespace.

Walkthrough 6-2: Working with Change Sets

By writing code in the Save pipeline, you can access the change set that’s associated with a save operation. Change sets are very useful for several reasons. You can use a change set to find the data items that have changed, retrieve the original values prior to the change, or to discard any changes that a user may have made. You can access a change set by using the details member of a data source, and you can access change sets through the client and server projects.

The ability to access changes sets in both server and client projects can be very useful. To demonstrate the benefit of accessing change sets in server code, this walkthrough will show you how to audit data changes. And to demonstrate the benefit of accessing change sets in client code, this section will show you how to allow users to review changes prior to saving, and to provide the option to discard their changes.

Auditing Data Changes on the Server

To demonstrate how to access the original values of a record, this walkthrough will show you how to audit status changes. Each time the status of a help-desk issue changes, the system records the original status, the new status, and the time that the change takes place.

We’ll begin by creating a table that stores the status history. Create a table called IssueStatusChange and create a property called ChangeDate. Now create two (zero-or-one)-to-many relationships between the IssueStatus and IssueStatusChange tables. Call your navigation properties OldStatus and NewStatus.

Also create a (zero-or-one)-to-many relationship between the Issue and IssueStatusChange tables. Figure 6-21 shows how the table appears in the designer.

Open the Issue table in the table designer and click the Write Code button. Select Issues_Updated Method and enter the code shown in Listing 6-6.

Now, run your application and modify an existing issue. When you do this, the server code adds a record to the IssueStatusChange table.

The first thing to notice is the file location of your code file . LightSwitch creates your code file in the server project because the code relates to logic in the server-side Save pipeline. Because the issues table is stored in the Intrinsic database, LightSwitch names the file _ApplicationDataService.lsml.[cs/vb] (after the name of your data source).

The signature of the updated method includes a parameter called entity . This enables you to refer to the item that’s currently being saved.

The key part of this code is shown in . The Details member of the entity object allows you to retrieve additional information about the entity and allows you to find the original value of the IssueStatus property.

The final part of this code records the change time using UTC (Coordinated Universal Time). It’s good practice to use UTC, particularly if you want to calculate time differences later on. It means that you don’t need to consider the effects of daylight saving time whenever you carry out calculations.

Notice that you don’t need to call SaveChanges at the end of this code. As the Save pipeline diagram shows, execution returns to the start of the Save pipeline whenever you add entities from within the pipeline. If you trigger this code from a screen that shows issues and change records, remember that the newly added change records won’t show automatically when you save the screen. You need to call one of the Refresh methods to display the data, as explained earlier.

Reviewing and Discarding Changes on the Client

In addition to accessing change sets in server-side code, you can also access change sets in client code, and this section shows you how.

This walkthough in this example will show you how to display a message box when a user clicks the Save button. This message box shows a review of the data values that have changed and provides users with the option of undoing their changes. Figure 6-22 shows the HTML client version of this example.

To carry out this example in the HTML client, open the AddEditEngineer screen, click the Write Code button, choose the beforeApplyChanges method, and add the code that’s shown in Listing 6-7.

The initial line of code retrieves a list of the fields in the table by calling the getModel method on the details member . The next part loops through the properties in the Engineer record and checks if the property has been edited . If this condition is true, the code builds a message that includes both the original and the updated property value . If the code detects that one or properties have been modified, it calls the showMessageBox method to display a message to the user. You can configure the title and buttons that appear in a message box by supplying an object that defines the settings . If the record has been modified, the code displays the message box by calling the showMessageBox method. If the user accepts the modifications, the code calls a method to save the record, and, on success, it navigates the user back to the calling page . If the code displays the message box, it sets the return value of the beforeApplyChanges method to false in order to cancel the default save operation .

Reviewing Multiple Changes on Desktop Applications

In general, the screens in HTML client applications are designed to edit a single record. The common screen templates steer you toward creating single-record entry and edit screens. In desktop applications, you can easily create screens that users can use to edit multiple records; for example, editable grid screens, as well as list and detail screens. This example adapts the code so that it works with multiple records. Figure 6-23 shows a screenshot of this example. The confirmation box shows a list of the records that have changed, rather than the individual fields within a record, as with the HTML client example.

Open the EditableIssuesGrid screen, click the Write Code button, choose the saving method, and add the code that’s shown in Listing 6-8.

In the initial section of this code, the GetChanges method in line returns a collection of entities that have been modified. Notice how the code uses the OfType method to return only the issue records that have been modified. Without this line, you would return all records that have changed. The code loops through these records and builds a message that includes the issue ID and subject field.

When the code finishes looping through the records, it calls the ShowMessageBox method to ask the user to confirm whether they want to keep their changes . If the user chooses to discard the changes, the code does so by calling the DiscardChanges method at the data-source level . This discards all changes in the data source, and is not limited to just those changes in the issue table. If you want to discard individual records, you can call the discard method for the entity object, as shown in the commented out code . If the user chooses to discard their changes, the code sets the handled parameter to true to cancel the operation that would closes the screen.

Managing Transactions in LightSwitch

Whenever LightSwitch updates data in the Intrinsic or attached SQL Server data sources, it does so within a transaction. In this section, I’ll describe how transactions work in LightSwitch.

One of the key principles of a transactional system is atomicity. This is often known as the “all or nothing” rule and means that all operations in a transaction must all either succeed or fail.

For the Intrinsic and SQL Server data sources, the save operation happens inside a transaction. If a user makes data changes in multiple tables, LightSwitch ensures that all changes succeed. In the event of any failure, LightSwitch rolls back all changes and ensures that no data whatsoever is modified.

When a LightSwitch application reads data from a SQL Server data source, it never shows uncommitted changes. This happens because LightSwitch uses SQL Server’s default transaction isolation level of Read Committed.

Understanding Transactions

When the client calls the server’s SaveChanges operation, the server creates a new data workspace and then loads the change set that it receives from the client.

Each data workspace creates a SQL Server connection and starts a SQL Server transaction within the Save pipeline process. Because the Save pipeline operates against a single data source, transactions can’t happen across multiple data sources. And because working against multiple data sources raises the possibility for data inconsistency, LightSwitch prevents users from updating multiple data sources on the same screen.

If you create a screen in a desktop application that contains data from multiple data sources, LightSwitch only allows users to modify data in a single data source. It renders any controls that are bound to other data sources read-only (Figure 6-24).

During the save operation, LightSwitch uses a transaction isolation level of RepeatableRead. This means that other transactions are unable to modify data that is being read by the current transaction until the current transaction completes.

Walkthrough 6-3: Creating Transactions in the Save Pipeline

By default, LightSwitch’s save process operates against a single data source. But by using transactions, you can override this behaviour, and in this walkthrough I’ll show you how. When a user saves an engineer record in the Intrinsic database, we’ll create an audit record in a second (SQL Server) data source called AuditDataSource. If the update to the Intrinsic database fails, the audit record will not be created (and vice versa). Here are the steps to recreate this walkthrough:

1. Create a new SQL Server database. Use Management Studio to create a new database, either on your local machine or on a remote server.
2. Create a new table called AuditDetail. Add the following fields to the table, as shown in Figure 6-25.

   

   Figure 6-25. Schema of the AuditDetail table

3. Open your LightSwitch project and attach the AuditDetail table. You can refer to Chapter 2 if you need help on how to do this.
4. Add a reference to the System.Transactions.dll in your server project. To do this, right click the server project in Solution Explorer and select the Add Reference menu item.
5. Open the Engineer table in the table designer and add code to the Executing and Executed methods, as shown in Listing 6-9.

The final step is to start the Distributed Transaction Coordinator service on your machine. You can do this though the Control Panel Administrative Tools Services section on your computer.

You can now test your application. Open an engineer record and make a modification. When you save your changes, the code adds a new record into the audit database. Now, reconfigure the audit database to stop it from working. For instance, you can use Management Studio to deny yourself access to the table, or you can take the database offline completely. Now, return to your LightSwitch application and attempt to modify the engineer record again. Any changes you to make to the record will not succeed until the audit database becomes functional again.

This code works by creating an ambient transaction in the Executing phase of the Save pipeline and committing it in the Executed phase. Ambient transactions were introduced in .NET 2.0 and help to simplify the task of working with transactions. Operations that are based on transaction-aware providers can participate in an ambient transaction. Therefore, you could also add transaction-aware RIA Service data sources into your transactional logic.

The code first declares a class-level TransactionScope object and sets it to a new instance of a TransactionScope object in the SaveChanges_Executing method. The TransactionScope constructor allows you to supply a TransactionOption object in order to set the transaction isolation level.

The next piece of code builds up a string that summarizes the changes that have been made to the Issue table . Next, it creates a new data workspace and uses this to create a record in the AuditDetail table.

If the data changes in the Intrinsic database succeeds, the code commits the transaction in the SaveChanges_Executed method . Otherwise, it disposes the transaction scope in the SaveChanges_ExecuteFailed method, thus rolling back the transaction . This means that the audit record that was created in the SaveChanges_Executing method will not be persisted to the data store, nor will any other data changes that were attempted in the Save pipeline.

Understanding Concurrency

If two users attempt to modify the same record, LightSwitch prevents the second user from overwriting any changes that have been made by the first user. When this condition occurs, LightSwitch provides a data-conflict screen to allow the second user to view the conflicting fields. For more complex scenarios, you can even write your own code to resolve data conflicts.

Displaying a Data-Conflict Screen

LightSwitch checks for data conflicts whenever a user performs a save. If it detects a conflict, it displays a screen that allows the user to fix the data (Figure 6-26).

This screen shows the data when the record was first opened, the current data, and the proposed changes. This allows the user to make an informed choice before resaving the record.

The data-conflict screen in HTML client applications provides fewer details, as shown in Figure 6-27.

If you attempt to update a record that’s been deleted by another user, LightSwitch displays the error message that’s shown in Figure 6-28. In this case, it isn’t possible for LightSwitch to recover the deleted record.

LightSwitch detects data conflicts by applying a row version number to each record. When the data service updates the database, it uses this to detect whether another user has modified the record by comparing the version numbers.

Walkthrough 6-4: Handling Conflicts in Code

Aside from using LightSwitch’s built-in data-conflict screen, you can also perform data-conflict resolution manually in code. In this walkthrough, I’ll show you how to build an issue-detail screen that always updates an issue, even if it overwrites changes that another user has made. In a typical application, this type of screen might be suitable for administrative users or managers.

To recreate this example, add a details screen for the Issue table and name it IssueDetailManager. Click the Write Code button and select the saving method. Now, enter the code as shown in Listing 6-10.

You can now run your application. To test this solution, open an existing record with the Issue Detail Manager screen, but don’t make any changes. Open the same record in a different screen (for example, an editable grid screen) and make some modifications. Return to the Issue Detail Manager screen and modify the record. The modifications you make here will overwrite your previous changes.

Here’s how this code works: When a data conflict occurs, the data service throws a ConcurrencyException. Handling this exception is the key to handling data conflicts in code.

The Saving method on the client includes a handled parameter. If you set this to true , LightSwitch doesn’t call the data service’s save operation. This allows you to manually call the SaveChanges method and to catch the ConcurrencyException.

When the data service raises a ConcurrencyException, it returns the conflicting data in a collection called EntitiesWithConflicts . LightSwitch represents conflicts by using an EntityConflict object. Figure 6-29 shows the methods that this object exposes.

If you need to do so, you could also find the exact properties that are in conflict by examining the ConflictingProperties collection. The IsDeletedOnServer property returns true if another user has deleted the record.

The last part of the code resolves the conflict by calling the ResolveConflicts method . This method accepts the arguments ConflictResolution.ClientWins or ConflictResolution.ServerWins. ClientWins overwrites the server copy with the out-of-date client version. ServerWins replaces the client version with the up-to-date server version.

Here’s a closing comment to round off this section: It isn’t possible to handle concurrency exceptions on the server by using a method such as SaveChanges_ExecuteFailed. This is because you can’t reinitiate a save from within the Save pipeline. So, even if you correct any data conflicts by writing code in the SaveChanges_ExecuteFailed method, there wouldn’t be any way for you to save those changes.

Summary

LightSwitch provides an API you can use to create, read, update, and delete data through code. You can intuitively write data-access code because the API exposes objects with names that match the tables and fields in your project.

The DataWorkspace object is the top-level object that provides access to all the data sources in your application. This object exposes an ApplicationData object that provides access to the Intrinsic data source. Each data-source object exposes an EntitySet object for each table in the data source. You can use entity-set objects to access the individual records in a table, or you can call the AddNew method to create a new record.

Once you modify data with code, you need to call the data-source object’s SaveChanges method to commit your changes to the database. An important point to be aware of is this: If you write code that uses the default DataWorkspace object, the SaveChanges method also saves all other screen changes, or all other application changes in the case of an HTML client application. If this isn’t your desired behaviour, and if you want to make isolated changes to data, you can do so by instantiating and working with a new instance of DataWorkspace object.

With HTML client applications, the JavaScript API provides a pattern for executing long-running data tasks asynchronously. If you call a method to retrieve data, the method returns a promise object while LightSwitch performs the data task in the background. This enables the user to continue using your application without the UI hanging and waiting for the long-running task to complete. By using the promise pattern, you can define code that executes when the operation either succeeds or fails. A useful coding technique is to chain together multiple promises in order to carry out several tasks asynchronously.

When you construct LightSwitch screens, you can use the Add Data Item button to add local properties to a screen. These properties can represent either an individual record or a collection of data. If you choose to write code that queries the navigation properties of a local property, it’s important to understand the performance implications. If you build a query that returns a subset of data from a navigation property (for instance, if you want to return issues that are assigned to an engineer over a specific date range), querying a local property can be slow because LightSwitch carries out the filtering locally. In some circumstances, you can improve performance by modifying your code so that it queries the corresponding IDataServiceQueryable object. When you query this object, SQL Server filters the data on the server, which results in faster performance. This is because the amount of data that LightSwitch needs to transfer between the server and client is reduced. With HTML client applications, you can carry out remote query execution by supplying an OData query string to the filter method on an EntitySet object.

The Save pipeline describes the process that happens on the server when a user initiates a save operation. This process consists of various stages and includes several phases where you can add custom code. By writing code in the Save pipeline, you can add features such as permission checks, additional validation, or data auditing.

LightSwitch maintains an object called a change set. This object stores the original and updated value of any records that a user modifies. You can access these values in both client- and server-side code through the details member of an entity object. The details member exposes each field in a record, and each field exposes an originalValue property that you can use to determine the original value. The details member also provides a method you can call to discard the changes to a specific record. If you want to discard all changes rather than just the changes to a specific record, you can do so by calling the DiscardChanges method through the details member of the data-source object.

For Intrinsic and SQL server data sources, LightSwitch carries out save operations inside a transaction. For example, if you modify a set of parent and child records, LightSwitch ensures that the save operation happens atomically. In the event of any failure, LightSwitch rolls back all changes and ensures that no data whatsoever is modified.

By writing code in the Save pipeline, you can create transactions that span multiple data sources. The technique I showed you begins an ambient transaction in the executing phase of the Save pipeline. On success, the code commits the transaction in the executed phase. If an error occurs, the code rolls back the transaction in the execute failed method.

Finally, LightSwitch includes a mechanism to ensure that users cannot overwrite changes that other users have made. For the Intrinsic data source, LightSwitch does this by maintaining a version number for each record in a table. In desktop applications, LightSwitch displays an auto-generated screen when it detects a data conflict. This screen shows the current and proposed values of all conflicting fields and allows the user to resolve the conflict. By writing custom code, you can build a screen to always overwrite data in the event of a conflict. In desktop applications, you can access a concurrency exception when a conflict occurs. You can use this object to access each conflicting entity and specify the method by which you want to resolve the conflict.