What is Full-Text Search? Implementing SQL Server Full-Text Search In An ASP.NET MVC Websites With Entity Framework

Hello Busy MVC Developers!

Today here, I am going to introduce What is Full-Text Search in SQL Server? and How do we can implement modern products searching options in ASP.NET MVC eCommerce websites with Entity Framework for .NET MVC developers.

What is Full-Text Search in ASP.NET eCommerce Websites?

In easy terms, Full-Text Search depends on the indexing of character information, creating it lots faster to perform matches against the index than the scans needed by the SQL statements that use sort predicate. Full-Text looking is additionally plenty more flexible than using sort predicate, that solely supports the wildcard operator.

Why Do We Need a Product Search Functionality in our eCommerce Websites?

Literally, each web site, from huge eCommerce sites like Amazon.com, all the way down to tiny blogs or personal sites has an large need of product search functionality. Once your site’s content starts to grow, your guests can wish to be able to search your products, accessories and other site informations. Such search utility can create your web site a lot of interactive, from a customer’s purpose of view and can encourage them to stay longer and buying options. whereas there are many main approaches in implementing search functionality, like full text search, we are attending to cover the fundamentals of the ‘Full-Text Search within the underlying SQL DataBase’, that is additionally usable for an ASP.NET MVC developers who are working with eCommerce and shopping carts.

Important Steps for the Full-text Search functionality for .NET Website

Here are the important steps to implementing SQL server Full-Text Search in an ASP.NET MVC web application with Entity Framework.

Step 1: Create a Table for the Product Search.

CREATE TABLE [dbo].[ProductSearch](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ProductId] [int] NOT NULL,	
	[ProductCode] [varchar](50) NOT NULL,
	[ProductName] [varchar](250) NULL,
	[ProductDescription] [nvarchar](max) NULL,
        [ProductActive] [bit] NOT NULL,	 
    CONSTRAINT [Pk_ProductSearch] PRIMARY KEY CLUSTERED (Id ASC))

Step 2: Create a Full-Text Catalog for the searching.

CREATE FULLTEXT CATALOG FTSearch

Step 3: Create the Full-Text index.

CREATE FULLTEXT INDEX ON ProductSearch(
ProductCode,
ProductName,
ProductDescription,		
LANGUAGE 1033) 
KEY INDEX Pk_ProductSearch ON FTSearch

Step 4: Create Procedure for the search functionality.

Create PROCEDURE [dbo].[Sp_ProductSearch]	        
	@SearchTerm NVARCHAR(250) AS
IF(@SearchTerm LIKE '% %')
BEGIN	
       SELECT Id,
	   (CASE WHEN 
		ProductCode like '%' + @SearchTerm + '%' OR
		ProductName like  '%' + @SearchTerm + '%' OR
		ProductDescription like  '%' + @SearchTerm + '%'
		THEN 0 ELSE 1 END) as RowIndex FROM ProductSearch		        
		INNER JOIN FREETEXTTABLE(ProductSearch,(ProductCode,ProductName,ProductDescription), @SearchTerm) 
		AS fttpd ON id = fttpd.[KEY] Order By RowIndex 
END
ELSE
BEGIN
      SELECT Id,
      (CASE WHEN 
      ProductCode like '%' + @SearchTerm + '%' OR
      ProductName like  '%' + @SearchTerm + '%' OR
      ProductDescription like  '%' + @SearchTerm + '%'      
      THEN 0 ELSE 1 END) as RowIndex FROM ProductSearch          
      where Contains((ProductCode,ProductName,ProductDescription), @SearchTerm)       
      Order By RowIndex
END

Step 5: Performing a Full-Text Search.

Now here I am going to introduce fastest searching options is “Contains” and “FREETEXT”, yes it’s faster than using “LIKE” while we are dealing with SQL Server. In our example we need Contains and FREETEXT both searching options both.

  • FREETEXT
  • FREETEXTTABLE
  • CONTAINS
  • CONTAINSTABLE

Let’s now code it!

FREETEXT:

Is a predicate used in the Transact-SQL WHERE clause of a Transact-SQL SELECT statement to perform a SQL Server full-text search on full-text indexed columns containing character-based data types. This predicate searches for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches:

  • Separates the string into individual words based on word boundaries (word-breaking).
  • Generates inflectional forms of the words (stemming).
  • Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.
Syntax :
SELECT * FROM table WHERE FREETEXT(Column1,Column1, 'test');
SELECT * FROM ProductSearch WHERE FREETEXT (ProductName,ProductDescription, 'Pop Rivets' )

CONTAINS:

You can use “*” before and after in contain syntax same as like operator . but you need to use double quote before and after the search string
You can not used space in using Contains.

Syntax :
SELECT * FROM table WHERE CONTAINS(Column1,Column1, 'test');

Example

SELECT * FROM ProductSearch WHERE Contains (ProductName,ProductDescription, 'Pop' )
SELECT * FROM ProductSearch WHERE Contains (ProductName,ProductDescription, '"Po*"' )

Example used in Entity Framework:

Here is the full code with real time example of full-text searching products that I am done with my ASP.NET MVC eCommerce website.  


public class ProductSearch
{
        public int RowIndex { get; set; }
        public int Id { get; set; }                
}

<strong>Add below method You Repository and Interface<br /></strong>
//productSearchRepository class
string ConnectionString = System.Configuration.ConfigurationManager.AppSettings[&quot;ConnectionString&quot;];
public IList ProductSearchs(string srarchTerm){
	using (var connection = eCommerceConnectionString.CreateSqlConnection()){
		IList productSearchs = new List();
		try{
			using (var command = connection.CreateCommand()){
				command.CommandText = &quot;[dbo].[Sp_ProductSearch]&quot;;
				command.CommandType = CommandType.StoredProcedure;
				// input parameters
				command.Parameters.Add(new SqlParameter(&quot;@SearchTerm&quot;, SqlDbType.NVarChar, 250) { Value = srarchTerm });
				command.CommandTimeout = 0;
				command.ExecuteNonQuery();
				var rdr = command.ExecuteReader();
				while (rdr.Read()){
					productSearchs.Add(new ProductSearch{
						Id = rdr.GetInt32(rdr.GetOrdinal(&quot;Id&quot;)),
						RowIndex = rdr.GetInt32(rdr.GetOrdinal(&quot;RowIndex&quot;))});
				}                        
				rdr.Close();
				return productSearchs;
			}
		}
		catch (Exception ex){
			throw ex;
		}
		finally{
			if (connection.State == ConnectionState.Open)
				connection.Dispose();
		}
	}
}        

srting searchTerm =&quot;Search key word&quot;
IQueryable productQuery =
                productSearchRepository.Query
				.Where(p => p.ProductActive == &quot;Y&quot;)
                    
var products = new List();
IList allProduct = productQuery.ToList();

//Here execute Store Procedure
IList freeTextProduct = productSearchRepository.ProductSearchs(searchTerm);

//Add Exact Match Products, RowIndex == 0
products.AddRange(allProduct.Where(x => freeTextProduct.Any(z => z.RowIndex == 0 && z.Id == x.Id)).ToList());

//Add Relevant Products, RowIndex == 1
products.AddRange(allProduct.Where(x => freeTextProduct.Any(z => z.RowIndex == 1 && z.Id == x.Id)).ToList());

Summary

I provided a basic introduction to Full-Text Search, and showed how to create a Full Text Search index from inside SQL Server with Entity Framework. It then mentioned some basic queries and showed how to create a stored procedure to come up with paged results from a Full Text Search. Finally it showed the way to use the database.SqlQuery technique of the Entity Framework DbContext to execute the stored procedure and to pass the result back to C# code.

Find more useful blogs related Asp.Net MVC development for the unique Asp.Net MVC developers. You may also Hire Experienced Asp.Net MVC developers from our Satva Solutions company.

Thanks

By | 2017-07-03T15:39:40+00:00 April 24th, 2017|Asp.Net MVC, E-Commerce, SQL Server|