CODEWITHSUNDEEP
c#sql-serverfull-text-search
leroy
leroy

Reputation: 63

Stored procedure fulltext search

Can I have some help with next:

My c# app calls for sql stored procedure, and passes some search term (string containing multiple words).

    public List<ZapisModel> GetZapis_FullText(string Parametar)
    {
        using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SQLConn.CnnVal(Program.db)))
        {
            var output = connection.Query<ZapisModel>("dbo.spZapis_GetByTest_FTC @parametar", new { parametar = Parametar }).ToList();
            return output;
        }
    }

Sql stored procedure should take that passed parameter, and do a fulltext search with each word from string. I want to enable user to search for ALL words from string, for ANY word from string, so that is why I need to split string, and include each word in search using AND or OR, based on user preferences.

Also I would like this job entirely done in stored procedure.So app passes search term, stored procedure processes it, and returns search result.

Upvotes: 1

Views: 588

Answers (1)

andrews
andrews

Reputation: 2173

I suggest you don't parse your search criteria string at SQL procedure level. Instead, prepare the correct FTS search string using C#. In my projects I use a small (just 2 files) freeware library called EasyFTS.

Here is the EasyFTS project page on GitHub: https://github.com/SoftCircuits/FullTextSearchQuery

The syntax is very easy:

var fts = new EasyFts();
var orignalNonFTSEnabledQueryString = "Center Neck Spray Bottle";
var ftsEnabledsearchText = fts.ToFtsQuery(orignalNonFTSEnabledQueryString).

The above code will convert the normal non-FTS-enabled search string eg. Center Neck Spray Bottle to the FTS-enabled version like:

"(((FORMSOF(THESAURUS, Center) AND FORMSOF(THESAURUS, Neck)) AND FORMSOF(THESAURUS, Spray)) AND FORMSOF(THESAURUS, Bottle))"

which you can use in the CONTAINS/CONTAINSTABLE or FREETEXT/FREETEXTTABLE clauses.

Theare many configuration options for the EasyFTS class. You can have it general FTS query strings with words separated by AND or OR. Just examine their docs.

This article contains very thorough description of EasyFTS: http://www.blackbeltcoder.com/Articles/data/easy-full-text-search-queries .

Disclaimer: I'm not the author of the EasyFTS library.

HTH

Upvotes: 1

Related Questions