Last week, we gave an overview of the Course Manager sample and walked through some scenarios from end-user’s perspective. We are now ready to start “reverse-engineering” and learn how to build Course Manager. We will start our exploration by examining its tables, relationships, and queries.

In this tutorial, I will create a LightSwitch VB project called SimpleCourseManager. You can find the corresponding C# code in the C# sample project on code gallery.

Data

Before we build any application, we need to think about what our business requirements are and what we’re trying to model with our data. Conceptually, there are 5 “entities” we’re working with in our business scenarios:

Entity	Description
Category	A Category to categorize different Courses. For example, “Animation” category includes courses like “Animation 101”, “Special Effects”, etc.
Course	A Course is a subject that the school is offering. A Course can be categorized into many Categories. Each Course offers have many Sections.
Section	A Section is a time slot offered by a Course. Each Section can have many Students and is taught by one Instructor.
Instructor	Each Instructor can teach many different Sections.
Student	A Student can enroll in many Sections.

Tables

Course Manager models these concepts with 7 tables. Using the table designer, create the following tables:

Table	Fields & Properties
Category	Name (String, required)
Course	Title (String, required) Description (String, not required, max length=3000)
Instructor	FirstName (String, required) LastName (String, required) Title (String, not required) Picture (Image, not required) Phone (Phone Number, not required) Office (String, not required) Bio (String, not required, max length=3000) CreatedTime (Date Time, required, not displayed by default) UpdatedTime (Date Time, required, not displayed by default) UpdatedBy (String, required, not displayed by default, not searchable)
Section	SectionID (String, required) MaxEnrollment (Integer, required) AcademicYear (Integer, required) AcademicQuarter (String, required, choice={Fall, Winter, Spring, Summer}) MeetingDays (String, required) MeetingTime (Date Time, required) StartDate (Date, required) EndDate (Date, required)
Student	FirstName (String, required) LastName (String, required) Email (Email Address, required, unique Index) Phone (Phone Number, required) Picture (Image, not required) Street (String, required) City (String, required) State (String, required) Zip (String, required) CreatedTime (Date Time, required, not displayed by default) UpdatedTime (Date Time, required, not displayed by default) UpdatedBy (String, required, not displayed by default, not searchable)
CourseCategory	Since Course and Category have a many-to-many relationship. We will use this as the mapping table. Leave the table empty for now.
Enrollment	Since Student and Section have a many-to-many relationship. We will use this as the mapping table. Leave the table empty for now.

Relationships

Now we need to set up relationships for the tables we created. Using the “Add Relationship” button in the command bar of the table designer, create the following relationships:

• From CourseCategory table, add a many-to-1 relationship to Course
  
• From CourseCategory table, add a many-to-1 relationship to Category
  
• From Enrollment table, add a many-to-1 relationship to Section
  
• · From Enrollment table, add a many-to-1 relationship to Student
  
• From Section table, add a many-to-1 relationship to Course
  
• From Section table, add a many-to-1 relationship to Instructor.
  

To help you understand where we are, here is the schema diagram of what we just built with LightSwitch:

Customization

Now we have our tables and relationships set up. We can go ahead and build some screens at this point. But before we jump into screens, there are many things we can still tweak to further customize our data.

Adding computed fields and the summary property

To have a better string representation of a record, we can customize the summary property of a table. I have defined some computed fields on Student, Instructor, Section, and CourseCategory. Some are set to be the summary property of the table. For example, since CourseCategory is a mapping table, it doesn’t really have any string representation. If we simply create a list-details screen on CourseCategory, it will show a list of Ids, which isn’t very meaningful for the user. Instead, I set it to show the category name.

        Private Sub Summary_Compute(ByRef result As String)

            If (Category IsNot Nothing) Then

                result = Category.Name

            End If

        End Sub

For more information about how to customize the summary property, please see Getting the Most out of LightSwitch Summary Property.

Defining a default sort order

By default, LightSwitch sorts a data set by the Id field. This wouldn’t be ideal in certain cases. For example, Student or Instructor records are usually presented alphabetically by last name then first name. Instead of customizing a screen query with sort orders (on every screen), a better way to achieve this is by overriding the sort order in the server side query. Meaning, all student queries will inherently have this particular sort order.

To do this, double click on the Student table to open up the table designer. Use the “Write Code” dropdown menu in the command bar and select Students_All_PreprocessQuery.

This will take you to the code editor for ApplicationDataService.vb. The logic defined in this file is run on the logic tier (or middle tier), which processes requests from a client to fetch data, update data, or to perform other operations. In the body of the method, override the query by adding a sort order:

        Private Sub Students_All_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Student))

            query = From s As Student In query

                    Order By s.LastName, s.FirstName

        End Sub

Follow the Course Manger sample to define sort orders on Instructor, Section, and Category.

Pre-setting values when an entity is created

In many scenarios, we need to track who created or updated a record and when. In Course Manager, both Student and Instructor tables have CreatedTime, UpdatedTime, and UpdatedBy fields. These values are automatically set when an entity is created. We don’t need to ask for user input.

To set a value when a record is created, open the table designer for Student. Use the “Write Code” dropdown menu on the command bar, select Student_Created. Write:

        Private Sub Student_Created()

            CreatedTime = DateTime.Now

            UpdatedTime = DateTime.Now

            UpdatedBy = Application.User.Name

        End Sub

Do the same for Instructor table.

Updating a record in the middle-tier

Now, UpdatedTime and UpdatedBy fields also need to be updated whenever the record is being updated and saved. Again, go the table designer for Student. Use the “Write Code” dropdown menu in the command bar and select Student_Updating. Write:

        Private Sub Students_Updating(entity As Student)

            entity.UpdatedTime = DateTime.Now

            entity.UpdatedBy = Application.User.Name

        End Sub

Do the same for Instructor table.

Writing validation logic (in the middle tier)

In Course Manager, every Section has a maximum number of enrollments allowed. We’d like to show an error message when a Student tries to register over the limit. We’d like to run the validation logic when a new Enrollment is being saved. To do so, go to the table designer for Enrollment. From the “Write Code” dropdown menu in the command bar, select Enrollments_Validate. Write:

        Private Sub Enrollments_Validate(entity As Enrollment, results As EntitySetValidationResultsBuilder)

            If (entity.Section.SpaceRemaining < 0) Then

                results.AddEntityError("This section is full.")

            End If

        End Sub

This code checks to see if by adding this new record will exceed the enrollment limit.

Custom Query

Available Sections by Student

In Course Manager’s “Register Course” screen, selecting a student will filter down the section picker to only show sections this student has not enrolled in. Therefore, we need a query that takes a student as an optional parameter and returns a list of available sections this student has not enrolled in.

To create a query, right click on Section table in the Solution Explorer and select Add Query. Name it “AvailableSections.“

In the query designer, add a query parameter of type Integer called “StudentId” and mark it as optional.

You can typically build fairly straight-forward filtering queries with the query designer’s GUI. In our case, since we’re dealing with a many-to-many relationship, it’s easier to just write some code to define our query.

Use the “Write Code” dropdown menu in the command bar, select AvailableSections_PreprocessQuery.

In the body of the method, write some LINQ code:

            If (StudentId.HasValue) Then

                query = From s As Section In query Where

                        Not s.Enrollments.Any(Function(f) f.Student.Id = StudentId)

            End If

By default, this query returns all Sections. However, if “StudentId” is provided, it finds all Enrollments associated with the students, and filters out the Sections associated with these Enrollments.

Sections by Category

In Course Manager’s “Course Category” screen, you can filter the list of Sections by Categories. This is another example of custom query on a many-to-many relationship.

Follow the same steps above and create a “SectionsByCategory” query. Create an optional parameter called “CategoryId.” In the SectionsByCategory_PreprocessQuery code write:

            If (CategoryId.HasValue) Then

                query = From s As Section In query Where

                        s.Course.CourseCategories.Any(Function(f) f.Category.Id = CategoryId)

            End If

By default, this query returns all Sections. However, if “CategoryId” is provided, it only returns the Sections that map to this Category.

Conclusion

We’ve successfully set up our tables and relationships. We wrote special logic in the middle tier and created parameterized queries. NOW we’re ready to build some screens!

Coming up next: Course Manager Sample Part 3 – User Permissions & Admin Screens.

-andy