Update SQL Server Data To SharePoint Using CLR Trigger And SharePoint Client Object Model

What are we going to do?

Very often we will have to integrate the external line of business systems with our database. In the case of SQL Server, we have the 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 SharePoint SQL Server database directly from 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 the data whenever a new record is created in a SQL Server table. There are SharePoint specific solutions like business connectivity Services, which can be used to achieve this, but we will see how to create SQL Server specific solutions 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 SharePoint list.

A quick over view of the upcoming action

User inserts a new row, as shown below.



A new row is created in SQL Server and it invokes the trigger to update SharePoint via a Console Application.



SharePoint Client Object Model updates SharePoint List with CLR Trigger data from SQL.


Let’s get started.

SQL Server Table Design

We will be using EmployeeInfo table, which is present in the database Employee to perform the implementation walk through.



SharePoint List Design

SharePoint List is named Employee, which has the same columns as SQL Server table.



Create the SQL CLR Trigger

As the first step, we will create SQL CLR Trigger, using SQL Server database project template.



Initially, the solution structure will look, as shown 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 SLQ 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 table reference unresolved error given below.



This is because we will have to import a copy of the database to our solution to get started working with CLR Trigger on a particular database table. In order to do this, 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 Finish.



Now, let's uncomment line number 9, which we had commented out earlier. If we try to rebuild the solution, it will be successful.



Publish the CLR Trigger

If we need to get CLR Trigger to 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 Publish to deploy CLR Trigger assembly to SQL Server.



Most likely, we will get the error shown below, 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 the database settings, select the ‘Trustworthy’ Check box.

Also, set the permission level to UNSAFE in 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 responds to update events. Thus, we can see CLR Trigger is working with the default settings, which is given below.


Now, we will have to update CLR Trigger code, 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’. Now, we will have to create SQL Connection object and Command object, followed by retrieving the values that are inserted into 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 SharePoint Update Logic in the same CLR Trigger solution? The reason why we are creating a separate console application is 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, during registration. 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, which 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 configurations, 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 SQL statement, which we will 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 the project properties.



In the same Window, specify the target database connection string.



We also have to open up 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 CLR Trigger.



Add SharePoint client side libraries, which are present in the location ‘C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI’ to the console Application.



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 this, we will create a SharePoint Client Context object .This is the primary SharePoint object, which provides other SharePoint Objects to work with. We will then create the List Object, where we have to create SharePoint List item, using 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.