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

By: Harshad
Updated: April 24, 2017 | Technology: Asp.Net MVC, E-Commerce, SQL Server

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

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

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

Freetest

Contains

The example used in Entity Framework

Summary

Hi 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 a 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 pieces of information. 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 useful for 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.

Create a Table for the Product Search.

Copy to Clipboard

Create a Full-Text Catalog for the searching.

Copy to Clipboard

Create the Full-Text index.

Copy to Clipboard

Create Procedure for the search functionality.

Copy to Clipboard

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.

Copy to Clipboard

CONTAINS:

You can use “*” before and after in containing syntax same as a like operator. but you need to use double quote before and after the search string

You can not use space in using Contains.

Copy to Clipboard

Example:

Copy to Clipboard

The example used in Entity Framework:

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

Copy to Clipboard

[csharp] public class ProductSearch { public int RowIndex { get; set; } public int Id { get; set; } } //Add below method You Repository and Interface //productSearchRepository class string ConnectionString = System.Configuration.ConfigurationManager.AppSettings[“ConnectionString”]; public IList ProductSearchs(string srarchTerm){ using (var connection = eCommerceConnectionString.CreateSqlConnection()){ IList productSearchs = new List(); try{ using (var command = connection.CreateCommand()){ command.CommandText = “[dbo].[Sp_ProductSearch]”; command.CommandType = CommandType.StoredProcedure; // input parameters command.Parameters.Add(new SqlParameter(“@SearchTerm”, 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(“Id”)), RowIndex = rdr.GetInt32(rdr.GetOrdinal(“RowIndex”))}); } rdr.Close(); return productSearchs; } } catch (Exception ex){ throw ex; } finally{ if (connection.State == ConnectionState.Open) connection.Dispose(); } } } srting searchTerm =”Search key word” IQueryable productQuery = productSearchRepository.Query .Where(p => p.ProductActive == “Y”) 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()); [/csharp]

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 to 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…!!