CSOM

Csom Context Sharepoint ClientContext

By fredericdietrich On 23/05/2023

c# Connect to Sharepoint with ClientContext appId and certificat ThumbPrint


using Microsoft.Identity.Client;
using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Security;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;

namespace MY.PNP.Powershell.EXT.CSOM
{
    public class CsomContext : IDisposable
    {
        public ClientContext _clientContext;
        public ClientRuntimeContext _context;

        private async Task GetAccessToken(string tenantName, string clientId, string thumbprint)
        {
            var certificate = GetCert(thumbprint);
            var authority = $"https://login.microsoftonline.com/{tenantName}.onmicrosoft.com/";
            var azureApp = ConfidentialClientApplicationBuilder.Create(clientId)
                .WithAuthority(authority)
                .WithCertificate(certificate)
                .Build();

            var scopes = new string[] { $"https://{tenantName}.sharepoint.com/.default" };
            var authResult = await azureApp.AcquireTokenForClient(scopes).ExecuteAsync();
            return authResult.AccessToken;
        }

        public async Task CallClientObjectModel(string tenantName, string url, string clientId, string thumbprint)
        {
            var token = await GetAccessToken(tenantName, clientId, thumbprint);
            var siteUrl = url;//;$"https://{tenantName}.sharepoint.com";

            var context = new ClientContext(siteUrl);

            context.ExecutingWebRequest += (s, e) =>
            {
                e.WebRequestExecutor.RequestHeaders["Authorization"] =
                    "Bearer " + token;
            };

            var web = context.Web;
            context.Load(web);
            context.ExecuteQuery();
            Console.WriteLine(web.Title);
            Console.WriteLine(web.Url);
            _context = web.Context;
            _clientContext = context;
        }

        private X509Certificate2 GetCert(string thumbprint)
        {
            X509Store certStore = new X509Store(StoreName.My, StoreLocation.CurrentUser);
            certStore.Open(OpenFlags.ReadOnly);
            X509Certificate2Collection certCollection = certStore.Certificates.Find(
                                       X509FindType.FindByThumbprint,
                                         // Replace below with your cert's thumbprint
                                         thumbprint,
                                       false);
            X509Certificate2 cert = null;
            // Get the first cert with the thumbprint
            if (certCollection.Count > 0)
            {
                cert = certCollection[0];
                // Use certificate
                Console.WriteLine(cert.FriendlyName);
            }
            certStore.Close();
            return cert;
        }

        public void Dispose()
        {
            if (_context != null)
                _context.Dispose();
            if (_clientContext != null)
                _clientContext.Dispose();
        }
    }
}

In Powershell

Add WebPart On Modern Page PNP Powershell

By fredericdietrich On 07/05/2021

Add custom WebPart On Modern Page Powershell

List your webpart with :

$page = Get-PnPPage -Identity "aTestDisp2.aspx"

$page.AvailablePageComponents() select your webpart by name or guid

Listwp 1


param([string]$siteUrl="https://myyTenant.sharepoint.com/sites/muSite", [string]$pageName="aTestDisp2", [string]$webPartName="Test - Display Item")
clear
Connect-PnPOnline -Url $siteUrl -UseWebLogin
#set your wenpart properties
$wpProps = @{webUrl="https://myyTenantsharepoint.com/sites/muSite"
            listId="b4d5f780-bd74-4cd5-b051-fed423e43125"
            field="Title"
            itemId="1"}

#get the page
$page = Get-PnPPage -Identity $pageName

# get the target webpart
$wp = $page.AvailablePageComponents() | Where-Object {$_.Name -eq $webPartName }
if($wp -eq $null)
{
    Write-Host "webpart '$($webPartName)' not found " -ForegroundColor:Red
}
#Add webpart with default user, to column 1 in section 1
Add-PnPPageWebPart -Page $pageName -Component $wp -Section 1 -Column 1 -WebPartProperties $wpProps
Disconnect-PnPOnline

You can check webpart properties, in result

Wpresults

In Powershell

PowerShell Csom Batch Delete / delete all list items

By fredericdietrich On 09/03/2021

Powershell CSOM clear list items / delete all items


clear
# Connect-PNPOnline -url $ProdWaveplaceURL -UseWebLogin -ErrorAction Stop
# Disconnect-PnPOnline

$listTitle = "testTaxo"
$list = Get-PnPList -Identity $listTitle -ThrowExceptionIfListNotFound


$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 100
$startDate = Get-Date
$deletedItemsCount = 0;

Do{
    $stringBuilder = New-Object System.Text.StringBuilder
 
    $stringBuilder.Append("<View scope='RecursiveAll'>") | Out-Null
    $stringBuilder.Append("<Query><Where></Where>")| Out-Null
    $stringBuilder.Append("<Orderby><Fieldref ascending='TRUE' name='ID'></Fieldref></Orderby>")| Out-Null
    $stringBuilder.Append("</Query>")| Out-Null
    $stringBuilder.Append("<Viewfields>")| Out-Null
    $stringBuilder.Append("<Fieldref name='ID' />")| Out-Null
    $stringBuilder.Append("</Viewfields>")| Out-Null
    $stringBuilder.Append("<Rowlimit paged='TRUE'>$($rowLimit)</Rowlimit>")| Out-Null
    $stringBuilder.Append("</View>")| Out-Null
    
    $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $spqQuery.ViewXml = $stringBuilder.ToString();
    $spqQuery.ListItemCollectionPosition = $page


    $pageNumber ++;

    $spqQuery.ViewXml = $stringBuilder.ToString();
    $itemki=$list.GetItems($spqQuery); 
    $spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition

    $ctx.Load($itemki)
    $ctx.ExecuteQuery();


    Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
    Write-Host "processing query results. Recs: $($itemki.Count)"

    $Counter = $itemki.Count;
    if($itemki.Count -eq 0){
        exit 0
    }
    do{
        $itemki[$Counter - 1].DeleteObject()

        $Counter--
        $deletedItemsCount++
    }while($Counter -gt 0)
    Invoke-PnPQuery
    Write-Host "deletedItemsCount $($deletedItemsCount)"
    $page = $itemki.ListItemCollectionPosition
    $comp = $endDate - $startDate
    $endDate = Get-Date
    Write-Host "time to delete $($rowLimit) elements : Days '$($comp.Days)' Hours '$($comp.Hours)' Minutes '$($comp.Minutes)' Seconds '$($comp.Seconds)'"
 }   
 Until($page -eq $null) 

$comp = $endDate - $startDate
Write-Host "time to delete : Days '$($comp.Days)' Hours '$($comp.Hours)' Minutes '$($comp.Minutes)' Seconds '$($comp.Seconds)'"

In SQL

Sharepoint Caml Query

By fredericdietrich On 08/09/2020

Basic CSOM caml query

Query Null Text fields or not

by ID

Get tasks by itemId (relatedItems in tasks list)

<Where>
   <Eq>
       <FieldRef Name='RelatedItems'/>
       <Value Type='RelatedItems'>""ItemId"":435,</Value>
   </Eq>
</Where>

boolean / Yes/No

Comparators

<Eq> => equals

<Neq> => not equals

<Lt> => Less than

<Leq> => less or equal

<Gt> greater than

<Geq> greater or equal

Errors

Sharepoint error	Error in my cases
One or more field types are not installed properly. Go to the list settings page to delete these fields.	Fields in your query are not in list
Cannot complete this action. Please try again.	Syntax error in your query

In SharepointOnLine

CSOM Delete Items By Id Range

By fredericdietrich On 02/09/2020

CSOM delete items by id range

Get items

public ListItemCollection GetItems(string listRelativUrl, string where, string orderBy, string viewFields, int rowlimit = 100)
{
    string xmlView = "";
    try
    {
        List lst = CurrentWeb.GetList(CurrentWeb.ServerRelativeUrl + listRelativUrl);

        StringBuilder vf = new StringBuilder();
        if (!string.IsNullOrEmpty(viewFields))
        {
            vf.Append("<ViewFields>");
            foreach (string fieldName in viewFields.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
            {
                vf.Append($"<FieldRef Name='{fieldName.Trim()}' />");
            }

            vf.Append("</ViewFields>");
        }

        CamlQuery camlQuery = new CamlQuery();
        camlQuery = new CamlQuery();
        Logger.LogInfo($"listRelativUrl {listRelativUrl} : <View><Query>{where}{orderBy}</Query>{vf.ToString()}<RowLimit>{rowlimit}</RowLimit></View>");
        xmlView = $"<View><Query>{where}{orderBy}</Query>{vf.ToString()}<RowLimit>{rowlimit}</RowLimit></View>";
        camlQuery.ViewXml = xmlView;

        ListItemCollection coll = lst.GetItems(camlQuery);
        SPContext.Load(coll);
        SPContext.ExecuteQuery();

        Logger.Log($"coll.Count : '{coll.Count}' : camlQuery.ViewXml {camlQuery.ViewXml}");

        return coll;
    }
    catch (ServerException ex1)
    {
        Logger.LogError($"Error ServerException Common.Sharepoint.SPTools.GetItems url '{_url}' login : '{_login}' " +
            $" listRelativUrl '{listRelativUrl}' where '{where}' orderBy '{orderBy}' rowlimit '{rowlimit}' xmlView : '{xmlView}' Exception '{ex1}'");
        throw;
    }
    catch (Exception ex)
    {
        Logger.LogError($"Error .Common.Sharepoint.SPTools.GetItems url '{_url}' login : '{_login}' " +
            $" listRelativUrl '{listRelativUrl}' where '{where}' orderBy '{orderBy}' rowlimit '{rowlimit}' xmlView : '{xmlView}' Exception '{ex}'");
        throw;
    }
}

delete items


public int DeleteItemByIdRange(string listRelativeUrl, int rowLimit, int from, int to = -1)
{
    int deleted = 0;
    try
    {
        string req = "";
        if (to == -1)
            req = $"<Where><Geq><FieldRef Name='ID'/><Value Type='Counter'>{from}</Value></Geq></Where>";
        else
        {
            if (from > to)
                throw new Exception($"DeleteItemByIdRange from should be < than to listRelativeUrl '{listRelativeUrl}' from '{from}' to '{to}'");
            req = $"<Where><And><Geq><FieldRef Name='ID' /><Value Type='Counter'>{from}</Value></Geq><Leq><FieldRef Name='ID' /><Value Type='Counter'>{to}</Value></Leq></And></Where>";
        }
        ListItemCollection coll = GetItems(listRelativeUrl, req, "", "ID", rowLimit);

        while (coll.Count > 0)
        {
            string id = coll[0].Id.ToString();
            coll[0].DeleteObject();
            this.ExecuteQuery($"DeleteItemByIdRange listRelativeUrl '{listRelativeUrl}' from '{from}' to '{to}' Id = '{id}'");
            deleted++;
        }
        return deleted;
    }
    catch (Exception ex)
    {
        Logger.LogError($"Error Sharepoint.SPTools.DeleteItemByIdRange from id '{from}' to id '{to}' url '{_url}' login : '{_login}'  Exception '{ex}'");
        throw;
    }
}

catch ServerException to get more details on CSOM exceptions

In Powershell

CSOM powershell SSL TSL site

By fredericdietrich On 23/11/2019

Exception calling "ExecuteQuery" with "0" argument(s): "The request was aborted: Could not create SSL/TLS secure channel." SOLVED

Exception lors de l'appel de ExecuteQuery" avec 0" argument(s): La demandea été abandonnée: Impossible de créer un canal sécurisé SSL/TLS." : résolu

How to connect to a SSL/ TSL sharepoint site with CSOM in powershell

Start to call a request with your certificate

$anUrl = https://mySharepoint

$CertificateThumbprint = "53836D3C35F949959D7E4038D5D39D7B"

$response = Invoke-WebRequest -Verbose -URI $anUrl -CertificateThumbprint $CertificateThumbprint -UseDefaultCredentials -SessionVariable websession -ErrorAction:Stop

# extract cookies from your response, and isolate wich one is authentication cookie

$cookies = $websession.Cookies.GetCookies($anUrl)

$global:cookieName3= "$($cookies[$i].name)".Trim();

$global:cookieVal3 = "$($cookies[$i].value)".Trim();

# get correct certificate in your store

$global:cert = Get-ChildItem -Path cert:\CurrentUser\My | ?{$_.Thumbprint -eq $CertificateThumbprint}

$context987 = New-Object Microsoft.SharePoint.Client.ClientContext($anUrl);

# set the security protocol

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor ` [Net.SecurityProtocolType]::Tls11 -bor ` [Net.SecurityProtocolType]::Tls -bor ` [Net.SecurityProtocolType]::Ssl3

# add an ExecutingWebRequest event, which will be raised on each Execute query

$context987.add_ExecutingWebRequest({ })

# in this ExecutingWebRequest you should add the certificate to yoour request and add the authentification cookie

$request = $EventArgs.WebRequestExecutor.WebRequest

$request.ClientCertificates.Add($global:cert)

$request.CookieContainer = New-Object System.Net.CookieContainer

$c3 = New-Object System.Net.Cookie($global:cookieName3, $global:cookieVal3, "/", $global:cookieDomaine);

$request.CookieContainer.Add($c3);

$context987.Load($context987.web);

$context987.ExecuteQuery();

Write-Host "Title : $($web.Title)"

get full script here

to list all your available certificate execute following script

[System.Security.Cryptography.X509Certificates.X509Store]$storeNew-Object System.Security.Cryptography.X509Certificates.X509Store([System.Security.Cryptography.X509Certificates.StoreName]::My, [System.Security.Cryptography.X509Certificates.StoreLocation]::CurrentUser) # LocalMachine

$store.Open([System.Security.Cryptography.X509Certificates.OpenFlags]::ReadOnly);

$store.Certificates | select Thumbprint, FriendlyName, Subject

Powershell Csom Paged Caml Query

By fredericdietrich On 18/09/2019



$list = Get-PnPList -Identity $listTitle -ThrowExceptionIfListNotFound

$ctx = Get-PnPContext
$page = $null
$pageNumber = 0;
$rowLimit = 200
Do{
    $stringBuilder = New-Object System.Text.StringBuilder
 
    $stringBuilder.Append("<View Scope='RecursiveAll'>") | Out-Null
    $stringBuilder.Append("<Query><Where></Where>")| Out-Null
    $stringBuilder.Append("<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>")| Out-Null
    $stringBuilder.Append("</Query>")| Out-Null
    $stringBuilder.Append("<ViewFields>")| Out-Null
    $stringBuilder.Append("<FieldRef Name='ID' />")| Out-Null
    $stringBuilder.Append("<FieldRef Name='Title' />")| Out-Null
    $stringBuilder.Append("</ViewFields>")| Out-Null
    $stringBuilder.Append("<RowLimit Paged='TRUE'>$($rowLimit)</RowLimit>")| Out-Null
    $stringBuilder.Append("</View>")| Out-Null
    
    $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $spqQuery.ViewXml = $stringBuilder.ToString();
    $spqQuery.ListItemCollectionPosition = $page


    $pageNumber ++;

    $spqQuery.ViewXml = $stringBuilder.ToString();
    $itemki=$list.GetItems($spqQuery); 
    $spqQuery.ListItemCollectionPosition = $itemki.ListItemCollectionPosition

    $ctx.Load($itemki)
    $ctx.ExecuteQuery();


    Write-Host "################## PAGE " $($page.PagingInfo) " #########################"
    Write-Host "processing query results. Recs: $($itemki.Count)"

    $Counter = 0;
    foreach($item in $itemki)
    {
        Write-Host "$($item["ID"]) title pageNumber '$($pageNumber)' : $($item["Title"])"
    }

    $page = $itemki.ListItemCollectionPosition
 }   
 Until($page -eq $null)

get script

In Powershell

Add SPList With CSOM

By fredericdietrich On 15/08/2019

function AddList()

{param($url0, $title)

$context0 = New-Object Microsoft.SharePoint.Client.ClientContext($url0);

$context0.AuthenticationMode = [Microsoft.SharePoint.Client.ClientAuthenticationMode]::FormsAuthentication

$credentials0 = New-Object Microsoft.SharePoint.Client.FormsAuthenticationLoginInfo("d03305", $mypsw);

$context0.FormsAuthenticationLoginInfo = $credentials0;

[Microsoft.SharePoint.Client.Web]$web0 = $context0.Web;

$context0.Load($web0)

$context0.Load($web0.ListTemplates)

$context0.ExecuteQuery()

$template.Name

#check if list exists

$list = $null;

try

{

$list = $web0.GetList("$($web0.ServerRelativeUrl.TrimEnd("/"))/$($title)");

$context0.Load($list);

$context0.ExecuteQuery();

}

catch

{

$list = $null;

}

if($list -eq $null)

{

Write-Host "list is null"

#creation de la liste

$ListInfo = New-Object Microsoft.SharePoint.Client.ListCreationInformation

$ListInfo.Title = $title

[System.Int32]$lstId = 100#$template.ListTemplateTypeKind;

$ListInfo.TemplateType = $lstId;

$List1 = $web0.Lists.Add($ListInfo);

$List1.Description = "$($title) GED Corporate";

$List1.Update()

$context0.ExecuteQuery()

}

else

{

Write-Host "list is not null"

}

$list.Title;

$context0.Dispose();

$context0 = $null;

}