Native SharePoint lists as SQL Azure Web Frontend with full CRUD

 

​​The Cloud Connector for Microsoft Office 365 and SharePoint 2010 connects almost any on-premise data source, e.g. ODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), SQL databases like MS SQL Server, SQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to native SharePoint lists in the Microsoft Cloud - or any other Microsoft SharePoint 2010 installation - in just minutes without any programming. For any questions please use the FAQ Web Form or contact [email protected] directly.

 

​It's easy to connect native Microsoft SharePoint 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.

 

azure create data table with guid

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.

 

native sharepoint list connected to sql azure

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 [email protected]; 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 [email protected]; 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 [email protected] if you have any questions about this sample scenario.

Ready to go next steps?

Icon for Product Regsitration - Layer2 leading solutions

Register for free download.

Keep your Sharepoint in sync. Download and try today.

Contact Us Icon for Layer2 leading solutions

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.