It's easy to connect native Microsoft SharePoint 2010 lists to SQL Azure tables and queries codeless and in just minutes. Optionally the connection can be bi-directional with two-way data replication. Full CRUD operation supported: create, update, delete. That means for example, you can use SharePoint on-premise or in the cloud as web frontend for SQL Azure. Please follow this instructions:
First create any data table in SQL Azure. In our sample this table will host announcement items from the SharePoint list with title, body and an expiration date. Please be sure to have some database-side primary key in your table.
Fig.: Create a data table in SQL Azure
In our sample we use a GUID field of type uniqueidentifier and set a new GUID when a new record is created. In this way any record has an unique primary key.
Additionally you have to create the Announcement list on the SharePoint side. We use the Announcement template to create. It's also available for Office 365 or SharePoint Online.
Fig.: A SharePoint Announcement list named AzureCRUD is connected to SQL Azure
To connect the created SQL Azure table with the SharePoint Announcements list we use the
Cloud Connector with the following connection definition file:
<?xml version="1.0" encoding="utf-8" ?>
<connection xmlns="http://www.layer2.de/schemas/cloudConnections"
interval="10" version="1.2" enabled="true">
<conflictResolution mode="warnAndContinue" />
<dataEntities>
<dataEntity name="Azure"
provider="System.Data.SqlClient"
connectionString="Server=tcp:myserver.database.windows.net; Database=myDB;
User ID=MyUser@myServer.database.windows.net; Password=myPwd;
Trusted_Connection=False; Encrypt=True;"
selectStatement="Select * from Announcements"
>
</dataEntity>
<dataEntity name="Office365"
provider="Layer2.SharePoint.Provider"
connectionString="Data Source=https://myDomain.sharepoint.com/sites/mySite/;
Initial Catalog=AzureCRUD; Authentication=Office365;
User Id=myO365User@myO365Domain.onmicrosoft.com; Password=myO365Pwd;"
selectStatement=""
>
</dataEntity>
</dataEntities>
<fieldMappings>
<fieldMapping>
<conflictResolution mode="winnerLoser">
<winningEntity name="Office365" />
</conflictResolution>
<field entity="Azure" name="Title"/>
<field entity="Office365" name="Title" />
</fieldMapping>
<fieldMapping>
<conflictResolution mode="winnerLoser">
<winningEntity name="Office365" />
</conflictResolution>
<field entity="Azure" name="Body"/>
<field entity="Office365" name="Body" />
</fieldMapping>
<fieldMapping>
<conflictResolution mode="winnerLoser">
<winningEntity name="Office365" />
</conflictResolution>
<field entity="Azure" name="Expires"/>
<field entity="Office365" name="Expires" />
</fieldMapping>
</fieldMappings>
</connection>
Please note:
- An Office 365 SharePoint list is used in this sample as web frontend for the SQL Azure table.
- Both data entities are described in their DataEntity sections.
- The data is not replicated in real-time. In this case the interval is set to 10 minutes (see connection section).
- Both data entities have primary keys, the GUID on SQL Azure side and the ID column at SharePoint side. The connector automatically recognize and use these PKs for replication settings.
- Database fields are mapped to SharePoint list columns in the field mapping section.
- There is a conflict resolution option on field level. If the SQL Azure field and the mapped SharePoint column are both changed, the winning entity defined above will survive.
- Data types are automatically mapped.
Please contact sales@layer2.de if you have any questions about this sample scenario.