Beginning LightSwitch in VS 2013 Part 1: What’s in a Table? Describing Your Data
NOTE: This is the Visual Studio 2013 update of the popular Beginning LightSwitch article series. For previous versions see:
- Visual Studio 2012: Part 1: What’s in a Table? Describing Your Data
- Visual Studio 2010: Part 1: What's in a Table? Describing your Data
Welcome to Part 1 of the Beginning LightSwitch in Visual Studio 2013 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.
Not only is LightSwitch managing the underlying database tables for you as you model entities, it is also is creating a service layer automatically that can expose data via the OData protocol. This allows other business systems and external clients (like Excel for instance) to connect to your data easily and securely. Any business logic and user permissions that you have written on your entities will execute as well, no matter what client is accessing the services.
For a tour of the Data Designer, see Data: The Information Behind Your Application
For a video demonstration on how to use the Data Designer, see: How Do I: Define My Data in a LightSwitch Application?
For more information on OData services in LightSwitch see: Creating and Consuming LightSwitch OData Services.
For more information on the overall architecture of a LightSwitch application see: Exploring LightSwitch Architecture
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 2013 (Professional or higher). Then select your language of choice, Visual Basic or C#, then select the LightSwitch node and choose the type of application you want to build. For this series we will build an HTML application. (If you want to build a desktop Silverlight application, see the 2012 series.) Name the project 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 (internal) 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 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 perspective as indicated at the bottom of the designer. The Server perspective allows you to configure the storage and validation properties as well as the default display name of the field.
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.
If you are supporting multiple languages then you will use the Display Name to set the resource identifier instead. For more information, see this Walkthrough: Localizing a LightSwitch Application
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.
You could also set a maximum value here, but that would hard-code a static value in the validation check. Instead, we probably want to check the value dynamically in code. In fact, it’s going to be very common to write snippets of code to perform common validations on data. For instance, what if we want to make sure that the user doesn’t enter a date in the future? Click on the “Custom Validation” link in the properties window and provide the code to do the check. This check always runs on the server any time a contact is being saved.
VB:
Private Sub BirthDate_Validate(results As EntityValidationResultsBuilder)
'Write code here:
If Me.BirthDate.HasValue AndAlso Me.BirthDate > DateTime.Today Then
results.AddPropertyError("Birthdate cannot be in the future.")
End If
End Sub
C#:
partial void BirthDate_Validate(EntityValidationResultsBuilder results)
{
//Write code here:
if (this.BirthDate.HasValue && this.BirthDate > DateTime.Today)
{
results.AddPropertyError("Birthdate cannot be in the future.");
}
}
For more information on validation rules see: Common Validation Rules in LightSwitch Business Applications
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. A choice list is appropriate for choices that are always static and relatively small like gender in this case or “Yes/No” values. If your list of choices is dynamic (or is a very large list) then you should create a table to hold the lookup values and then relate that to your master table via a many-to-one relationship. This will cause LightSwitch to automatically create a picker list for you on screens. More on relationships in the next post.
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. (Tip: If you want to store a string as varchar(max) in the database, just erase the 255 value so the field remains blank.)
Using the Properties window you can also configure settings on the entity itself. Select the title bar of the Contact entity in the Server perspective. You’ll notice a checkbox on in the properties window that defaults to “Enable Created/Modified properties”. When checked, this tells LightSwitch to automatically track when the record was created or modified and by who. This will automatically add four fields to the table: Created (DateTime), CreatedBy (String), Updated (DateTime) and UpdatedBy (String). The fields are not shown in the Data Designer, but will appear in the Screen Designer so you can choose whether to display them or not.
Now switch to the Client Perspective. 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.
For more information on Summary Properties see: Getting the Most out of LightSwitch Summary Properties
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 Browse Data screen. Then drop down the Screen Data and select Contacts and then click OK.
This will open the screen designer with a default layout which shows the contacts in a simple list. We’ll talk more about how to customize screens later. For now, let’s add some adding & editing capabilities to this app. Expand the Command Bar node, Click Add… to add a new button, then choose an existing method: addAndEditNew.
Notice that LightSwitch provides predefined actions to automatically interact with our entities. We’ll dive more into these commands later.
When we select addAndEditNew command we need to also specify the screen to navigate to. LightSwitch detects we need a new screen so just click OK. This will open up the Add New Screen dialog again with all the selections made for us. Click OK to add the new AddEditContact screen to our app.
We can also wire up a tap event so that when a user touches/clicks a contact in the list, they can also edit the details as well. Flip back to the BrowseContact screen, select the Contacts list, and in the Properties window click the Tap action.
Choose an existing method but this time select editSelected. LightSwitch will notice that we already have a screen that can do this and automatically fills that in for us. Click OK.
To build and launch the application hit F5. Now you can enter information into the contact table using this screen. Click the Add Contact button on bottom of the screen to add new contacts.
Notice that if we do not enter a Last Name an error will display on the screen indicating it’s a required field. Also if you enter invalid data as specified by the settings we made, a validation error will also be displayed. Also notice that as you resize your screen, LightSwitch will automatically resize the layout for you. This responsive design makes it so that the app will display nicely on tablets, phones, and any modern device that supports HTML5.
For more information on the user experience LightSwitch provides with its HTML client see: A New User Experience
When you are done editing, click the Save button at the top of the dialog. 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!
Enjoy!
Read the next article ---> Beginning LightSwitch in VS 2013 Part 2: Feel the Love - Defining Data Relationships
Comments
Anonymous
October 16, 2013
GreatAnonymous
October 16, 2013
Well done!!You're making the creation of my in-class course manual way too easy ;-)Anonymous
October 18, 2013
Thanks Beth!Anonymous
October 19, 2013
I was working with your example and this.BirthDay.HasValue errored out. Stated that system.DateTime does not contain definition for HasValue. Did you add a difference reference other than the defaults?AlohaAnonymous
October 20, 2013
Link Broken?Should the "Common Validation Rules in LightSwitch Business Applications" linkBe pointing here? blogs.msdn.com/.../common-validation-rules-in-lightswitch-business-applications.aspxAnonymous
October 21, 2013
@Kevin : make sure the Required checkbox is checked off in the Entity designer, so a Nullable<DateTime> property will be generated instead of a simple DateTime property.Anonymous
October 21, 2013
@Kevin -- like Jan said - if you make the BirthDate not required, then the value will be a Nullable Boolean and it will have the .HasValue method.@Steve -- thanks! fixed.Anonymous
October 21, 2013
Thanks Jan and Beth...Aloha,KevinAnonymous
October 27, 2013
Great !Anonymous
November 08, 2013
The comment has been removedAnonymous
November 13, 2013
The link to 'Getting the most out of Summary Properties' is brokenAnonymous
February 26, 2014
This is most likely a dumb question but if you have more than 1 person with the same last name how do you tell them apart?Thank you.Anonymous
February 27, 2014
@Marc -- You use the screen designer to lay out fields exactly how you want, for instance, adding the first name :-). If you want to skip ahead read part 3 where we really dig in and design the screens.Anonymous
March 02, 2014
Hi Beth, first of all, thanks for your great tutorials! I have a question, I have added a datasource to some SQL Server tables. They are properly show, but: some fields are shown as 'Required', both on Server as well as on HTMLClient side (both in Lightswitch). But they are not marked as 'required' in the database. Do you know what is went wrong and how to fix this, because this really frustrates me actually... Thanks for your help!Anonymous
March 03, 2014
PerfectthanksAnonymous
March 03, 2014
Hi Beth, ignore my previous question, I've sorted it out. I made a mistake :(Anonymous
April 04, 2014
Hi BethHow can I make the 'Client' perspective looks the same as 'Server' perspective?I have one table in which the order of the fields are diferent, between both perspectives.Anonymous
April 04, 2014
I just restarted the IDE and it works. Thanks.Anonymous
April 15, 2014
Thanks Beth. Very nicely done. You explain easily that bring a lot of substance as we go along. One note... When you mention, "Now switch to the Client perspective." I had no idea what you were talking about being the LightSwitch newbie that I am. It took me a minute or so to realize that I should change from "Server" to "HTML Client" at the bottom of the data designer screen. Once I found what I was supposed to be doing, it made perfect sense. I just didn't know where to go to change the perspective. That is the only moment I was slightly stymied by the presentation.Excellent job and I am going forward to the next chapter. So much to learn!Anonymous
August 14, 2014
The comment has been removedAnonymous
September 11, 2014
when i define a choice list to a existing record and play the screen the old record goes away living me with the pick up list. how can i keep the old record and have the pickup list at the same time? For eg. My Table has 2 fields.1 is Product and 2nd is Product Status. I have named the product has Apple and Status has YES. Now i want to define a choice list as: YES, No, NA. When i play it, i see that the Product Status is no longer shows YES (old entry) but shows blank with pickup list. (This is desktop project)Anonymous
September 28, 2014
Terrific ... thanks Beth. This new version of LightSwitch is great.Anonymous
March 30, 2015
Im creating an app and i need to create a table to input data valid for the same. How can that be done????Anonymous
July 17, 2015
Thanks for the write up. When I run (debug) the app it lets me enter a new contact just fine. If I click an existing contact it opens the Add/Edit screen but the fields are blank. it is not letting me edit a contact. can you tell me what I have missed?Anonymous
October 01, 2015
how do you generate the employeeID?Anonymous
December 01, 2015
when ur next post will come.????...hurryyyy....pls...Anonymous
March 31, 2016
Menos mal que he dado con esta información. No sabe el tiempo que llevaba buscándola. Gracias. Abrazos!!!Anonymous
August 14, 2016
Hey There. I discovered your blog using msn. This is a reall neatly written article.I'll make sure to bookmark it and return to learn more of your helpful information. Thank you for the post.I'll certainly return.Anonymous
August 20, 2016
Generally I don't read post on blogs, however Iwish to say that this write-up very compelled me to take a look aat aand do it! Your writing taste has been amazed me.Thanks, very nice article.Anonymous
September 28, 2016
The comment has been removedAnonymous
November 23, 2016
Pretty! This was an extremely wonderful article.Many thanks for providing this information.