Beginning LightSwitch Part 1: What’s in a Table? Describing Your Data

NOTE: This is the Visual Studio 2010 version of the popular Beginning LightSwitch article series. For other versions see:

• Visual Studio 2013 & HTML Client: Part 1: What’s in a Table? Describing Your Data
• Visual Studio 2012: Part 1: What’s in a Table? Describing Your Data

Welcome to Part 1 of the Beginning LightSwitch 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 forms-over-data 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, 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.

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?

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 LightSwitch and create a new 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.

For more information on validation rules see: Common Validation Rules in LightSwitch Business Applications

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.

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.

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 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 on the ribbon at the top left of the application shell. 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!

Go to next article –> Part 2: Feel the Love - Defining Data Relationships

Comments

• Anonymous
  December 19, 2011
  This is unreadable on android phone :/

• Anonymous
  December 19, 2011
  Sorry Przemek, I'll see if there is a way to tweak the MSDN blog template. FWIW, it looks great on my Windows Phone 7 ;-)

• Anonymous
  December 19, 2011
  hi Beth, its not rendering on Chrome properly too :) .. have to scroll all the way down till the "archive" list on the right finishes and then the main content starts. But just to make sure, I checked another blog on msdn, its blogs.msdn.com/.../windows-server-appfabric-logging.aspx, and this post is just like yours, text with lots of screenshots, and renders fine on Chrome. Dont know however whats wrong in this entry. This post renders fine on IE, and also checked on my Nokia N900's microb browser (ff derived), renders just as good as IE on the mobile device. Strange. But anyway, nice post!

• Anonymous
  December 22, 2011
  Hi guys, I think I fixed the Chrome issue, I am using a slightly different blog template. Let me know if you are still seeing issues.

• Anonymous
  December 24, 2011
  Hey Beth, First of All Merry Christmas. I love your Vids, they are Sooo helpful. Quick Question. Is there any real advantage or disadvantage of using the Internal Lightswitch Database vs say SQL Server as an External Source.

• Anonymous
  January 03, 2012
  Hi Gary, Happy New Year! If you need to create the database for your LightSwitch application I would recommend doing it through the LightSwitch development environment. It's easier to model all in one tool instead of having to update the schema manually and then importing the changes into LightSwitch. I think you'll find the iterative development a lot faster this way. LightSwitch can also do a lot more for you on deployment by creating the database update scripts for you. HTH, -Beth

• Anonymous
  January 05, 2012
  I am totally impressed with the RAD of Lightswitch and the .net environment. How do you compare it to MS Access? I have developed solid applications geared for small to medium size businesses for many yeas using MS Access from 97 through 2010 having from 1 to about 20 concurrent users without a problem. In 2010 with the macro development and Sharepoint, I started to wonder about the future of MS Access for cloud development. Lightswitch seems to be the upcoming solution ...

• Anonymous
  January 08, 2012
  Hi, watched ur WINDOWS FORMS OVER DATA vidoe series and it's great. Am ai Sierra Leone and the internet speed is preety slow. Can u show me a site that i can get visual basic tutorials.Thanks

• Anonymous
  January 10, 2012
  Hi Beth, is there a reason why i can´t set a choice list when i have a field of type byte or is it a bug? At all other datatypes it seems to work except byte. Stephan

• Anonymous
  January 12, 2012
  Beth, how do you compare the future of MS ACCESS 2010 and the changes implemented to be web compatible, to the future of Lightswitch and .net? I have loved working with MS Access for many, many years, but seem to always be convincing users and IT people that it is a solid program, when used and developed properly. Using MS Access and SQL works very well. Love it. But I have started to wonder ... I can see how Lighswitch can be a tool for RAD for small databases which need to be web driven. Would love to know your opinion. I have been watching your videos and you understand database development issues very, very well.

• Anonymous
  January 12, 2012
  The comment has been removed

• Anonymous
  February 21, 2012
  Beth: I would like to track the username who added a record in a Lightswitch application which uses Windows Authentication, as well as the date a record was added. What is the best way to do this? Thanks!

• Anonymous
  March 05, 2012
  Beth, Is it possible to copy a table from one Lightswitch project to another? Regards, Bud

• Anonymous
  March 30, 2012
  @Lisa - See this post on how to create a simple audit trail: blogs.msdn.com/.../how-to-create-a-simple-audit-trail-change-log-in-lightswitch.aspx @Bud - Unfortunately, no you cannot copy a table from one project to another. However others have requested this in regard to screens. You can create a suggestion for this here: visualstudio.uservoice.com/.../127959-visual-studio-lightswitch Thanks!

• Anonymous
  May 17, 2012
  Hi, Available programs to modify the LastName of Display Nmae? I need to be modified for other languages​​?

• Anonymous
  May 28, 2012
  After following these first simple steps, F5 or debug fails with a connection string error. I thought this was intrinsic and wasn't reaching out to a local SQL instance? So how do you set the connection string?

• Anonymous
  September 11, 2012
  Very good tutorials. Congratulations, Beth Massi!

• Anonymous
  January 23, 2013
  Great tute, Beth! Wish I discovered LightSwitch so much earlier in my career.  8-) ~ Scott