Some folks have been asking for this so I just released the completed Address Book sample that you build step-by-step in the Beginning LightSwitch Article Series.
Are you completely new to LightSwitch and software development? Read this entry-level article series to get started with the most important concepts you need to build any LightSwitch application.
Visit the LightSwitch Developer Center to download LightSwitch and get started learning.
Download Visual Studio LightSwitch
Watch the instructional LightSwitch "How Do I?" videos
Get essential training
Ask questions in the LightSwitch forums
Enjoy!
I had a reader ask a question this week on how to use Visual Studio LightSwitch to open different Edit Detail screens based on the “type” of record. This is a very common thing to do especially for data models that use table inheritance (or sub-tables). For instance say we are building an application for a pet store to manage their inventory of animals. We need to collect shared properties of all the animals in an Animal table but we also want to set up table inheritance to Fish, Cat and Dog tables which collect specific properties of those types of animals. When a user selects an animal on a search screen, we need to display a screen that lets them work with the correct data from the specific table. LightSwitch provides a “Show as Link” on labels which automatically opens up the default details screen for that entity, but in our case we could have multiple screens. There are also a few techniques you need to know when dealing with these types of relationships in LightSwitch, particularly around the insert pipeline. In this post I will show you how you can achieve this functionality.
First let’s start by creating a simple inheritance data model for the pet store. We will build an Animal table that collects general properties of any animal in the store. Then we will build Cat, Dog and Fish tables to collect specific properties of those types of animals. The relationship between the Animal table and the others will be a ‘one to zero or one’ relationship.
For this example, the Animal table has two required properties, Name and Type. Type is a Choice List with the static values “Cat”, “Dog”, “Fish” to correspond to the type of record the animal is. We will use this to drive what records are inserted into which specific table as well as use it to determine which screens to display.
Next, create the Cat, Dog and Fish tables to collect properties specific to these types of animals.
Finally, set up the relationships from Animal to these tables as a “one to zero or one” relationship with a delete behavior of “Cascade Delete”.
Our data model now looks like this.
Next we’ll need to enter new animals into the system. Create a New Data Screen and select Animal as the screen data. When you do this, LightSwitch will include all the one-to-one related tables as fields on the screen. However, we only want to require the user to enter the general Animal properties on this screen. Once they save, we will direct them to the correct Cat, Dog or Fish detail screen. So first delete all the items in the content tree of the Screen Designer except for Name and Type.
Next, create an Edit Details Screen and select the Animal screen data again. Name the screen CatDetail and uncheck “Use as Default Details Screen”.
Again you will see that LightSwitch reads these special relationships and adds all the fields from all four tables to the screen. We will need to make some changes. First, change the “Type” from an Auto Complete box to a Label. Once the user has entered the type of animal on the New Data screen, it cannot be changed. Next, delete all the fields related to Dog and Fish from the screen.
Repeat the same process to create the DogDetail and FishDetail screens, removing the fields from the screen that do not belong.
Finally, add a Search Data Screen and select the Animal as the screen data again. Select the “Name” content item and in the properties window uncheck “Show as Link”.
Instead of using the built-in “Show as Link” functionality, we need to create our own command. We can put commands anywhere on the screen and we can show them as buttons or links. (For more information see: “I Command You!” - LightSwitch Screen Commands Tips & Tricks) For instance we could put an edit button on the screen command bar, the grid command bar, or even in the row itself. Let’s add a command into the row itself.
Right-click on the Data Grid Row Command Bar in the content tree and select “Add Button…”. Create a new method called “Edit”.
We want to show the command as a link instead of a button so select the button in the content tree and change it to a link.
Now we need to write some code to pull this all together. Right-click on the button we just created and select “Edit Execute Code” to open the code editor. Here we will check what type of animal we have to open the corresponding screen.
Private Sub Edit_Execute() If Me.Animals.SelectedItem IsNot Nothing Then Select Case Me.Animals.SelectedItem.Type Case "Cat" Me.Application.ShowCatDetail(Me.Animals.SelectedItem.Id) Case "Dog" Me.Application.ShowDogDetail(Me.Animals.SelectedItem.Id) Case "Fish" Me.Application.ShowFishDetail(Me.Animals.SelectedItem.Id) End Select End If End Sub
Next we need to write similar code on the New Data screen so that it opens the correct detail screen after the user saves a new animal record. Open the CreateNewAnimal screen and in the upper right drop down the “Write Code” button and select the CreateNewAnimal_Saved method. First comment out the call to ShowDefaultScreen and then write code to determine which screen we need to show instead:
Private Sub CreateNewAnimal_Saved() Me.Close(False) 'Application.Current.ShowDefaultScreen(Me.AnimalProperty) Select Case Me.AnimalProperty.Type Case "Cat" Me.Application.ShowCatDetail(Me.AnimalProperty.Id) Case "Dog" Me.Application.ShowDogDetail(Me.AnimalProperty.Id) Case "Fish" Me.Application.ShowFishDetail(Me.AnimalProperty.Id) End Select End Sub
Finally, we need to implement a business rule on Animal in the save pipeline. This rule is important so that a new record is added to the correct table based on the animal’s type. That way after the user saves the CreateNewAnimal screen, a record will be written to the correct table and the detail screen will open and allow the user to edit the specific fields in the Cat, Dog or Fish table. Double-click on Animals in the Solution Explorer under the Data Sources/ApplicationData node to open the Data Designer. Drop down the Write Code button and select the Animals_Inserting method and write this code:
Private Sub Animals_Inserting(entity As Animal) Select Case entity.Type Case "Cat" If entity.Cat Is Nothing Then entity.Cat = New Cat() End If Case "Dog" If entity.Dog Is Nothing Then entity.Dog = New Dog() End If Case "Fish" If entity.Fish Is Nothing Then entity.Fish = New Fish() End If End Select End Sub
Enter new animals on the Create New Animal screen and when you click Save, the correct detail screen will open allowing you to enter more specific details based on the type of animal.
On our search screen, we can click the Edit link on any of the rows and it will open to the correct details screen as well.
I hope this post answered the reader’s question on how to open different detail screens depending on the record’s type. All it takes is implementing your own command. I also touched on one to one relationships and how to set up basic table inheritance. I encourage you to experiment more with this type of relationship. They come in particularly handy when you need to extend external data sources with additional properties or when you need to set up inheritance hierarchies. The trick is to get a record inserted into the right table when you insert the master / parent record. I’ll expand on a couple other techniques in a future post.
A common technique on data entry screens is using one “drop down list” (called an auto-complete box in LightSwitch) as a filter into the next. This limits the amount of choices that need to be brought down and guides the user into easily locating values. This technique is also useful if you have cascading filtered lists where the first selection filters data in the second, which filters data in the next, and so forth. LightSwitch makes this easy to do using parameterized queries and parameter binding. In this post let’s take a look a couple common scenarios.
Let’s take an example where we have a table of States and a child table of Cities. Cities are then selected on Customers when entering them into the system. So we have one-to-many relationships between State and Cities and City and Customers. Our data model looks like this:
When the user is entering new customers we don’t want to display thousands of cities in the dropdown to choose from. Although the users can use the auto-complete box feature to locate a city, bringing down all these records affects performance. It’s better to either use a Modal Window Picker search dialog (like I showed in this article) or present the list of States first and then filter the list of Cities down based on that selection.
First we need to create a couple queries. The first will simply sort the list of States so that they show up in alphabetical order in the list. Right-click on the States table in the Solution Explorer and Add Query to open the Query Designer. Create a query called “SortedStates” that sorts on the state’s Name Ascending:
Next create a query called “CitiesByState” by right-clicking on the Cities table in the Solution Explorer and selecting Add Query again. This time we will create a parameterized query: Where the State.Id property is equal to a new parameter called Id. The Query Designer should now look like this:
Now create the Customer Detail Screen like normal. Right-click on the Screens node and select “Add Screen…”, select the Edit Details Screen template then choose Customers for the screen data. The Screen Designer opens and all the fields in the Customer entity will be in the content tree. The City field is displayed as an auto-complete box.
Next we’ll need to add a data item to our screen for tracking the selected State. We will use this value to determine the filter on the City list so that users only see cities in the selected state. Click “Add Data Item” and add a Local Property of type State called SelectedState.
Next, drag the SelectedState over onto the content tree above the City. LightSwitch will automatically create an auto-complete box control for you.
Since we want to display the states sorted, next add the SortedStates query to the screen. Click “Add Data Item” again, this time select Query and choose SortedStates.
Then select the SelectedState auto-complete box in the content tree and on the Properties window, set the Choices property to SortedStates.
Next, add the CitiesByState query to the screen and set that as the Choices property of the Cities auto-complete box. Again, click “Add Data Item” and choose the CitiesByState query.
Then select the Cities auto-complete box and set the Choices property to this query.
Lastly we need to hook up the parameter binding. Select the Id parameter of the CitiesByState query and in the properties window set the Parameter Binding to SelectedState.Id. Once you do this a grey arrow on the left side will indicate the binding.
Once you set the value of a query parameter, LightSwitch will automatically execute the query for you so you don’t need to write any code for this. Hit F5 and see what you get. Notice that the Cities drop down list is empty until you select a State at which point it feeds the CitiesByState query and executes it. Also notice that if you make a city selection and then change the state, the selection is still displayed correctly, it doesn’t disappear. Just keep in mind that anytime a user changes the state, the city query is re-executed against the server.
One additional thing that you might want to do is to initially display the state to which the city belongs. As it is, the Selected State comes up blank when the screen is opened. This is because it is bound to a screen property which is not backed by data. However we can easily set the initial value of the SelectedState in code. Back in the Screen Designer drop down the “Write Code” button at the top right and select the InitializeDataWorkspace method and write the following:
Private Sub CustomerDetail_InitializeDataWorkspace(saveChangesTo As List(Of Microsoft.LightSwitch.IDataService)) ' Write your code here. If Me.Customer.City IsNot Nothing Then Me.SelectedState = Me.Customer.City.State End If End Sub
Now when you run the screen again, the Selected State will be displayed.
Another option is to create cascading lists based on a single table. For instance say we do not want to have a State table at all. Instead it may make more sense to store the State on the City. So our data model could be simplified by having just a City table related to many Customers.
This time when we create a parameterized query called CitesByState, we’ll set it up Where the State is equal to a new parameter called State.
On the screen, select “Add Data Item” to add the CitiesByState to the screen and set it as the Choices property of the City auto-complete box just like before.
This time, however, the State is the query parameter we need to bind. Add a string screen property to the screen to hold the selected state. Click “Add Data Item” again, add a required Local Property of type String and name it SelectedState.
Drag the SelectedState onto the content tree above the City. This time LightSwitch will create a textbox for us since this is just a local string property.
Finally, we need to set up the query parameter binding. Select the State query parameter and in the properties window set the Parameter Binding to SelectedState.
In order to set the SelectedState when the screen opens, the same code as before will work. Now when we run this, you will see a textbox that will filter the list of cities.
However, this may not be exactly what we want. If the user has a free-form text field then they could mistype a state code and the query would return no results. It would be better to present the states in a auto-complete box like before. Close the application and open the Screen Designer again. Select the SelectedState screen property. Notice in the properties window you can display this as a static list of values by creating a Choice List.
Enter the states that the user should select from and then run the application again. Now we get an auto-complete box like before. However, this approach leaves us having to define the choice list of states on every screen we want this functionality. The first approach using a State table solves this issue but there is also one other approach we could take to avoid having to create a separate table.
We could improve this situation by defining the choice list of states on the Customer entity. Then we would only have to define the lists of states in one place. Create a State property on the Customer using the Data Designer and on the properties window select Choice List and specify the list of states there.
Now add a new detail screen for Customer and you will see the State and City properties are set up as auto-complete boxes. Next add the CitiesByState query to the screen by clicking “Add Data Item” like before. We can use the same CitiesByState query as the previous example. Select the City auto-complete box and in the properties window set the Choices property to CitiesByState like before.
The difference is the query parameter binding. Select the State query parameter on the left and in the properties window set the Parameter Binding to Customer.State.
With this technique, you also do not need to write any code to set the initial selected State because we are now storing that value on the Customer record. Run the application again and the screen should behave the same. The only difference now is that we are storing the State on the Customer instead of a separate table.
This technique is the simplest to set up, so if you have a lot of Customer screens, then this may be the best choice for you if you have a static list of values like States. However if you have dynamic list of values then it’s better to store these in a separate table like the first technique showed.
For more information on filtering data and configuring lists see:
Happy New Year everyone! I hope you all had a wonderful holiday and enjoyed some well-deserved time off. I sure did! Now that I’m back in the office I’m a few days overdue for this post so here ya go! A few months ago I started posting a rollup of interesting community happenings, content, samples and extensions popping up around Visual Studio LightSwitch. If you missed those rollups you can check them out here:
Usually December is a pretty slow month all around, but there were still a lot of awesome things around LightSwitch, especially the number of community articles this month! Check them out…
Do you have what it takes to be a LightSwitch Star? Show us your coolest, most productive, LightSwitch business application and you could win a Laptop and other great prizes!
In November The Code Project launched the “LightSwitch Star” contest. You just answer some questions and email them a screenshot or two. They’re looking for apps that show off the most productivity in a business as well as apps that use extensions in a unique, innovative way. Prizes are given away each month (see here for more details on prizes). In early December they announced the November winners and today they announced the December winners in each category:
Congrats to all! Can’t wait to see what people submit in January. There were some really cool applications submitted in December. Here’s a breakdown of what was submitted last month (see all 23 apps that have been submitted so far here). Make sure to vote for your favorite ones, grand prize is a laptop!
7 More Production Apps…
3 More Tutorials/Samples/Videos…
ComponentOne released another LightSwitch extension last month in addition to the OLAP Control. This ready-to-use LightSwitch screen gives you a complete Outlook-style scheduling application! Check it out:
Here’s some more of the fun things the team and community released in December.
Build your own extensions by visiting the LightSwitch Extensibility page on the LightSwitch Developer Center.
In December I kicked off a series aimed at beginner developers just getting started with LightSwitch and was featured in the MSDN Flash Newsletter. This brought A LOT of traffic to my blog – in fact, it is at an all-time high since the month of December! WOW! This series is popular!
Matt Thalman also wrote the following article that address a top request in the LightSwitch Forums: Creating a Custom Login Page for a LightSwitch Application
The Visual Studio LightSwitch Facebook Page has been increasing in activity thanks to you all. Become a fan! Have fun and interact with us on our wall. Check out the cool stories and resources.
Also here are some other places you can find the LightSwitch team:
LightSwitch MSDN Forums LightSwitch Developer Center LightSwitch Team Blog LightSwitch on Twitter (@VSLightSwitch, #VisualStudio #LightSwitch)
The community has been using the hash tag #LightSwitch on twitter when posting stuff so it’s easier for me to catch it (although this is a common English word so you may end up with a few weird ones ;-)). Join the conversation! And if I missed anything please add a comment to the bottom of this post and let us know!
Welcome to Part 6 of the Beginning LightSwitch series! In parts 1 thru 5 we built an Address Book application and learned all about the major building blocks of a Visual Studio LightSwitch application -- entities, relationships, screens, queries and user permissions. If you missed them:
In this post I want to talk about themes. Themes allow you to change the colors, fonts and styles of all the visual elements in the user interface. Now that our Address Book application is complete, we’re almost ready to get this in front of real users. But before we do, it would be really nice to apply a different look-and-feel to our application in order to make it stand out above the rest. Visual Studio LightSwitch comes with only one theme out of the box, but you can download more. In fact, there are all kinds of extensions you can download to enhance what LightSwitch can do out of the box, not just themes.
LightSwitch provides an entire extensibility framework so that professional developers can write extensions to enhance the LightSwitch development experience. Many third-party component vendors as well as the general community have released all sorts of extensions for LightSwitch. These include custom controls, business types, productivity libraries and designers, and of course themes. Check out some of the featured extensions from our partners. If you are a code-savvy, professional .NET developer with Visual Studio Professional or higher, then you can create your own extensions. For more information on creating extensions see the Extensibility section of the LightSwitch Developer Center.
Luckily, you don’t need to be a hardcore programmer to use extensions. They are easy to find and install. Just open the Extension Manager from the Tools menu.
The Extension Manager will come up and display all your installed extensions. Select the “Online Gallery” tab to choose from all the LightSwitch extensions from the Visual Studio Gallery. (Note: if you have Visual Studio Professional or higher and not just the LightSwitch edition installed, then you will need to filter the Extension Manager on “LightSwitch” to see those extensions.)
You can also download these extensions directly from the Visual Studio Gallery. Select the extension you want and click the download button to install.
For our Address Book application I’m going to apply the LightSwitch Metro Theme which is one of the most popular extensions (at the time of this writing) so it’s right at the top. Once you install the extension, you’ll need to restart Visual Studio LightSwitch. After extensions are installed, you need to enable them on a per-project basis. Open the project properties by right-clicking on the project in the Solution Explorer and select “Properties”. Then select the “Extensions” tab to enable the extension.
For our Address Book application, enable the Metro Theme extension. Also notice that there is the “Microsoft LightSwitch Extensions” also in this list which is always enabled and used in new projects. This is an extension that is included with the LightSwitch install and contains the business types Email Address, Phone Number, Money and Image that you can use when defining your data entities like we did in Part 1. You should never have to disable these.
Now that the theme extension is installed and enabled, you can apply the theme by selecting the “General Properties” tab and then choosing the LightSwitch Metro Theme.
Then just build and run (F5) the application to see how it looks!
For more information on the Metro Theme extension (and source code) see the LightSwitch Metro Theme Extension Sample.
Besides the Metro Theme, there are a lot of other nice looking themes available, some for free and some for a small fee. If you open the Extension Manager to the Online Gallery tab and enter the search term “theme” you will see a long list of them. Here are the top 5 most popular on the gallery (at the time of this writing):
Also check out Paul Patterson’s “Uber Theme Resource” which provides more screenshots and reviews of all the themes on the gallery!
As you can see it’s easy to download and enable themes in Visual Studio LightSwitch in order to change the look-and-feel of your business applications. LightSwitch provides an entire extensibility model that not only allows the community to create themes, but all kinds of extensions that enhance the LightSwitch development experience that you can take advantage of. If you’re a code-savvy developer that wants to create your own themes, head to the Extensibility section of the LightSwitch Developer Center to get set up and then read this walkthrough Creating a Theme Extension.
Well that wraps up what I planned for the Beginning LightSwitch Series! I hope you enjoyed it and I hope it has helped you get started building your own applications with Visual Studio LightSwitch. For more LightSwitch training please see the Learn section of the LightSwitch Developer Center. In particular, I recommend going through my “How Do I” videos next.
Now I’m going to go enjoy some well-earned Christmas vacation. I’ll be back in a couple weeks. Happy Holidays LightSwitch-ers! Enjoy!
Welcome to Part 5 of the Beginning LightSwitch series! In parts 1 thru 4 we learned about entities, relationships, screens and queries in Visual Studio LightSwitch. If you missed them:
In this post I want to talk about user permissions, also known as Access Control. In most business applications we need to limit what resources users can access in the system, usually because of different job function or role. For instance, only system administrators can add new users to the system. Certain data and screens in the application may be sensitive and should be restricted unless that user has rights to that part of the system. LightSwitch makes it easy to define user permissions and provides hooks on entities, screens and queries that allow you to check these permissions.
For a video demonstration on how to set up user permissions see: How Do I: Set Up Security to Control User Access to Parts of a Visual Studio LightSwitch Application?
There are two pieces of information LightSwitch applications need in order to determine which users have rights to what parts of the system. First, the system needs to verify the user accessing the application. This is called Authentication. In other words: “Prove you are who you say you are.” There are two supported types of authentication in LightSwitch; Windows and Forms.
Windows authentication means that the application trusts the user based on their Windows credentials. So once a user successfully logs into their Windows desktop, those credentials are automatically passed to the LightSwitch application. Forms authentication means that the application requests a username & password of its own, completely independent of any other credentials. So when you choose to use Forms authentication a login screen is presented to the user and they must type their username and password every time they want to access the application.
Once a user is authenticated, the application can determine access to parts of the system by reading their user permissions. This is called Authorization. In other words: “Now that I know who you are, here’s what you can do in the system.”
It all starts on the Access Control tab of the Project Properties. To open it, right-click on the name of your project in the Solution Explorer and select “Properties” from the menu.
Then select the Access Control tab to specify the type of authentication you want to employ as well as what user permissions you want to define.
By default, the application doesn’t have authentication enabled so here is where you select the type of authentication you want to use.
Using Forms authentication means you will be storing usernames and encrypted passwords inside the LightSwitch database. This type of authentication is appropriate for internet-based applications where users are not on the same network and you need to support other operating systems besides Windows.
Using Windows authentication is appropriate if all your users are on the same network/domain or workgroup, like in the case of an internal line-of-business application. This means that no passwords are stored by your LightSwitch application. Instead the Windows logon credentials are used and passed automatically to the application. This is a more secure and convenient option if you can use it. In this case you can also choose whether you want to set up specific users and roles or whether any authenticated user has access to the application.
The best way to think of the two options for Windows authentication are:
Next you define user permissions that you check in code in order to access resources (we’ll work through an example next). There is always a SecurityAdministration permission defined for you that is used by LightSwitch once you deploy the application. When you deploy, LightSwitch will create a single user with this permission which gives them access to the screens necessary to define the rest of the users and roles in the system. However, while debugging your application, LightSwitch doesn’t make you log in because this would be tedious to do every time you built and ran (F5) the application. So instead you can use the “Granted for debug” checkbox to indicate which sets of permissions should be turned on/off in the debug session.
Let’s walk through a concrete example by implementing some security in our Address Book (Contact Manager) application we’ve been building in this series.
Let’s start by selecting an authentication scheme. Since this application will be used by a small business to manage all their contacts, I’ll choose Windows authentication. I’ll also select “Allow any authenticated Windows user” into the system so that everyone in the company can search for and edit contacts by default. However, in order to add or delete contacts, users will need special permissions to do that.
So we need to create two new permissions. You can name the permissions whatever you want. You only see the name in code. When the system administrator sets up users and roles later, they will see the Display Name on the screen so be descriptive there. So add two permissions; CanAddContacts and CanDeleteContacts.
Next, leave the “Granted for debug” unchecked for both of those permissions so that we can test that they are working. When you leave this unchecked, the permission will not be granted. This allows us to easily test combinations of permissions while debugging. In order to see the Users and Roles screens while debugging you can enable it for SecurityAdministration. Now that we have these permissions set up here, we need to check them in code. As I mentioned, LightSwitch provides method hooks for you so you can write code when you need for all sorts of custom validation and business rules, including access control.
For more information on writing code in LightSwitch see the Working with Code topic on the LightSwitch Developer Center.
For more information on writing custom business rules see: Common Validation Rules in LightSwitch Business Applications
So in order to implement the security, we need to write a couple lines of code to check these permissions. LightSwitch provides access control methods on entities, screens and queries. To access these methods, drop down the “Write code” button on the top right of any designer and you will see an “Access Control Methods” section in the list. When you want to restrict viewing (reading), inserting (adding), editing or deleting entities, open the entity in the Data Designer and drop down the “Write code” button and select the appropriate access control method.
For this application, select the Contacts_CanDelete method and this will open the code editor to that method stub. All you need to do is write one line of code (in bold below) to check the CanDeleteContacts permission you set up:
Namespace LightSwitchApplication Public Class ApplicationDataService Private Sub Contacts_CanDelete(ByRef result As Boolean) 'Add this one line of code to verify the user has permission to delete contacts: result = Me.Application.User.HasPermission(Permissions.CanDeleteContacts) End Sub End Class End Namespace
Note: This code is Visual Basic. If you chose C# as your programming language when you created the ContactManager project in Part 1, you will need to write the code like this: result = this.Application.User.HasPermission(Permissions.CanDeleteContacts);
Now go back to the “Write Code” button on the designer and select Contacts_CanInsert and then similarly write the following line of code (in bold) to check the CanAddContacts permission:
Namespace LightSwitchApplication Public Class ApplicationDataService Private Sub Contacts_CanDelete(ByRef result As Boolean) 'Add this one line of code to verify the user has permission to delete contacts: result = Me.Application.User.HasPermission(Permissions.CanDeleteContacts) End Sub Private Sub Contacts_CanInsert(ByRef result As Boolean) 'Add this one line of code to verify the user has permission to add contacts: result = Me.Application.User.HasPermission(Permissions.CanAddContacts) End Sub End Class End Namespace
You may be wondering why we are checking these permissions in the entity instead of the screens. Checking permissions in the entity guarantees that no matter what screen the user is working with, the data actions are protected. It’s best practice remember to secure your entities first if you need to implement user permissions in your application. However, our application also has a “Create New Contact” screen and we don’t want to show this to the user on the menu if they do not have permission to add contacts to the system. If we forget to hide this screen from the menu, then the user would be able to open it and fill it out with data. However, when they click Save the Contacts_CanInsert method above will run and prevent the actual data from saving.
So in order to hide this screen from the navigation menu, we need to add one more check. Double-click the CreateNewContact screen in the Solution Explorer to open the Screen Designer. Drop down the “Write Code” button on the top right and you will see the one Access Control method available to you for screens:
Select the CreateNewContact_CanRun method and then write the following line of code (in bold):
Namespace LightSwitchApplication Public Class Application Private Sub CreateNewContact_CanRun(ByRef result As Boolean) 'Add this one line of code to verify the user has permission to run the screen: result = Me.User.HasPermission(Permissions.CanAddContacts) End Sub End Class End Namespace
Now we are ready to test the application so build and run by hitting F5. Because we didn’t grant the CanAddContacts and CanDeleteContacts permissions for debug you will notice first that the CreateNewContact screen is not showing up on the menu. The first screen that displays is the custom search screen we created in Part 4. If you click on a contact then the details screen will open which allows the user to edit that contact’s information. In order to see if we are restricted from deleting or adding new contacts let’s make a small change to the search screen. While the search screen is open click the “Design Screen” button at the top right to open the screen customization mode.
In the content tree on the left, expand the command bar under the Data Grid and add two commands; DeleteSelected and AddAndEditNew.
Click Save (ctrl+shift+S) to save and exit customization mode and notice that the commands are disabled. Since we do not have permission to add or delete contacts this behavior is correct. Also since we are checking these permissions at the entity level all screen commands behave appropriately with no additional code necessary.
If you go back to your project properties Access Control tab you can check off combinations of permissions you want to test and you will see all commands enable/disable appropriately.
Before we wrap up I want to quickly walk through the Users and Roles screens. When you enable the SecurityAdministration permission for debugging, these screens are available on the menu. However, keep in mind that the data you put into these screens isn’t used at debug time. It isn’t later until you deploy the application when these screens are used. So putting data into these screens is for demonstration purposes only. When your application is deployed the first time, LightSwitch will ask you for an administrator username & password that it deploys into the users table and grants the SecurityAdministration permission. That administrator can then enter the rest of the users into the system.
First you define roles and add the permissions you defined to that role using the Roles Screen.
Then you can add new users using the Users Screen and assign them the appropriate Roles.
As you can see defining and checking user permissions in Visual Studio LightSwitch is a simple but important task. Access control is a very common requirement in professional business applications and LightSwitch provides an easy to use framework for locking down all parts of the application through method hooks on entities, screens and queries. Once you deploy your application, the system administrator can start setting up users and roles to control access to the secure parts of your application.
For more information on user permissions and deploying applications see Working with User Permissions and Deploying LightSwitch Applications topics on the LightSwitch Developer Center.
In the next post we’ll look at themes and how to change the look and feel of your application by installing LightSwitch extensions. We’ll look at what’s available for free as well as some inexpensive ones that really look great. Until next time!
Go to next article –> I Feel Pretty! Customizing the "Look and Feel" with Themes
Welcome to Part 4 of the Beginning LightSwitch series! In part 1, 2 and 3 we learned about entities, relationships and screens in Visual Studio LightSwitch. If you missed them:
In this post I want to talk about queries. In real life a query is just a question. But when we talk about queries in the context of databases, we are referring to a query language used to request particular subsets of data from our database. You use queries to help users find the information they are looking for and focus them on the data needed for the task at hand. As your data grows, queries become extremely necessary to keep your application productive for users. Instead of searching an entire table one page at a time for the information you want, you use queries to narrow down the results to a manageable list. For example, if you want to know how many contacts live in California, you create a query that looks at the list of Contacts and checks the State in their Address.
If you’ve been following this article series, you actually already know how to execute queries in LightSwitch. In part 3 we built a Search Data Screen. This screen has a built-in search feature that allows users to type in search terms and return rows where any string field matches that term. In this post I want to show you how you can define your own queries using the Query Designer and how you can use them on screens.
The Query Designer helps you construct queries sent to the backend data source in order to retrieve the entities you want. You use the designer to create filter conditions and specify sorting options. A query in LightSwitch is based on an entity in your data model (for example, a Contact entity). A query can also be based on other queries so they can be built-up easily. For instance, if you define a query called SortedContacts that sorts Contacts by their LastName property, you can then use this query as the source of other queries that return Contacts. This avoids having to repeat filter and/or sort conditions that you may want to apply on every query.
For a tour of the Query Designer, see Queries: Retrieving Information from a Data Source
For a video demonstration on how to use the Query Designer, see: How Do I: Sort and Filter Data on a Screen in a LightSwitch Application?
Let’s walk through some concrete examples of creating queries in LightSwitch using the Contact Manager Address Book application we’ve been building. In part 3 we built a Search Data Screen for our Contacts, but if you notice the Contacts are not sorted when the screen is initially displayed. The user must click on the desired grid column to apply a sort manually. Once the user changes the sort order LightSwitch will remember that on a per-user basis, but we should really be sorting the Contacts for them when the Search screen is displayed the first time.
To create a query, in the Solution Explorer right-click on the entity you want to base it on (in our case Contacts) and choose “Add Query”.
The Query Designer will open and the first thing you do is name your query. We’ll name ours “SortedContacts”. Once you do this, you will see the query listed under the entity in the Solution Explorer.
Next we need to define the sort order so click “+Add Sort” in the Sort section of the designer then select the LastName property from the drop down. Click “+Add Sort” again and this time select the FirstName property. Leave the order Ascending for both.
Before we jump into Filter conditions and Parameters, let’s see how we can use this simple query on our Search Screen. It’s actually easier in this case to re-create the Search Screen we created in Part 3 because we didn’t make any modifications to the layout. So in the Solution Explorer select the SearchContacts screen and delete it. Then right-click on the Screens node and select “Add Screen…” to open the Add New Screen dialog.
Select the Search Data Screen template then for the Screen Data you will see the SortedCOntacts query. Choose that then click OK.
Hit F5 to build and run the application and notice this time the contacts are sorted in alphabetical order immediately.
Our Search Screen is in better shape now but what if we wanted to allow the user to find contacts who’s birth date falls within a specific range? Out of the box, LightSwitch will automatically search across all String properties on an entity but not Dates. Therefore, in order to allow the user the ability to search within a birth date range, we need to define our own query.
So let’s create a query that filters by date range but this time we will specify the Source of the query be the SortedContacts query. Right-click on the Contacts entity and choose “Add Query” to open the Query Designer again. Name the query “ContactsByBirthDate” and then select “SortedContacts” in the Source drop down on the top right of the designer.
Now the query is sorted but we need to add a filter condition. Defining filter conditions can take some practice (like designing a good data model) but LightSwitch tries to make it as easy as possible while still remaining powerful. You can specify fairly complex conditions and groupings in your filter, however the one we need to define isn’t too complex. When you need to find records within a range of values you will need 2 conditions. Once that checks records fall “above” the minimum value and one that checks records fall “below” the maximum value.
So in the Query Designer, click “+ Add Filter” and specify the condition like so:
Where
the BirthDate property
is greater than or equal to
a parameter.
Then select “Add New” to add a new parameter.
The parameter’s name will default to “BirthDate” so change it to MinimumBirthDate down in the Parameters section.
Similarly, add the filter condition for “Where the BirthDate property is less than or equal to a new parameter called MaximumBirthDate”. The Query Designer should now look like this:
One last thing we want to think about with respect to parameters is whether they should be required or not. Meaning must the user fill out the filter criteria parameters in order to execute the query? In this case, I don’t want to force the user to enter either one so we want to make them optional. You do that by selecting the parameter and checking “Is Optional” in the properties window.
Okay now let’s use this query for our Search Screen. In the Solution Explorer select the SearchSortedContacts screen and delete it. Then right-click on the Screens node and select “Add Screen…” to open the Add New Screen dialog again. Select the Search Data Screen template and for the Screen Data select the ContactsByBirthDate query and click OK.
Hit F5 to build and run the application. Notice the contacts are still sorted in alphabetical order on our search screen but you see additional fields at the top of the screen that let us specify the birth date range. LightSwitch sees that our query specified parameters so when we used it as the basis of the screen, the correct controls were automatically generated for us! And since both of these parameters are optional, users can enter none, one, or both dates and the query will automatically execute correctly based on that criteria.
As you can see using queries with parameters like this allows you to perform much more specialized searches than what is provided by default. When using queries as the basis of Screen Data, LightSwitch will automatically look at the query’s parameters and create the corresponding screen parameters and controls which saves you time.
For more information on creating custom search screens, see: Creating a Custom Search Screen in Visual Studio LightSwitch and How to Create a Screen with Multiple Search Parameters in LightSwitch
For a video demonstration, see: How Do I: Create Custom Search Screens in LightSwitch?
Before we wrap this up I want to touch on one more type of query. What if we wanted to allow the user to search Contacts by phone number? If you recall our data is modeled so that Contacts can have many Phone Numbers so they are stored in a separate related table. In order to query these, we need to base the query on the PhoneNumber entity, not Contact.
So right-click on the PhoneNumbers entity in the Solution Explorer and select “Add Query”. I’ll name it ContactsByPhone. Besides searching on the PhoneNumber I also want to allow users to search on the Contact’s LastName and FirstName. This is easy to do because the Query Designer will allow you to create conditions that filter on related parent tables, in this case the Contact. When you select the property, you can expand the Contact node and get at all the properties.
Where the Contact’s LastName property
contains
a parameter
The parameter’s name will default to “LastName” so change it to SearchTerm down in the Parameters section and make it optional by checking “Is Optional” in the properties window.
We’re going to use the same parameter for the rest of our conditions. This will allow the user to type their search criteria in one textbox and the query will search across all three fields for a match. So the next filter condition will be:
Or the Contact’s FirstName property contains the parameter of SearchTerm
And finally add the last filter condition:
Or the Phone property contains the parameter of SearchTerm. I’ll also add a Sort on the PhoneNumber Ascending. The Query Designer should now look like this:
Now it’s time to create a Search Screen for this query. Instead of deleting the other Search screen that filters by birth date range, I’m going to create another new search screen for this query. Another option would be to add the previous date range condition to this query, which would create a more complex query but would allow us to have one search screen that does it all. For this example let’s keep it simple, but here’s a hint on how you would construct the query by using a group:
For more information on writing complex queries see: Queries: Retrieving Information from a Data Source and How to Create Composed and Scalar Queries
So to add the new Search Screen right-click on the Screens node again and select “Add Screen…” to open the Add New Screen dialog. Select the Search Data Screen template and for the Screen Data select the ContactsByPhone query this time and click OK.
Now before we run this I want to make a small change to the screen. The default behavior of a search screen is to make the first column a link that opens the Details screen for that record. Since we had to base our query on the PhoneNumber entity, LightSwitch will make the Phone property a link and not the Contact. So in the Screen Designer we need to make a small change. Select the Phone in the content tree and in the properties window uncheck “Show as Link”. Then select the Contact and check “Show as Link”.
Also change Display Name in the properties window for the PhoneNumberSearchTerm textbox to “Phone Number or Name” to make it more clear to the user what they are filtering on. And while we’re at it, we should disable the default search box by selecting the ContactsByPhone query on the left-hand side and unchecking “Supports search” in the properties window. This isn’t necessary since we are providing more search functionality with our own query.
Okay hit F5 and let’s see what we get. Open the “Search Contact By Phone” Search screen from the navigation menu and now users can search for contacts by name or by phone number. When you click on the Contact link, the Details screen we created in part 3 will open automatically.
As you can see queries help narrow down the amount of data to just the information users need to get the task done. LightSwitch provides a simple, easy-to-use Query Designer that lets you base queries on entities as well as other queries. And the LightSwitch Screen Designer does all the heavy lifting for you when you base a screen on a query that uses parameters.
Writing good queries takes practice so I encourage you to work through the resources provided in the Working with Queries topic on the LightSwitch Developer Center.
In the next post we’ll look at user permissions and you will see how to write your first lines of LightSwitch code! Until next time!
Go to next article –> Part 5: May I? Controlling Access with User Permissions
Welcome to Part 3 of the Beginning LightSwitch series! In part 1 and part 2 we learned about entities and relationships in LightSwitch and how to use the Data Designer to define them. If you missed them:
In this post I want to talk about screens. Screens are a common term in everyday life usually denoting a television or computer screen – the shiny thing we look at to interact with the device. In LightSwitch screens present data to the user and form the majority of the application’s user interface (UI). The default LightSwitch application shell presents screens in the center of the application window in a tabbed display. At the top is a ribbon of commands Save and Refresh, and on the left is the navigation menu.
Screens also allow users to search for, edit, insert and delete data in the backend data sources. LightSwitch makes the creation of screens really simple by providing templates you can choose from. You can then use them as is, or customize them further for your needs. Once you have some entities defined (like you learned in the previous posts) then you are ready to create screens.
You can add a screen to your project by clicking the “+ Screens…” button at the top of the Data Designer or by right-clicking on the Screens folder in the Solution Explorer and selecting “Add Screen…”
When you do this the “Add New Screen” dialog appears which asks you to select a screen template as well as the data you want to display on the screen. There are five screen templates in Visual Studio LightSwitch; the Details Screen, Editable Grid Screen, List and Details Screen, New Data Screen, and Search Data Screen. Depending on the screen template you choose, your options in the “Screen Data” section on the right changes.
When you select the Screen Data drop down, you select the entity you want to use on the screen. On any screen template you choose, the controls are created in a similar way by looking at the entity definition. For instance, the labels for required properties appear as bold text. Also by default, screens use controls that match the underlying data types. So for instance, an editable String property is represented by a TextBox control, a Boolean becomes a CheckBox control, a Phone Number type gets the Phone Number control, etc. This is why it’s important to model your data correctly before creating screens. Although you can customize most of these things on a screen-by-screen basis, LightSwitch reads the settings in your data model to create smart defaults, speeding up the time it takes to create the application.
Let’s walk through this list of screen templates and some guidelines on why you would choose them.
You use the List and Details Screen when you want to see a list of all the records in a table and edit their details on one screen. By default, the screen contains two areas. On the left you will see a list that displays the summary of each record. On the right, it displays the details about the record that is selected. You can also choose to add any related entities to the screen and they will display below the detail controls in a grid. Users can use the search & sort functionality on the list to find records and then edit them all on one screen.
Use this template if you want to allow users to modify all the records in your table on one screen with no additional workflow. This can be particularly well suited for tables that have a smaller number of rows (records), like administration screens, and where only a few users would be modifying the data at the same time. Like all screens that work with multiple data rows, the query will return 45 rows of data at a time by default (you can change this on the screen properties). So it’s usually better to choose entities that don’t have too many or large fields so the application stays responsive as the database grows.
Similar to the List and Details Screen, you can also use the Editable Grid Screen when you want to modify all the records at the same time on one screen. In this case though, users are presented with a grid of data to edit in a tabular format. Users can press Tab to move from field to field to make changes and edit everything inline. You’ll want to choose this template if your users need to enter a lot of data quickly into a single table like maintenance and lookup tables or where you would only be editing a subset of fields so the user doesn’t need to scroll too much horizontally.
The Details Screen allows you to edit a single row of data from a table and optionally, any related data. When you click on a link or button to open a single record, the Details screen for that entity is displayed. LightSwitch generates a default details screen for you automatically at runtime if you do not create one yourself. Typically you will want to show all the editable properties for an entity on this screen as well as any related grids of data. This screen template has an additional option in the Add New Screen dialog. Once you select the Screen Data, you will see a checkbox “Use as Default Details Screen” which means to open this screen anytime the user opens a single record from this table anywhere in the system. This is also specified in the Data Designer for the “Default Screen” property for the entity.
The New Data Screen allows you to insert new rows into a table one at a time. By default, the screen contains entries for every field of data. Although you can add related entities to this screen as grids, you may want to choose a small subset of just the required or common fields and then flow to another screen to enter the rest. LightSwitch will automatically open the default Details screen after the user saves so this type of “Add New, Save, then Edit” workflow is automatically set up for you.
You use the Search Data Screen when you want a user to be able to locate a single row of data in a table and then open another screen to edit just that row. By default, the screen displays a read-only grid of all the rows in a single table (again only 45 are returned at a time by default). To search for a record, you use the search box at the top right of the screen. The search returns all the rows where any string field matches the search term. Clicking on the summary link for the record opens the Details Screen. It is a very common workflow to have users search for records first before allowing edits to them so the search screen provides this behavior automatically. Typically this should be the first screen a user interacts with for large sets of data because it puts them into an efficient workflow that keeps the system responsive even as the database grows.
Now that you understand what each screen template gives you, let’s create some screens for the Address Book application we’ve been building in this series. At the end of the previous posts we used the List and Details screen so that we could quickly test our data model. If the number of contacts you expect in your address book is relatively small (say less than 100), that screen may be enough to use for the entire application. Users can use the search functionality on the list to find contacts and then edit them all on one screen.
However, if we wanted to manage thousands of contacts for a business and allow multiple users accessing that data, it’s probably better to put users into a workflow where they first search for a contact and then can either edit one at a time or add a new one if they don’t find the contact they were looking for. It’s always good to create a simple flowchart of how the application should work:
First the user searches for a contact and if found then open the Contact Details screen and let them edit all the related contact data. Once the user saves the screen the data is simply redisplayed on the same screen until they close it. If the user does not find the contact they were looking for, they open the New Contact screen which allows them to enter just the contact entity data. When they Save, the Contact Details screen opens and they can finish entering any related data if they need.
So we just need three screens for our Address Book application; Search Data Screen, New Data Screen, and Details Screen. The order you create your screens is the default order in which they are displayed on the navigation menu. Note that Detail screens will not show up on the navigation menu because a specific record must be selected first. The first screen you create is the first one that is opened by default when the application starts so we’ll want to create the Search Screen first. You can change the screen navigation and how screens appear in the menu by right-clicking on the Solution Explorer and choosing “Edit Screen Navigation”. For a video demonstration on how to edit the screen navigation see: How Do I: Modify the Navigation of Screens in a LightSwitch Application?
So first create the Search Contact Screen by right-clicking on the Solution Explorer and selecting “Add Screen..” to open the Add New Screen dialog. Select the Search Data Screen template and then select the Contacts Screen Data and click OK.
Next create the New Data Screen in the same manner. This time, select the New Data Screen template. Then select the Contact entity for the Screen Data but do not select any related data. Then click OK
Finally we will add the Details Screen the same way but this time select all the related entities; EmailAddresses, PhoneNumbers, and Addresses. Also leave the “Use as Default Details Screen” checked.
Now that we have all our screens defined hit F5 to build and run the application and let’s see what we get. You will notice that the Search screen opens right away and on the navigation menu we have both Search Contacts and Create New Contact screens available. I only have a couple rows of test data in here but if I had hundreds of contacts, LightSwitch would display only 45 rows at a time and we would use the paging control at the bottom of the grid to retrieve the next set of rows.
If you click on the last name of a contact it will open the details screen which allows us to edit all the contact information. Notice that LightSwitch also creates links in the first column of the related grids so that you can click on them and open additional detail screens for those records as well.
If the user does not see the contact in the list, they can click on the Create New Contact screen to enter new data.
Then once they save this screen, the Contact Details Screen opens for further editing of all the contact information.
If the Search screen is still open, the user can see newly added records by clicking the Refresh button on the ribbon.
Notice we have a LOT of functionality and a totally working Address Book application by just creating a data model and picking some screen templates.
As I mentioned earlier, all the screen templates are totally customizable so you can use them as-is or modify the layouts completely for your needs. In fact, you as the developer can change the layouts of screens while the application is running. When you are developing your application within Visual Studio LightSwitch you will see a “Design Screen” button at the top right of the running application window. Open a screen and then click that button to open the Screen Customization Mode. Here you can manipulate the content tree exactly how you like and see your changes in real time.
This comes in extremely handy for quickly modifying the order of fields as well as what, where, and how things are displayed on the screen. However, in order to customize your screen with code or additional data you will need use the Screen Designer back in Visual Studio. For more information on using the Screen Designer to customize screens see: Tips and Tricks for Using the Screen Designer.
For more information on building and customizing screens see the Working with Screens learn topic on the LightSwitch Developer Center and LightSwitch Tips & Tricks on dnrTV.
As you can see the built-in screen templates provide a ton of functionality for you. We now have a fully functional Address Book application and there was no need to write any code to do it! Next post we’ll look at queries and how to design them in order to filter and sort data on our screens exactly how we want. Until next time!
Go to next article –> Part 4: Too much information! Sorting and Filtering Data with Queries
Welcome to Part 2 of the Beginning LightSwitch series! In the last post we learned about tables, or entities, in LightSwitch and how to use the Data Designer to define them. If you missed it: Beginning LightSwitch Part 1: What’s in a Table? Describing Your Data
In this article I want to focus on data relationships as we build upon the data model we started in part 1. Relationships define how information in one table corresponds to information in another table in a database. Or more generically, relationships define how an entity corresponds to another entity in (or across) a data source. You can think of relationships between entities like relationships between things in everyday life. For instance, the relationship between a school and its students is one that exists in the real world. Similarly, a real-world relationship exists between students and the classes that those students attend. In a data model, you may have one entity that contains students and another that contains classes they are attending. When you tie the two entities together, you create a relationship.
In the example we started in part 1, we’re building an address book application that manages contacts. In our current data model, we’re only storing one phone number, one email address and one address for our contact.
However, in real life contacts typically have more than one email, phone number, and address information. In order to model this in the database we are building through LightSwitch, we need to define additional tables and relate them to the contact table in a one-to-many relationship. A one-to-many relationship is probably the most common type of relationship you can define. In our case, this means that one contact can have many email addresses. One contact can have many phone numbers. One contact can also have many physical addresses.
Let’s start with email address. If we want to collect multiple email addresses for a contact we have a couple options. One option is to add a fixed number of email properties to our contact in the form of Email1, Email2, Email3. This means that we would never be able to collect more than 3 email addresses for any given contact. Depending on the type of application you are building this may be just fine. But if you start adding too many properties with the same meaning (in this case email) to your table, then it’s time to think of a different strategy. Instead we should create an EmailAddress table and define a one-to-many relationship.
From the Data Designer, click the “New Table” button and define an entity called EmailAddress with two required properties: Email (of type Email Address) and EmailType (of type String).
For EmailType we’ll create an Choice List, which you learned about in Part 1 of the series. This property will capture the type of email, whether that’s Personal or Work. I’ve also chosen to make the maximum length only 25 characters – we won’t ever need all 255.
Why don’t we make the maximum length 8 characters, which is the length of the longest value in the choice list? Because if we need to add a value to the choice list later that is a little longer than 8 characters, then we won’t have to change the data model. Disk space is cheap these days so it’s better to err on the side of longer max lengths so that all your data can fit into the underlying table. This avoids having to change the data model too often.
Now that we have our EmailAddress entity it’s time to define the relationship. Click on the “Relationship…” button at the top of the Data Designer and this will open up the “Add New Relationship” dialog window. In the “To” column select Contact to set up the one-to-many relationship. The multiplicity is set to Many to One by default so we don’t need to change it. Multiplicity defines the type of relationship you want to create. In LightSwitch, you can also specify the multiplicity as One to Zero or One which means that only a maximum of one related entity would be allowed.
For more information on defining different types of relationships see: How to: Define Data Relationships
For information on how to model a many-to-many relationship in LightSwitch see: How to Create a Many-to-Many Relationship
You can also specify what happens to the email addresses when a contact is deleted. By default, this is set to “Restricted”. This means that a user would not be allowed to delete a Contact if they had any Email Addresses. Leaving the setting makes sense if we were working with Customers who had many Orders, for instance, but not in this case. We want LightSwitch to automatically delete any Email Addresses when we delete the Contact, so set the “On Delete Behavior” equal to “Cascade delete”.
The description at the bottom of the dialog is there to help you understand what you are doing when setting up the relationship. Once you click OK you will see the relationship in the Data Designer which will show all direct relationships to the entity you are working with. Notice that a Contact property is now added to the EmailAddress entity. This is called a navigation property and represents the Contact to which the EmailAddress belongs.
Double-click on the Contact entity to open it and you will notice a navigation property to all the EmailAddresses for that Contact. Navigation properties are used by LightSwitch on screens to navigate through your data, so it’s important to model them correctly.
Now that we’ve got the EmailAddress table defined and a relationship set up, we need to delete the Email property we had previously defined on the Contact itself. Select the Email property and hit the Delete key. Alternatively you can right-click and choose Delete on the menu. Do the same to delete the Address1, Address2, City, State, ZIP and Phone properties as well.
Next, let’s add a PhoneNumber table in the same way we added EmailAddress. You might wonder why we need to create a new table separate from the EmailAddress. This is because an EmailAddress and a PhoneNumber are different aspects of a Contact and have no relation to each other except through the Contact itself. Therefore, we need to create a new table.
Click the “Add Table” button and define the PhoneNumber entity with two required properties: Phone (of type Phone Number) and PhoneType (of type String). PhoneType will have a Choice List of “Cell”, “Fax”, “Home”, and Work”.
Next add the relationship to Contact exactly the same way as before. While the PhoneNumber entity is displayed, click the “Relationship…” button at the top of the Data Designer and specify the relationship to the Contact table.
Last but not least, we need to create an Address table to store multiple physical addresses for a Contact. Click the Add Table button and define the Address entity with the following properties AddressType, Address1, Address2, City, State, ZIP. Set the AddressType and Address1 properties as required. We will also specify a Choice List for the the AddressType property with values “Home”, “Work”, and “Other”.
Now set up the relationship for Address exactly as before. While the Address entity is displayed, click the “Relationship…” button and specify the relationship to the Contact table. Again we’ll choose “Cascade delete” so that any Addresses are deleted automatically if a Contact is deleted. This makes sense in the case of Contact because if the user deletes the contact from the system all their information should be automatically deleted. Keep in mind, however, that you may not want this behavior in other applications. For instance if you are building an order entry application you would want to restrict deletion of Customers if they had any Orders in the system in order to keep the Order history intact.
Now when we open the Contact entity in the Data Designer you can see all the direct relationships.
Now that we have the data model 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. Once you do this, you will see checkboxes for the additional related entities we created. Select all of them and click OK.
To build and launch the application hit F5. Now you can enter information using this screen. Click the “+” button on the top of the list box on the left to add new contacts.
Notice that LightSwitch read all the one-to-many relationships we set up in our data model and created a tabbed section of grids below the Contact details for Email Addresses, Phone Numbers and Addresses just like we would expect. The grids are editable by default so you can just type the related data directly into the rows.
Because we defined the relationships properly in our data model, LightSwitch was able to create a very usable screen for entering our data into all the tables in our database without much work at all. In the next post we’ll dive deeper into the Screen Templates and how to customize the layout of screens. Until next time!
Go to next article –> Part 3: Screen Templates, Which One Do I Choose?
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.
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.
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 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?
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
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!
Go to next article –> Part 2: Feel the Love - Defining Data Relationships