Loading data from Sql Server to Solr with a Data Import Handler

Here we will see how to import data in Solr with an existing Sql Server Database to achieve powerful search. Apache Solr is undoubtedly one of the best open source enterprise search engine which is based on Lucene. In most of the cases solr would not be your primary data storage location as it is mainly used as search platform. Solr provides you the ability to perform complex full-text search with real-time indexing and fast performance. To import data from Sql Server Database you need to use Apache Solr’s Data Import Handler.

The Data Import handler is a way of importing data from a database using JDBC drivers and indexing it.

Prerequisites to import data would be:

  • An running instance of solr.
  • An sample core created with basic configuration.

The below example is for Full import Data from Sql Server Database to Solr.

  • MS SQL Server connector

You need to download Microsoft JDBC Driver 4.0 for SQL Server from:

http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

Copy file ‘sqljdbc4.jar’ to ‘lib’ folder

  • Solrconfig.xml configuration

Add the following configuration lines to solrconfig.xml which will instruct Solr to load data import handler and SQL Server JDBC driver jar files.

 <lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-\d.*\.jar" />  
  <lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-extras-\d.*\.jar" />  

Add the following configuration lines to solrconfig.xml which will instruct Solr to register the Data Import handler.

 <requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler">  
             <lst name="defaults">  
                  <str name="config">data-config.xml</str>  
             </lst>  
  </requestHandler>  

Note: Config parameter is required as it specifies the location where Data Import Handler configuration file is located. This file contains details of how to connect to data source using JDBC, how to fetch data and how to process it to generate Solr documents that will be indexed.

  • Data-config.xml configuration

Go to <local path>\solr-6.1.0\server\solr\<your core name>\conf and create a new file data-config.xml. Following is the configuration which will be saved in this file. This configuration tells which entity to get the data from and it’s fields that should be saved in solr.

 <dataConfig>   
  <dataSource type="JdbcDataSource"    
     driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"    
     url="jdbc:sqlserver://servername\instancename;databaseName=mydb"    
     user="<username>"    
     password="<password>"   
  />   
  <document>   
   <entity name="Employee"    
   pk="id"   
   query="select Empid,Empname,EmpAge,EmpDoj,Dept_id from Employee">   
     <field column="Empid" name="id"/>   
     <field column="Empname" name="Empname"/>     
     <field column="EmpAge" name="EmpAge"/>    
     <field column="EmpDoj" name="EmpDoj"/>    
         <entity name="Employee_Department"    
              query="select Deptname from Department where Deptid='${Employee.Dept_id}'">   
                 <field column="Deptname" name="Deptname"/>                  
         </entity>   
   </entity>   
  </document>   
  </dataConfig>   
  • Scheme.xml configuration
    Make sure you have your unique key id configured in schema file as below.
 <uniqueKey>id</uniqueKey>  

Add required fields in schema file which you have defined in Data-config.xml.

 <field name="id" type="string" multiValued="false" indexed="true" required="true" stored="true"/>  
  <field name="Empname" type="text_general" multiValued="false" indexed="true" stored="true"/>  
  <field name="EmpAge" type="long" indexed="true" stored="true" multiValued="false"/>  
  <field name="EmpDoj" type="date" multiValued="false" indexed="true" stored="true"/>  
  <field name="Deptname" type="text_general" multiValued="false" indexed="true" stored="true"/>  

This completes all configuration that would be required for data import. Now we need to open solr admin panel to access the Dataimport section of the core you created and execute the import handler.

solr import data

You can view your imported records in solr admin panel.

In next blog we will add the ability to import data incrementally.

Advertisements

Sitecore Cloning – Part 1

Here we will see something about Sitecore cloning functionality. Sitecore clones are an efficient way to place your items throughout the content tree and then have the content controlled from one central location in the content tree.

It is basically used when you don’t have to sift through a huge content tree just to change a tiny thing to a parent content piece and all its duplicates.

A clone is an item based on the data template associated with the cloned item. Rather than inheriting values directly from standard values, clones inherit field values from the cloned item. In clones, Sitecore uses the __Source field in the Advanced section of the standard template to specify the cloned item.

  • It is different from Sitecore copy/duplicate in the following terms:
    • Cloning feature won’t appear after you right click the item on the content tree. It is available in the configure tab.
    • When something is duplicated in Sitecore, it’s made a double of the item, but the duplicated item has no relationship with the parent item.
    • When something is cloned in Sitecore, it’s still making a double of the item, but this time the cloned item receives input from the parent item. If you change pieces of the parent item those changes will reflect in cloned item.
    • If you change a piece of cloned item and then make changes to the parent item, the changes to the parent item will not override the changes to the cloned item, instead a change notification will be displayed on the cloned items when the field value in the original item has been changed.

  1. Review the original item
    • Go back to review the change of original item’s field value
  2. Accept the change
    • Apply the same field value change from original items to the cloned item field, and                the [original value] will be displayed next to the field item
  3. Reject the change
    • keep the existing value on the cloned item and hides the message

Continue reading Sitecore Cloning – Part 1

Auto Complete TextBox Using jQuery and ASP.NET MVC

Here we will see how to use JQuery autocomplete functionality in ASP.NET MVC. Application is going to have two entities [employee and department]. On typing names of employee in text box a auto-generated list of names starting with the search text would appear and on clicking any of the name from list the corresponding department and employee name will be shown on page.

Following are the steps to be followed:

Step 1) Create a new project [ASP.NET MVC4 Web Application] and provide the appropriate name [Ex AutoCompleteMVC]. Choose MVC empty application option and click on OK.

Step 2) Create Model Classes

Go to Model folder and right click to add class [Employee.cs] and [Department.cs].

[Employee.cs]

 public class Employee  
 {  
    public int Empid { get; set; }  
    public string Name { get; set; }          
    public int Deptid { get; set; }  
 }  

[Department.cs]

 public class Department  
 {  
    public int Deptid { get; set; }      
    public string Name { get; set; }  
 }  

Step 3) Create a data repository which will have a static list of employees and corresponding departments. [DataLibrary.cs]

 public static class DataLibrary  
 {  
   public static List<Employee> employees  
   {  
     get  
     {  
        return new List<Employee>{  
                      new Employee{Empid=1,Name="Aakash",Deptid=1},  
                      new Employee{Empid=2,Name="Ashwin",Deptid=1},  
                      new Employee{Empid=3,Name="Jorge",Deptid=1},  
                      new Employee{Empid=4,Name="Dilip",Deptid=6},  
                      new Employee{Empid=5,Name="Bihal",Deptid=5},  
                      new Employee{Empid=6,Name="Suresh",Deptid=4},  
                      new Employee{Empid=7,Name="Taylor",Deptid=2},  
                      new Employee{Empid=8,Name="Sedan",Deptid=3},  
                      new Employee{Empid=9,Name="Reena",Deptid=2},  
                      new Employee{Empid=10,Name="Anthony",Deptid=1},  
                      new Employee{Empid=11,Name="Mike",Deptid=2},  
                      new Employee{Empid=12,Name="Jones",Deptid=1}  
                      };  
      }  
    }  
    public static List<Department> departments  
    {  
      get  
      {  
        return new List<Department>{  
                      new Department{Deptid=1,Name="Sales"},  
                      new Department{Deptid=2,Name="Financial"},  
                      new Department{Deptid=3,Name="Marketing"},  
                      new Department{Deptid=4,Name="HR"},  
                      new Department{Deptid=5,Name="Accounts"},  
                      new Department{Deptid=6,Name="Labour"}  
                      };  
      }  
    }  
  }  

Continue reading Auto Complete TextBox Using jQuery and ASP.NET MVC

FIX for Wrapper assembly for type library “MSHTML” cannot be found after Windows 10 upgrade.

Here we will see how to resolve an issue that comes after you have upgraded to Windows 10. After upgrading the system visual studio started showing errors that Metadata file .dll could not be found. After re-building, re-starting the solution as well as changing the references nothing changed and the errors still appeared. If you closely look at the Build failed errors you will find the below para.

C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(2234,5): warning MSB3283: Cannot find wrapper assembly for type library “MSHTML”. Verify that (1) the COM component is registered correctly and (2) your target platform is the same as the bitness of the COM component. For example, if the COM component is 32-bit, your target platform must not be 64-bit.

Now this is very the problem is. If you check your references you will find an yellow warning mark to MSHTML. If you check this with your windows 8.1 machine it will be pointing to an ActiveX control that was registered from a file in GAC.

Continue reading FIX for Wrapper assembly for type library “MSHTML” cannot be found after Windows 10 upgrade.

Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.

Here we will see how to solve one of the most common runtime error ““Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.””

This error may occur in the following scenario’s

1) Response.End
2) Response.Redirect
3) Server.Transfer

Reason for this exception occurrence is because page execution is stopped and sent to the Application_EndRequest event in the application’s event pipeline.

When we call Response.End method it stops the page execution and raises the EndRequest event and thus the line of code after Response.End is not executed. Same thing happens when we use Response.Redirect and Server.Transfer methods because both they both call Response.End internally.

When a call is made to destroy a thread, the CLR internally throws a ThreadAbortException. This is a exception that can be caught and handled explicitly.

Use a try-catch statement to catch the exception as below

 try  
 {  
 }  
 catch (System.Threading.ThreadAbortException)  
 {  
     //do nothing  
 }  

While using Response.Redirect we can use an overload of the same

 Response.Redirect(String url, bool endResponse);  

It passes false for endResponse parameter to suppress the internal call to Response.End

Use Server.Execute instead of Server.Transfer as the execution of code happens on the new page.

Difference between Sitecore.Context.ContentDatabase and Sitecore.Context.Database

Here we will see something related to sitecore context. As we know the properties of Sitecore context indicate a variety of information including the current managed web site, database accessed, security context and the content item requested including language and version.
Continue reading Difference between Sitecore.Context.ContentDatabase and Sitecore.Context.Database

3-Tier Architecture In Asp.net Using C#.

Here we will see how to implement three tier architecture(also known as n-tier architecture) in asp.net application.Three tier architecture is a well known architecture in software development and you can implement it in web based as well desktop applications.

Three tier architecture basically means dividing your project into three layers

1) User Interface Layer [UI] (Also known as Application Layer or Presentation Layer)

2) Business Access Layer [BAL] (Also known as Business Logic Layer)

3) Data Access Layer [DAL]

The advantage of this functionality is it makes a logical separation of components so if in near future we want to replace / change our database we need to change only data layer or if we need to change user interface we need to change only user interface layer and rest remains the same.

Continue reading 3-Tier Architecture In Asp.net Using C#.