CODEWITHSUNDEEP
c#.netsql-serverfull-text-search
khurshed_nosirov
khurshed_nosirov

Reputation: 248

Full-text query returns no results

everyone! I would be very pleased if anyone could help me out.

I've developed a full-text search engine with C# and Sql Server 2008 as a DB. The query below returns results when I run it in SSMS, but it returns nothing when I run it from C# code with its parameterized form:

SqlCommand cmd = new SqlCommand("SELECT distinct occurrence "+
                                "FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL, @doc)', 1033, 0, 0) "+
                                "where display_term in "+
                                "( "+
                                  "SELECT display_term "+
                                  "FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL, @searchterm)', 1033, 0, 0) "+
                                 ")",conn);

            cmd.Parameters.Add("@searchterm",SqlDbType.VarChar);
            cmd.Parameters["@searchterm"].Value = "distributed property";
            cmd.Parameters.Add("@doc", SqlDbType.VarChar);
            cmd.Parameters["@doc"].Value = "in the search of distributed ways that are provided by properties";
            SqlDataReader dr = cmd.ExecuteReader();

Upvotes: 1

Views: 280

Answers (1)

Volearix
Volearix

Reputation: 1593

Does cleaning it up a bit with this help point out any differences between your in VS code from your SQL code? Also, please, use USING and ADDWITHVALUE when creating your queries. I don't know if it's just me, but this is a MUST in my opinion.

using (SqlConnection con = new SqlConnection(conString))
{
    string command = string.Format({0}{1}{2}{3}{4}{5},  
    "SELECT distinct occurrence ",
    "FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL, @doc)', 1033, 0, 0) ",
    "where display_term in "
    "( ",
    "SELECT display_term ",
    "FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL, @searchterm)', 1033, 0, 0) ",
    ")")
    using (SqlCommand cmd = new SqlCommand(command,conn))
    {
        cmd.Parameters.AddWithValue("@searchterm", "distributed property");
        cmd.Parameters.AddWithValue("@doc", "in the search of distributed ways that are provided by properties");

        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }
        using (SqlDataReader dr = new SqlDataReader(cmd))
        {
            if(dr.HasRows)
            {
                While(dr.Read())
                {
                    // Do stuff here...
                }
            }
        }
    }
}

Upvotes: 0

Related Questions