Using SharePoint Lists as LightSwitch Entities and with CSOM (Chris Rummel)

Using SharePoint Lists as LightSwitch Entities and with CSOM (Chris Rummel)

Rate This
  • Comments 3

Hi, I’m Chris Rummel, a tester on the LightSwitch team.  I work mostly on SharePoint features.  Brian has already gone over a lot of the benefits of SharePoint development with LightSwitch in his article A quick & easy LightSwitch app for SharePoint. In this post I wanted to go a little more in depth on using SharePoint data like lists in LightSwitch. If you haven’t read his article I encourage you to do so first.

Lists as Entities

Brian already covered attaching to a SharePoint list and modeling its entities in a SharePoint-enabled LightSwitch application.  There's more than that though - you can attach to an on-premises SharePoint site in any LightSwitch app using either a given username and password (so you could lock down the list so that only your LightSwitch app has access to it), or as the user using the LightSwitch app.

image

If you enable SharePoint in your LightSwitch application you’re indicating to LightSwitch that the app should be deployed to SharePoint and can work with SharePoint assets. You do this on the SharePoint Properties page:

image

If you point at an Office 365 site, you can also attach to lists on that Office 365 site, but only by letting LightSwitch choose the user identity for you.  At runtime, this is whoever launched the app from SharePoint.  While debugging, this is probably you or your test account.  So if you see an error message like “[…] does not appear to be a valid site.  SharePoint 2010 (or later) with an installation of WCF Data Services is required” when attempting to attach to a list on Office 365, then the problem is likely to be either SharePoint not being enabled, not using automatic identity, or you’re trying to attach to a different site than the one specified in the SharePoint properties.  If you are deploying to an Office 365 site, then that’s the only Office 365 site you can attach to with LightSwitch.

So what’s the advantage of using SharePoint lists as entities?  You can now treat the list data like any other LightSwitch data source – including using custom code in the save pipeline.  So using Brian’s example, after we add the customer list to the site, let’s make a few changes.  When a customer calls in, if one of our sales reps recently talked to them about something we’d like to be able to direct them to that same representative – they know the most about what’s going on and the customer will probably be more comfortable talking to them.  So let’s edit the list a bit.  Click on the Customers app tile and then on “Edit this list”:

image

Click on the add column plus sign, and then select “Date or Time”:

image

Call this column “Last Contact Date”.  Then add another column, this time a String called “Last Contact Person”.  Your list should look something like this:

image

Then select “Stop editing this list”.  Back in LightSwitch, if you already added the SharePoint site as a data source, right-click on it and select “Update Datasource”:

image

And just click “Finish”.  Then you can open up the Customer entity and see the new fields:

image

While we’re here, if you haven’t already changed the summary property of the Customer to be “FirstName”, now is a good time to do so.  This will make LightSwitch show the customer's first name in summary view that is used by default when you add a screen.  Be sure to click on the Client view button first:

image

Since we’re keeping track of our customer calls, let’s make a table for that.  Mine looks something like this:

image

That is; I added a new table with the name “CustomerCall”, and then added a relationship to the Customer list:

image

Then I added some additional fields.  Type is a choice list with the options of Refund, Purchase, Quote, and Other:

image

I also added ContactPerson for the person who talked to the customer and Notes for any general information.  We’ll keep this screen simple, just a Browse screen for CustomerCalls with an AddAndEditNew button sending us to an AddEditCustomerCall dialog.

image

image

So far this really isn’t anything new, but now let’s open up the CustomerCall entity again and write some code.  Drop down the “Write Code” button at the top of the Data Designer and select the Inserting method:

image

Then we can add this code:

(C#)

partial void CustomerCalls_Inserting(CustomerCall entity)
{
    entity.Customer.LastContactDate = DateTime.Now;
    entity.Customer.LastContactPerson = entity.ContactPerson;

    ServerApplicationContext.Current.DataWorkspace.SharePoint_SiteData.SaveChanges();
}

(Visual Basic)

Private Sub CustomerCalls_Inserting(entity As CustomerCall) 
entity.Customer.LastContactDate =
DateTime.Now
entity.Customer.LastContactPerson = entity.ContactPerson
ServerApplicationContext.Current.DataWorkspace.SharePoint_SiteData.SaveChanges()
End Sub

Note that you'll have to change the highlighted portion to match your data source name.

Now we can F5 the project and log a new customer call:

image

Then if you go back to your Customer list you should see something like this:

image

A pretty simple example, but it’s cool to see that SharePoint lists work a lot like every other entity type in LightSwitch.  We’ll do more like this in just a bit.

List Manipulation with CSOM

But let’s say the simple life is not for you.  If you want to be able to control every aspect of how LightSwitch is interfacing with SharePoint, you have access to the SharePoint Client-Side Object Model (CSOM).  One scenario this might come up in is a SharePoint-enabled LightSwitch app – you can add SharePoint lists to your project, but LightSwitch can’t attach to them because they don’t actually exist until the app is installed on a SharePoint site.

Building off Brian’s app again, we have Browse and Edit Customer screens already. Customer information is sensitive, so we’re going to audit any changes to it. Let’s add a list to our SharePoint project to capture this information.

Note that you may not want to do it this way - lists you add to your SharePoint project belong to the app web, which means they are created when the app is installed with the LightSwitch application and deleted when it is uninstalled, losing all the data.  This is different from the Customers list – which belongs to the host web and isn't related to a specific app.  MSDN has more information here on Host webs, app webs, and SharePoint components in SharePoint 2013.

This is just an example though, so we won't worry about keeping this data forever.  Switch to file view in project explorer, right-click on your SharePoint project, and select Add –> New Item:

image

“List” should be selected by default here; name it “AuditEntry” and click “Add”.  On the next screen LightSwitch will ask you what kind of list you want.  An audit log isn’t one of the defaults, so leave it as “Default (Custom List)” and we’ll set it up ourselves:

image

The list columns design will come up next.  Here we’ll want to add a Number field for the Customer ID of the customer being changed, a Text field for the user making the change, Date that the change happened, and a text field for the action taken:

image

If you try to update the SharePoint data source now, you wouldn’t see this list, because it doesn’t exist on the site until the project is either F5’d or deployed from a published SharePoint app package.  But CSOM will let us work with it anyway!  Open up the Customer entity and choose to write an Updating method:

image

Fill in your SharePoint_SiteDataService class (or whatever name you chose again) like so:

(C#)

using Microsoft.SharePoint.Client;

public partial class SharePoint_SiteDataService
{
    partial void Customers_Updating(Customer entity)
    {
        LogAuditEntry(entity, "Update");
    }

    partial void Customers_Deleting(Customer entity)
    {
        LogAuditEntry(entity, "Delete");
    }

    private void LogAuditEntry(Customer entity, string actionDescription)
    {
        using (ClientContext ctx = Application.SharePoint.GetAppWebClientContext())
        {
            Web web = ctx.Web;
            ListCollection lists = web.Lists;
            List auditLog = lists.GetByTitle("AuditEntry");
            ListItemCreationInformation ci = new ListItemCreationInformation();
            ListItem auditEntry = auditLog.AddItem(ci);
            auditEntry["Title"] = "Audit entry";
            auditEntry["CustomerID1"] = entity.Id;
            auditEntry["User1"] = Application.User.FullName;
            auditEntry["Date1"] = DateTime.Now;
            auditEntry["Action"] = actionDescription;
            auditEntry.Update();
            ctx.ExecuteQuery();
        }
    }
}

(Visual Basic)

Imports Microsoft.SharePoint.Client


Public Class
SharePoint_SiteDataService
Private Sub Customers_Updating(entity As Customer)
LogAuditEntry(entity,
"Update")
End Sub
 Private Sub Customers_Deleting(entity As Customer) 
LogAuditEntry(entity,
"Delete")
End Sub

Private Sub
LogAuditEntry(entity As Customer, actionDescription As String)
Using ctx As ClientContext = Application.SharePoint.GetAppWebClientContext()
Dim web As Web = ctx.Web
Dim lists As ListCollection = Web.Lists
Dim auditLog As List = lists.GetByTitle("AuditEntry")
Dim ci As New ListItemCreationInformation()
Dim auditEntry As ListItem = auditLog.AddItem(ci)


auditEntry(
"Title") = "Audit entry"
auditEntry("CustomerID1") = entity.Id
auditEntry(
"User1") = Application.User.FullName
auditEntry(
"Date1") = DateTime.Now
auditEntry(
"Action") = actionDescription

auditEntry.Update()
ctx.ExecuteQuery()
End Using
End Sub
End Class

Now if you F5 your project again and make a change to a customer, this is written out into the list that we added to the SharePoint project – when you F5’d, LightSwitch deployed your project to SharePoint, which includes creating items that you’ve added to the SharePoint project.  Now where is the list?  Take your SharePoint site URL, including the Developer site collection name if you have one, then add your app name and “/Lists/AuditEntry”.  For example, https://example.sharepoint.com/sites/Developer/ListAttachDemo/Lists/AuditEntry is mine.  This will look something like this:

image

So we can use lists outside of LightSwitch’s list attach too.  The other thing you may have noticed is the inconsistent column names for the list entry – “User1” vs. just plain “Action”.  I wanted to mention this since it stumped me at first – the column name SharePoint shows either in the list designer or on the web is sometimes misleading.  If you use a column name that SharePoint also thinks of as a type name or other reserved word, it’ll append a number to disambiguate.  So how do you know what the real column name is?  I use this:

(C#)

using (ClientContext ctx = Application.SharePoint.GetAppWebClientContext())
{
     Web web = ctx.Web;
     ListCollection lists = web.Lists;
     List auditLog = lists.GetByTitle("AuditEntry");
     ctx.Load(auditLog.Fields);
     ctx.ExecuteQuery();
}

(Visual Basic)

Using ctx As ClientContext = Application.SharePoint.GetAppWebClientContext() 
Dim web As Web = ctx.Web
Dim lists As ListCollection = Web.Lists
Dim auditLog As List = lists.GetByTitle("AuditEntry")

ctx.Load(auditLog.Fields)
ctx.ExecuteQuery()
End Using

You can set a breakpoint after this and look at the contents of auditLog.Fields to see what SharePoint actually thinks the names of the columns are.  If you get an error like “Column <ColumnName> does not exist.  It may have been deleted by another user”, then this is the first thing I would try.

There’s a lot of interesting things you can do with CSOM – just check out the MSDN reference page for more.  Adding and changing lists themselves, doing site administration tasks, uploading documents, and user and group management are all available through CSOM.  If there’s anything in particular you’re interested in, let me know and we’ll see if I can whip something up with it.

Chris Rummel – Tester, LightSwitch Team

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • This is very good content, but I'm confused as to why LightSwitch cannot also see the lists that are to be deployed along with the application (in SharePoint enabled apps). I can think of multiple scenarios where an App will need to be deployed with a list that is specific to the app. What makes LightSwitch so attractive is the ability to quickly define screens and business logic based on the data model used for the project. The use of CSOM is great in this scenario (auditing or co-writing records), but imagine a front end to an issues list or a custom list that will need browse, view and add/edit screens. The list should be a part of the application under the context of the local site where the application is deployed -- not pointing to a global list.

    Is there any guidance coming on how to accomplish that scenario?

  • Hi Chris, thanks for the great feedback.  We're always looking into ways to make LightSwitch easier.  Until then, on of the suggestions we got in the forums was to use a custom RIA service as a layer for SharePoint if you'd like to try that.

  • @Chris - thanks for the feedback.  This is something we debated a bit internally and you understand the current situation perfectly.  When you create and deploy the list with your project, there's no way to include that list in the LightSwitch model and leverage "what makes LightSwitch so attractive".  We are looking at this for a future release, but unfortunately I don't have a better answer for you today.

Page 1 of 1 (3 items)