2008.01.29 11:36 AM

FullTextSearch Normalization as SQL Server Function

Wanted to post this a week ago, but sickness and weather-related school closures have not been kind to my schedule. Also, I'm about done with a rewrite of my prior SQL Server Full Text Search condition normalization logic, to include some parsing improvements and noise word support, and hoped to post them simultaneously. Unfortunately, that's been delayed as well. So, rather than wait, I'm posting this now.

What this is is a kind contribution by Fabio Gava of Susegana, Italy explaining how he migrated my previously posted FTS condition normalization logic into SQL Server 2005 as a native function. This is possible, of course, because SQL Server 2005 includes support for hosting .NET assemblies, within certain limitations. This is a great solution for folks with a SQL Server/FTS back-end, but without support for hosting .NET logic on the front-end, whether for a web site or perhaps a client-based app.

I'm posting Fabio's instructions unaltered. I haven't actually tried this myself, but will with the new version when it's finished. It looks right to me, and he reports that it worked well for him. He also suggested that I "...please add also my email if someone needs help about this I could try to do it (since I'm not a C# developer)." So, if you have any questions or need some help, you can contact Fabio at fabio.gava AT bloomtech.it, or you can post a comment here and I'll try and take a look or will forward it to Fabio for you.

OK, I'll try to be as complete as possibile, let me know if you feel something is missing:

I begun with the this link:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CLRSProc.aspx

Using that instructions I was able to compile the FullTextSearch.cs, also if I had some troubles with the SAFE
options in creating the store function. 
Ah... I forgot to mention that I created a stored function, not procedure, since what I need is a returned value
(the normalized text), not a full dataset.

Since your class returns values as a standard DLL and it's not integrated with SQL Server, I added a class
"overwrapping" your class. This class has only one method, at the moment, who just call the NormalForm
method.
The code I added is:

 public sealed class btFullTextSearch {

 [Microsoft.SqlServer.Server.SqlProcedure]
    public static void NormalizzaRS(string condition)
    {
        FullTextSearch fts = new FullTextSearch(condition);

        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Normalizzato", SqlDbType.VarChar, 8000));
        SqlContext.Pipe.SendResultsStart(record);
                record.SetSqlString(0, fts.NormalForm);
  SqlContext.Pipe.SendResultsRow(record);
 SqlContext.Pipe.SendResultsEnd();
    }

    public static string NormalizzaVal(string condition)
    {
  FullTextSearch fts = new FullTextSearch(condition);
  return (string) fts.NormalForm;
    }
 }

Just before your class declaration.

Since I begun testing with recordset, I left both the method:
NormalizzaRS returns a dataset
NormalizzaVal returns a string

As you can see some SQL specific's units are required, so I added also:
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

That's all. I include also the T-SQL I used to declare external procedure and stored procedure and function:

CREATE ASSEMBLY NormalizzaFullText
FROM 'd:\fulltextsearch.dll' WITH PERMISSION_SET = UNSAFE

CREATE PROCEDURE sp_NormalizzaFullText
@testo NVARCHAR(500)
AS
EXTERNAL NAME FullTextSearchDLL.[Ewbi.btFullTextSearch].NormalizzaRS

CREATE FUNCTION sf_NormalizzaFullText(@testo NVARCHAR(500)) RETURNS
NVARCHAR(4000)
AS EXTERNAL NAME FullTextSearchDLL.[Ewbi.btFullTextSearch].NormalizzaVal

Thanks Fabio!


Comments

737 6
592 7
101 7
66 8
385 1
184 0
672 8
379 5

acessibizathe | 2009.11.27 12:24 PM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00e5500f99db8834

Listed below are links to weblogs that reference FullTextSearch Normalization as SQL Server Function: