How to use SOLR.NET to add/search documents from SOLR

In this article we will look into how to use SOLR to add and query data using  SOLR.NET

Install Apache Tomcat and  SOLR

1. Install Java version 6 or above

2.Download Apache Tomcat -Core: 32-bit/64-bit Windows Service Installer (pgp, md5) http://tomcat.apache.org/download-70.cgi

After you install apache tomcat you can check if service is running by checking the location -http://localhost:8080/

3.Download solr (zip) file http://www.gtlib.gatech.edu/pub/apache/lucene/solr/4.10.3/

Details on how to install and run SOLR is clearly stated in the below awesome articles

http://www.lawruk.com/blog/55/how-to-install-solr-on-windows-8

http://blog.navigationarts.com/installing-solr-4-6-on-windows/

Use SOLR.NET to connect to SOLR

So now that we have Apache and SOLR running let’s look at how to use this in ASP.NET(C#)

1.Download SOLR.NET - https://github.com/mausch/SolrNet

2.Compile the project and get the dlls from SOLRNET project (there will be SolrNet.dll, Microsoft.Practices.ServiceLocation.dll, HttpWebAdapters.dll and the pdb and xml files)

3.Create an empty ASP.NET project

4.Create a new folder in this project and copy the dlls from SOLRNET project (step 2) to that folder.

5.Add references to the 3 dlls

6.Add Global.asax and initialize SOLR.NET connection in application start

  protected void Application_Start(object sender, EventArgs e)

        {

            Startup.Init<SOLRPOST.PostData.SolrSearchResult>("http://localhost:8080/solr");

        }

7.Create a class which has the same fields as the schema in SOLR. For testing purpose I added only 2 fields from schema

public class SolrSearchResult

        {

            [SolrField("id")]

            public string id { get; set; }

            [SolrField("title")]

            public string title { get; set; }

        }

8.Now we are ready to add documents to SOLR! Given below is the function to add documents to SOLR

public void AddData()

        {

            ISolrOperations<SolrSearchResult> solr = ServiceLocator.Current.GetInstance<ISolrOperations<SolrSearchResult>>();

            SolrSearchResult test = new SolrSearchResult() { id = "changeme2", title = "changeme2" };

            solr.Add(test);

            solr.Commit();

        }

9.To search data use the below function

public void SearchData()

        {

            ISolrOperations<SolrSearchResult> solr = ServiceLocator.Current.GetInstance<ISolrOperations<SolrSearchResult>>();

            SolrQueryResults<SolrSearchResult> results = solr.Query(new SolrQuery("id:\"changeme2\""));

            foreach (SolrSearchResult result in results)

            {

                Response.Write(result.title);

            }

        }

Group and pivot data in SQL

Given below is an example of how to group and pivot data in SQL .This example shows you how to group data in CSV format and pivot it.

--Create temp table

CREATE TABLE  #car_data (company varchar(10), model varchar(20))

--Insert data

INSERT INTO #car_data (company,model) values ('Ford','Escape')

INSERT INTO #car_data (company,model) values ('Ford','Explorer')

INSERT INTO #car_data (company,model) values ('Ford','Expedition')

INSERT INTO #car_data (company,model) values ('Chevy','Cruze')

INSERT INTO #car_data (company,model) values ('Chevy','Impala')

INSERT INTO #car_data (company,model) values ('Chevy','Silverado')

-- Group data in CSV format

SELECT  t.company

    ,STUFF((SELECT ', ' + model

        FROM #car_data with(nolock)

        WHERE company = t.company

        FOR XML PATH(''), TYPE)

    .value('.','VARCHAR(400)'),1,2,' ') List_Output

FROM #car_data t with(nolock)

GROUP BY t.company