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

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

Rate This
  • Comments 7

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:

Download the LightSwitch HTML Client Preview 

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
clip_image001

· From CourseCategory table, add a many-to-1 relationship to Category
clip_image002

· From Enrollment table, add a many-to-1 relationship to Section
clip_image003

· From Enrollment table, add a many-to-1 relationship to Student
clip_image004

· From Section table, add a many-to-1 relationship to Course
clip_image005

· From Section table, add a many-to-1 relationship to Instructor
clip_image006

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

clip_image007

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.

clip_image009

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.

clip_image011

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.

clip_image012

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

clip_image013

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

clip_image014

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.

clip_image015

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.

clip_image016

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

Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
  • You use a code for updating  with the VB sample.  However, when trying to use C#, there is a problem with the code, and I noticed on the C# sample, you do not have the code.  How would we update the records automatically in C#.

  • Where is the option in VS 2012 Pro / LightSwitch to display a database schema diagram?

    Thank you.

  • The table names and column names (Fields & Properties) in this article do not match the tables / columns provided in the example C# solution.

    Also, as asked by another person, is this series of articles available with C# code, instead of VB code - aside from the actual downloaded files?  I would like to walk through the entire article series actually performing each step.  But I use C#, not VB.

    Thank you.

  • @Don - Thank you for the feedback! Some of the table columns are generated by LightSwitch. For example, when you create a relationship between Enrollment and Student, LightSwitch automatically creates the foreign keys for you. You will see Enrollments in the Student table indicating that each student may have multiple enrolments.  Perhaps that's the reason you see a mismatch between the walkthrough and the completed sample. If you follow the walkthrough from scratch, you should end up with exactly the same configuration as the sample.

    I also verified that the code from the walkthrough are available in both VB and C# samples. We will work on inserting the corresponding C# snippets in the walkthrough. In the mean time, you should be able to find the C# code in the sample.  

    Here is a tutorial for getting the database schema diagram in Visual Studio. blogs.msdn.com/.../how-to-connect-to-and-diagram-your-sql-express-database-in-visual-studio-lightswitch.aspx.

  • Thank you for the database schema diagramming tip.  

    1. Do you also have copies of the design documents that you can share? e.g. business requirements, functional requirements, detailed design, etc?  

    2. More importantly, what approach(es) did you use in designing and implementing this solution?

    3. Did you use TFS tasks, etc. for any of the above?

  • I am getting an missing library reference for Student entity when i open code for Students_Updating method. Please help.

  • @S.SRIVASTAV - Please post a question in the LightSwitch forum so we can troubleshoot. We will need the exact error message and repro steps.

    social.msdn.microsoft.com/.../home

Page 1 of 1 (7 items)