If you are working on any large organization’s Sitecore project
then certainly you have to integrate Sitecore with other existing external system.
This is the real challenge where you have to study their external system and
find the way to integrate it with Sitecore.
Recently I come across with one situation where we have
to show search result both from Sitecore and external system. Getting search result
is not a big deal but how to integrate it with Sitecore.
So we have created indexes of external system and merge
with Sitecore indexes. This technic is working fine but now client want it
should show search on real time basis.
Now this is the real technical challenge. Indexing is very
costly process and scheduling in off- hour is fine but how to do it on real
time?
After thinking a while on this situation, I was able to
get the solution.
External system is referring SQL server database. So I
decided to create trigger which call stored procedure which finally call MVC
method and run the rebuild index code.
 |
| Real Time Indexing Technic Sequence |
Write Rebuild index method in MVC controller action method
Here is a code for action method inside HomeController as
public class HomeController : Controller
{
/// <summary>
/// Rebuild Index in Sitecore
/// </summary>
/// <param
name="TableName"></param>
/// <param
name="RecordId"></param>
/// <param
name="OperationType"></param>
/// <param
name="Token"></param>
/// <returns></returns>
public string RebuildIndex(string TableName, string RecordId, string OperationType, string Token)
{
string result = string.Empty;
if (Token.Equals("dbtrigger"))
{
//Write
code for rebuild the index
string message = string.Format("Date={0},
Table Name={1}, RecordId={2}, OperationType={3}, Token={4}", DateTime.Now.ToString(),
TableName, RecordId,
OperationType, Token);
System.IO.File.AppendAllText("d:\\webtest1.txt",
message + Environment.NewLine);
result = DateTime.Now.ToString() + " Index Rebuild";
}
else
{
result = "Token is incorrect";
System.IO.File.AppendAllText("d:\\webtest1.txt",
result);
}
return result;
}
}
This is just a pseudo code. You can refer my article “How
to rebuild index programmatically in Sitecore” for more detail on how to rebuild
Sitecore indexes?
For implementing security check, I am passing token so
that this rebuild indexes code will run only with matching passcode.
For testing purpose, I am also logging all the request details
in file which help us to understand that our method is running properly or not.
You must test this action method and note the URL as
 |
| MVC Action Method Result |
SQL Table
Run below script for creating a demo table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_State](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[StateName]
[nvarchar](50) NULL,
CONSTRAINT [PK_tbl_State] PRIMARY
KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
How
to call MVC controller action method from Stored procedure
--This Stored procedure call to MVC
action method and do the index rebuild
CREATE PROCEDURE dbo.[usp_RebuildIndexViaMVCMethod]
@OperationType
VARCHAR(20) = null,
@TableName
VARCHAR(20) = null,
@RecordId
INT = null
AS
DECLARE @obj int
DECLARE @url VarChar(400)
DECLARE @response VarChar(1000)
SET @url= 'http://localhost:62364/home/RebuildIndex?TableName='+ @TableName + '&RecordId='+ CONVERT(VARCHAR(15),@RecordId) +'&OperationType='+@OperationType+'&Token=dbtrigger'
EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @url, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'responseText',
@response OUT
--SELECT @obj
SELECT @response [RESPONSE]
--SELECT @url
EXEC sp_OADestroy @obj
RETURN
This is the main step of this technical challenge. This
stored procedure receive parameters like
- Operation type - Insert, update or delete flag
- Table Name – Name of the table
- RecordId - Record id for which received by trigger
Specify the URL properly in SQL format.
Note:
- You
can also use web service URL. But I found more complication with web service.
So my suggestion is to use MVC method than web service.
You can test this stored procedure as
--Run this stored procedure
EXEC [usp_RebuildIndexViaMVCMethod] 'INSERTED', 'tbl_State', 10
Note:-
You may get below “SQL
Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole
Automation Procedures'” error on executing above stored procedure as
Msg 15281, Level 16, State 1, Procedure
sp_OACreate, Line 1
SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component
is turned off as part of the security configuration for this server. A system
administrator can enable the use of 'Ole Automation Procedures' by using
sp_configure. For more information about enabling 'Ole Automation Procedures',
search for 'Ole Automation Procedures' in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure
sp_OAMethod, Line 1
SQL Server blocked access to procedure
'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Ole Automation Procedures' by
using sp_configure. For more information about enabling 'Ole Automation
Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure
sp_OAMethod, Line 1
SQL Server blocked access to procedure
'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Ole Automation Procedures' by
using sp_configure. For more information about enabling 'Ole Automation
Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure
sp_OAGetProperty, Line 1
SQL Server blocked access to procedure
'sys.sp_OAGetProperty' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Ole Automation Procedures' by
using sp_configure. For more information about enabling 'Ole Automation
Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OADestroy,
Line 1
SQL Server blocked access to procedure
'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Ole Automation Procedures' by
using sp_configure. For more information about enabling 'Ole Automation
Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
Run below configuration statement which allow SQL to run Ole
Automation Procedure to correc tthe above errors as
sp_configure 'show
advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Ole
Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show
advanced options', 1
GO
RECONFIGURE;
You should get below successful messages by executing
above configuration statements.
Configuration option 'show advanced options'
changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures'
changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options'
changed from 1 to 1. Run the RECONFIGURE statement to install.
Trigger
Below is a trigger which run on tbl_state table on insert / update / delete operation and call
store procedure with all the parameters details as
-- Create Trigger on State table for
insert, update and delete option
-- This trigger calls stored procedure
to rebuild index through MVC action method
CREATE TRIGGER dbo.trig_State ON
tbl_State AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Id INT
IF EXISTS(SELECT * FROM INSERTED)
BEGIN
SELECT @Id = INSERTED.ID FROM INSERTED
EXEC dbo.[usp_RebuildIndexViaMVCMethod] 'Inserted', 'tbl_State', @Id
PRINT 'Insert Operation';
END
IF EXISTS(SELECT * FROM DELETED)
BEGIN
SELECT @Id= DELETED.ID FROM DELETED
EXEC dbo.[usp_RebuildIndexViaMVCMethod] 'Deleted', 'tbl_State', @Id
PRINT 'Delete Operation';
END
END
GO
Test the
complete process
If I fire any insert/update/delete query on tbl_state table, it must fire trigger,
stored procedure and finally call MVC rebuild index action. You should get
response from MVC method in SQL query result as well as logging entry in our sample
log file as
 |
| SQL and Log file Output |
Please leave your comments or share this Sitecore lesson if it’s useful for you.
