Automate SharePoint Integration Tasks from PowerShell

The CData ADO.NET Provider for SharePoint is a standard ADO.NET Provider 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

---

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for SharePoint; this tutorial shows how to use the Provider to create, retrieve, update, and delete SharePoint data.

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

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

$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

$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

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