Welcome to Part 1 of the Beginning LightSwitch in Visual Studio 2012 series! To get things started, we’re going to begin with one of the most important building blocks of a LightSwitch application, the table. Simply put, a table is a way of organizing data in columns and rows. If you’ve ever used Excel or another spreadsheet application you organize your data in rows where each column represents a field of a specific type of data you are collecting. For instance, here’s a table of customer data:

Customer table.

LastName

FirstName

BirthDate

Doe

Jane

10/20/1972

Smith

John

11/12/1956

When you work with databases, the data is stored in a series of tables this way. You then create relationships between tables to navigate through your data properly. We’ll talk about relationships in the next post. For this post let’s concentrate on how to create and work with tables in LightSwitch.

Tables (Entities) in LightSwitch

Applications you build with LightSwitch are data-centric applications that provide user interfaces for viewing, adding, and modifying data. LightSwitch simplifies the development of these applications by using screens and tables. Because LightSwitch can work with other external data sources that do not necessarily have to come from a database, we sometimes call tables “Data entities” or just “entities” in LightSwitch. So whether you have a table in a database or a list in SharePoint, both the table and the list are entities in LightSwitch. Similarly, a field in a table or a column in a list is referred to as a “property” of the entity.

Entities are how LightSwitch represents data and are necessary to assemble an application. You create these data entities by using the built-in application database, or by importing data from an external database, OData service, a SharePoint list, or other data source. When you create a new project in LightSwitch, you need to choose whether you want to attach to an existing data source or create a new table. If you choose to create a new table, LightSwitch will create it in the built-in database, also referred to as the intrinsic database. You then design the table using the Data Designer.

When you create tables and relate them together you are designing a data model, or schema. Describing your data this way takes some practice if you’ve never done it before, however, you will see that it’s pretty intuitive using LightSwitch. The better you are at describing your data model, the more LightSwitch can do for you when you create screens later.

The LightSwitch Data Designer

The Data Designer is where all your data modeling happens in LightSwitch whether you’re attaching to an existing data source or creating a new database. By using the Data Designer, you can define properties on your entities and create relationships between them. LightSwitch handles many typical data management tasks such as field validation, transaction processing, and concurrency conflict resolution for you but you can also customize these tasks by modifying properties in the Properties window, and/or by writing code to override or extend them.

Creating a “Contact” Entity

Let’s walk through a concrete example of creating an entity. Suppose we want to create an application that manages contacts, like an address book. We need to create an entity that stores the contact data. First open Visual Studio 2012 (Professional or higher) and create a new LightSwitch project called ContactManager.

After you click OK on the New Project dialog, the LightSwitch home page will ask you if you want to create a new table or attach to an external data source.

Click “Create new table” and this will open the Data Designer. Now you can start describing the contact entity. Your cursor will be sitting in the title bar of the entity window when it opens. Name it “Contact” and hit the Enter key.

Once you do this you will see “Contacts” in the Solution Explorer under the ApplicationData node in the Data Sources folder. ApplicationData represents the intrinsic database that LightSwitch creates for you. Contacts refers to the table in the database that stores all the contact rows (or records). You can also think of this as a collection of entities, that’s why LightSwitch makes it plural for you.

Now we need to start defining properties on our entity, which correlates to the columns (or fields) on the table. You should notice at this point that the Contact entity has a property called “Id” that you cannot modify. This is an internal field that represents a unique key to the particular row of data. When you model tables in a database, each row in the table has to have a unique key so that a particular row can be located in the table. This Id is called a primary key as indicated by the picture of the key on the left of the property name. It is always required, unique, and is stored as an integer. LightSwitch handles managing primary keys automatically for you.

So we now need to think about what properties we want to capture for a contact. We also will need to determine how the data should be stored by specifying the type and whether a value is required or not. I’ve chosen to store the following pieces of data: LastName, FirstName, BirthDate, Gender, Phone, Email, Address1, Address2, City, State and ZIP. Additionally, only the LastName is required so that the user is not forced to enter the other values.

Also notice that I selected types that most closely match the type of data I want to store. For Phone and Email I selected the “Phone Number” and “Email Address” types. These business types give you built-in validation and editors on the screens. The data is still stored in the underlying table as strings, but is formatted and validated on the screen automatically for you. Validation of user input is important for keeping your data consistent. From the Properties window you can configure rules like required values, maximum lengths of string properties, number ranges for numeric properties, date ranges for date properties, as well as other settings. You can also write your own custom validation code if you need.

If you don’t see the Properties window hit F4 to open it. Select a property on the entity and you will see the related settings you can configure for it. Depending on the type of data you chose for the property, you will see different settings. All properties have an “Appearance” section in the property window that allow you specify the Display Name that will appear in field labels on screens in the application. By default, if you use upper camel case (a.k.a Pascal case) for your entity property names then LightSwitch will put a space between the phrases. For instance, the Display Name for the “LastName” property will become “Last Name” automatically. So it’s best practice to use this casing for your entity properties.

You can also enter a “Description” for properties when their names aren’t intuitive enough for the user, or you just want to display a standard help message. The Description is displayed on screens as a Tooltip when the user hovers their mouse over the data entry control on any screen displaying that field.

Settings you make here in the Data Designer affect all the screens in the application. Although you can make additional customizations on particular screens if needed, you will spend the bulk of your time configuring your data model here in the Data Designer. That way, you don’t have to configure settings every time you create a new screen. The better you can model your entities, the more LightSwitch can do for you automatically when creating the user interface.

For the Contact entity let’s set a few additional settings. First, select the Id field and in the Appearance section, uncheck “Display by default”. This makes it so that the property doesn’t show up anywhere in the user interface. As mentioned earlier, the primary key is an internal field used to locate a row in the table and isn’t modifiable so the user does not need to see it on any screens in the application.

For BirthDate, set the minimum value to 1/1/1900 so that users can’t enter dates before that. Also when we’re viewing the birthdate, we want to display the date on the screen in it’s long format, i.e. “Monday, June 15, 1984”. You can specify formatting for numbers and dates in LightSwitch directly on the properties window. In our case, specifying “D” as the format pattern will display the long date. To see this date format you change the Date control to a Label or a Text Box control in the screen designer.

For Gender, we want to display a fixed set of static values to the user: “Female”,“Male”. In order to do this in LightSwitch we can use a Choice List. Click on “Choice List…” on the Properties window and this will open a window that will let you define the values that are stored in the table and the display name you want the user to see. For our purposes, we just want to store an “F” or “M'” in the underlying database table. Therefore, also set the Maximum Length to 1.

By default, maximum lengths of strings are set to 255 characters and should handle most cases, but you can change this for your needs.

Using the Properties window you can also configure settings on the entity itself. Select the title bar of the Contact entity and notice that there is a setting called Summary Property. Summary properties are used to “describe” your entity and are used by LightSwitch to determine what to display when a row of data is represented on a screen. By default, LightSwitch selects the first string property you defined on your entity but you can change that here.

You can also create computed properties to use as the summary property when you want to format values or display values from multiple fields.

Testing the Contact Entity

Now that we have the Contact entity designed, let’s quickly test it out by creating a screen. At the top of the Data Designer click the “Screen…” button to open the Add New Screen dialog. We’ll talk more about screens in a future post but for now just select the List and Details screen. Then drop down the Screen Data and select Contacts and then click OK.

To build and launch the application hit F5. Now you can enter information into the contact table using this screen. Click the “+” button on the top of the list box to add new contacts.

Notice that the labels are displayed properly with spaces and the Last Name is bolded to indicate it’s a required field. Also if you enter invalid data as specified by the settings we made, a validation error will be displayed. When you are done, click the Save button at the bottom of the screen. This will save the data back into your development database. This is just test data stored in your internal database while you develop the application. Real data doesn’t go into the system until you deploy the application to your users.

In the next post we’ll talk about relationships and build upon our data model. Until next time!

What if I want to have more than one independent unique fields in a table?

how to

21 Nov 2012 11:34 PM

Error: this template attempted to load component assembly ‘Microsoft.LightSwitch.Design.VSTemplateWizard, Version=10.0.0.0, Culture=neutral, PublickKeyToken=b03f5f7f11d50a4a', For more information on this problem and how to enable this template, please see documentation on Customising Project Template.

Joseph

31 Oct 2013 11:22 PM

I have already bookmarked all your LightSwitch articles. Great articles...!!!