In this post we will explore accessing a SharePoint Online Custom List from an ASP.NET Web API using CSOM. You can find the source code on Git Hub: https://github.com/codedebate/Samples-SPOAccessWebAPI.

While many services exist today to ease access and orchestration of data and process flows like Power Platform and Azure Logic Apps, etc. You will run into scenarios where your own REST API is required. With embracing the citizen developer culture at a large number of companies today, many power users resort to PowerApps for building single-purpose basic business applications and use SharePoint Online Custom Lists as the database. This is why I wrote this blog post 🙂

When dealing with on-premises SharePoint deployments, SharePoint farm solutions have dominated the stage for a long time as the high risk/high reward type of answer to every problem, be it Event Receivers, Workflows, etc. Back then, you really had to know what you were doing, otherwise you might crash the whole SharePoint farm if not being careful. The catch of course was that your DLLs always sat on the SharePoint Servers with the .NET Framework Global Assembly Cache (GAC).

With SharePoint Online, this is not the case, you will need to use SharePoint Client Object Model (CSOM) and you will need to to include extra plumbing work for access authorization using bearer access tokens. If not done right, you will waste time chasing error codes across the web.

So let’s get to know the sample we are about to build. For starters we have a Custom List called “GarageParkedCars” and its there like any pointless demo asset to log something, in this case it’s being used to store cars being parked in our office garage. For each record we store: the plate numbers, driver, parking spot number, and the date. Check the list below. We will build an ASP.NET Web API that will use CSOM to consume the Custom List.

Step 1: Know your NuGet packages

To do this correctly, and avoid wasting your time, you will need to install the following packages after creating your ASP.NET Web API project. Make sure you respect the sequential order below:

1. Microsoft.IdentityModel
2. Microsoft.Identity.Model.Extensions
3. AppForSharePointOnlineWebToolkit

Yes the name AppForSharePointOnlineWebToolkit sounds weird. Trust me, you will need it to use CSOM effectively and consume access tokens to access content from SharePoint Online.

Now, you might ask, why the panic and warnings for following the order mentioned above, if not, well then you will end up with the following fun error message: “Failed to add reference. The package ‘AppForSharePointOnlineWebToolkit’ tried to add a framework reference to ‘Microsoft.IdentityModel’ which was not found in the GAC. This is possibly a bug in the package. Please contact the package owners for assistance.Reference unavailable.“. Check out the screenshot below for dramatic effects 😉 Looking for a resolution over the Web, especially for this error, will take you to the wrong places.

Step 2: Register your Web API in SharePoint Online

To do that, use your site collection URL and navigate to “/_layouts/15/AppRegNew.aspx”. Since I am not actually deploying this sampleWeb API and will be testing it from my machine locally, I used localhost. Make sure to use the correct domain, and watch out for sub-domains.

Once done with the registration, make sure to note both Client Id and Client Secret. We will need them both to request the access token later on when accessing the SharePoint Online Custom List.

Step 3: Authorize your new registered Web API in SharePoint Online

To do that, use your site collection URL and navigate to “/_layouts/15/AppInv.aspx”. To start, paste the Client Id into the App Id field and click on Lookup. Not sure why it’s called App Id instead of Client Id.

Don’t be spooked by the name “App’s Permission Request XML”. This is simple XML block that defines what type of authorization the app will have, in this case our Web API. For the sake of this demo, I used the below block, which simply granted my Web API access to the Site Collection. Don’t do this in a real production setup. Make sure to check the link here for the examples of what can be the right XML block depending on your scenario.

<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl"/>
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="FullControl"/>
</AppPermissionRequests>

Once you create the authorization, SharePoint will ask you if you Trust the Web API with the permissions you have listed in the XML block, this time they are listed in simple English.

Side Note

At any time you can remove the authorization by navigating to the Site Settings page and clicking on “Site collection app permissions”

Once in, you will find a list of all registered applications, and in our sample the Web API. To revoke access, simply proceed and delete the registration.

Step 4: Add the AppSettings keys to the Web API configuration file

So to recap our progress until now:

• We created the ASP.NET Web API project and installed the needed NuGet packages
• We registered the Web API in SharePoint Online and granted it access authorization

Now, we need to take advantage of the web toolkit and make sure it can request an access token for our CSOM requests to SharePoint Online. To do that, we need to add both ClientId and ClientSecret keys and use the values we obtained earlier when we registered the Web API in SharePoint Online. Make sure you have the correct spelling for the keys, otherwise web toolkit will not be able to find them and request the access token.

Step 5: Use CSOM with CAML to query the SharePoint Online Custom List

Now we are ready to get some action with SharePoint Online and our Web API. Let’s start first by creating a model to simplify access and storage of the Custom List items.

public class GarageParkedCar
{
    public string PlateNumber { get; set; }
    public string Driver { get; set; }
    public string ParkingSpot { get; set; }
    public string RecordCreated { get; set; }

    public GarageParkedCar(string plateNumber, string driver, string parkingSpot, string recordCreated)
    {
        PlateNumber = plateNumber;
        Driver = driver;
        ParkingSpot = parkingSpot;
        RecordCreated = recordCreated;
    }

    public GarageParkedCar()
    {
    }
}

Next, make sure to update your Web.config file with two additional keys in AppSettings, one for the WebUri and the other is for the ListTitle. The WebUri is the URL to the SharePoint Online site that hosts the Custom List and the ListTitle is the name of the Custom List.

Finally our GET action. I have included comments inline with the code to explain it. Couple of thoughts here:

1. We create a collection to store the list items coming back. Some folks like to use DataTables, I like to use a strongly typed approach instead 🙂
2. We use Collaborative Application Markup Language (CAML) to build queries for filtering the list items on SharePoint Online instead of getting all list items and using LINQ. This comes very handy and performant when your lists have lots of list items and/or large number of requests

// GET: api/GarageParkedCar
public IEnumerable<GarageParkedCar> Get()
{
    // The collection we will use to store and return 
    // all the records coming back from the SharePoint Online Custom List
    var response = new List<GarageParkedCar>();

    // Get the URL to the SharePoint Online site
    var webUri = 
        new Uri(
            ConfigurationManager.AppSettings["WebUri"]);

    // Get the access token. The web toolkit will do all the work
    // for you. Remember, you will need ClientId and ClientSecret in the Web.config
    var realm = TokenHelper.GetRealmFromTargetUrl(webUri);
    var accessToken = TokenHelper.GetAppOnlyAccessToken(
        TokenHelper.SharePointPrincipal,
        webUri.Authority, realm).AccessToken;

    // Initialize the SharePoint Online access context 
    var context = TokenHelper.GetClientContextWithAccessToken(
        webUri.ToString(), accessToken);

    // Create an object to access the SharePoint Online Custom List
    var garageParkedCarsList = 
        context.Web.Lists.GetByTitle(
            ConfigurationManager.AppSettings["ListTitle"]);

    // Create a new query to filter the list items. As we are looking to 
    // retrieve all items, you can leave the query blank
    var query = new CamlQuery();

    // Create an object to store the list items coming back from 
    // SharePoint Online and execute the query request
    var garageParkedCarsCollection = garageParkedCarsList.GetItems(query);
    context.Load(garageParkedCarsCollection);
    context.ExecuteQuery();

    // Loop all list items coming back, and create a new object from our
    // model for each list item, so we can access and process them
    foreach (var item in garageParkedCarsCollection)
    {
        response.Add(
            new GarageParkedCar(
                item["Title"].ToString(), 
                item["Driver"].ToString(), 
                item["ParkingSpot"].ToString(), 
                item["Created"].ToString()));
    }

    // Return the collection back to as the response 
    return response;
}

So, let’s go ahead and run our Web API, simplest way, hit F5 and navigate to “/api/GarageParkedCar“. This will trigger the GET action, and query SharePoint Online for the list of parked car records in the garage. For dramatic effect, the screenshot below uses the XML output instead of JSON 😉

What’s Next?

You have a simple way for accessing SharePoint Online resources using CSOM. The web toolkit takes care of authentication/authorization, and simply just looks up Client Id and Client Secret from your Web.config file. A word of advice to close this post, always try exploring Azure Logic Apps, Functions, Flow, and other no-code/low-code tools before considering writing your own REST API. It’s awesome to have your own code running, I get it. Yet from a maintenance and evolution perspective, double check if this is really needed.