Update sql server data to sharepoint using clr trigger and sharepoint client object model

Very often we will have to integrate the external line of business systems with our database. Database for dummies In the case of SQL Server, we have the options like Linked Server to connect to other databases. A database is a collection of SharePoint integration is one such integration scenario. Library database SharePoint is the most popular Content Management System from Microsoft, which has its back end in SQL Server.


Database node 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. Data recovery after format 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. Database 2015 The solution is designed such that

• Whenever a new item is created in SQL Server table, a CLR Trigger is invoked from SQL Server. Data recovery nyc 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. Database weekly It will retrieve the inserted values as command line arguments and use SharePoint Client Side Object Model library to update SharePoint list.

This will create a new class file with the below default code base. Data recovery utah As you can see line number 9 is commented out by default. Data recovery deleted files 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. Database health check By default the Event is set to trigger on Update. R studio data recovery software However in the coming section, we will change it to trigger on an Insert Event. Data recovery iphone For the time being let’s keep it as it is. Ease use data recovery Once we test the basic trigger functionality, we will make the Event Change.

Before making further changes, we have to make sure the build is successful. Free database software 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. Raid 0 data recovery software In order to do this, we will temporarily comment out the line number 9, as shown below.

Let’s try to quickly test the basic CLR functionality, which responds to update events. Mail database Thus, we can see CLR Trigger is working with the default settings, which is given below.

Set the event parameter to ‘FOR INSERT’. Hdata recovery master Now, we will have to create SQL Connection object and Command object, followed by retrieving the values that are inserted into SQL Server table. In database These will be present in the Inserted Magic table. Drupal 7 database query 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. Data recovery usa Though we can register unsupported assemblies using CREATE ASSEMBLY statements, SharePoint assemblies will face issues, during registration. Data recovery business 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’).

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.

banner