Accessing SharePoint Online Data Directly From SQL Server and REST

If you have ever worked with SharePoint, either on premises or online, and ever needed to fetch records or manage Lists programmatically, you know how hard of a task this can be. Learning the SharePoint API presents a significant learning curve even for senior developers. In this post, you will see how you can interact with SharePoint Online using simple SQL statements or simple REST commands, and how you can tap into SharePoint directly from within SQL Server (such as triggers, views, and functions). Although this blog post focuses on the online version of SharePoint, the examples below also work with SharePoint on premises. Accessing SharePoint directly from SQL Server (or simple REST commands) requires the use of Enzo Unified, a real-time integration platform that hides the complexities of APIs.

Managing SharePoint Objects

SharePoint offers a large array of management options through its APIs, including the ability to create Sites, Lists, Folders, and Security. At the time of this writing, Enzo Unified allows you to access basic SharePoint management features using SQL, such as the ability to create a SharePoint list, and add Fields to the list. For example, creating a new SharePoint Task List using SQL looks like this:

EXEC SharePoint.createlist 'Ch9Tweets', 'Tasks', 'Channel9 Tweets Received'

You can also add new fields to a SharePoint list using SQL. For example, let’s add a new field called ScreenName of type Text:

EXEC SharePoint.addfield 'Ch9Tweets', 'ScreenName', 'Text'

Other SQL procedures allow you to create Folders where you can store Documents.

Reading From and Writing To SharePoint Lists

Now that we have created a SharePoint List using SQL, we can add a new record. Let’s use an INSERT statement to add a record in the SharePoint List we just created. The INSERT statement allows you to specify the field names you want to provide a value for.

INSERT INTO sharepoint.list@Ch9Tweets (Title, ScreenName) VALUES ('New tweet received.', '@hroggero')

image

The Messages window shows you that one record was affected. You may have noticed a minor variation to the usual SQL syntax: the table name is called ‘list@Ch9Tweets’. The @ sign is used to provide the actual name of the SharePoint list. The UPDATE and DELETE statements are similar:

UPDATE SharePoint.list@Ch9Tweets SET ScreenName = '@enzo_unified' WHERE ID = 1

DELETE FROM sharepoint.list@Ch9Tweets WHERE ID = 1

Selecting from a SharePoint list allows you to fetch SharePoint data directly using a SQL command, as such:

SELECT ID, Title, ScreenName, Priority FROM sharepoint.list@Ch9Tweets

It is also possible to include the TOP and WHERE clauses, which automatically issue the proper CAML query for SharePoint to filter the data. For example, you could select a subset of records like this:

SELECT TOP 5 ID, Title, ScreenName, Priority FROM sharepoint.list@Ch9Tweets WHERE ID < 10

image

Integrating SharePoint And SQL Server

The above examples work just fine if you are connected to Enzo Unified directly; however when used directly from within SQL Server (such as a trigger), you will need to created a Linked Server to Enzo Unified, and add the Server Name to the query. For example, the previous SELECT command would look like this when called within a Stored Procedure (notice the addition of the linked server):

SELECT TOP 5 ID, Title, ScreenName, Priority FROM [localhost,9550].bsc.sharepoint.list@Ch9Tweets WHERE ID < 10

Most of the operations to access and change SharePoint data can also be performed using specific stored procedures within Enzo Unified. For example, it is possible to insert a new item using the AddListItemRaw command. This command inserts a new list item in the Ch9Tweets list, where the values for the Title and ScreenName fields are provided in the XML document passed in.

EXEC SharePoint.AddListItemRaw ‘Ch9Tweets’, ‘<root><Title>New tweet!</Title><ScreenName>@hroggero</ScreenName></root>’

REST Access

In addition to supporting native SQL commands from SQL Server, Enzo Unified also provides a simple REST interface that can be used from mobile phones. Let’s call the GetListItemsEx method using a REST command. To know which parameters to send to Enzo Unified, let’s run the HELP command on GetListItemsEx first:

exec bsc.SharePoint.GetListItemsEx help

image

We can see that GetListItemsEx requires a view name, and optionally accepts the columns and where parameters. Calling the GetListItemsEx method using the HTTP endpoint of Enzo Unified (listening on 19560 in this example) looks like this (note: the call requires and authentication token to work):

image

The JSON response provides the list of tweets that match the WHERE clause:

image

Conclusion

The ability to integrate directly with SharePoint from SQL Server without the need to use an ETL tool and without the need to learn the SharePoint APIs allows developers and DBAs to build direct database-level integration with SharePoint. As such, it is possible to interact directly with SharePoint lists through the following SQL Server objects:

  • - Stored Procedures
  • - Functions
  • - Triggers
  • - Views
  • - SQL Jobs

In addition to low-level integration with SQL Server, Enzo Unified provides a REST interface allowing developers to communicate securely to SharePoint using HTTP or HTTPS.

About Herve Roggero

Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Enzo Unified (http://www.enzounified.com/). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, aPluralSight author, and runs the Azure Florida Association.

About Enzo Unified

Enzo Unified is a data platform that helps companies reduce development and data integration project timelines, improve data quality, and increase operational efficiency by solving some of the most complex real-time data consumption challenges. For more information, contact info@enzounified.com, or visit http://www.enzounified.com/.

Print | posted @ Wednesday, March 30, 2016 11:01 AM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.