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

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

Rate This
  • Comments 19

NOTE: This article was written against Visual Studio LightSwitch 2011 but has been updated for LightSwitch in Visual Studio 2012. To view the latest version click here.

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
    clip_image001[4]
  • From CourseCategory table, add a many-to-1 relationship to Category
    clip_image002[4]
  • From Enrollment table, add a many-to-1 relationship to Section
    clip_image003[4]
  • · From Enrollment table, add a many-to-1 relationship to Student
    clip_image004[4]
  • From Section table, add a many-to-1 relationship to Course
    clip_image005[4]
  • From Section table, add a many-to-1 relationship to Instructor.
    clip_image006[4]

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

clip_image008[4]

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.

clip_image009[4]

        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.

clip_image010[4]

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.

clip_image011[4]

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

clip_image012[4]

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

clip_image013[4]

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_image014[4]

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.

clip_image015[4]

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

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
  • Thanks for publishing this article, I have a question >

    How does the studentId get populated? the selection of the student is done by the combo / autocomplete box before that, but it aint referred anywhere?

    Thanks,

    Sawan

  • Hi Sawan,

    I will go into more details on screens in a later post. In the meantime, you can open up RegisterCourse in the screen designer and see how the parameter (StudentId) is bound. Karol's post on parameterized queries is a good reference too. Stay tuned!

    blogs.msdn.com/.../how-to-use-lookup-tables-with-parameterized-queries-karol-zadora-przylecki.aspx

  • Hello Andy, very nice tutorial, I have a question about the Section.SpaceRemaining implementation, in the downloaded sample, I found the following code, that does't appear in the tutorial:

    Public Class Section

           Private Sub SpaceRemaining_Compute(ByRef result As Integer)

               result = MaxEnrollment - Enrollments.Count

           End Sub

           Private Sub Title_Compute(ByRef result As String)

               result = Course.Title & " [" & SectionID & "]"

           End Sub

           Private Sub Summary_Compute(ByRef result As String)

               result = Title & " - " & MeetingDays & " " & MeetingTime.ToShortTimeString()

           End Sub

    End Class

    Do I'm missing something?

    thanks in advance...

  • @Manuel - You're correct. The tutorial did not go over each individual computed property. It only touches the high level concepts. I'm glad you're using the downloaded sample as a reference, which is what the tutorial is intended for!

    The code you pointed out is for additional computed properties. SpaceRemaining is the number of spots left in the Section. Title and Summary are string representations of the table. You can use either on a screen depending on whether you want to show more/less info. Beth has a good post on computed property: blogs.msdn.com/.../getting-the-most-out-of-lightswitch-summary-properties.aspx

    Hope that helps!

    -andy

  • This is a very good tutorial. Now I can see the power of Lightswitch.

    Thanks so much.

    Rachida

  • Very nice post.

    If (StudentId.HasValue) Then

      query = From s As Section In query Where

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

    End If

    Where is this Function(f) defined ?

  • Hi Gurvinder,

    This is just a function without a name that's being used in-line in the query. Have a look at these resources that should help explain how to use these expressions:

    msdn.microsoft.com/.../bb531253.aspx

    msdn.microsoft.com/.../bb737915

    Cheers,

    -Beth

  • Thanks for reply. I figured it out as Lamda functions :-)

  • Hi Andy,

    I have been following your tutorial. The schema diagram that you presented above, how is it possible for me to see this diagram in LightSwitch Beta 2?If the diagram is not from LS, how can I have an overview of the ralationship table & their respective properties that 1 created in LS?

    Thank you.

  • @wei.ng - The diagram wasn't directly generated from LightSwitch. After I deployed the application. I used Visual Studio's Server Explorer to generate the database diagram.

  • I'm having a concurrency issue on the UpdatedTime if I use datagrid and detail information on the same screen.  I believe this code is triggering it:

    Private Sub Students_Updating(entity As Student)

               entity.UpdatedTime = DateTime.Now

               entity.UpdatedBy = Application.User.Name

           End Sub

    So, when I make a change it makes it both on the grid and the detail section.

    Is there a way to programmtically accept the local or server value?

    Is there a better way of doing it altogether?

    THanks

  • @vorgang - would you mind providing the repro steps and describing what you'd like to acheive a little more?

  • Hi,

    Thanks for this tutorial, i am following the steps in this tutorial to build an Helpdesk application. I am having problem with send emails through the application. I will appreciate if you could help me in this area of send email through lightswitch.

    Cheers.

  • @James - Beth Massi wrote a blog on "How To Send HTML Email from a LightSwitch Application" you might find helpful: blogs.msdn.com/.../how-to-send-html-email-from-a-lightswitch-application.aspx

  • You write:"@wei.ng - The diagram wasn't directly generated from LightSwitch. After I deployed the application. I used Visual Studio's Server Explorer to generate the database diagram."

    I tried to generate a diagram using Visual Studio 2010 after I had created the tables but couldn't do so. Do I have to deploy in order to generate? And, can I deploy immediately after creating the tables before I have created screens etc?

Page 1 of 2 (19 items) 12