Extending A LightSwitch Application with SharePoint Data

Article
02/01/2011

In my previous posts, I have discussed my demo application that a training company’s sales rep can use to manage customers and the courses they order. The application includes both remote data and local data. The remote data consists of the Courses, Customers and Orders tables in the TrainingCourses SQL Server database. The local data consists of the Visits and CustomerNotes tables in a local SQL Server Express database. In this post I want to extend the application by adding SharePoint data.

March 30, 2011: The original version of this was posted on February 1, 2011 and was based on Beta 1. I have updated this for Beta 2. I reshot all the screens and have made some minor changes to both the text and the narrative. There are two primary differences. The minor change is that I need to add code to enable editing of data in two different data sources. The more significant change is in what I need to do to create a one to many relationship between Courses in SQL Server and KBs in SharePoint.

Consider the following fairly typical scenario. The enterprise maintains the SQL Server database but a department wants to track additional information that is related to the information in the database. Rather than ask the database owner to add a table to the database, the department uses SharePoint instead. The department can quickly create a SharePoint site and start using it right away.

I want to show this scenario in my sample application. The Courses table in the SQL Server database contains information on courses, including name, type (online or instructor led), price, date of release and date of retirement. I want to maintain a knowledge base, which will contain tips and tricks on each course. So I created a SharePoint site and added two lists to it. The Courses list is a simple list of course names. Then I created a KB list with columns for Title, Course and Notes. The Course column in the KB list is linked to the Title column in the Courses list.

I added the Courses list to the SharePoint site so that I didn’t have to retype course names every time I added a KB item. Rather, I can select the course from a drop-down list when I add a KB item.

There can be multiple KB entries for each course and I want to be able to view and add/edit/delete them in the LightSwitch application. In the Solution Explorer, I can right-click on Data Sources and select Add Data Source. In the Attach Data Source Wizard, I can select SharePoint.

I then enter the SharePoint site and specify my login credentials.

I then specify the list I want to use (KB in this example). I also change the data source name to SPTrainingCoursesData rather than Training_CoursesData.

When I click Finish I am informed that the Courses list will also be imported. This is because the KB list is linked to the Courses list. Note that in Beta 1, UserInformationList was also listed. It now doesn’t show up in this message.

I click Continue and I see a new data source node in the Solution Explorer. ApplicationData represents the data I am storing locally in SQL Server Express. TrainingCoursesData represents the data in the SQL Server database. SPTrainingCoursesData represents the SharePoint data. Notice that SPTrainingCoursesData contains not only Courses and KBs, but also UserInformationLists. The user information is imported into LightSwitch when you import a list.

The SQL Server database has a Courses table and the SharePoint site has a Courses list. I can’t have two entities both named Course, so I renamed the SharePoint entity to SPCourse.

In the Entity Designer I see the one-to-many relationship between the SharePoint Courses and KBs lists. I also see that UserInformationList was imported so that I could see the name of the person who created and modified list items.

But in the LightSwitch application, course information comes from the SQL Server Courses table. What I want is a one-to-many relationship between Courses in SQL Server and KBs in SharePoint. But when I try and create the relationship, I run into an issue. The course id does not show up in the list of KB properties. It shows up in the list (see the first figure above), but not in LightSwitch. That is because in the SharePoint list, CourseId is a lookup field with a relationship to another list. So LightSwitch does not treat this as a foreign key and bring it down.

So I add a CourseNumber field to the KB list, and make it a number. Then I manually set the value of CourseNumber to Course:ID.

Then I will go back into LightSwitch, right-click on SPTrainingCoursesData and select Update DataSource. When I go back into the Entity Designer and try and set the relationship, I run into another roadblock. Even though I defined CourseNumber as an integer in SharePoint, LightSwitch lists it as a double.

LightSwitch uses the SharePoint OData service to read list definitions and that service reports that the CourseId column is a double. I can see this by navigating to https://<servername>/TrainingCourses/_vti_bin/listdata.svc/$metadata in the browser.

To solve this conundrum, I added a CourseNumber column to my SQL Server Courses table and made it a float data type. In LightSwitch this property is a double and I can then create the relationship using Course.CourseNumber and KB.CourseNumber

In spite of the fact that I had to jump through a hoop or two and use a workaround, this is still very cool. I will never tire of pointing out the ability of LightSwitch to create relationships across data sources. It is a killer feature.

Now that I have the relationship established, I can create the CourseDetail screen to display both courses and KBs. This is delightfully simple in LightSwitch. I just create a Detail screen selecting Course as the screen data and ask to include KBs. The bottom row of the screen contains a DataGrid that will display KBs. By default, all of the columns in the list will appear. I will delete all of them except KB and Notes.

The screen contains data from two different data sources, so I need to add the following code so that I can add and edit KB items from this screen. See Using Both Remote and Local Data in a LightSwitch Application for more details on why I need to do this.

 ' VB
Public Class CourseDetail
  Private Sub CourseDetail_InitializeDataWorkspace(
    saveChangesTo As System.Collections.Generic.List(Of Microsoft.LightSwitch.IDataService))

    saveChangesTo.Add(Me.DataWorkspace.TrainingCoursesData)
    saveChangesTo.Add(Me.DataWorkspace.SPTrainingCoursesData)
  End Sub

  Private Sub CourseDetail_Saving(ByRef handled As Boolean)
    Me.DataWorkspace.TrainingCoursesData.SaveChanges()
    Me.DataWorkspace.SPTrainingCoursesData.SaveChanges()
    handled = True
  End Sub
End Class

 // C#
public partial class CourseDetail
{
  partial void CourseDetail_InitializeDataWorkspace(List<IDataService> saveChangesTo)
  {
    saveChangesTo.Add(this.DataWorkspace.TrainingCoursesData);
    saveChangesTo.Add(this.DataWorkspace.SPTrainingCoursesData);
  }

  partial void CourseDetail_Saving(ref bool handled)
  {
    this.DataWorkspace.TrainingCoursesData.SaveChanges();
    this.DataWorkspace.SPTrainingCoursesData.SaveChanges();
    handled = true;
  }
}

Now when I run the application and select a course, I see not only the course details but also the KBs. And if I select a KB, I see the details on it.

Before I deploy this application to real live users I will have to create my own screen for this. The users don’t need to see Content Type, SPCourse, CreatedBy, ModifiedBy or CourseNumber. I can also add, edit or delete KBs by clicking buttons in the Command Bar on the CourseDetail page.

When I click Save, the KB is saved to SharePoint. So cool!!

Notice that when I save this new KB, the related Course name is not written to the list. I can create a simple workflow to fix this.

Extending A LightSwitch Application with SharePoint Data

Additional resources