Point2Share | Daniel McPherson's SharePoint Blog

Quick Launch

All Site Content

The zevenseas Community > Blogs > Point2Share | Daniel McPherson's SharePoint Blog > Posts > Announcing: MOSS SQL SearchCoder

June 04

Announcing: MOSS SQL SearchCoder

by Daniel McPherson

on 04/06/2008 06:46

Category: Tools

DOWNLOAD

Search in MOSS, from a programmatic perspective, has come a long way in MOSS, specifically it is more robust (though could still be MORE robust), more bug free and therefore more suited for use as plumbing in applications. I have found it particularly useful to get over some of the scale restrictions SharePoint places on developers, and taken advantage of it to meet the need for aggregation.

In a recent project however I found myself writing this little piece of spaghetti code.

        1: //Build the Select Statement

       2: string baseQuery =

       3:     @"SELECT Title, Rank, Description, Created, Path, NumComments, NumViews, NumLinkbacks, Tags, Categories FROM portal..scope()";

       4:  

       5: //Set the base scope

       6: string baseScope = @" (""SCOPE"" = 'All http://" + strSiteURL.Host + @"/ Blogs') ";

       7:  

       8: // Build the Where Clause

       9: string baseWhere;

switch (tag)

{

    case "":

        baseWhere = @"WHERE " + baseScope;

        break;

    default:

        //TODO: MUST ADD TAG FIELD SUPPORT

        baseWhere = @"WHERE CONTAINS (""Categories"",'""" + tag + @"""') AND " + baseScope;

        break;

}

  

//Build the OrderBy Clause

string localSortField;

if (_sortField == "")

    localSortField = "Created";

else

    localSortField = _sortField;

  

string orderQuery;

if (localSortField == "")

    orderQuery = "";

else

    orderQuery = @"ORDER BY """ + localSortField;

  

//End the Query

string endQuery = @""" DESC";

  

//Putting it all together

string strsql = baseQuery + baseWhere + orderQuery + endQuery;

Debug.Write(strsql);

  

return strsql;

I'm not proud of it, and couple of small bugs aside, was revisiting it to ensure it was "solid" (as if code like this could EVER be solid) when I threw in the towel, sick of writing code to write code every time I hit a project that needs to use search. There should be an easier/better way.

The result of my frustration is a tool that takes spaghetti code like this and turns it into:

       1: MOSSSearch mossSearch = new MOSSSearch();

       2:  

       3: ArrayList Scopes = new ArrayList();

       4: Scopes.Add("All http://" + strSiteURL.Host + @"/ Blogs");

       5: mossSearch.Scopes = Scopes;

       6:  

       7: ArrayList returnProperties = new ArrayList();

       8: returnProperties.Add("Title");

       9: returnProperties.Add("Path");

returnProperties.Add("Rank");

returnProperties.Add("Description");

returnProperties.Add("Created");

returnProperties.Add("NumComments");

returnProperties.Add("NumViews");

returnProperties.Add("NumLinkbacks");

returnProperties.Add("Tags");

returnProperties.Add("Categories");

mossSearch.ReturnProperties = returnProperties;

  

WhereContains whereStatement = new WhereContains();

whereStatement.FirstWhereProperty = "Categories";

  

if (!string.IsNullOrEmpty(tag))

{

    WhereProperty whereTags = new WhereProperty();

    whereTags.Property = "Tags";

    whereTags.SearchTerm = tag;

    whereTags.LogOperator = WhereProperty.LogicalOperator.OR;

    whereStatement.WhereProperties.Add(whereTags);

  

    WhereProperty whereCategories = new WhereProperty();

    whereCategories.Property = "Categories";

    whereCategories.SearchTerm = tag;

    whereStatement.WhereProperties.Add(whereCategories);

}

mossSearch.Where = whereStatement;

  

SortStatement sortStatement = new SortStatement();

sortStatement.SortProperty = _sortField;

sortStatement.ResultSortOrder = SortDirection.Descending;

mossSearch.SortStatement = sortStatement;

  

Debug.Write(mossSearch.GetSQLStatement());

return mossSearch.GetSQLStatement();

Which looks much more elegant to me. Of course actually creating the query in the first place is tricky too, and while a couple of test tools are available, I wanted to put one together that:

Tested the SQL generated by the OM
Output not only the SQL Statement, but the OM code as well, making using it as simple as cut and paste.
Was inspired by the now missing in action tool MossQueryTool.exe written by SharePoint legend Steve Peschka (was on GotDotNet).

The result is the zevenseas SearchCoder and it looks like this:

Here you build your query graphically, when done you click on the "Build SQL" or "Execute Query" buttons which take you here:

Note the SQL Statement is generated, an on the C# SearchCoder OM Tab:

Anyway, this is the .9 release, still a little polishing to do, including:

- Add Sort order (it is in the OM, but not in the tool)

- Add support for multiple sort orders

- Better exception handling (of course!)

But I would rather it was out there than sitting on my hard disk, so grab it now from CodePlex:

CodePlex Project

DOWNLOAD

3 Comment(s)

Comments

Chris OBrien

This looks pretty damn useful. I remember going through an exercise to de-spaghettify some search code not too long ago - next time I'll take a look at this, well done mate.

Daniel McPherson

on 16/11/2008 07:05

Jessica Hardy

Oh yes. We'll be using this one for sure. Thanks, Dagsy.

System Account

on 26/08/2009 06:51

cool stuff

It saved lot of time

<akhil/>

on 28/11/2010 09:18

AHAPPYDEAL

Article was written well ，thanks for sharing.I also like to share a nice product
<a href=http://www.ahappydeal.com/hobbies-toys-c-103.html>cheap toys</a>I hope you will like it

on 04/04/2013 23:57

Add Comment

Title *

Body *

CommentUrl

CAPTCHA *

Attachments

Statistics

Views:	648
Comments:	3
Tags:
Published:	1644 Days Ago

Point2Share | Daniel McPherson's SharePoint Blog