I created my  blog series on SharePoint BCS quite some time ago in order to illustrate how easy it is to access data outside of SharePoint and present it within a list or document library.  I have been meaning for quite some time to demonstrate how writing code for external lists\libraries is very similar to writing code for internal lists\libraries.  Today I am going to start a new three part series on SharePoint BCS development by showing you how you can enhance your solutions using PowerShell to access data in external lists.  The posts aren’t going to be too in-depth, but meant to get you started.  I hope to build on them later and post a series on creating an entire solution around BCS.

Using PowerShell to Manage Data in Internal Lists

With SharePoint PowerShell you are basically using the server object model for development.  So this means you have access to the methods and processes utilised by this system.  What you’ll notice is that code to perform inserts, updates, reads and deletions is pretty much exactly the same.  Where the code differs between internal and external is actually around accessing the item to be updated, read or deleted.

To illustrate I have created a script that performs an insert, update, read and delete.  To show you what’s happening here’s the config for the code:

 

As you can see the code should insert a new item, update two, read a third and finally delete.  The code is pretty straight forward:

Using the following list:

Using PowerShell to Access Data In External Lists - Internal List Before Code

And running the above script:

Using PowerShell to Access Data In External Lists - InternalListRunningCode

We end up with the result:

Using PowerShell to Access Data In External Lists - Internal List After Code

 

Using PowerShell to Update External Lists

The code is almost identical.  What actually changes is the code used for finding the items we want to update.  We also have to define the ServiceContext scope to process the transactions.  External list manipulation requires the scope to import the data catalogue (more information can be found here).  First the config to see what the plan is:

Then using the code:

Notice in the code above that we are using a pipe to gather the data.  One thing to note with External Lists is they do not have a numerical ID field.  However, in this case we are grabbing the ID field of the Asset so we can search on the AssetID of the item.

We then update the following list:

Using PowerShell to Access Data In External Lists - External List Before Code

by running the code:

Using PowerShell to Access Data In External Lists - External List Running Code

And receive the following updated list:

Using PowerShell to Access Data In External Lists - External List After Code

 

That’s the basics of updating an External List with PowerShell.  In the next post I will cover updating using CSOM.

 

Thanks for Reading!