Automate SharePoint Integration Tasks from PowerShell

The CData Cmdlets for SharePoint are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to SharePoint.

Getting Started

---

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to SharePoint, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete SharePoint data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for SharePoint. To access SharePoint data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for SharePoint.

Once you have acquired the necessary connection properties, accessing SharePoint data in PowerShell can be enabled in three steps.

Set the URL property to the base SharePoint site or to a sub-site. This allows you to query any lists and other SharePoint entities defined for the site or sub-site.

The User and Password properties, under the Authentication section, must be set to valid SharePoint user credentials when using SharePoint On-Premise.

If you are connecting to SharePoint Online, set the SharePointEdition to SHAREPOINTONLINE along with the User and Password connection string properties. For more details on connecting to SharePoint Online, see the "Getting Started" chapter of the help documentation

PowerShell

1. Install the module:

   Install-Module SharePointCmdlets

2. Connect:

   $sharepoint = Connect-SharePoint -User "$User" -Password "$Password" -Auth Scheme "$Auth Scheme" -URL "$URL" -SharePointEdition "$SharePointEdition"
   

3. Search for and retrieve data:

   $location = "Chapel Hill"
   $mycustomlist = Select-SharePoint -Connection $sharepoint -Table "MyCustomList" -Where "Location = `'$Location`'"
   $mycustomlist
   

   You can also use the Invoke-SharePoint cmdlet to execute SQL commands:

   $mycustomlist = Invoke-SharePoint -Connection $sharepoint -Query 'SELECT * FROM MyCustomList WHERE Location = @Location' -Params @{'@Location'='Chapel Hill'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for SharePoint\lib\System.Data.CData.SharePoint.dll")
    

2. Connect to SharePoint:

    
   $conn= New-Object System.Data.CData.SharePoint.SharePointConnection("User=myuseraccount;Password=mypassword;Auth Scheme=NTLM;URL=http://sharepointserver/mysite;SharePointEdition=SharePointOnPremise;")
   $conn.Open()
   

3. Instantiate the SharePointDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Name, Revenue from MyCustomList"
   
   $da= New-Object System.Data.CData.SharePoint.SharePointDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.revenue
   }
    

Update SharePoint Data

PowerShell

Update-SharePoint -Connection $SharePoint -Columns @('Name','Revenue') -Values @('MyName', 'MyRevenue') -Table MyCustomList -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SharePoint.SharePointCommand("UPDATE MyCustomList SET Location='Chapel Hill' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SharePoint.SharePointParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert SharePoint Data

PowerShell

Add-SharePoint -Connection $SharePoint -Table MyCustomList -Columns @("Name", "Revenue") -Values @("MyName", "MyRevenue") 

ADO.NET

$cmd = New-Object System.Data.CData.SharePoint.SharePointCommand("INSERT INTO MyCustomList (Location) VALUES (@myLocation)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SharePoint.SharePointParameter("@myLocation","Chapel Hill")))
$cmd.ExecuteNonQuery()

Delete SharePoint Data

PowerShell

Remove-SharePoint -Connection $SharePoint -Table "MyCustomList" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SharePoint.SharePointCommand("DELETE FROM MyCustomList WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SharePoint.SharePointParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject