Using SharePoint Data in your LightSwitch Applications

One of the great features of LightSwitch is that it lets you connect to and manipulate data inside of SharePoint. Data can come from any of the built in SharePoint lists like Tasks or Calendar or it can come from custom lists that you create in SharePoint. In this post I’m going to show you how you can work with user task lists in SharePoint via LightSwitch. For a video demonstration of this please see: How Do I: Connect LightSwitch to SharePoint Data?

Connecting to SharePoint

The first thing we do is connect to a SharePoint 2010 site and choose what data we want to work with. If you have just created a new LightSwitch project then on the “Start with Data” screen select Attach to External Data Source. Otherwise you can right-click on the Data Sources node in the Solution Explorer on the right and select “Add Data Source…” to open the Attach Data Source Wizard.

Select SharePoint and Click Next. Next you need to specify the SharePoint site address. In the SharePoint Site Address box, type the URL of the SharePoint site that contains the list that you want to connect to. For example, if the URL of your list is https://sharepoint/sites/mysite/Lists/Tasks/AllItems.aspx, type https://sharepoint/sites/mysite/ into the SharePoint Site Address box. Then it will ask you how you want to log into SharePoint. If you are building a LightSwitch application for internal/company use then you should select Windows credentials here. This means that users on the company domain will connect to SharePoint using their Windows credentials when they run your LightSwitch application. Click Next.

Lastly you need to select the lists that you want to pull into LightSwitch and name the Data Source. For this example I’m selecting Tasks. Notice that UserInformationList is selected by default. This is because every list item in SharePoint has a Modified and Created By field that relates to the users who have access to the site. When you select a table from a database or a list from SharePoint, any related entities are also pulled into LightSwitch for you. Click Finish and LightSwitch will warn you that it can’t import predecessor tasks because it does not support many-to-many relationships at this time. Click Continue and the Data Designer will open allowing you to modify the Task and UserInformationList entities.

LightSwitch sets up some smart defaults for you when it imports content types from SharePoint. For Task you will notice that a lot of the internal fields are set to not display on the screen by default. Also LightSwitch creates any applicable Choice Lists for you. For instance, you will see that you can only select from a list of predefined values that came from SharePoint for Priority and Status.

Modifying your SharePoint Entities

Even though LightSwitch did some nice heavy-lifting here for us we still are going to want to make some modifications. First I’ll select the ContentType field and uncheck the “Display by Default” in the properties window. This property is automatically set to “Task” by SharePoint when we save an item and I’m going to create a screen that only displays Tasks so I don’t need to show this to the user. I’ll do the same for the TaskGroup property. I’ll also set the Summary Property of the Task entity to the Title and the UserInformatuinList to the Name property. Finally I’ll set the StarteDate and DueDate to type Date instead of Date Time since I don’t want to display the time portion to the user.

Depending on how you’ll be displaying the data from SharePoint you typically want to tweak your entities a little bit like this. I also want to implement a couple business rules that set some defaults on properties for me. The first thing I want to do is set the Complete percentage to 1 or 0 if the user selects a Status value of Completed or Not Started. In the Data Designer click the Status property and then drop down the “Write Code” button at the top right of the Designer and select “Status_Validate” method. Write this code:

 Private Sub Status_Validate(results As EntityValidationResultsBuilder)
    If Me.Status <> "" Then

        Select Case Me.Status
            Case "Not Started"
                Me.Complete = 0

            Case "Completed"
                Me.Complete = 1

        End Select
    End If
End Sub

Using Windows Authentication for Personalization

Next I want to automatically set the AssignedTo property on the Task to the logged in user. I also want to be able to present the logged in user with just their tasks on their startup screen. To do that, I need to specify that my LightSwitch application uses Windows Authentication just like SharePoint. Open the project Properties (from the main menu select Project –> Application Properties…) and then select the Access Control tab. Select “Use Windows authentication”.

If you want to allow any authenticated user on your Windows domain access to the application then select that option like I have pictured above. Otherwise if you want to control access using permissions and entering users and roles into the system then you have that option as well. (For more information on access control see: How Do I: Set up Security to Control User Access to Parts of a Visual Studio LightSwitch Application?)

Because the the AssignedTo property on the Task is of the type UserInformationList we need to query SharePoint for the right user record. Fortunately, once we have enabled Windows authentication this is easy to do by using a query. In the Solution Explorer right click on UserInformationLists and select “Add Query” to open the query designer. Name the query GetUserByName and add a filter where Account is equal to a new parameter named Account. Also in the Properties window make sure to change the “Number of Results Returned” to “One”.

Now that we have this query set up we can go back to the Data Designer by double-clicking on Tasks in the Solution Explorer and then drop down the “Write Code” button again, but this time select the Task_Created method. xxx_Created methods on entities are there so you can set defaults on fields. The method runs any time a new entity is created. In order to call the query in code, you use the DataWorkspace object. This allows you to get at all the data sources in your application. In my case I have only one data source called Team_SiteData which is the SharePoint data source. Once you drill into the data source you can access all the queries. Notice that LightSwitch by default generates query methods to return single records in a data source in which you pass an ID. You can access the complete sets as well (Tasks and UserInformationLists in this case).

What we need to pass into our GetUserByName query is the account for the logged in user which you can get by drilling into the Application object. Here’s the code we need:

 Private Sub Task_Created()
    Me.AssignedTo = Me.DataWorkspace.Team_SiteData.GetUserByName(Me.Application.User.Name)
End Sub

We also want to add another query for our screen that will only pull up tasks for the logged in user. Create another query but this time right-click on the Tasks entity in the Solution Explorer and select “Add Query”. This time call it MyTasks and add a filter by expanding the AssignedTo property and selecting Account equals a new parameter named account. Also add a sort by DueDate ascending.

Creating a Screen to Edit SharePoint Data

Next we just need to create a screen based on the MyTasks query. From the query designer you can click the “Add Screen” button or you can right click on the Screens node in the Solution Explorer and select Add Screen. Select the List and Details Screen and for the Screen Data select MyTasks. Click OK

The Screen Designer opens and you should notice that LightSwitch has added a textbox at the top of our content tree for the screen parameter that it added called TaskAccount which is fed into the query parameter Account. Once we set the TaskAccount with a value the query will execute. As it is though, the user would need to enter that data manually and that’s not what we want. We want to set that control to a Label and set the value programmatically.

To do that drop down the Write Code button on the Screen Designer and select the InitializeDataWorkspace method. This method runs before any data is loaded. Here is where we will set the value of TaskAccount to the logged in user:

 Private Sub MyTasksListDetail_InitializeDataWorkspace(
            saveChangesTo As System.Collections.Generic.List(Of Microsoft.LightSwitch.IDataService))
    ' Write your code here.
    Me.TaskAccount = Me.Application.User.Name
End Sub

Hit F5 to build and run the application. The My Task List Detail screen should open with just the logged in user’s tasks. Click the “Design Screen” at the top right to tweak the layout. I suggest changing the ModifiedBy and CreatedBy to summary fields because these are automatically filled in by SharePoint when you save. I also changed the List to a DataGrid and show the DueDate since we are sorting by that and made the description field bigger. You can make any other modifications you need here.

Add a new task and you will also see the AssignedTo field is automatically filled out us when we add new records. Cool! In the next post I’ll show you how you can relate SharePoint list data to your LightSwitch application data and work with it on the same screen.

Enjoy!