Learn more
 

Automate SharePoint Integration Tasks from PowerShell


Are you in search of a quick and easy way to access SharePoint data from PowerShell? This article demonstrates how to utilize the SharePoint Cmdlets for tasks like connecting to SharePoint data, automating operations, downloading data, and more.

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.

About SharePoint Data Integration

Accessing and integrating live data from SharePoint has never been easier with CData. Customers rely on CData connectivity to:

  • Access data from a wide range of SharePoint versions, including Windows SharePoint Services 3.0, Microsoft Office SharePoint Server 2007 and above, and SharePoint Online.
  • Access all of SharePoint thanks to support for Hidden and Lookup columns.
  • Recursively scan folders to create a relational model of all SharePoint data.
  • Use SQL stored procedures to upload and download documents and attachments.

Most customers rely on CData solutions to integrate SharePoint data into their database or data warehouse, while others integrate their SharePoint data with preferred data tools, like Power BI, Tableau, or Excel.

For more information on how customers are solving problems with CData's SharePoint solutions, refer to our blog: Drivers in Focus: Collaboration Tools.


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()

Ready to get started?

Download a free trial of the SharePoint Data Provider to get started:

 Download Now

Learn more:

SharePoint Icon SharePoint ADO.NET Provider

Provides .NET developers with the power to easily connect their Web, Desktop, and Mobile applications to data in SharePoint Server Lists, Contacts, Calendar, Links, Tasks, and more!

In this article

Related articles