FullTextSQL query in Sharepoint

Here is the sample on how to use FullTextSQLQuery in Sharepoint


DataTable queryDataTable = new DataTable();

                FullTextSqlQuery myQuery = new FullTextSqlQuery(serverContext);

                myQuery.QueryText = "SELECT FirstName,LastName,MobilePhone,JobTitle FROM scope() WHERE  \"scope\" = 'People' and (MobilePhone like '%" + key + "%' )";

                myQuery.ResultTypes = ResultType.RelevantResults;

                myQuery.RowLimit = 10;

                ResultTableCollection queryResults = myQuery.Execute();

                ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];

                int intTotalRecords = queryResultsTable.TotalRows;

                queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);

Create Search scope programmatically in Sharepoint

In order to create a search scope programmatically, you need to get instance of Search context.

Here is the code

SearchContext searchContext = SearchContext.GetContext(

context);
               Scopes scopes = new Scopes(searchContext);
               Scope newScope =
scopes.AllScopes.Create("MycustomScope",
"This is a sample scope for testing purpose", null, true,
"Resultpage.aspx", ScopeCompilationType.AlwaysCompile);
               Schema sspSchema = new Schema(searchContext);

newScope.Rules. CreatePropertyQueryRule(ScopeRuleFilterBehavior.Include,
sspSchema.AllManagedProperties["contentclass"],
"urn:content-class:SPSPeople");
               scopes.StartCompilation();

 

You can deploy it via feature. Here is the complete code.

public override void
FeatureActivated(

SPFeatureReceiverProperties properties)
       {
           ServerContext context = ServerContext.Current;
           if (context != null)
           {
               SearchContext searchContext = SearchContext.GetContext(context);
               Scopes scopes = new Scopes(searchContext);
               Scope newScope =
scopes.AllScopes.Create(GetConfigValue("DropDownText"),
GetConfigValue("Description"), null, true,
GetConfigValue("ResultPage"), ScopeCompilationType.AlwaysCompile);
               Schema sspSchema = new Schema(searchContext);

newScope.Rules.CreatePropertyQueryRule(ScopeRuleFilterBehavior.Include,
sspSchema.AllManagedProperties["contentclass"],
"urn:content-class:SPSPeople");
               scopes.StartCompilation();
           }

       }
       public override void
FeatureDeactivating(SPFeatureReceiverProperties properties)
       {
           ServerContext context = ServerContext.Current;
           if (context != null)
           {
               SearchContext searchContext = SearchContext.GetContext(context);
               Scopes scopes = new Scopes(searchContext);
               Scope newScope = null;

               try
               {
                   newScope = scopes.GetScope(null,
"MycustomScope");
               }
               catch (Exception ex)//For Scope Not Found Exception
               { }
               if (newScope != null)
               {
                   scopes.GetScope(null,"MycustomScoe").Delete();
                   scopes.Update();
               }

           }


       }
       public override void
FeatureInstalled(SPFeatureReceiverProperties properties)
       {
           //throw new NotImplementedException();
       }
       public override void
FeatureUninstalling(SPFeatureReceiverProperties properties)
       {
           //throw new NotImplementedException();
       }

How to add custom search scope to the search drop down in sharepoint

Once you've created a custom search scope in search adminstration, you may need to add it the search drop down of a site collection. You can create a feature to add the custom search scope.

In feature activated event I'm adding the custom search scope to the search dropdown and in feature deactivating event I'm removing it.

Here is the code


public override void
FeatureActivated(SPFeatureReceiverProperties properties)
       {
           SPWebApplication webApp =
((SPWebApplication)properties.Feature.Parent);
           foreach (SPSite site in webApp.Sites)
           {
               using (SPWeb spWeb = site.OpenWeb())
               {
                   string SPsiteurl = site.Url;
                   spWeb.AllowUnsafeUpdates = true;



                   SearchContext searchContext =
SearchContext.GetContext(spWeb.Site);
                   Scopes scopes = new Scopes(searchContext);
                   Scope skillSearchScope =
scopes.GetSharedScope("MycustomScope");
                   ScopeDisplayGroupCollection
scopedisplayGroupCollection = scopes.AllDisplayGroups;
                   ScopeDisplayGroup displayGroup =
scopes.GetDisplayGroup(new Uri(spWeb.Url), "Search Dropdown");
                   displayGroup.Remove(skillSearchScope);
                   displayGroup.Add(skillSearchScope);
                   displayGroup.Update();
                   spWeb.Update();
                   spWeb.AllowUnsafeUpdates = false;
               }
           }
       }


       public override void
FeatureDeactivating(SPFeatureReceiverProperties properties)
       {
           SPWebApplication webApp =
((SPWebApplication)properties.Feature.Parent);
           foreach (SPSite site in webApp.Sites)
           {
               using (SPWeb spWeb = site.OpenWeb())
               {
                   string SPsiteurl = spWeb.Site.Url;
                   spWeb.AllowUnsafeUpdates = true;

                   Uri siteuri = new Uri(spWeb.Site.Url);
                   SearchContext searchContext =
SearchContext.GetContext(spWeb.Site);
                   Scopes scopes = new Scopes(searchContext);

                   Scope skillSearchScope =
scopes.GetSharedScope("MyCustomScope");
                   ScopeDisplayGroup displayGroup =
scopes.GetDisplayGroup(new Uri(spWeb.Url), "Search Dropdown");
                   displayGroup.Remove(skillSearchScope);
                   displayGroup.Update();

                   spWeb.Update();
                   spWeb.AllowUnsafeUpdates = false;
               }
           }
       }
       public override void
FeatureInstalled(SPFeatureReceiverProperties properties)
       { }
       public override void
FeatureUninstalling(SPFeatureReceiverProperties properties)
       { }