Corey Roth [MVP]

Hi Corey,

I use FullTextSqlQuery. It doesn't have SelectProperties.Add method. Is there a way to return custom properties for the metadata?

Thanks

Dan

Here's my code-

using (Microsoft.Office.Server.Search.Query.FullTextSqlQuery fquery = new Microsoft.Office.Server.Search.Query.FullTextSqlQuery(src))

       {

           fquery.StartRow = 0;

           fquery.RowLimit = 10;

           fquery.HighlightedSentenceCount = 3;

           fquery.EnableStemming = true;

           fquery.TrimDuplicates = false;

           fquery.KeywordInclusion = KeywordInclusion.AllKeywords;

           fquery.ResultTypes = ResultType.RelevantResults;

           string strcolumns = "Title, IsDocument,WorkId,Title,Author,HitHighlightedSummary,Size,Path,PictureThumbnailURL,CollapsingStatus,SiteName,Write,Description,ContentClass,HitHighlightedProperties";

           fquery.QueryText = "SELECT " + strcolumns + " FROM Scope() WHERE ";

           fquery.QueryText += " \"Scope\" ='All Sites' AND ";

           fquery.QueryText += " FREETEXT(defaultproperties,'Report', 'HR') ) ";

           fquery.QueryText += " ORDER BY Rank Desc";

           ResultTableCollection rtCol = fquery.Execute();

           ResultTable relevantResults = rtCol[ResultType.RelevantResults];

           DataTable dt = new DataTable();

           dt.Load(relevantResults, LoadOption.OverwriteChanges);

           gridSearch.DataSource = dt;

           gridSearch.DataBind();

       }

Hi,

How can I search for both keywords as well as managed properties in keyword query? Is this correct?

keywordQuery.QueryText = string.Concat(keywords + string.Format(" +Color:\"{0}\"","Red"));

where 'keywors' contains the string to be searched in all results where color=red.

How can I use the Earlier Than and Later Tahn expressions in the KeywordQuery.KeywordQueryText syntax?

@Tom Those are only supported in SharePoint 2010.  If you are using SharePoint 2010, you simply use the > or < operators.  For example "write < '6/3/0/2010".

That's a bad news!  

Is there any way to achieve a similar filter effect in SharePoint 2007?  I'm developing a web part that basically creates a property search programmatically - it needs to replicate some of the capability of the advanced search property restrictions - so a typical query would be DocumentDate "Earlier Than" 7/2/2010. (DocumentDate is a custom Managed Property with a datetime data type).

Here is the code I have so far:

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Data;

using System.Drawing;

using System.Xml.Serialization;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

using Microsoft.Office.Server;

using Microsoft.Office.Server.Search.Query;

namespace CustomWebParts

{

   [Guid("01482ed3-b90c-428d-bacd-706267645f89")]

   public class CustomDateSearch : System.Web.UI.WebControls.WebParts.WebPart

   {

       Label lblDateSearch;

       Label lblQueryResults;

       DropDownList ddlYear;

       Button btnSearch;

       DateTime dtToday;

       DateTime dtPastYear;

       DateTime dtPast2Years;

       DateTime dtPast5Years;

       String strPastYear;

       String strPast2Years;

       String strPast5Years;

       String strSelectedValue;

       DataGrid grdResults;

       public CustomDateSearch()

       {

       }

       protected override void CreateChildControls()

       {

           base.CreateChildControls();

           //DateTime Variables

           dtToday = DateTime.Today;

           dtPastYear = dtToday.AddYears(-1);          

           dtPast2Years = dtToday.AddYears(-2);

           dtPast5Years = dtToday.AddYears(-5);

           //String Variables

           strPastYear = dtPastYear.ToString().Remove(10);          

           strPast2Years = dtPast2Years.ToString().Remove(10);

           strPast5Years = dtPast5Years.ToString().Remove(10);

           //Label

           lblDateSearch = new Label();

           lblDateSearch.Text = "Year Range Search: ";

           this.Controls.Add(lblDateSearch);

           //Drop Down List

           ddlYear = new DropDownList();

           ddlYear.Items.Add(new ListItem(" Select a Search Option", "0"));

           ddlYear.Items.Add(new ListItem(" Past Year", strPastYear));

           ddlYear.Items.Add(new ListItem(" Past 2 Years", strPast2Years));

           ddlYear.Items.Add(new ListItem(" Past 5 Years", strPast5Years));            

           this.Controls.Add(ddlYear);

           //Search Button

           btnSearch = new Button();

           btnSearch.Text = "Search";

           btnSearch.Click += new EventHandler(cmdSearch_Click);

           this.Controls.Add(btnSearch);  

           //Test Label

           lblQueryResults = new Label();

           lblQueryResults.ForeColor = Color.Red;

           this.Controls.Add(lblQueryResults);

       }

       void cmdSearch_Click(object sender, EventArgs e)

       {

           strSelectedValue = ddlYear.SelectedValue.ToString();

           if (strSelectedValue != "0")

           {

               lblQueryResults.Text = " ";

               keywordQueryExecute(strSelectedValue);

           }

           else

           {

               lblQueryResults.Text = "  *You Must Select a Search Option!";

           }          

       }

       private void keywordQueryExecute(string strQueryText)

       {

           KeywordQuery kRequest = new KeywordQuery(ServerContext.Current);

           string strQuery = "DocumentDate:" + "6/29/2009";

           kRequest.SelectProperties.Add("Title");

           kRequest.SelectProperties.Add("SiteName");

           kRequest.SelectProperties.Add("HitHighlightedProperties");

           kRequest.SelectProperties.Add("Path");

           kRequest.SelectProperties.Add("Author");

           kRequest.SelectProperties.Add("Write");

           kRequest.SelectProperties.Add("DocumentDate");

           kRequest.SortList.Add("DocumentDate", Microsoft.Office.Server.Search.Query.SortDirection.Ascending);

           kRequest.QueryText = strQuery;

           //Configure the query to return relevant results

           kRequest.ResultTypes |= ResultType.RelevantResults;

           //Execute the query

           ResultTableCollection resultsTbls = kRequest.Execute();

           if ((int)ResultType.RelevantResults != 0)

           {

               ResultTable tblResults = resultsTbls[ResultType.RelevantResults];

               if (tblResults.TotalRows == 0)

               {

                   lblQueryResults.Text = " *No Search Results Returned!";

               }

               else

               {

                   //lblQueryResults.Text = tblResults.TotalRows.ToString();

                   ReadResultsTable(tblResults);

               }

           }

       }

       void ReadResultsTable(ResultTable rt)

       {

           //Create a Data Table

           DataTable relResultsTbl = new DataTable();

           relResultsTbl.TableName = "Relevant Results";

           //Create a Data Set, add the Data Table to the Data Set, Load Data into the Data Table

           DataSet ds = new DataSet("resultsset");

           ds.Tables.Add(relResultsTbl);

           ds.Load(rt, LoadOption.OverwriteChanges, relResultsTbl);

           //Add the Data Set to the Data Grid (Display)

           fillResultsGrid(ds);

       }

       private void fillResultsGrid(DataSet grdDs)

       {

           //Create an instance of the DataGrid and set its

           //DataSource property to the supplied DataSet

           grdResults = new DataGrid();

           grdResults.DataSource = grdDs;

           //Set the display properties in the data grid

           grdResults.GridLines = GridLines.None;

           grdResults.CellPadding = 4;

           grdResults.Width = Unit.Percentage(100);

           grdResults.ItemStyle.ForeColor = Color.Black;

           grdResults.ItemStyle.BackColor = Color.White;

           grdResults.ItemStyle.Font.Size = FontUnit.Smaller;

           grdResults.ItemStyle.Font.Name = "Tahoma";

           //Set the display properties in the data grid Header

           //grdResults.HeaderStyle.BackColor = Color.Navy;

           //grdResults.HeaderStyle.ForeColor = Color.White;

           //grdResults.HeaderStyle.Font.Bold = true;

           //grdResults.HeaderStyle.Font.Name = "Tahoma";

           //grdResults.HeaderStyle.Font.Size = FontUnit.Medium;

           //Turn off AutoGenerate for the columns so that not all columns are returned

           //grdResults.AutoGenerateColumns = false;

           grdResults.AutoGenerateColumns = true;

           //DataGrid configured to display only the desired columns

           //Site Name

           HyperLinkColumn colSite = new HyperLinkColumn();

           //colSite.HeaderText = "Site Name";

           colSite.DataTextField = "Title";

           colSite.DataNavigateUrlField = "SiteName";

           grdResults.Columns.Add(colSite);

           //***Hit HighlightedProperties is not showing up!!! - Bug Fix

           //Highlight

           BoundColumn colHighLight = new BoundColumn();

           //colHighLight.HeaderText = "HitHighlightedProperties";

           colHighLight.DataField = "HitHighlightedProperties";

           grdResults.Columns.Add(colHighLight);

           //Path

           HyperLinkColumn colPath = new HyperLinkColumn();

           //colPath.HeaderText = "Path";

           colPath.DataTextField = "Path";

           colPath.DataNavigateUrlField = "Path";

           colPath.ItemStyle.ForeColor = Color.Green;

           grdResults.Columns.Add(colPath);

           //Author

           BoundColumn colAuthor = new BoundColumn();

           //colAuthor.HeaderText = "Author";

           colAuthor.DataField = "Author";

           colAuthor.ItemStyle.ForeColor = Color.Gray;

           grdResults.Columns.Add(colAuthor);

           //Write - Created Date

           BoundColumn colWrite = new BoundColumn();

           //colWrite.HeaderText = "Write";          

           colWrite.DataField = "Write";

           colWrite.ItemStyle.ForeColor = Color.Gray;

           colWrite.DataFormatString = "{0:d}";

           grdResults.Columns.Add(colWrite);

           //DocumentDate

           BoundColumn colDocDate = new BoundColumn();

           //colDocDate.HeaderText = "DocumentDate";

           colDocDate.DataField = "DocumentDate";

           colDocDate.ItemStyle.ForeColor = Color.Gray;

           colDocDate.DataFormatString = "{0:d}";

           grdResults.Columns.Add(colDocDate);

           //Bound the data to the DataGrid

           grdResults.DataBind();

           Controls.Add(grdResults);

       }

   }

}

@Tom Try using a FullTextSqlQuery instead.  It allows you to do comparisons, but it may take some trial and error on your part.

www.dotnetmafia.com/.../how-to-use-the-moss-enterprise-search-fulltextsqlquery-class.aspx

Is there a way to programmatically invoke your wild card search web part?  I was thinking of doing a wild card search - like "DocumentDate: *" and then filtering the results with a for loop before displaying in a Datagrid.

@Tom I don't think you could invoke it that way.  I think your best bet for the wildcard search is to use the FullTextSqlQuery and then you can bind it to your datagrid.

Hey Corey,

You were right, I used the FullTextSQL Query Class along with the DATEADD function and I got the results I wanted.  My updated code is below:

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Data;

using System.Drawing;

using System.Xml.Serialization;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

using Microsoft.Office.Server;

using Microsoft.Office.Server.Search.Query;

namespace CustomWebParts

{

   [Guid("01482ed3-b90c-428d-bacd-706267645f89")]

   public class CustomDateSearch : System.Web.UI.WebControls.WebParts.WebPart

   {

       Label lblDateSearch;

       Label lblQueryResults;

       DropDownList ddlYear;

       Button btnSearch;

       DateTime dtToday;

       DateTime dtPastYear;

       DateTime dtPast2Years;

       DateTime dtPast5Years;

       String strToday;

       String strPastYear;

       String strPast2Years;

       String strPast5Years;

       String strSelectedValue;

       String strSelectedItem;

       public CustomDateSearch()

       {

       }

       protected override void CreateChildControls()

       {

           base.CreateChildControls();

           //DateTime Variables

           dtToday = DateTime.Today;

           dtPastYear = dtToday.AddYears(-1);          

           dtPast2Years = dtToday.AddYears(-2);

           dtPast5Years = dtToday.AddYears(-5);

           //String Variables

           strToday = dtToday.ToString().Remove(9);

           strPastYear = dtPastYear.ToString().Remove(9);          

           strPast2Years = dtPast2Years.ToString().Remove(9);

           strPast5Years = dtPast5Years.ToString().Remove(9);

           //Label

           lblDateSearch = new Label();

           lblDateSearch.Text = "Year Range Search: ";

           this.Controls.Add(lblDateSearch);

           //Drop Down List

           ddlYear = new DropDownList();

           ddlYear.Items.Add(new ListItem("Select a Search Option", "0"));

           ddlYear.Items.Add(new ListItem("Past Year", strPastYear));

           ddlYear.Items.Add(new ListItem("Past 2 Years", strPast2Years));

           ddlYear.Items.Add(new ListItem("Past 5 Years", strPast5Years));            

           this.Controls.Add(ddlYear);

           //Search Button

           btnSearch = new Button();

           btnSearch.Text = "Search";

           btnSearch.Click += new EventHandler(cmdSearch_Click);

           this.Controls.Add(btnSearch);  

           //Test Label

           lblQueryResults = new Label();

           lblQueryResults.ForeColor = Color.Red;

           this.Controls.Add(lblQueryResults);

       }

       void cmdSearch_Click(object sender, EventArgs e)

       {

           strSelectedValue = ddlYear.SelectedValue.ToString();

           strSelectedItem = ddlYear.SelectedItem.ToString();

           if (strSelectedValue != "0")

           {

               lblQueryResults.Text = " ";

               fullTextSqlQueryExecute(strSelectedItem);

           }

           else

           {

               lblQueryResults.Text = "  *You Must Select a Search Option! " + strSelectedItem;

           }          

       }

       private void fullTextSqlQueryExecute(string strQueryText)

       {

           FullTextSqlQuery ftQuery= new FullTextSqlQuery(ServerContext.Current);

           if (strQueryText == "Past Year")

           {

               ftQuery.QueryText = "SELECT Title, Description, Write, HitHighlightedProperties, Author, Path, DocumentDate FROM SCOPE() WHERE DocumentDate >=DATEADD (Year, -1, GETGMTDATE())";

           }

           else if (strQueryText == "Past 2 Years")

           {

               ftQuery.QueryText = "SELECT Title, Description, Write, HitHighlightedProperties, Author, Path, DocumentDate FROM SCOPE() WHERE DocumentDate >=DATEADD (Year, -2, GETGMTDATE())";

           }

           else if (strQueryText == "Past 5 Years")

           {

               ftQuery.QueryText = "SELECT Title, Description, Write, HitHighlightedProperties, Author, Path, DocumentDate FROM SCOPE() WHERE DocumentDate >=DATEADD (Year, -5, GETGMTDATE())";

           }

           ftQuery.ResultTypes = ResultType.RelevantResults;

           ResultTableCollection oResultCollection = ftQuery.Execute();

           if ((int)ResultType.RelevantResults != 0)

           {

               lblQueryResults.Text = " Results Returned: ";

           }

           else

           {

               lblQueryResults.Text = " No Results Returned ";

           }

           // Execute the query and load the results into a datatable //

           ResultTable oResult = oResultCollection[ResultType.RelevantResults];

           DataTable dtResults = new DataTable();

           dtResults.TableName = "Result";

           dtResults.Load(oResult, LoadOption.OverwriteChanges);

           DataSet ds = new DataSet("All_Results");

           ds.Tables.Add(dtResults);

           DataGrid datagrid = new DataGrid();

           datagrid.DataSource = ds.Tables[0];

           //General Datgrid Formatting

           datagrid.GridLines = GridLines.None;

           datagrid.CellPadding = 4;

           datagrid.Width = Unit.Percentage(100);

           datagrid.ItemStyle.ForeColor = Color.Black;

           datagrid.ItemStyle.Font.Size = FontUnit.Smaller;

           datagrid.ItemStyle.Font.Name = "Tahoma";

           datagrid.AutoGenerateColumns = false;

           //Document Name Link

           HyperLinkColumn colDocNameLink = new HyperLinkColumn();

           //colSite.HeaderText = "Site Name";

           colDocNameLink.DataTextField = "Title";

           colDocNameLink.DataNavigateUrlField = "Path";

           datagrid.Columns.Add(colDocNameLink);

           //Document Name

           BoundColumn colDocName = new BoundColumn();

           //colSite.HeaderText = "Title";

           colDocName.DataField = "Title";

           datagrid.Columns.Add(colDocName);

           //Highlight (No Data Shows!)

           BoundColumn colHighLight = new BoundColumn();

           //colHighLight.HeaderText = "HitHighlightedProperties";

           colHighLight.DataField = "HitHighlightedProperties";

           datagrid.Columns.Add(colHighLight);

           //Path

           HyperLinkColumn colPath = new HyperLinkColumn();

           //colPath.HeaderText = "Path";

           colPath.DataTextField = "Path";

           colPath.ItemStyle.ForeColor = Color.Green;

           colPath.DataNavigateUrlField = "Path";

           datagrid.Columns.Add(colPath);

           //Author

           BoundColumn colAuthor = new BoundColumn();

           //colAuthor.HeaderText = "Author";

           colAuthor.DataField = "Author";

           colAuthor.ItemStyle.ForeColor = Color.Gray;

           datagrid.Columns.Add(colAuthor);

           //Document Date

           BoundColumn colDocDate = new BoundColumn();

           //colAuthor.HeaderText = "Author";

           colDocDate.DataField = "DocumentDate";

           colDocDate.DataFormatString = "{0:d}";

           colDocDate.ItemStyle.ForeColor = Color.Gray;

           datagrid.Columns.Add(colDocDate);

           //Bind and Add

           datagrid.DataBind();

           Controls.Add(datagrid);

       }      

   }

}

Thanks Corey!

Tom

@Tom Excellent!  Glad I could help.

Hey Corey,

Do you know how to add the document Icon to the search results\data grid?  

Tom

@Tom I'm not exactly sure how the CRWP gets it, but it uses a field called imageurl.  It's not listed as a managed proeprty but it may be reserved/hidden.  Otherwise try PictureThumbnailURL and see if that gets you anywhere.

Hey Corey,

Using SearchCoder I can tell that PictureThumbnailURL just returns a null value.  This seems to be one of those many unsolved problems with MOSS customization as evidenced by the many unanswered Blogs out there, like these two below:

www.eggheadcafe.com/.../custom-search-document-ic.aspx

www.itweb.co.za/.../index.php

This blog seems to provide an answer, but I'm not sure how to incorporate the answer provided into the datagrid I'm using, here's the blog:

social.msdn.microsoft.com/.../a16429ce-345b-444f-a386-92718675536f

Anyway, I'm still stumped on this one, any code examples would be greatly appreciated.

Tom

Pingback from  Search Sharepoint with keyword Query | Bruce Chao: Sharepoint Learning Blog

Pingback from  SharePoint 2010 Enterprise Search KeywordQuery Class, KeywordQuery Class

Pingback from  Study Guide and Tips n Tricks for Passing MS SharePoint Application Development 070-573 &laquo; Dani&#039;s Blog

Pingback from  One Cause for &ldquo;The creator of this fault did not specify a Reason.&rdquo; &laquo; Paul Galvin&#039;s SharePoint Space

Hi corey,

Is there any poossiblity that we can put the above keyword class with all the functionalities in WCF web service????

Thanks

@Altaf You can use the KeywordQuery class in a WCF service if the service is hosted on the SharePoint server.  If you need to access it remotely then you will have to call Search.asmx to make the query.

Hi Corey,

Nice blog here.. I am trying to use custom managed properties using keyword API.. I have tried using this code but it seems like some of the OOB properties returns blank.. Any idea?

e.g. I have custom managed property called - DocumentType and I would like to return some of the OOB ones like SiteName or HitHighlightedProperties. It seems like using SelectProperties, these properties returns blank.. Have you came acroos this?

           keywordQuery.SelectProperties.Add("Title");

           keywordQuery.SelectProperties.Add("SiteName");

           keywordQuery.SelectProperties.Add("DocumentType");

Nik

@Nik that is the way I always do it.  Be sure and specify every property you want in the SelectProperties collection.

Thanks Cory for your reply.

It seems like KeywordQuery API applies security trimming by default when you access the Search Data using API.

Is there anyway we can override and remove the security trimming? I have requirements which would require data access by users who don't have source data access crawled in search.

Thanks,

Nik

Hi Corey,

How can i use refiners with KeywordQuery ? I'm getting refinement from sharepoint default refinement panel.

Thanks ....

Hi Cory,

Can this be used to search inside documents?

Neil

@Nik.  I think your best bet would be to put the call to the KeywordQuery class inside a RunWithElevatedPrivileges delegate so that it runs as the application pool account.

@Neil Yes, it will search the contents of your documents automatically.

Thanks Corey...

Pingback from  Working with SharePoint Foundation Search API &laquo; DotNetAdil

Pingback from  Step by Step &#8211; Building Custom SharePoint 2010 Search Applications using KeyWordQuery API | Nik Patel&#039;s SharePoint World

Pingback from  Search does not returning list item | sharepoint2013admin

Pingback from  SharePoint 2013 Search Keyword Query Class &#8211; ??????|?????????????????????