Search for Data in SQL/C#

I wanted to implement a blog search function ... not because I thought I had so much content, but simply because of the cool factor of having the functionality.

I started to think about the ways to do this. The most robust that came to mind is creating a full text index on the blog tables. I really didn't want to do this, though. Frankly, a full text index for this type of thing is a bit of an overkill. They really shine for proximity and inflection searches on a lot of data.

Take my employer's catalog -- tens of thousands of products, each with attributes like color, size, and style. Full text indexing is a requirement for this type of thing.

Some of the drawbacks to full text indexing -- and why they can be a hassle -- include things like: updating, maintenance, and permissions. In SQL 7 days, for example, full text indexes were a no-no on clustered databases because the index itself was not fault tolerant. If I remember correctly it would work as long as you never had a failover.

But I digress ... a few years back I implemented a small-scale search at a job, and I decided to do the same type of thing here. This method is more robust than simply doing:

SELECT * FROM myData WHERE description like '%'+searchwords+'%'


What we can do is parse the input (ie textbox) into words, format it as XML, and pass it to a stored procedure and join against the appropriate columns. A sample XML document with keywords may look like:



A stored procedure to handle this can be coded as follows:

CREATE PROCEDURE dbo.BlogSearch

    @keywords varchar(1000)

AS

BEGIN

    SET NOCOUNT ON
    DECLARE @DocHandle int

    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @keywords

    SELECT
        blogId
        ,blogTitle
        ,blogDatePublished
        ,blogText

    FROM blogArticles_art

    JOIN
        OPENXML (@DocHandle, '/search/keyword',1) WITH (value varchar(100)) AS Query
        ON (
            blogText like ('%' + Query.value +'%')
            or blogTitle like ('%' + Query.value +'%')
        )

    ORDER BY blogDatePublished DESC

    EXEC sp_xml_removedocument @DocHandle

END


Last things first: always call sp_xml_removedocument at the end of the procedure! This is pretty cool and opens up a lot of possibilities of using XML. For this purpose, it is similar enabling SQL to take a parameterized array of elements; easy to do in code but not in SQL.

A pretty simple example, but it works great. The sizes of the fields need tweaking based on the application, and this is just an example. The next step is to parse a string and create an XML document like the one shown above. The way I've done it is to add a method to my "common" static methods class. The code looks like:

private static string GetKeywordsXml(string SearchKeywords)
{
    if (SearchKeywords == null || SearchKeywords.Length == 0) {
        return string.Empty; }

    SearchKeywords = System.Web.HttpUtility.UrlDecode(SearchKeywords);
            string[] searchArray = SearchKeywords.Split(' ');
            
    XmlDocument searchXmlDoc = new XmlDocument();
    XmlElement main = searchXmlDoc.CreateElement("search");
    searchXmlDoc.AppendChild(main);

    for (int x=0; x<=searchArray.GetUpperBound(0); x++)
    {
        XmlElement keyword = searchXmlDoc.CreateElement("keyword");
            
        XmlAttribute attr = searchXmlDoc.CreateAttribute("value");
                attr.Value = searchArray[x];
        keyword.Attributes.Append(attr);
        main.AppendChild(keyword);
    }

    return searchXmlDoc.OuterXml;
}


Simple enough, huh? This makes it very easy to take a textbox, and pass the required XML to the stored procedure's parameter.

cmd.Parameters.Add("@keywords",SqlDbType.VarChar,1000);
cmd.Parameters["@keywords"].Value = GetKeywordsXml(SearchWords);


That's it for this code. Pretty basic and needs some tweaking for each implementation, but SQL Server 2000 allows for some really cool XML functionality.

Comments (1) -

Andreas
Andreas
10/25/2005 8:04:23 AM #

A great solution. Thanks for that!

Comments are closed

My Apps

Dark Skies Astrophotography Journal Vol 1 Explore The Moon
Mars Explorer Moons of Jupiter Messier Object Explorer
Brew Finder Earthquake Explorer Venus Explorer  

My Worldmap

Month List