Archive for July, 2017

This is the second part in my series on developing code to modify external lists.  In this post we will be updating SharePoint external lists using CSOM.  Like standard PowerShell there is not any real difference in the execution of the update between internal and external lists.  What is different is how the data is obtained when performing an update, read or deletion.  Like PowerShell, in CSOM you can find the item you wish to access using the ID.  However, because there isn’t a list ID field in an external list you have to query to find the item(s) you need.  You could loop through each item in the list until you find one with the particular attributes you are looking for, but that is not efficient and it may be difficult if the fields you are checking have multiple rows with the same values.  So brush up on your CAML query.

Using CSOM to Manage Data in Internal Lists

CSOM has come a long way since its inception in SharePoint 2010.  I would still be recommending it over REST except that unlike REST things are a bit different between on-prem and SharePoint Online CSOM versions (you need a different versions for each).  This post isn’t meant as a discussion on the pros and cons of REST vs CSOM though (You can get more information here if you are curious though).  CSOM is probably a little more complicated than the PowerShell examples I provided in my previous post, but one you get the hang of loading the data you need before working with it the process is pretty easy.

To show you what we are going to be doing here’s the list we will be starting off with:

Using this config:

<Lists>
    <List ParentSiteURL="http://teams.drevsp2016.com/bcs/" ListName="DemoCode">
        <ListItems>
            <ListItem Action="Insert" ID="">
                <Fields>
                    <Field Title="Title" Value="CSOM Added Item"/>
                    <Field Title="Description" Value="This Field was created using PowerShell and CSOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Update" ID="5">
                <Fields>
                    <Field Title="Title" Value="BCS is the bees knees"/>
                    <Field Title="Description" Value="This Field was UPDATED using PowerShell and CSOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Update" ID="11">
                <Fields>
                    <Field Title="Title" Value="I did this with CSOM"/>
                    <Field Title="Description" Value="This Field was UPDATED using PowerShell and CSOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Read" ID="5">
                <Fields>
                    <Field Title="Title" Value=""/>
                    <Field Title="Description" Value=""/>
                </Fields>
            </ListItem>
            <ListItem Action="Delete" ID="1">
                <Fields>
                    <Field Title="" Value=""/>
                    <Field Title="" Value=""/>
                </Fields>
            </ListItem>
        </ListItems>
    </List>
</Lists>

we will update the list with the following code:

foreach($xmlList in $testInternalList.Config.Lists.List)
{
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($xmlList.ParentSiteURL)
    $spLists = $ctx.web.Lists;
    $spList = $spLists.GetByTitle($xmlList.ListName);

    foreach($xmlItem in $xmlList.ListItems.ListItem)
    {
        switch($xmlItem.Action)
        {
            "Insert"
            {
                $spListItemInfo =  New-Object Microsoft.SharePoint.Client.ListItemCreationInformation;
                $newListItem = $spList.AddItem($spListItemInfo);
                
                foreach($xmlField in $xmlItem.Fields.Field)
                {
                    $newListItem[$xmlField.Title] = $xmlField.Value;
                }

                #Update the new list item
                $newListItem.Update();

                #execute the query
                $ctx.load($spList);      
                $ctx.executeQuery();

                break;
            }

            "Update"
            {
                $updateListItem = $spList.GetItemByID($xmlItem.ID);

                foreach($xmlField in $xmlItem.Fields.Field)
                {
                    $updateListItem[$xmlField.Title] = $xmlField.Value;
                }

                #Update the new list item
                $updateListItem.Update();

                #execute the query
                $ctx.load($updateListItem);      
                $ctx.executeQuery();

                break;
            }

            "Read"
            {
                $readListItem =  $spList.GetItemByID($xmlItem.ID);

                #execute the query
                $ctx.load($readListItem);      
                $ctx.executeQuery();

                foreach($xmlField in $xmlItem.Fields.Field)
                {
                    write-host("The Value of field '{0}' is '{1}'" -f $xmlField.Title, $readListItem[$xmlField.Title]) -ForegroundColor Cyan;
                }
                
                break;
            }
            "Delete"
            {
                $deleteListItem =  $spList.GetItemByID($xmlItem.ID);
                $deleteListItem.DeleteObject();

                #execute the query      
                $ctx.executeQuery();

                Write-Host("Successfully deleted item #{0}" -f $xmlItem.ID) -ForegroundColor Magenta;
                
                break;
            }
        }
    }
}

Running the code:

The list gets updated as expected:

Using CSOM to Update External Lists

As I stated above, the code is very similar to internal lists except there you need to use CAML Query to access the items.  Note with CSOM you don’t have to define the ServiceContext scope.

The list:

Will get modified based on this config:

<Lists>
    <List ParentSiteURL="http://teams.drevsp2016.com/bcs/" ParentSiteColURL="http://teams.drevsp2016.com" ListName="SmallAsset">
        <ListItems>
            <ListItem Action="Insert" AssetID="">
                <Fields>
                    <Field Title="Type" Value="Tablet"/>
                    <Field Title="Make" Value="Microsoft"/>
                    <Field Title="Model" Value="Surface Pro 4"/>                        
                    <Field Title="Details" Value="This Item Added using PowerShell and CSOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Update" AssetID="30">
                <Fields>
                    <Field Title="Location" Value="Denver"/>
                    <Field Title="Details" Value="This Field was UPDATED using PowerShell and CSOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Update" AssetID="29">
                <Fields>
                    <Field Title="Type" Value="Monitor"/>
                    <Field Title="Make" Value="Samsung"/>
                    <Field Title="Model" Value="30inch"/>
                    <Field Title="Details" Value="This Field was UPDATED using PowerShell and CSOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Read" AssetID="9">
                <Fields>
                    <Field Title="Type" Value=""/>
                    <Field Title="Make" Value=""/>
                    <Field Title="Model" Value=""/>
                    <Field Title="Details" Value=""/>
                </Fields>
            </ListItem>
            <ListItem Action="Delete" AssetID="32">
                <Fields>
                    <Field Title="" Value=""/>
                    <Field Title="" Value=""/>
                </Fields>
            </ListItem>
        </ListItems>
    </List>
</Lists>

Reviewing the code, note the insertion of the CAML Query to find a specific item.  One important thing to note with the CAML query.  You have to use the name of the function used to define the Read-List process in the External Content Type

foreach($xmlList in $testExternalList.Config.Lists.List)
{
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($xmlList.ParentSiteURL)
    $spLists = $ctx.web.Lists;
    $spList = $spLists.GetByTitle($xmlList.ListName);

    foreach($xmlItem in $xmlList.ListItems.ListItem)
    {
        switch($xmlItem.Action)
        {
            "Insert"
            {
                $spListItemInfo =  New-Object Microsoft.SharePoint.Client.ListItemCreationInformation;
                $newListItem = $spList.AddItem($spListItemInfo);
                
                foreach($xmlField in $xmlItem.Fields.Field)
                {
                    $newListItem[$xmlField.Title] = $xmlField.Value;
                }

                #Update the new list item
                $newListItem.Update();

                #execute the query
                $ctx.load($spList);      
                $ctx.executeQuery();

                break;
            }

            "Update"
            {
                $camlText = ("<View><Method Name='Read List'/><Query><Where><Eq><FieldRef Name='AssetID' /><Value Type='Number'>{0}</Value></Eq></Where></Query></View>" -f [int]$xmlItem.AssetID);
                $spQuery = New-Object Microsoft.SharePoint.Client.CamlQuery;
                $spQuery.ViewXml = $camlText;

                $updateItemList = $spList.GetItems($spQuery);

                $ctx.Load($updateItemList)
                $ctx.ExecuteQuery()

                foreach($updateListItem in $updateItemList)
                {
                    foreach($xmlField in $xmlItem.Fields.Field)
                    {
                        $updateListItem[$xmlField.Title] = $xmlField.Value;
                    }

                    #Update the new list item
                    $updateListItem.Update();

                    #execute the query
                    $ctx.load($updateListItem);      
                    $ctx.executeQuery();
                }
                break;
            }

            "Read"
            {
                $camlText = ("<View><Method Name='Read List'/><Query><Where><Eq><FieldRef Name='AssetID' /><Value Type='Number'>{0}</Value></Eq></Where></Query><ViewFields><FieldRef Name='Type' /><FieldRef Name='Make' /><FieldRef Name='Model' /><FieldRef Name='Details' /></ViewFields></View>" -f [int]$xmlItem.AssetID);
                $spQuery = New-Object Microsoft.SharePoint.Client.CamlQuery;
                $spQuery.ViewXml = $camlText;

                $readItemList = $spList.GetItems($spQuery);

                $ctx.Load($readItemList)
                $ctx.ExecuteQuery()
                
                foreach($readListItem in $readItemList)
                {
                    foreach($xmlField in $xmlItem.Fields.Field)
                    {
                        write-host("The Value of field '{0}' is '{1}'" -f $xmlField.Title, $readListItem[$xmlField.Title]) -ForegroundColor Cyan;
                    }
                }
                break;
            }
            "Delete"
            {
                $camlText = ("<View><Method Name='Read List'/><Query><Where><Eq><FieldRef Name='AssetID' /><Value Type='Number'>{0}</Value></Eq></Where></Query></View>" -f [int]$xmlItem.AssetID);
                $spQuery = New-Object Microsoft.SharePoint.Client.CamlQuery;
                $spQuery.ViewXml = $camlText;

                $delItemList = $spList.GetItems($spQuery);

                $ctx.Load($delItemList)
                $ctx.ExecuteQuery()
                
                $deleteListItem = $delItemList[0];  
                #$deleteListItem =  $spList.GetItemByID($xmlItem.ID);
                $deleteListItem.DeleteObject();

                #execute the query      
                $ctx.executeQuery();

                Write-Host("Successfully deleted item #{0}" -f $xmlItem.AssetID) -ForegroundColor Magenta;
                
                break;
            }
        }
    }
}

Running the code:

Leaves us with the following updated list:

So again, other than gathering the item to update, managing items in an external list is very similar to managing items in an internal list.  My next post we will cover the differences in manipulating internal and external items with REST.

Thanks for reading!!