With LightSwitch, you can connect to a wide range of data sources. However, if you can’t natively connect to the data source of your choice, you can still consume the data by writing a WCF RIA service (Windows Communication Foundation Rich Internet Application Service). In this chapter, you’ll learn how to do the following:

• create an RIA service to retrieve and update data
• consume an RIA service from your LightSwitch application
• call SQL Server stored procedures

You can create a custom RIA service to connect to data sources that are not well supported. These data sources include non-relational data or data sources that don’t have an Entity Framework data provider. For example, you could create an RIA service to expose the data within XML files, a Windows event log, or even the IIS log file on the server.

RIA Services also allows you to create aggregated views of data, or to create views that join multiple database tables. An aggregate view could provide counts, sums, averages, or totals of a group of data. This can help you create views of your data for statistical or reporting purposes.

In this chapter, you’ll find out how to create an RIA service to improve the performance of the Help Desk application. You’ll learn how to perform data operations and also how to call an SQL server stored procedure to delete engineer records.

The History behind RIA Services

To begin this chapter, let’s look at a brief history of RIA Services. Microsoft first announced RIA Services in 2009. In a nutshell, RIA Services allows Silverlight clients to call code that runs on the server. Typically, this server-side code carries out data access, but it can also carry out other tasks such as validation. RIA Services first became available in Visual Studio 2010 (Service Pack 1) and was well supported in Visual Studio 2012. In 2013, Microsoft donated the source code for RIA Services to an open-source foundation, and the ongoing development of this product now continues through an open-source model. The open-source version is called Open RIA Services, and you can download it and find out more from the following website: http://openriaservices.codeplex.com/.

As part of the transition to an open-source model, Microsoft removed several of the tools that supported RIA Services from Visual Studio 2013. Specifically, Microsoft removed a wizard you could use to build domain service classes, a Business Application Project template, and other toolbox items. Developers that want to use these features can continue to do so by using Open RIA Services and installing the tooling add-ons you can download from the website.

Microsoft still supports the version of RIA Services from Visual Studio 2013 and earlier. This version supports the majority of the use-case scenarios that you’ll encounter when you develop a LightSwitch solution. You can use this version by referencing the correct DLLs; this is the approach that I’ll describe in this chapter. It’s useful to understand this background, because if you research this topic further, you’ll find documents on the web that refer to Visual Studio tools and Windows that no longer exist in Visual Studio 2015.

Creating an RIA Services Project

Let’s now examine the steps to build an RIA service that you can use in a LightSwitch project. This process consists of the following four steps:

1. Create a class library project.
2. Write a class that represents your entity.
3. Add a domain service class.
4. Add data-access code to your domain service class.

Throughout the remainder of this chapter, I’ll cover these steps in greater detail. The example I’ll use in this chapter focuses on performance. To illustrate a typical performance bottleneck, let’s examine the engineer table. This table stores engineer details and a photo. Let’s suppose you add a screen that shows a list of engineer first names and surnames. Although you don’t show the Photo field on this screen, the LightSwitch client still fetches the photo data from the server. This is because the server can only return entity sets to the client, and entity sets can’t exclude fields. If your average photo size is 500 KB, and you retain the default page size of 45 records (for desktop applications), you could incur an additional 20 MB of data transfer between the client and server each time the page loads. By creating an RIA service that excludes the photo data, you can build a screen that performs more quickly.

In this chapter, I’ll show you how to address this exact performance problem. I’ll also show you how an RIA service can improve the performance of a computed column. Let’s imagine an Engineer screen that shows a count of issues for each engineer (Figure 14-1).

As a result of the high number of records in the system, this screen performs poorly—LightSwitch needs to retrieve every issue record for each engineer just to calculate the count (you’ll find the computed column code in Chapter 2). We can address this type of performance problem by retrieving the count with more-optimal SQL and exposing the results through an RIA service.

Creating a New Project

To create an RIA service, open Visual Studio and create a new project with the Class Library template. Name your project HelpDeskDataService. Next, add references to the assemblies that are shown next. You can do this by right-clicking the References node in Solution Explorer and clicking the “Add Reference” menu item.

• System.ServiceModel.DomainServices.Server
• System.ServiceModel.DomainServices.Hosting
• System.ComponentModel.DataAnnotations

Creating an Entity Class

Your RIA service project needs a class that represents an Engineer entity. This class doesn’t need to contain every column in the engineer table. As I mentioned earlier, an ideal way to optimize performance is to return just a subset of columns. In your project, create an EngineerRecord class and add the code that’s shown in Listing 14-1.

This code shows how you must decorate the primary-key property (ID) with the key attribute and set the Editable attribute to false . LightSwitch uses these attributes to prevent users from editing your engineer’s ID property.

The Required attribute indicates that the property can’t be null or empty. If a user fails to enter any of the required properties, LightSwitch triggers its built-in validation and prevents the user from saving the record.

The StringLength attribute allows you to specify the maximum length of a property. LightSwitch’s built-in validation uses this value to prevent users from entering text that exceeds the maximum length that you specify.

It’s important to apply the StringLength and Required attributes to stop users from entering invalid data.

In addition to the attributes that I’ve shown you here, you can also define relationships and navigation properties by setting similar attributes. If you do this, LightSwitch will recognize the relationships and navigation properties that you specify when you attach to your RIA service.

Creating a Domain Service Class

The next step is to create a domain service class. To do this, right-click your project and choose the option to add a new class. Name your class EngineerDataService. Once you do this, you can begin to write your data-access logic.

Retrieving Data

To retrieve the engineer records from the database, this code uses ADO.NET. Listing 14-2 shows the data-access code that you will need to add to your class. To support the function that retrieves the database connection string, you need to add a reference to the System.Configuration assembly. As before, you can do this by right-clicking the references node and clicking the “Add Reference” menu item.

The GetEngineerData method returns the engineer data. Notice how the attribute Query(IsDefault=true) decorates this method. LightSwitch uses this attribute to determine the method it should call in order to retrieve a collection of data. If you don’t decorate a method with this attribute, and don’t decorate any properties with the Key attribute, LightSwitch won’t allow you to add the entity set to your application.

This code uses ADO.NET to connect to your SQL data source. If you were writing an RIA service to connect to a non-supported data source, you would adapt the code in this method to use a different data-access mechanism.

The data-access code here creates an SqlConnection object and uses an SqlCommand object to specify the SQL command to execute. The SQL that this method specifies produces the issue count much more efficiently than does the native LightSwitch computed column code.

Retrieving a Connection String from web.config

It is good practice to store your database connection strings in the web.config file of your LightSwitch application. By doing this, you can more easily modify your database connection string after you deploy your application.

When you connect to an RIA service at design time, LightSwitch displays a prompt that you can use to enter a connection string (you’ll see this dialog later in the “Consuming Your RIA Service” section).

LightSwitch saves your connection string in the ConnectionStrings section of your web.config file, and it keys the entry with the fully qualified name of your class.

You can obtain the connection string value in your RIA service code by using the methods in the ConfigurationManager namespace, as shown here:

VB:
ConfigurationManager.ConnectionStrings(Me.[GetType]().FullName) 
.ConnectionString

C#:
ConfigurationManager.ConnectionStrings[this.GetType().FullName] 
.ConnectionString;

In practice, you might also want to write some additional error-checking code to make sure that the connection-string setting exists, and that it isn’t null or empty.

Also note how the code decorates the name of the domain service class with the description attribute. LightSwitch shows this description when you attach to the RIA service from the Add Data Source wizard.

Updating Data

In addition to retrieving data, you can use an RIA service to add and update records in a database. To extend your RIA service to support the addition and amendment of engineer records, add the code that’s shown in Listing 14-3.

This RIA service uses the UpdateEngineerData and InsertEngineerData methods to insert and update data. LightSwitch understands that these are the methods it needs to call to insert and update data through the presence of the Insert and Update prefixes in the method names. There isn’t any more that you need to do to indicate that these methods are responsible for inserting or updating data.

The insert and update methods use ADO.NET code to update the database. This code uses named parameters to prevent rogue users from carrying out SQL injection attacks.

The SQL command within the InsertEngineerData method includes an additional command that selects @@Identity . This command retrieves the auto-generated identity value that SQL Server assigns to the newly added record. This code then executes the command by calling the command’s ExecuteScalar method. This allows it to retrieve the ID value of the newly added engineer entity, which it then assigns to the engineer’s ID property (). This allows your user to see the ID number of the record onscreen immediately following a save operation.

Calling SQL Stored Procedures

If you work with existing SQL Server databases, the ability to call SQL Server stored procedures can be very useful. For security reasons, it’s not uncommon for database administrators to block developers from accessing tables directly. The only way that you can often work with an SQL database that you don’t own is to retrieve data through views and update data through stored procedures.

Another scenario for using stored procedures is to improve performance. For certain data-intensive tasks, it can be more efficient to perform the work at the database through a stored procedure rather than through the application pulling large amounts of data into the business logic layer.

To demonstrate, I’ll show you how to extend the domain service to support the deletion of engineers through a stored procedure. In this scenario, using a stored procedure will also help you perform an additional piece of functionality. It allows you to cascade-delete all related issue records when a user deletes an engineer.

Although LightSwitch provides basic cascade-delete functionality, there are some challenges that you need to overcome to make it work with our sample application. First, the engineer table includes a self-relationship that stores the manager for each engineer. Self-join navigation properties don’t include the ability to disassociate related records on deletion. So, if the engineer that you want to delete is the manager of other engineers, you can’t automatically set the manager field for all subordinate engineers to null.

Second, there are two relationships between the engineer and issue tables. For each issue, an engineer can be assigned to the issue or to the engineer that closes the issue. LightSwitch only allows you to specify a single cascade-delete rule on any pair of tables, so you would need to manually carry out the cascade deletion. Figure 14-2 illustrates these issues.

When you cascade-delete records, it’s also important to consider performance. During this process, the Save pipeline loads the records that it needs to cascade-delete. So, in this example, LightSwitch will load all issue records that are related to the engineer before deleting the issues and the engineer.

For each issue record that LightSwitch deletes, it needs to load the issue response and document records in order to carry out the cascade-delete rules that are defined on the issue table. And because each issue-document record can be large, there’ll be a noticeable delay in performance. In this situation, a stored procedure provides an efficient alternative, because it bypasses the need to load data prior to deletion.

To carry out this example, you’ll need to create a stored procedure in your database. To define a stored procedure in your development database, you can use the Server Explorer tool in Visual Studio. The first step is to build and run your application. This attaches your database in LocalDB. Next, you can use the right-click menu item in Server Explorer to add a connection to your database. When the Add Connection dialog opens, enter the server name (localdb)MSSQLLocalDB and use the drop-down to select your database (Figure 14-3). Once you add your connection, you can use the right-click context menu on the Stored Procedures node to define a new stored procedure in your database.

Listing 14-4 shows the stored procedure code that carries out the deletion. Note that the table column names in your underlying SQL database may vary from the names that are shown in this listing. You can use the Tables node in Server Explorer to work out the exact column names to use in your stored procedure code.

After you create your stored procedure, add the delete method to your domain service, as shown in Listing 14-5.

Just like with the update and insert methods, you need to prefix the delete method with the word delete. This indicates that the method is responsible for deleting the engineer entity.

This ADO.NET code is similar to the code I showed you earlier. An important difference is that you must set the CommandType property of the SqlCommand object to StoredProcedure . Your SQL command will not run correctly if don’t do this.

This completes everything that you need in order for your RIA service to work. Now, build your project and make a note of the output DLL file that your project produces.

Consuming Your RIA Service

Once you build your RIA service, you can easily consume it from your LightSwitch application. To do this, right-click your SQL Server project, select the “Add Data Source” menu item, and choose the WCF RIA Service option.

When the Choose a WCF RIA Service dialog appears (Figure 14-4), click the Add Reference button. When the Reference Manager dialog appears, click on the Browse button and select the HelpDeskDataService.dll file that you built earlier.

The next step prompts you to select the entities and properties you want to use, just as you would for any other data source (Figure 14-5). You can specify a name for your data source in this dialog. This is the name that identifies your data source in Solution Explorer and the name that you use to access the data source in code.

This dialog also contains a text box you can use to enter a connection string (Figure 14-5). Notice how the text box includes the placeholder text Enter the connection string to the HelpDesk Database. This help text comes from the description attribute of your domain service class, which was shown in Listing 14-2.

To complete the addition of your RIA service data source, click the Finish button. You can now recreate the Engineer screen using your RIA service. You’ll find that this new screen performs much more quickly than the previous screen.

Summary

You can use RIA Services to perform data-related tasks that LightSwitch doesn’t natively support. For instance, you could write an RIA service to connect to data within a Microsoft FoxPro or Access database, or even from a non-relational data source.

You can use RIA Services to create views of your data. These could be aggregated views that contain counts, sums, and averages of your data, or might even be a view that contains joined-up data from multiple tables. Instead of joining data, you could alternatively create a view that returns a subset of columns from a table. This technique can help optimize the performance of your application, particularly if you have a table that contains records with large rows (a table that contains images, for example).

To create an RIA service, you need to create a class library project with a domain service class. Within this library project, you need to create a class that represents your entity. If you want to return an Engineer entity, for example, you’ll need to create an engineer class and include properties that represent the employee ID, surname, first name, and so on. You must decorate the property that acts as the primary key with the key attribute. If you don’t specify a key property, LightSwitch won’t allow you to import your entity.

Every RIA service must contain a method that returns a collection of data. The return value from this method must be an object that implements the IQueryable interface. For LightSwitch to recognize this as the method that returns all entities, you must decorate the method with the attribute Query(IsDefault:=True).

To update data, you would create methods that you prefix with Insert, Update, or Delete. These methods must include a parameter to accept an instance of your entity.

You can store the database connection string that your RIA service uses in your web.config file. This allows you to change the setting after you deploy your application. You can easily set the connection string when you attach your RIA service, and retrieve it in code by using the methods in the ConfigurationManager namespace.

Once you create an RIA service, you can consume it from your LightSwitch application by attaching to it through the Attach Data Source wizard, just as you would for any other data source.