Use SQL Queries to Search NCache in .NET

In this video we are going to show you how to use SQL queries to search NCache in your .NET applications. So, let's jump right into it.

NCache Query Overview

Now, NCache provides numerous features to retrieve the data from the cache and one of those features is called the SQL Search or NCache Query feature. Using this feature of NCache you are able to perform criteria based or attribute based searching on the cache and that too with high performance.

Now, NCache is highly scalable, so unlike a backend database which gets choked whenever the load is increased. NCache have multiple cache servers to distribute load and give high performance as well. So, in order to give you a bit of an overview of NCache query feature, this will provide you ease of use. So, NCache has SQL like query syntax. So it is very easy for developers to write and execute the queries.

• Ease of Use

  Apart from that, this feature is very easy to configure. I'm going to show you all the ways where you can do that as well. And, then NCache provides multiple options to perform the criteria base searching.

• Benefits

  If you talk about the benefits of using this feature, the first benefits is obviously you are going to achieve high performance because you're accessing in-memory data as compared to a backend database which is a disk based store. Then you have multiple cache servers to ensure enhanced search scalability and parallel query execution. The key benefit here is that NCache performs searches on indexed data. So, this will ensure that you get lightning fast speed for your applications, since you have a lighting fast access to the data as well.

• Reference Data Use Case

  We typically recommend this feature for a Reference Data use case where you have the data is not frequently changed. You have very large data, you have some dashboard which requires some criteria to search the data then this feature is perfect for those scenarios. You have to make sure that the complete data is cached, the complete data is stored within the cache so that you do not get any partial results based on some queries.

Environment Setup

So, let's talk about how to implement this feature in your applications. In the environment setup in the implementation steps first of all of course you have to make sure that NCache is installed and configured. So, if I show you I have this cache configured 'ReferenceCache' hosted on two nodes 101 and 102.

We have a separate video for this as well where you can configure it. And then, if I go go back, this is the console application that we're going to use for this test. These are some method that we are going to use and, some model classes for criteria based searching. So, I'm going to focus on customers’ orders and products here.

Step 1: Install the NCache Enterprise NuGet Package

Now, the first step is to install the NCache Enterprise NuGet package in your application. So, let's go to the NuGet package manager and go here and search for simply NCache. Now, you're going to see the Alachisoft.NCache.SDK. This is the NuGet package, the Enterprise NuGet package. I've already installed it on two of my projects here, as you can see right here. So, let's go back now.

Step 2: Include NCache Namespaces

And, in the second step we're going to - we're going to include the NCache namespaces in the applications. So, I'm just going to go ahead and type here using Alachisoft.NCache.Client, there we go. And, then I'm going to do this on one of the classes as well. I'm going to explain how and why we are doing this. So, NCache.Runtime.Caching. There we go, perfect. Let's go back now.

Step 3: Configure Query Indexes

The third step would be to configure the query indexes, as you can see right here. So, NCache provides you three options to do that. The first option is programmatically. So, you can include the query indexable tag in your code. You have NCache PowerShell command line tools. And, then you have the Management Center - the GUI tool for this.

• Programmatical Approach

  So, let's start off with the first approach, the programmatical approach here as you can see. So, let's go back. I'm going to go to the Orders class where we added the assembly reference, and I'm just going to go ahead and type [QueryIndexable] here.

  Now, this would mean that the complete attributes, as you can see here are indexed, and you can perform criteria based searching on all these attributes. Let's go back.

• Using Command Line Tool

  The second option is to use the command line tool. So, I'm going to use PowerShell here. Let me actually bring it here, there we go. And, I have a command already copied which I'm just going to go ahead and take it and paste it right here, there we go. So, this command is called Add-QueryIndex command as you can see.

  

  First of all you specify the -CacheName, referenceCache in our case. The AssemblyPath where the class library is present. The name of the class which has the attributes you want to do query index, indexing and then the attributes that you want to query index and the server where you want to perform where you want to do these configurations.

  I'm just going to go ahead and press Enter here, and this will automatically configure the query indexes on both nodes as you can see right here.

• NCache Management Center

  Let's minimize, and let's move on to the third option which is using the NCache Management Center. So, let's go back. I'm just going to Refresh this cache so that it picks up the latest configurations, then go into the View Details, scroll down a little bit, and in the advanced settings section we have the Query Indexes option here. And, as you can see the product class is already added that's what we added using the command line tool you can see here.

  Let's click on the add button, click on browse, you'll go to the location where the class library is present. This is the location in my case. Let me search for model classes, there we go. Let me open it, expand it, expand it again and let's select customer this time. So, click on Add Selected Classes. And again, you can select attributes for which you want to perform the criteria based searching. For this video I'm just going to select the complete attributes, but let's say, if you have one or two attributes you want to perform searches on you can select those attributes as well. Let's click on okay and then click on Save changes after viewing this, yeah there we go. Everything looks perfect yeah.

  

  All right, let's go back to the main page and start the cache. By the way all these options that I have shown you here these are already available on our documentation. So, we have this configure query indexes complete doc available. So, it gives you the management center and then command line tool options to do that as well. Let's go back. The cache is ready. Let's move on to the application now.

Step 4: Implement SQL Query Commands

So, this is the fourth step where we implement the SQL query commands in the applications. So, let's go back. Now, this is the application, as you can see here, first of all we'll go to the initialized cache. This will initiate the connection to the cache as well. So, first of all we're getting the cache name from the App.config file as you can see here.

This is the cache value, reference cache for our video. Let's go back. And, it's simply calling the GetCache API to get the cache handle. Let's go back again. And, then it's just going to add some data in the cache as well.

Search Data using ExecuteReader

Now, in order to implement SQL queries NCache gives you certain APIs to perform the queries or criteria based searching. So, the first API is the ExecuteReader. So, you can search data using ExecuteReader in your applications. And how you can do that is I can show you right now. Let's go inside the first method here. So, first of all you'll give the query string. Now, this string would be a simple SQL like query that you typically use for a database. And, then this string query is provided to the query command API of NCache. So, NCache will convert this string into a query command, and then this query command, as you can see, will be provided to the ExecuteReader API. So, NCache has the SearchService.ExecuteReader API, and you'll provide this query command there.

//region ExecuteReader
private static void QueryAll()
{
	try
	{
		var query = "SELECT * FROM Model_Classes.Orders";
		var querycommand = new QueryCommand(query);
		ICacheReader reader = _cache.SearchService.ExecuteReader(querycommand, true);
		PrintData<Orders>(reader);

		var query1 = "SELECT * FROM Model_Classes.Product WHERE ProductID > ? AND UnitPrice < ?";
		var querycommand1 = new QueryCommand(query1);
		querycommand1.Parameters.Add("ProductID", 20);
		querycommand1.Parameters.Add("UnitPrice", (decimal)70);
		ICacheReader reader1 = _cache.SearchService.ExecuteReader(querycommand1, true);
		PrintData<Product<(reader1);
	}
	catch (Exception)
	{
		throw;
	}
}

Let's go inside this ExecuteReader for now. So, these are the parameters that NCache provides for execute reader. So, first of all we have query command, then we have getData. Now, it's by default set to true, but as the name suggests what it does is that, if the getData is set to true, NCache will not only bring the key, it will also bring the complete object for your applications. So, let's say, if you want to get the key as well as value, you can use the get data true and you can just keep it as is. But, let's say, if you just want to use the keys based on certain criteria you can set it to false, NCache will simply give you the keys and you can you know perform any operation accordingly. And then, we have a chunk size here as you can see. It will simply give you, you can configure it in bytes and NCache will retrieve the data in batches based on the chunk size.

ICacheReader ExecuteReader(QueryCommand queryCommand, bool getData = true, int chunkSize = -1);

Now, let's talk about the NCache query syntax or how what sort of operators that NCache support here. So, as you can see, you can either you select all, we have some projections. You can select some columns here. If I scroll down NCache also support like queries as well. So let's say, if I want to search some customers where the countries are like let's say USA in our case, as you can see right here. So you can do that as well. Then we have in operator support as well, if you have any use case regarding that. NCache also support group by. So, let's say, as you can see we have selecting some min-max here. And then, let's say, if you want to sort something, if I scroll down, there we go, let's say, if you want to sort some retrieval item that are being retried from the cache NCache has the support for that as well. So, whatever items are retrieved by the NCache, NCache will sort it for you and then return it to the application. So you can as you can see here do that based on these queries.

So, let's go ahead and let me actually scroll down a little bit and run the application, there we go. Let's bring up the monitor now. Yeah, the client is connected and everything looks dandy. So we have some request initialization, request coming in, items are added in the cache. Let's go back and now press Enter. There we go. So, all these results you're seeing are based on the criteria based searching that we have just provided. So you can see in the background, in the monitor we have some requests coming in some latency items, additions per second. So, all that good stuff. You can actually review it here. So, everything is working as per our expectations. So, this was all in regards to the NCache execute reader here.

Search Data with ExecuteScalar

Now, let's talk about the second API that NCache provide which is the Execute Scalar. So, let's say, if you want to get a single value based on any criteria NCache supports that as well. So, if I go back in the application, let me actually comment these methods out, we don't need them anymore, this one as well, and then let's uncomment this one, yeah. Let's go inside this method.

//region ExecuteScalar

private static void QueryAverage()
{
	try
	{
		 string query = "SELECT AVG(UnitPrice) FROM Model_Classes.Product WHERE UnitsInStock > ?";
		 // Use QueryCommand for query execution
		 var queryCommand = new QueryCommand(query);
		 queryCommand.Parameters.Add("UnitsInStock", 100);
		 // Executing QueryCommand through ICacheReader
		 decimal average = (decimal)_cache.SearchService.ExecuteScalar(queryCommand);
		 Console.WriteLine($"Average price is {average}.");

	}
	catch (Exception)
	{
		 throw;
	}
}

Again, everything is exactly the same. You specify the query string in our case we are simply selecting the average of unit price based on certain criteria here, and then we're providing it to query command and then that query command is being sent to execute scalar. If I go inside this API you can see there's just single parameter that you have to use here.

object ExecuteScalar(QueryCommand queryCommand);

Let's go back and go back again actually and then run the application, there we go. I think the client is connected, yeah. And, let's press Enter now. There we go. So, we have a request came in in the request per second section in the monitor back in the background, and you can see the average price, as you can see here is displayed here as well. So, everything is dandy. Let's close it.

So let's say if you have a use case which require Execute Scalar you can definitely review this as well. Let me actually comment it now.

Delete Data with ExecuteNonQuery

Now, let's move on to the third API which is the Execute Non Query. So, let's say, if you want to delete some data based on any criteria NCache the support for that as well. So, we have Execute Non Query if you want to implement it you can go to the APIs, let me actually go into this method, and again everything else is exactly the same. So, this is the query string where we're deleting some items based on certain criteria. And, we're just passing it in the ExecuteNonQuery() here.

//region ExecuteNonQuery
private static void DeleteData()
{
	try
	{
		 string query = "DELETE FROM Model_Classes.Product WHERE UnitsInStock > ?";

		 var queryCommand = new QueryCommand(query);
		 queryCommand.Parameters.Add("UnitsInStock", 10);

		 _cache.SearchService.ExecuteNonQuery(queryCommand);


		 string query1 = "DELETE FROM Model_Classes.Orders WHERE OrderID > ?";

		 var queryCommand1 = new QueryCommand(query1);
		 queryCommand1.Parameters.Add("OrderID", 10248);

		 _cache.SearchService.ExecuteNonQuery(queryCommand1);
	}
	catch (Exception)
	{
		 throw;
	}
}

Let's go inside this one. Again, it has only one parameter that you want have to use and that is the query command.

int ExecuteNonQuery(QueryCommand queryCommand);

Let's go back, and go back again and run the application. Here we go. Perfect. One client is connected. Now, before I press Enter, right now you can see the count is around 400-500 per box. As soon as I press Enter it will drop down. There we go. So, the count is now went back to 100 items or less than 100 items per box.

So based on any criteria you can simply delete any items from the cache, you don't have to call the APIs again and again, delete APIs or remove APIs again and again for this.

So, this was all in regards to the NCache APIs, the Execute Reader, Execute Scalar, Execute Non Queries. And all these APIs, all these implementations, and the code it's already available on our website. So, in the SQL Query in Cache, we have this documentation. It has the complete details. So, you have the overview. You can use execute reader, scalar, if you want to use or delete data. Then we have SQL Reference documentation. This will explain all the SQL syntaxes that are supported by NCache, the query formats that are supported by NCache. So, all of these can be reviewed here. So you can go to this doc and review this on your end.

Next Steps

Download NCache (Free 30-Day Trial)

Now, if you talk about the next steps, I will highly recommend you guys to download the NCache free 30-day trial. This will give you a fully work working product for 30 days. In order for you to do that just go to our website alachisoft.com and on the top right click on this download button to download NCache and start the POC.

Try NCache Playground

The second step would be to try the NCache Playground. Now, NCache playground is a hosted service that we provide. It will give you a 30-minutes sandbox session where you will have a 2-node cache cluster and you will be able to use it to edit and run .NET as well as Java samples. And, in order for you to do this is just go to our website and on the top you can go to our resources and click on this Try Playground here or you can go to our website and on the top right you can see the Try Playground button here.

Schedule a Personalized NCache Demo

Lastly, I would also highly recommend you to schedule a personalized NCache demo where we talk about the NCache architectures, features, we get your use case and all that good stuff. And, in order for you to do that is again go to our website and go to Resources and click on Schedule a Demo, fill out a form and we'll take it from there.

So, guys this was all in regards to the NCache SQL Query SQL Search feature, how to implement it. If you have any questions feel free to reach out, thank you so much for watching this video. Have a nice day!

What to Do Next?

• Download NCache
• Schedule a Demo
• Edition Comparison