Course Manager VS 2012 Sample Part 2 – Setting up Data (Andy Kung)

Yesterday, we gave an overview of the Course Manager sample and walked through some scenarios from end-user’s perspective. If you missed it:

Course Manager VS 2012 Sample Part 1 – Introduction

We are now ready to start “reverse-engineering” and learning how to build Course Manager. We will kick off 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:

 

LightSwitch Course Manager End-to-End Application (Visual Studio 2012)

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 “+ 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. 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 Properties.

We can also create other computed fields that aren’t summary fields. For example, we can add a SpaceRemaining field to the Section table that indicates the number of available spots in the section.

Private Sub SpaceRemaining_Compute(ByRef result As Integer)
    result = MaxEnrollment - Enrollments.Count
End Sub

I also have defined some computed fields on Student, Instructor, Section, and CourseCategory. Some are set to be the summary property of the table so take a look at the sample.

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 the section has reached the max enrollment limit, show an error
    If (entity.Section.SpaceRemaining < 0) Then
        results.AddEntityError("This section is full.")
    End If
End Sub

The code checks to see if 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
    ' Return only sections which the student has not registered
    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
    ' Return only sections which fall into the category
    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