Lets go over how we can setup a custom database with a custom index then query it with Sitecore ContentSearch. You could also skip step 1 if you’re getting your data from somewhere else that’s not stored in Sitecore as items.
1. Create database
If you’re using SQL open Sql Server Management Studio and add a new database. In this case I’ll create it next to my Sitecore databases. Setup the tables how you want them. For demonstration purpose I’ll just setup a basic customer table. Here’s an image for reference to the code later on.
2. Create Solr core
At your Solr installation folder, go to <pathToSolr>\server\solr\ and duplicate the core directory for sitecore_master_index and rename to desired indexname, for instance:
- companyName_custom_index
Go into the newly created folder and delete everything except the conf folder.
Next open up your Solr admin and add a new core by putting in your new foldername in previous step. Should look something like this.
3. Create Custom Index configuration
Now we need to create the index configuration for the core to connect with your Sitecore instance.
- Create a new .config file in your solution and add this sample configuration
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:role="http://www.sitecore.net/xmlconfig/role/" xmlns:search="http://www.sitecore.net/xmlconfig/search/">
<sitecore>
<contentSearch search:require="solr">
<configuration type="Sitecore.ContentSearch.ContentSearchConfiguration, Sitecore.ContentSearch">
<indexes hint="list:AddIndex">
<index id="scempty102_custom_index" type="Sitecore.ContentSearch.SolrProvider.SolrSearchIndex, Sitecore.ContentSearch.SolrProvider">
<param desc="name">$(id)</param>
<param desc="core">$(id)</param>
<param desc="propertyStore" ref="contentSearch/indexConfigurations/databasePropertyStore" param1="$(id)" />
<configuration ref="contentSearch/indexConfigurations/defaultSolrIndexConfiguration">
</configuration>
<locations hint="list:AddCrawler">
<crawler type="MyProject.Foundation.Indexing.Crawlers.MyCrawler, MyProject.Foundation.Indexing">
</crawler>
</locations>
</index>
</indexes>
</configuration>
</contentSearch>
</sitecore>
</configuration>
Create your Indexable class
namespace MyProject.Foundation.Indexing.Indexable
{
public class IndexableCustomerField : IIndexableDataField
{
private readonly Customer _customer;
private readonly PropertyInfo _propertyInfo;
public IndexableCustomerField(Customer customer, PropertyInfo fieldInfo)
{
_customer = customer;
_propertyInfo = fieldInfo;
}
public string Name
{
get { return _propertyInfo.Name; }
}
public string TypeKey => string.Empty;
public Type FieldType => _propertyInfo.PropertyType;
public object Value => _propertyInfo.GetValue(_customer);
public object Id => _propertyInfo.Name.ToLower();
}
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string Email { get; set; }
}
//indexable class
public class IndexableCustomer : IIndexable
{
private Customer _customer;
public IndexableCustomer(Customer customer)
{
_customer = customer;
}
public void LoadAllFields()
{
Fields = _customer.GetType()
.GetProperties(BindingFlags.Public
| BindingFlags.Instance
| BindingFlags.IgnoreCase)
.Select(fi => new IndexableCustomerField(_customer, fi));
}
public IIndexableDataField GetFieldById(object fieldId)
{
return Fields.FirstOrDefault(x => x.Id.Equals(fieldId));
}
public IIndexableDataField GetFieldByName(string fieldName)
{
return Fields.FirstOrDefault(x => x.Name.Equals(fieldName));
}
public IIndexableId Id => new IndexableId<string>(_customer.Id.ToString());
public IIndexableUniqueId UniqueId => new IndexableUniqueId<IIndexableId>(Id);
public string DataSource => "Customer";
public string AbsolutePath => "";
public CultureInfo Culture => CultureInfo.CurrentCulture;
public IEnumerable<IIndexableDataField> Fields { get; private set; }
}
}
Create your Crawler class
This is just an example using a simple data provider with System.Data.SqlClient. Use whatever method you prefer. You could use an external API and index that data if it’s a viable way for your solution. That is up to you to figure out.
namespace MyProject.Foundation.Indexing.Crawlers
{
public class MyCrawler: FlatDataCrawler<IndexableCustomer>
{
protected override IndexableCustomer GetIndexableAndCheckDeletes(IIndexableUniqueId indexableUniqueId)
{
return null;
}
protected override IndexableCustomer GetIndexable(IIndexableUniqueId indexableUniqueId)
{
return null;
}
protected override bool IndexUpdateNeedDelete(IndexableCustomer indexable)
{
return false;
}
protected override IEnumerable<IIndexableUniqueId> GetIndexablesToUpdateOnDelete(
IIndexableUniqueId indexableUniqueId)
{
return null;
}
//If you get data from external api you could also save it to disk
//in a json file then return that list instead depending on the size.
protected override IEnumerable<IndexableCustomer> GetItemsToIndex()
{
var customersToIndex = new List<IndexableCustomer>();
string connectionString = "Data Source=(local);Initial Catalog=custom_Customer;User ID=<id>;Password=<password>";
//Here i'm just using a simple Sql connection to showcase with some data.
//You would not really do this in a real case.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Customer_table";
command.CommandTimeout = 15;
command.CommandType = CommandType.Text;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var customer = new Customer();
customer.Id = reader.GetInt32(reader.GetOrdinal("Id"));
customer.Name = reader.GetString(reader.GetOrdinal("Name"));
customer.Age = reader.GetInt32(reader.GetOrdinal("Age"));
customer.Email = reader.GetString(reader.GetOrdinal("Email"));
customersToIndex.Add(new IndexableCustomer(customer));
}
}
connection.Close();
}
return customersToIndex;
}
}
}
However you wish to execute this code is up to you. You could setup a schedule task that runs the code and add your data to the index. You’ll find your way and with real data you’ll probably have a more complex code.
4. Query Custom index
To query the custom index you need to extend the SearchResultItem with your own properties, for example
public class ExtendedSearchResultItem : SearchResultItem
{
[IndexField("name")]
public string CustomerName { get; set; }
[IndexField("age")]
public int CustomerAge { get; set; }
[IndexField("email")]
public string CustomerEmail { get; set; }
}
Then you just search against the index by creating a searchcontext that match your search result type.
public class SearchClass
{
public IEnumerable<ExtendedSearchResultItem> Search()
{
var searchContext = ContentSearchManager.GetIndex("scempty102_custom_index").CreateSearchContext();
IQueryable<ExtendedSearchResultItem> queryable = searchContext.GetQueryable<ExtendedSearchResultItem>();
queryable.Where(x => x.CustomerAge > 30); //insert your own predicate here.
var searchResults = queryable.GetResults();
return searchResults.Hits.Select(i => i.Document);
}
}
Sitecore Tackled 