SharePoint: Insert SQL Server data using SQL CLR Trigger and SP Client Object Model
What are we going to do
Very often we will have to integrate external line of business systems with our database. In case of SQL Server, we have options like Linked Server to connect to other databases. SharePoint integration is one such integration scenario. SharePoint is the most popular Content Management System from Microsoft which has its back end in SQL Server. However we cannot directly modify the SharePoint SQL Server database directly from the SQL Server Management Studio or using other SQL Transactions as it is strictly unsupported by Microsoft.
We will try to cover the scenario where we have to update SharePoint List with data whenever a new record is created in a SQL Server table. There are SharePoint specific solutions like Business Connectivity Services that can be used to achieve this, but we will see how to create an SQL Server specific solution to accomplish this requirement. The solution is designed such that:
- Whenever a new item is created in SQL Server table, a CLR Trigger is invoked from SQL Server. It will get the inserted values from Inserted Magic table and pass it to a .Net Console application.
- The Console Application is invoked from the CLR Trigger. It will retrieve the Inserted values as command line arguments and use SharePoint Client Side Object Model library to update the SharePoint List.
A quick over view of the upcoming action:
User inserts a new row -
A new row is created in SQL Server and it invokes the trigger to update the SharePoint via a Console Application.
The SharePoint Client Object Model updates the SharePoint List with CLR Trigger data from SQL.
Let's get started.
SQL Server Table Design
We will be using the `EmployeeInfo' table present in the database Employee to perform the implementation walk through.
SharePoint List Design
SharePoint List named Employee has the same columns as in the SQL Server table.
Create the SQL CLR Trigger
As the first step we will create the SQL CLR Trigger using the SQL Server Database Project template.
Initially, The Solution structure would look like below:
Right click the solution and add a new item - SQL CLR C# Trigger.
This will create a new class file with the below default code base. As you can see line number 9 is commented out by default. We can make use of this line to attach the trigger to a specific table in SQL Server database.
In the Target Parameter, we can specify the table name. By default, the Event is set to trigger on Update. However, in the coming section, we will change it to trigger on an Insert Event. For the time being, let's keep it as it is. Once we test the basic trigger functionality we will make the Event Change.
Resolve CLR Trigger Build Issues
Before making further changes we have to make sure the build is successful. However, we will get the below table reference unresolved error.
This is because we will have to import a copy of the database to our solution to get started working with the CLR Trigger on a particular database table. In order to do that, we will temporarily comment out the line number 9 as shown below.
Now let's import the database copy by right clicking the solution.
Specify the connection from the Import Database window.
Once done, click on Finish.
Now lets un-comment line number 9 which we had commented out earlier. If we try to rebuild the solution, it would be successful.
Publish the CLR Trigger
If we need to get the CLR Trigger to the SQL Server Database we have to publish the trigger.
While clicking on Publish we will get the window where we can specify the Database connection.
We can either use an existing connection or create a new one.
Click on Publish to deploy the CLR Trigger assembly to SQL Server.
Most likely we will get the below error if there is a target platform mismatch.
From project settings, change the target SQL Server platform to the desired one.
We have changed it from SQL Server 2016 to SQL Server 2012.
In the same window from Database settings, select the `Trustworthy' Check box.
Also, set the Permission Level to UNSAFE in the SQLCLR tab.
This step is done to prevent Security exceptions, as shown below, from happening:
Now redo the publishing and we can see that the publishing has succeeded this time.
Quick Test of CLR Trigger
Heading over to the Employee Database we can see that the assembly is successfully deployed to SQL Server.
Let's try to quickly test the basic CLR functionality which response to Update Events. So as we can see below the CLR Trigger is working with the default settings.
Now we will have to update the CLR Trigger code and change the event to Inserted and we will write our custom logic.
Update the CLR Trigger with Custom Logic
Set the Event Parameter to `FOR INSERT'. Then we will have to create an SQL Connection object and Command object and retrieve the values that are inserted into the SQL Server table. These will be present in the Inserted Magic table. Once the data is retrieved we will pass these values as command line arguments to a .NET Console Application which we will be creating in the upcoming section.
So the obvious question gets asked, why we can't write the SharePoint Update Logic in the same CLR Trigger solution. The reason why we are creating a separate console application to perform SharePoint operation is because it requires SharePoint DLLs which are not supported in SQL CLR Trigger. Though we can register unsupported assemblies using CREATE ASSEMBLY statements, SharePoint assemblies will face issues while registration. So the only option is to pass the data from the trigger to a different console application and do SharePoint specific operations in that application.
The starting of the Console Application Process is relatively simple as we just have to specify the location of the exe file (In our case we will be calling the console application that we are going to implement as `UpdateSharePointFromCLRTrigger.exe').
**Full Code for CLR Trigger **
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
using System.IO;
using System.Security;
public partial class Triggers
{
[Microsoft.SqlServer.Server.SqlTrigger (Name= "SQLTrigger_SharePoint" , Target= "EmployeeInfo" , Event= "FOR INSERT" )]
public static void SqlTrigger_SharePoint ()
{
try
{
string employeeID = string.Empty, employeeName = string.Empty, experience = string.Empty;
using (SqlConnection connectionObj = new SqlConnection("context connection=true"))
{
connectionObj.Open();
SqlCommand commandObj = new SqlCommand();
commandObj.Connection = connectionObj;
//Get Employee ID from the inserted magic table
commandObj.CommandText = "select EmployeeID from inserted" ;
employeeID = commandObj.ExecuteScalar().ToString();
//Get Employee Name from the inserted magic table
commandObj.CommandText = "select EmployeeName from inserted" ;
employeeName = commandObj.ExecuteScalar().ToString();
//Get Employee Experience from the inserted magic table
commandObj.CommandText = "select Experience from inserted" ;
experience = commandObj.ExecuteScalar().ToString();
}
string sharePointData = employeeID + "#" + employeeName + "#" + experience;
string exePath = @"C:\Trigger\UpdateSharePointFromCLRTrigger.exe";
//Create the ProcessStartInfo object required to start the Process
ProcessStartInfo startInfo = new ProcessStartInfo(exePath);
startInfo.Arguments = sharePointData;
startInfo.UseShellExecute = false ;
startInfo.CreateNoWindow = true ;
//Start the Console Application that will update SharePoint list with SQL Data
Process proc = new Process();
proc.StartInfo = startInfo;
proc.Start();
//Send status back to SQL Server
SqlContext.Pipe.Send( "Trigger Fired and SharePoint Update Initiated." );
}
catch (Exception ex)
{
SqlContext.Pipe.Send( "Trigger Failed to Execute" + ex.Message);
}
}
}
Debugging the CLR Trigger
Once we have completed the creation of CLR trigger we can debug the trigger with a bit of configuration as discussed below. We will add a SQL Script file which will contain the T-SQL Statements that will invoke the trigger.
Select Script (Not in build) option.
Add the SQL Statement which we would be using in SQL Server into this SQL Script file.
Now we have to set this script file as the startup script from the Debug section of project properties.
In the same window, specify the target database connection string.
We also have to open up the SQL Server Object explorer from the View menu.
From SQL Server Object Explorer, right-click SQL Server instance and select `Allow SQL/CLR Debugging'.
This will attach the sqlserver.exe process to the solution to start the debugging of the solution.
Creation of Console Application to do SharePoint operations
Let's create a console application which will work in unison with the CLR Trigger.
Add the below SharePoint client side libraries present in the location `C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI' to the console application.
**Code Walkthrough **
The inserted values which are passed as a concatenated single string(EmployeeID#EmployeeName#Experience) from the trigger will be caught by the command line parameter `args'.We can split the string on the character `#' and get the values from the array.
Now we will use SharePoint programming model to update SharePoint lists with these values. In order to do that, we will create a SharePoint Client Context object.This is the primary SharePoint object that provides other SharePoint Objects to work with. We will then create the List Object where we have to create the SharePoint List item using the SQL Server data. Finally, we will create the list item and update it to SharePoint List as shown below.
**Full Code for SharePoint Update from Console Application **
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SharePoint.Client;
using System.IO;
using System.Diagnostics;
using System.Net;
namespace UpdateSharePointFromCLRTrigger
{
class Program
{
static void Main(string[] args)
{
using (StreamWriter writer = new StreamWriter(@"C:\Trigger\ExceptionCatcher.txt"))
{
//Get the data passed from the CLR Trigger
string employeeID = args[0].Split('#')[0];
string employeeName = args[0].Split('#')[1];
string experience = args[0].Split('#')[2];
try
{
ClientContext context = new ClientContext("http://sp2013s1/sites/Employee");
//Specify the SharePoint Service Account credentials
context.Credentials = new NetworkCredential("AzureAD\\UserName", "Password");
//Create a new list item in SharePoint based on SQL Server trigger data
List employeeList = context.Web.Lists.GetByTitle( "Employee" );
ListItemCreationInformation itemCreationInfo = new ListItemCreationInformation();
ListItem employeeItem = employeeList.AddItem(itemCreationInfo);
employeeItem[ "EmployeeID" ] = employeeID;
employeeItem[ "EmployeeName" ] = employeeName;
employeeItem[ "Experience" ] = experience;
employeeItem.Update();
context.ExecuteQuery();
}
catch (Exception ex)
{
writer.WriteLine(ex.Message);
}
}
}
}
}
Summary
Thus we have seen how to make use of a SQL CLR Trigger and SharePoint Client Object Model to update SQL Server table data to SharePoint synchronously.
See Also
This article can be viewed from the below link as well :