A Visual Studio 2012 LightSwitch “Course Manager” Application Clone with Real-World Data: Part 1

Andy Kung’s six-part Course Manager tutorial for Visual Studio 2012’s LightSwitch implementation describes creating a registrar’s application for a fictitious Fine Arts school with Visual Basic. Following are links to Andy’s posts:

• Course Manager VS 2012 Sample Part 1 – Introduction
• Course Manager VS 2012 Sample Part 2 – Setting up Data
• Course Manager VS 2012 Sample Part 3 – User Permissions & Admin Screens
• Course Manager VS 2012 Sample Part 4 – Implementing the Workflow
• Course Manager VS 2012 Sample Part 5 – Detail Screens
• Course Manager VS 2012 Sample Part 6 – Home Screen

Downloadable VB and C# source code for Andy’s tutorial is available here: LightSwitch Course Manager End-to-End Application (Visual Studio 2012).

While the Course Manager tutorial is useful in demonstrating LightSwitch basics, its underlying database has only a few records in each table.

In preparation for a consulting project in which I plan to use LightSwitch as a forms-over-data front end, I wanted to demonstrate to my client the user experience with a database having tables containing more realistic numbers of records. I also wanted a real-world comparison of performance with an on-premises instance of SQL Server Express versus that for a Windows Azure SQL Database back-end. Thus this project.

Contents, Part 1:

• The Oakmont University Sample Database
• Setting Up the OakmontSQL Data Source
• Starting a LightSwitch Project from the OakmontSQL Data Source
• Adding Summary Properties and Computed Values
• Adding Queries as Data Sources

Updated 10/9/2012 1:00 PM by adding a “Maintaining Referential Integrity by Restricting Deletions of Primary Key Table Rows” subsection to the Starting a LightSwitch Project from the OakmontSQL Data Source section.

Updated 10/8/2012 12:00 PM by adding a clustered primary key to the Grades table. The lack of a primary key caused the table to be missing from the Attached Data Source Wizard’s list. Changed all non-clustered primary keys to clustered for compatibility with Windows Azure SQL Database and uploaded new OakmontSQL.zip file to SkyDrive.

Updated 10/7/2012 4:00 PM PDT by adding the last two sections above.

Updated 10/7/2012 10:00 AM PDT with a fix for the missing relationship in the “Setting Up the OakmontSQL Data Source” section’s step 8, a discussion of the missing Grade entity and collection, and a change from C# to Visual Basic as the programming language.

The Oakmont University Sample Database

Oakmont University is a fictitious, private four-year institution located in Navasota, TX*. The OakmontSQL database is designed to serve both students and registrars. The original version was created by Steven Gray and Rick Lievano for Roger Jennings' Database Workshop; Microsoft Transaction Server 2.0, which published in late 1997. Versions with updated dates and faculty members assigned to departments were later included in multiple editions of my Special Edition Using Microsoft Access, Introducing Microsoft Office InfoPath 2003, and Microsoft Access 2010 In Depth for QUE Books.

Following is the OakmontSQL database diagram displayed by SQL Server Management Studio 2012:

The only significant changes from the preceding version is movement of the Capacity field from the Courses to Sections table (to correct a long-term error) and advancing the current date to 2012’s fall semester.

The following table lists the number of records in each of the eight database tables:

Primary Key Table	Rows	Foreign Key Table	Rows
Students	29,992	StudentTransactions	45,711
Courses	590	Enrollments	59,996
Sections	1,770	Grades	59,996
Departments	14
Employees	2,320

All tables have clustered primary-key indexes. Windows Azure SQL Database (SQL Azure) doesn’t support (heap) tables with non-clustered indexes.

You can download OakmontSQL.mdf (50 MB) and OakmontSQL_log.ldf (3.5 MB) from my Skydrive account as OakmontSQL.zip (9.7 MB).

*According to Wikipedia:

Navasota is a city in Grimes County, Texas, United States. The population was 6,789 at the 2000 census. In 2005, the Texas Legislature named the city "The Blues Capital of Texas," in honor of the late Mance Lipscomb, a Navasota native and blues musician.

For more about Mance Lipscomb, see my The Southeast Texas – East Bay Music Connection Window Azure Web Site page.

---

Setting Up the OakmontSQL Data Source

1. Install SQL Server 2012 Express with Tools (ENU\x64\SQLEXPRWT_x64_ENU.exe or ENU\x86\SQLEXPRWT_x86_ENU.exe) to create a local server instance (named SQLEXPRESS) and SQL Server Mangement Studio (SSMS) 2012 to manage it from the download page.

2. Download OakmontSQL.zip from my Skydrive account and extract its files to your C:\Program Files\Microsoft SQL Sever\MSSQL11.SQLEXPRESS\MSSQL\DATA folder.

3. Launch SSMS 2012, which displays the Connect to Server dialog. Open the Server Name list, select your local computer’s name, and add the \SQLEXPRESS suffix:

4. Click Connect to open SSMS, right-click Object Explorer’s Databases node, choose Attach to open the Attach Databases dialog, and click the Add button to open the Locate Database Files dialog, and select OakmontSQL.mdf in the list:

 

5. Click OK to close the dialog, open the Owner list, and choose sa as the database owner (dbo):

6. Click OK to close the dialog, expand Object Explorer’s Databases, OakmontSQL and Database Diagrams nodes, and select dbo.Relationships to display the diagram shown earlier:

7. Optionally, close SSMS.

---

Starting a LightSwitch Project from the OakmontSQL Data Source

1. Launch Visual Studio 2012 Professional or higher, choose File, New, Project to open the New project dialog, select the LightSwitch template and choose VB or C# as the programming language (VB for this example), and type a project name (OakmontLS) for this example:

Note: VB was chosen for this example to conform to its use in Andy Kung’s Course Manager tutorial.

2. Click OK to open Visual Studio with the Start with Data form active:

3. Click the Attach to External Data Source button to open the Attach Data Source Wizard dialog with the default Database icon selected:

4. Click Next to open the Choose Data Source dialog and select Microsoft SQL Server:

5. Click the Continue button to open the Connection Properties dialog, type the computer name (or select it from the list), add an \SQLEXPRESS suffix, and select OakmontSQL from the Select or Enter a Database Name list:

6. Click the Test Connection button to verify the database connection is valid, and click the message’s OK button to close it.

7. Click the Connection Properties dialog’s OK button to open the wizard’s Choose Your Database Objects dialog, and mark the Tables check box to select all tables and then clear the dtproperties and sysdiagrams text boxes:

Note: The Grades table was missing from earlier versions of this post due to lack of a primary key. LightSwitch doesn’t import tables without a primary key.

8. Click Finish, which displays the following Warning message:

Note: This issue will be dealt with in steps 11 through 14.

9. Click Continue to open the designer for the “Cours” entity and click the “Cours” title bar to display the entity’s properties sheet at the lower right:

Note: LightSwitch doesn’t always return pluralized collection or singularized entity names correctly.

10. Scroll down in the Properties sheet and add an “e” to “Cours” in the Name text box to correct the entity name in three locations:

Note: A refactoring feature changes the entity name to Course in all entities which refer to it.

11. Double-click the Enrollments item to open its designer:

Notice that a many-to-one relation to the Section entity is missing, as explained in step 8.

12. Click the Add: Relationship button to open the Add New Relationship dialog, open the Name/To list, and select Section to create a many-to-one relation with the Section entity:

13. Open the first Enrollment (Foreign) list, select CourseID (String), open the second Enrollment (Foreign) list and select Section (Short Integer):

14. Click OK to add the Section type to the Enrollment entity and the navigation property to a Section entity:

15. Open and review the Departments, Employees, Sections, Students and StudentTransactions collections.

16. Press F5 to build and run the application, which displays an empty OakmontLS window.

Note: This part will be updated when a conclusion is reached about the applicability of the warning in step 8. I’ve opened an Is There a Way to Avoid Spurious Foreign Key Constraint Omissions from SQL Server Data Sources? thread in the Visual Studio LightSwitch – General Questions forum about the problem.

Maintaining Referential Integrity by Restricting Deletions of Primary Key Table Rows

Andy Kung’s Part 2 adds a Restricted value to the On Delete Behavior property of the One side of relationships created manually between entities, as shown here for the Course entity:

 

SQL Server maintains referential integrity by foreign-key (FK) constraints, which throw errors if you attempt to delete a row from a primary key table that has dependent rows in other table(s). Relying on SQL Server to maintain referential integrity results requires a round-trip to the server when attempting such a deletion.

It would be more efficient to handle referential integrity issues in your LightSwitch application, but the presence of the FK constraint disables the On Delete Behavior/To list and adds the note highlighted below:

---

Adding Summary Properties and Computed Values

Part 2 of Andy Kung’s Course Manager tutorial, Setting Up Data, describes customizing the Summary property of and adding computed properties to entities.

Summary Properties

The Summary property designates the value to appear in the details section of an entities’ list-details screen. By default, this value is the first field of the String data type in the field list. This is often an abbreviated code, such as DepartmentID, rather than a meaningful name, such as a department’s Description.

To change the Summary property of the Department entity, do the following:

1. Double-click the Departments item in Solution Explorer to open it in the Designer, click the Department header bar, scroll the Properties list to expose the Summary Property item at the end and open its list:

2. Select Description to replace DepartmentID as the Summary property.

3. Repeat steps 1 and 2 for the Course entity, replacing CourseID with Name as the Summary property.

Adding Computed Fields for Summary Properties

The Employee and Student entities have FirstName as the summary property, but a LastName, FirstName format is preferable for lists. LightSwitch enables defining computed fields and populating them with an expression whose arguments involve other field values. Computed fields are defined by the LightSwitch application and aren’t reflected in the database tables.

To add a computed FullName field to the Employee entity and select it as the Summary property, do the following:

1. Double-click Solution Explorer’s Employees item, click the Computed Property button to add a row to the Properties list, and type FullName in the first row:

2. Click the Edit Method button to open code editor stub for the FullName_Compute() method of the Employee class:

3. Type result = Me.LastName + ", " + Me.FirstName under the comment and press Ctrl+s to save your changes:

See Beth Massi’s Getting the Most out of LightSwitch Summary Properties post for more details about optimizing Summary property values.

4. Double-click Employees again, replace FirstName with FullName as the Summary property value, and press Ctrl+s to save your changes:

5. Repeat steps 1 through 4 for the Student entity.

Tip: To enhance the FullName value, substitute result = Me.LastName + ", " + Me.FirstName + " (" + Me.GraduationYear.ToString() + ")" in step 3.

Adding a Numerical Computed Field and Validating Operations with Its Value

Andy’s Part 2 includes instructions for adding a computed SpaceRemaining value computed by subtracting the count of Enrollments for a Section from the MaxEnrollment value and defining a validation rule that prevents overflowing sections.

OakmontSQL’s Sections table has a Capacity value of the Short Integer data type, so follow this similar approach:

1. Double-click Solution Explorer’s Sections item, click the Computed Property button, type SeatsAvailable as its name and select Short As its data type:

2. Click the Edit Method button, type result = Me.Capacity – Me.Enrollments.Count below the comment and press Ctrl+s to save your changes:

Note: The Section table’s default Capacity value is 20.

3. Open the Enrollments designer and click the extend symbol at the right of the command bar to display the Write Code button:

4. Click the Write Code button to open the code editor with the Section class procedure and add the following code below the SeatsAvailable method:

Public Class Enrollment
    Private Sub Enrollments_Validate(entity As Enrollment, results As _
                                     EntitySetValidationResultsBuilder)
        ' If the section has reached the max enrollment limit, show an error
        If (entity.Section1.SeatsAvailable < 0) Then
            results.AddEntityError("This section is full.")
        End If
    End Sub

    Private Sub EntitySetValidationResultsBuilder(Optional p1 As _
                                                  Object = Nothing)
        Throw New NotImplementedException
    End Sub
End Class

Note: You’ll test this event handler in Part 2.

---

Adding Queries as Data Sources

[To follow]

Technorati Tags: Visual Studio LightSwitch 2012,Visual Studio 2012,LightSwitch,SQL Server 2012 Express,SQL Server 2012,Entity Framework v5