Storing String Data

The string data type includes support for international character types (for example, Arabic, Chinese, and Japanese). For maximum performance, it’s a good idea to set the maximum field size to a value that’s appropriate for the data that you want to store.

If you want to store unlimited length or multiline text, clear the contents of the maximum field text box and leave it blank, as shown in Figure 2-2.

Storing Numbers (Double and Decimal Types)

If you want to store numbers with decimal places, LightSwitch provides a choice of either double or decimal data types.

The practical difference between the two types is that doubles can store a wide range of numbers in a smaller amount of memory. However, doubles are less precise and are subject to rounding errors when calculations are performed against them.

Decimals don’t suffer from such rounding errors but take up more space and are slower to compute. Sums of money should always be based on the decimal data type.

Other important attributes that relate to decimals are precision and scale. Precision defines the total number of digits in a number. Scale defines the number of digits after the decimal place. Figure 2-3 illustrates precision and scale using the example number 123456.789.

When you create a new decimal field, LightSwitch defaults the precision and scale values to 18 and 2, respectively. In keeping with good practice, you should shorten these values if you don’t require that level of accuracy.

Formatting Numeric Fields

This is a new feature in LightSwitch 2012. You can specify a display format for each numeric property that you define in LightSwitch. This means that LightSwitch formats the number on the user’s screen using the format that you’ve specified.

Figure 2-4 shows a field that stores a feedback rating. A format string of N2 means that the number is shown to 2 decimal places.

.NET format strings begin with a Format Specifier, followed by a number that indicates the desired number of decimal places. Table 2-1 shows the Format Specifiers that you can use.

Storing Images

The Image Type allows you to store images. LightSwitch includes Image Editor and Image Viewer controls that allows users to upload and view images.

Note that these controls support only images in JPG and PNG format. If you want to upload and view image files in other formats, you’ll need to write or purchase a third-party custom control.

Storing Binary Data

You can use the binary data type to store binary large objects such as documents, videos, or other file types. Chapters 7 and 8 show you how to design a screen that allows users to upload and download files.

Ensuring Unique Values

Each field contains an Include In Unique Index check box. Selecting this check box adds the field into a combination index.

It’s not possible to create individual unique fields through the designer. If you want to do this, you can write validation to enforce uniqueness at a field level. Chapter 5 contains sample code that shows you how to do this.

Alternatively, you could create unique indexes on your SQL Server table if you’re using an attached SQL database.

Changing Data Types

If you’re working on tables in your Intrinsic database and make some major table changes, LightSwitch can discard the data in your tables. At worst, it can even destroy and re-create your entire development database. However, it generally warns you before any data loss occurs. (See Figure 2-5.)

Storing Email Addresses

As its name suggests, the Email business type provides storage for email addresses.

When you add an Email field, you’ll find two properties that you can set (as shown in Figure 2-6):

• Default Email Domain: If the user leaves out the email domain, LightSwitch appends the default email domain that you specify to the end of the email address. This setting is ideal for internal systems that are used by a single company.
• Require Email Domain: If this is checked, the user must enter an email domain when entering an email address.

Storing Money Values

When you add a Money field to a table, the additional properties that you can set are shown, as you can see in Figure 2-7. These are

• Currency Code: Use this field to specify the locale for the currency. For example, if you want to specify United States Dollars, specify USD. Appendix A shows a list of valid codes that you can use.
• Is Formatted: If you check this, LightSwitch applies formatting when you use the currency control on a screen to display your money value. The formatting that the control applies includes the currency symbol, the grouping separator, and decimal places.

The following options apply only if you check the Is Formatted check box:

• Is Grouped: If checked, LightSwitch shows digit-grouping separators. For example, it displays 1,234,567.89 rather than 1234567.89.
• Symbol Mode: The symbol mode dropdown allows you to select Currency Symbol, ISO Currency Symbol, or No Currency Symbol. Here are some examples of how LightSwitch would format a money value using the available symbol modes:

• Currency Symbol: $123.45
• ISO Currency Symbol: 123.45 USD
• No Currency Symbol: 123.45

• Decimal Places: This defines the number of decimal places that LightSwitch shows when it formats a money value.

Storing Phone Numbers

The Phone Number business type is designed to store phone numbers, and it validates data by making sure that users can enter only phone numbers that match a list of predefined formats.

You can define formats by using the dialog that’s shown in Figure 2-8. The symbols that you can use to define a format are

• C – Country Code
• A – Area or City Code
• N – Local number

Other additional symbols that you can use include the following: +, −, (,), .

When a user enters a phone number through the Phone Number control, the control attempts to validate the phone number against the first format in the list. If the digits match the format, LightSwitch displays the phone number using that format. Otherwise, the control attempts to validate the phone number against all remaining formats in the list until a match is found. If it doesn’t find a match, LightSwitch prevents the record from being saved.

When LightSwitch saves the phone number in the database, it does so without any formatting. If you want to create reports or use the phone number data outside of LightSwitch, you’ll need to write your own procedure to format the data.

Unfortunately, it isn’t possible to specify additional formats on a global or application basis. They must be specified each time a Phone Number field is used in a table.

Storing Web Addresses and Percentage Values

The Web Address business type is new to LightSwitch 2012. It allows you to store Web addresses, and it allows users to add and edit data using the Web Address Editor and Web Address Viewer controls.

Also new to LightSwitch 2012 is the Percent business type. This Business Type allows you to store percentage values. Just like the other business types, LightSwitch provides a Percent Editor and Percent Viewer control for data entry.

Choice Lists vs. Related Tables

If you want your users to enter data by choosing from a list of available choices, the choice values can either come from a choice list or a related table.

A choice list is ideal for data items that are relatively static. The disadvantage of using a choice list is that adding or deleting items requires you to recompile and redeploy your application, which can be cumbersome. Table 2-3 summarizes the pros and cons of choice lists and related tables.

Defining One-to-Many Type Relationships

One-to-many relationships are a common type of relationship that you can define. This type of relationship allows you to define a data model where a parent record in one table may reference several child records in another table. The following example shows you how to set up your tables so that engineers can be assigned to multiple help desk issues.

To begin, create the Issue and Engineer tables as shown in Figure 2-10. At this point, no relationships have been defined.

To create the relationship, click the Relationships button in the screen designer toolbar and set up the relationship as shown in Figure 2-11.¹

The Multiplicity row allows you to define the type of relationship between the two tables—in this case, we’ve created a one-to-many relationship. This means that when a help desk operator creates an issue, they must assign an engineer.

The last row of the dialog allows you to set the Navigation Property names for each table. These allow you to give a meaningful name to the relationship. In our case, we’ve named the Navigation Property on the Issue table AssignedTo.

If you want to add another relationship to the Engineer table (for example, to store the name of the engineer who closes the issue), you can rename the Navigation Property to ClosedBy to distinguish it clearly from the other relationships that might exist between the two tables. The default Navigation Name in this example would be Engineers1, which isn’t very meaningful.

The purpose of a Navigation Property is to give you a way to navigate related entities. When you’re writing code, these properties appear as objects that you can use to reference child and parent entities. You can also query navigation properties by constructing Language Integrated Query (LINQ) expressions in code.

How Relationships Are Defined in SQL Server

With LightSwitch, you can define relationships using simple language and navigation properties. Although this simplifies the process for a novice, it might feel alien for more experienced database developers. If you fall into this category, let’s take a look at what happens when you create a relationship in LightSwitch.

Figure 2-12 compares what you see in the LightSwitch designer with what it creates in SQL Server.

This diagram shows how we’ve adapted the Issue table in order to store the name of the engineer who closes the issue. This relationship is defined as a (zero-or-one)-to-many relationship, and the navigation property is called ClosedByEngineer. A (zero-or-one)-to-many relationship means that for an issue, specifying a value for “closed by engineer” isn’t mandatory.

For the AssignedTo relationship, notice how LightSwitch creates a column in the issue table called Issue_Engineer. And for ClosedByEngineer, it creates a nullable column called Engineer_Issue. This allows SQL Server to support the (zero-or-one)-to-many relationship that we’ve defined. For both these columns, LightSwitch also creates a SQL Server foreign key relationship to the Id column in the Engineer table.

So when you create relationships in the table designer, LightSwitch carries out the same keying that you would need to carry out if you were to create the tables manually.

Defining Self-Referencing Relationships

Self-referencing or recursive relationships are relationships where entities reference themselves. You’ll often find these when modeling hierarchies. To illustrate this type of relationship, let’s modify the Engineer table so that it stores the name of the engineer’s manager.

To create a self-join, open the relationship dialog and select the same table name in the To side of the relationship, as shown in Figure 2-13. Rename the Navigation Properties to Subordinates (in the From column) and Manager (in the To column). This will result in two navigation properties for the engineer entity, each representing one end of the relationship.

Defining Many-to-Many Relationships

In the relationship designer, many-to-many type relationships are not natively supported. If you want to create a many-to-many relationship, you’ll need to create an intermediary table that contains two one-to-many relationships.

In this example, we’ll create a set of tables for storing engineer skills. An engineer can have many skills and a skill can be assigned to one or more engineers.

After creating the Engineer and Skill tables, create an EngineerSkill table to store the many-to-many relationship. In the table designer for the EngineerSkill table, create a one-to-many relationship between the Engineer and EngineerSkill tables. Next, create a one-to-many relationship between the Skill and EngineerSkill tables. Figure 2-14 illustrates the table structure that we have created.

This completes the table design; Chapter 7 describes how you can create screens to enter many-to-many data.

Determining How Child Records Are Deleted

When you create a relationship, the ’On Delete Behavior’ setting allows you to determine what happens when a user deletes a record. The three possible options are

• Cascade delete
• Restricted
• Disassociate

In the engineer and issue example, setting the deletion behavior to cascade delete will delete all related issue records when a user deletes an engineer.

If you set the behavior to restricted, engineers can’t be deleted until all related issues have been deleted.

The disassociate option sets all engineer references to null on related issues prior to the deletion of an engineer. The disassociate option is valid only on (zero-or-one)-to-many relationships.

The deletion behavior option is not available and is grayed out if you attempt to create a relationship between two separate data sources. Furthermore, these deletion options are available only for tables that belong in the Intrinsic database. They aren’t available for external data sources.

Attaching to an Existing Database

To attach to an existing SQL Server database, right-click the Data node in Solution Explorer and select the Add A Datasource option. Select the Database option, and follow the steps in the wizard.

You’ll eventually reach the dialog that’s shown in Figure 2-16. This allows you to choose the tables and views that you want to use in your LightSwitch application.

Stored procedures, user-defined functions, and other SQL objects are not supported and don’t appear in the dialog that’s shown. If you want to use stored procedures, you can do so by writing a custom RIA service.

Attaching to SharePoint Data

You can connect your application to a SharePoint 2010 data source. LightSwitch uses a custom OData provider, so it doesn’t support versions prior to SharePoint 2010.

You can attach to SharePoint lists using the Data Source wizard in the same way that you would connect to a SQL Server database.

There are various limitations when using SharePoint data. First, LightSwitch isn’t able to manage SharePoint attachments or documents. Second, there’s limited support for the SharePoint column types of Picture and Hyperlink. This is because LightSwitch doesn’t include any native controls for showing these data items. It also doesn’t fully support multiline text because LightSwitch doesn’t include a native HTML control that allows users to edit the content.

Deleting a Data Source

If you delete a data source, LightSwitch removes all entities that relate to that data source from all screens in your application. The screens themselves will not be deleted, but instances of entities and related controls within those screens will be removed.

Creating Summary Properties

Each table allows you to specify a summary property through the properties sheet in the table designer. Summary properties are designed to summarize the contents of a data row for display purposes. To show a summary property on a screen, you’d use a summary control. This control is most often used in grids and autocomplete boxes.

In the example that follows, we’ll create a computed property in the Engineer table called FullName that concatenates the first name and surname of an engineer.

Open the Engineer table, and create a new property called FullName. In the General section of the properties sheet for the new property, check the Is Computed check box. Click on the Edit Method hyperlink, and enter the code that’s shown in Listing 2-1.

Listing 2-1.  Formatting Strings in a Computed Property

VB:
File: HelpDeskVBCommonUserCodeEngineer.vb
  
Private Sub FullName_Compute(ByRef result As String)
    result = String.Format("{0} - {1}", Surname, Firstname)
End Sub
  
C#:
File: HelpDeskCSCommonUserCodeEngineer.cs
  
partial void FullName_Compute(ref string result)
{
    result = String.Format("{0} - {1}", this.Surname, this.Firstname);
}

This code demonstrates how to format strings using .NET’s String.Format method. This method accepts two parameters. The first parameter specifies the string to be displayed and includes placeholders in the format {0},{1} and so on. The second parameter accepts a comma-separated list of data items, which are then substituted into the display string.

To set this as the summary property, open the properties sheet for the Engineer table and choose FullName from the Summary Property drop-down list (as shown in Figure 2-19 ).

Figure 2-19 shows the FullName summary property on a screen that’s designed for entering new issues.

Calculating Dates with Computed Properties

You can use computed properties to calculate dates. This example shows you how to calculate the difference between two dates. The Engineer table contains a date of birth field. By using a computed property, you can calculate the age of a person in years.

To do this, create an integer computed property called Age and enter the code that’s shown in Listing 2-2.

Listing 2-2.  Calculating Date Differences

VB:
File: HelpDeskVBCommonUserCodeEngineer.vb
  
Private Sub Age_Compute(ByRef result As Integer)
    result = DateDiff(DateInterval.Year, DateOfBirth, Now)
End Sub
  
C#:
File: HelpDeskCSCommonUserCodeEngineer.cs
  
partial void Age_Compute(ref int result)
{
    DateTime now = DateTime.Today;
    int age = now.Year - DateOfBirth.Year;
    if (DateOfBirth > now.AddYears(-age)) age--;
    result = age;
}

This code illustrates the use of the VB.NET DateDiff function. This function allows you to calculate date and time differences accurately, and also takes leap years into consideration. C# doesn’t have an equivalent method, and the calculation is therefore slightly more complex.

Summarizing Child Records with Computed Properties

You can easily refer to related records through navigation properties. In this example, we’ll create a computed property in the Engineer table that returns the number of help desk issues that are assigned to each engineer.

To do this, create a computed property called IssueCount and enter the code that’s shown in Listing 2-3.

Listing 2-3.  Using Navigation Properties in a Computed Property

VB:
File: HelpDeskVBCommonUserCodeEngineer.vb
  
Private Sub IssueCount_Compute(ByRef result As Integer)
    result = Issues.Count()
End Sub
  
C#:
File: HelpDeskCSCommonUserCodeEngineer.cs

partial void IssueCount_Compute(ref int result)
{
    result = this.Issues.Count();
}

The code in Listing 2-3 demonstrates how to refer to child items in a computed property. Specifically, it shows you how to use the aggregate count operator against a navigation property. Other aggregate operations that you can use include Sum and Average.

Returning Images with Computed Properties

You’re not just limited to returning string and numeric data in a computed property. This example shows you how to create a computed property that returns an image. If the current date exceeds the target end date, the computed property returns a warning icon. Otherwise, it returns a blank white image.

To create this example, create a computed image property called icon and enter the code that’s shown in Listing 2-4.

Listing 2-4.  Creating a Computed Property That Returns an Image

VB:
File: HelpDeskVBCommonUserCodeIssue.vb
  
Private Sub Icon_Compute(ByRef result As Byte())
    If TargetEndDateTime < DateTime.Now Then
        ' this string has been truncated for brevity
        Dim base64EncodedImage = "/9j/4A..."
        result = Convert.FromBase64String(base64EncodedImage)
    Else
        result = Nothing
    End If
End Sub
  
C#:
File: HelpDeskCSCommonUserCodeIssue.cs
  
partial void Icon_Compute(ref byte[] result)
{
    if (TargetEndDateTime < DateTime.Now)
    {
        // this string has been truncated for brevity
        string base64EncodedImage = @"/9j/4A...";
        result = Convert.FromBase64String(base64EncodedImage);
    }
    else
    {
        result = null;
    }
}

The computed property returns a hardcoded, base 64 encoded image. There are various web sites that allow you to upload an image and to find out its base 64 encoding. Alternatively, you can retrieve the image from a table, and Chapter 4 shows you how to access data from code. Figure 2-20 shows an example of how this computed property appears on a data grid.

Sorting and Filtering by Computed Properties

A slight limitation of computed properties is that you can’t sort or filter by these fields. If you create a query that uses a table with computed properties, these fields will not appear in the drop-down list of available fields when you’re creating a filter or sort.

If you need to sort grids by computed properties, a possible workaround is to use a SQL Server computed field rather than a LightSwitch computed property. This approach will work only with data in an external SQL data source. Furthermore, SQL Server computed columns are less powerful because you can’t refer to data in other tables.

If you want to sort a table by a computed value, you can do this by creating a query and writing code in the PreProcessQuery method. Chapter 6 shows you how to do this.