Sharepoint Custom Search
Points we need to make sure before using Search Option
Open the project "SharePoint.Search".
Go to the method "ExecuteFullTextSqlQuery" In SearchResults.ascx.cs page, where we can see query which retrieves data
"SELECT FileExtension, Rank, ContentClass, Path, Title, Abstract, IsDocument FROM SCOPE()"
Go to Central Administration,
Shared Services Administration: SharedServices1 > Search Settings > Managed Properties
Open Metadata Property Mappings page
Check All the fields which is specified in the query exist or not .
If the fields doesn't exist, go to New Managed Property at the top and add the field.
Check the checkbox "Use in Scope"
Common errors which may occur are :
Property doesn't exist or is used in a manner inconsistent with schema settings.
Possible reason : Field doesn't exist in Metadata Property Mappings
Your query is malformed. Please rephrase your query
Possible reason : Query is nor formatted properly
After doing this, again if the data is not coming, Comment the line
//sbFullTextSqlQuery.Append(" \"Scope\" = '" + _scope + "' ");
Because this may give error as "Scope in your query does not exist"
To know more about how this works, check this links
http://msdn2.microsoft.com/en-us/library/bb608305.aspx
http://www.ureader.com/msg/12294188.aspx
The code below says about how we can customize sharepoint search option
protected void btnSearch_Click(object sender, EventArgs e)
{
ResultTableCollection resultTableCollection = FetchRelevantSearchResults();
ResultTable relevantResultsResultTable = resultTableCollection[ResultType.RelevantResults];
DataTable relevantResultsDataTable = new DataTable();
relevantResultsDataTable.Load(relevantResultsResultTable, LoadOption.OverwriteChanges);
if (relevantResultsDataTable != null)
{
grdSearch.DataSource = relevantResultsDataTable;
grdSearch.DataBind();
}
}
// Fetch search result
private ResultTableCollection FetchRelevantSearchResults()
{
ResultTableCollection resultTableCollection = null;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
ExecuteFullTextSqlQuery(out resultTableCollection);
}
catch (Exception ex)
{
}
});
return resultTableCollection;
}
// Call CAML query
private void ExecuteFullTextSqlQuery(out ResultTableCollection resultTableCollection)
{
// Guid _currentSiteGuid = SPContext.Current.Site.ID;
//using (SPSite _SPSite = new SPSite(_currentSiteGuid))
//http://mossdev:6076
//http://mossdev:35643/
using (SPSite _SPSite = new SPSite("http://mossdev:35643"))
{
using (FullTextSqlQuery _FullTextSqlQuery = new FullTextSqlQuery(_SPSite))
{
_FullTextSqlQuery.StartRow = 0;
_FullTextSqlQuery.RowLimit = 2;
_FullTextSqlQuery.HighlightedSentenceCount = 3;
_FullTextSqlQuery.EnableStemming = true;
_FullTextSqlQuery.TrimDuplicates = true;
_FullTextSqlQuery.Culture = CultureInfo.CurrentCulture;
_FullTextSqlQuery.KeywordInclusion = KeywordInclusion.AnyKeyword;
if (SPSecurity.AuthenticationMode != AuthenticationMode.Windows)
{
_FullTextSqlQuery.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
}
else
{
_FullTextSqlQuery.AuthenticationType = QueryAuthenticationType.NtAuthenticatedQuery;
}
_FullTextSqlQuery.ResultTypes = ResultType.RelevantResults;
StringBuilder sbFullTextSqlQuery = new StringBuilder(string.Empty);
sbFullTextSqlQuery.Append("SELECT ");
sbFullTextSqlQuery.Append(" FileExtension, Rank, ContentClass, Path, Title, Abstract, IsDocument ");
sbFullTextSqlQuery.Append("FROM ");
sbFullTextSqlQuery.Append(" SCOPE() ");
sbFullTextSqlQuery.Append("WHERE ");
sbFullTextSqlQuery.Append(" FREETEXT(defaultproperties, 'raj') ");
sbFullTextSqlQuery.Append("AND ");
sbFullTextSqlQuery.Append(" \"Scope\" = 'Knowledge Objects' ");
sbFullTextSqlQuery.Append("AND ");
sbFullTextSqlQuery.Append(" ( ");
sbFullTextSqlQuery.Append(" (IsDocument = 1 AND ContentClass = 'STS_ListItem_DocumentLibrary') ");
sbFullTextSqlQuery.Append(" OR ");
sbFullTextSqlQuery.Append(" (IsDocument = 1 AND ContentClass = 'STS_ListItem_850') ");
sbFullTextSqlQuery.Append(" OR ");
sbFullTextSqlQuery.Append(" (ContentClass = 'STS_Web') ");
sbFullTextSqlQuery.Append(" ) ");
sbFullTextSqlQuery.Append("ORDER BY ");
sbFullTextSqlQuery.Append(" Rank DESC ");
// sbFullTextSqlQuery = new StringBuilder();
//sbFullTextSqlQuery.Append("SELECT Title, Abstract, Rank FROM scope() WHERE scope='All Sites' ");
//sbFullTextSqlQuery.Append("SELECT Account,CreatedBy FROM scope() ");
// fields needs mapping Title,Abstract,
// sbFullTextSqlQuery.Append("SELECT FileExtension, Rank, contentclass, Path,IsDocument FROM SCOPE() WHERE FREETEXT(defaultproperties, 'company') AND Scope = 'Knowledge Objects' AND ( (IsDocument = 1 AND ContentClass = 'STS_ListItem_DocumentLibrary') OR (IsDocument = 1 AND ContentClass = 'STS_ListItem_850') OR (ContentClass = 'STS_Web') ) ORDER BY Rank DESC") ;
//sbFullTextSqlQuery.Append("SELECT FileExtension,contentclass,Rank,Path,IsDocument FROM SCOPE() WHERE FREETEXT(defaultproperties, 'company')");
//sbFullTextSqlQuery.Append("AND ");
//sbFullTextSqlQuery.Append(" ( ");
//sbFullTextSqlQuery.Append(" (IsDocument = 1 AND ContentClass = 'STS_ListItem_DocumentLibrary') ");
//sbFullTextSqlQuery.Append(" OR ");
//sbFullTextSqlQuery.Append(" (IsDocument = 1 AND ContentClass = 'STS_ListItem_850') ");
//sbFullTextSqlQuery.Append(" OR ");
//sbFullTextSqlQuery.Append(" (ContentClass = 'STS_Web') ");
//sbFullTextSqlQuery.Append(" ) ");
//sbFullTextSqlQuery.Append("ORDER BY ");
//sbFullTextSqlQuery.Append(" Rank DESC ");
//sbFullTextSqlQuery.Append("SELECT ");
//sbFullTextSqlQuery.Append("Title,Abstract, FileExtension, Rank, ContentClass, Path,IsDocument ");
//sbFullTextSqlQuery.Append("FROM ");
//sbFullTextSqlQuery.Append(" SCOPE() ");
//sbFullTextSqlQuery.Append("WHERE ");
//sbFullTextSqlQuery.Append(" FREETEXT(defaultproperties, 'company') ");
_FullTextSqlQuery.QueryText = sbFullTextSqlQuery.ToString();
resultTableCollection = _FullTextSqlQuery.Execute();
}
_SPSite.RootWeb.Dispose();
}
}
