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);

How to add folder to Sharepoint list programmatically

Here is the code.


using (SPSite siteCollection = new SPSite("http://siteURL/"))

            {

                using (SPWeb web = siteCollection.OpenWeb())

                {

                    SPList list = web.Lists["ListName"];

                    SPListItemCollection itemColl = list.Items;

                    SPListItem item = itemColl.Add("/Lists/ListName", SPFileSystemObjectType.Folder, null);

                    item["Title"] = "folderName";

                    item.Update();

                }

            }

Expiration Date not getting updated when policy updated in Sharepoint

If the expiration date is no getting updated when policy gets updated then you need to run the Information management policy job & Expiration policy job.


Policy and Retention Execution

Policy and Retention Execution Logic includes two timer jobs

1.    Information Management Policy : Loops through all the lists in the site collections in a Web application and collects policy and usage data.

2.    Expiration Policy : Enumerates list items and looks for those with an expiration date that has already occurred. For those items, runs disposition processing. Disposition processing most often results in deleting items, but it can perform other actions, such as processing disposition workflows.

By default, these timer jobs are set to run weekly. Any stages that are due are processed at the time of expiration of the Expiration Policy timer job. However, if the policy schedule was just updated and the Information Management Policy timer job was not run to update the items, then they will not be processed by the Expiration policy timer job. To prevent this from happening, SharePoint Server 2010 runs the Information Policy timer job before the Expiration policy timer job by default.

To update the Expiration date in the document library, you can run the “Information Management Policy” timer job. Once you have done that the expiration date should be updated.

To run the job you can do it through central administration or use the below sample code to do it.

UI

Go to Central administration -> monitoring -> Review Job definition. Check for “Information Management Policy” under the corresponding webapplication and click ‘run now’.

SAMPLE CODE

using (SPSite site = new SPSite("http://YourSitecollectionURL"))

            {

                List<string> jobNamesToRun = new List<string>()

                {

                          "Information management policy",

                           "Expiration policy"

                };

                var jobsToRun = site.WebApplication.JobDefinitions.Where(job => jobNamesToRun.Contains(job.DisplayName));

                foreach (var job in jobsToRun)

                {

                    Console.WriteLine("Running job " + job.DisplayName);

                    job.RunNow();

                }

            }

            Console.WriteLine("Please click Enter to exit..");

            Console.ReadLine();

 

Pagination using SPQuery

Let's see how we can achieve pagination in SPQuery. We need to use ListItemCollectionPosition property to achieve pagination along with some logic.

The below code shows how many pages has to be shown for pagination. i.e. If you have 100 items in a list, and rowlimt=50 then the total no: of pages should be 2.

        static void Main()

        {

            SPListItemCollection items = GetListItems(3, 2);//rowlimit,Pagenumber

            foreach (SPListItem item in items)

            {

                Console.WriteLine(item["Title"]);

            }

            Console.ReadLine();

        }

        static SPListItemCollection GetListItems(int rowlimit, int pageNo)

        {

            string SiteCollectionUrl = "http://SiteURL/";

            using (SPSite site = new SPSite(SiteCollectionUrl))

            {

                using (SPWeb oWebsiteRoot = site.OpenWeb())

                {

                    SPList oList = oWebsiteRoot.Lists["list_name"];

                    int TotalListItems = oList.ItemCount;

                    int iPageCount = (int)Math.Ceiling(TotalListItems / (decimal)rowlimit);

                    Console.WriteLine("Total No of Pages: " + iPageCount); //No of pages to be shown

                    Console.WriteLine("Printing items from pageNo:" + pageNo.ToString());

                    SPQuery query = new SPQuery();

                    query.RowLimit = (uint)rowlimit;

                    query.Query = "<OrderBy Override=\"TRUE\"><FieldRef Name=\"FileLeafRef\" /></OrderBy>";

                    int index = 1;

                    SPListItemCollection items;

                    do

                    {

                        items = oList.GetItems(query);

                        if (index == pageNo)

                            break;

                        query.ListItemCollectionPosition = items.ListItemCollectionPosition;

                        index++;

                    }

                    while (query.ListItemCollectionPosition != null);

                    return items;

                }

            }

        }

How to add a new link to welcome drop down in Sharepoint

 In order to add a new link to the welcome dropdown, we should use custom actions.

Here is the feature.xml & element.xml


Feature.xml

<Feature
  Id="AA929AFF-4602-4d7f-A501-B80AC9A4BB52"
  Title="Add Links to user section"
  Description="Feature that adds a link to Welcome User section"
 Scope="WebApplication"
  xmlns="http://schemas.microsoft.com/sharepoint/">
  <ElementManifests>
    <ElementManifest Location="elements.xml" />
  </ElementManifests>
</Feature>


Elements.xml

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <CustomAction
      Id="0acdbd94-eba8-11db-8314-0800200c9a66"
      GroupId="PersonalActions"
      Location="Microsoft.SharePoint.StandardMenu"
      Sequence="100"
     
      Title="My Custom link Title"
      Description="My Custom link Description"
       >
    <UrlAction Url="_layouts/MycustomPage.aspx"/>
  </CustomAction>
 
</Elements>

Purge Workflow history list in Sharepoint

If your workflow history have large amount of data (say in millions), then purging it would be a challenge. If you try to delete the list in UI, you might get "Time out error". If you try to delete the list through Sharepoint designer or through stsadm cmdlet , it might run for hours. 
The best approach to purge the data is deleting them chunk by chunk.
You have to point the running workflow to a new history list, then we can delete the old the history list completely.
In the below sample I'm using processbatchdata for faster deletion. The below code will delete 2000 items in 3 iteration. You can increase the iteration at your will. Also you need to modify the startID accordingly after each execution.

  

private static int iteration = 3;

        private static int Count = 2000;

        static void Main(string[] args)

        {

            if (args.Length != 1)

            {

                Console.WriteLine("first argument: No of iteration");

                Console.WriteLine("YourprgramName.exe 5");

                Console.WriteLine("******Program terminated : Argument missing! Please give one argument ****");

             

            }

            else

            {

                try

                {

                    iteration = Convert.ToInt32(args[0]);

                }

                catch (Exception ex)

                {

                    Console.WriteLine("Error : Failed to convert arguments: {0}", ex.Message);

                    

                    return; // exit

                }

                Console.WriteLine("Current System Time (Start) : " + DateTime.Now.ToString());

                try

                {

                    for (int iterate = 0; iterate < iteration; iterate++)

                    {

                        int start =1;

                        Console.WriteLine("Start Index: "+ start.ToString());

                        using (SPSite site = new SPSite("http://siteurl"))

                        {

                            using (SPWeb web = site.OpenWeb())

                            {

                                SPList wflist = web.Lists["Workflow History"]; //workflow history

                                string wflistID = wflist.ID.ToString();

                                Console.WriteLine("No of items before deletion: " + wflist.ItemCount);

                                Console.WriteLine("Building query... ");

                                StringBuilder batchString = new StringBuilder();

                               batchString.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch OnError=\"Continue\">");

                                int end = start + Count - 1;

                                for (int i = start; i <= end; i++)

                                {

                                    batchString.Append("<Method>");

                                    batchString.Append("<SetList Scope=\"Request\">" + wflistID + "</SetList>");

                                    batchString.Append("<SetVar Name=\"ID\">" + Convert.ToString(i) + "</SetVar>");

                                    batchString.Append("<SetVar Name=\"Cmd\">Delete</SetVar>");

                                    batchString.Append("</Method>");

                                }

                                batchString.Append("</Batch>");

                                //Console.WriteLine(batchString.ToString());

                                try

                                {

                                    web.AllowUnsafeUpdates = true;

                                    Console.WriteLine("Executing query... ");

                                    Console.WriteLine("Batch Execution (Start) : " + DateTime.Now.ToString());

                                    string result = web.ProcessBatchData(batchString.ToString());

                                    //Console.WriteLine(result);

                                    web.AllowUnsafeUpdates = false;

                                    Console.WriteLine("Batch Execution (END) : " + DateTime.Now.ToString());

                                }

                                catch (Exception ex)

                                {

                                    Console.WriteLine("Process batch error : " + ex.Message);

                                }

                                Console.WriteLine("No of items before deletion: " + wflist.ItemCount);

                                using (SPSite site1 = new SPSite("http://siteurl/"))

                                {

                                    using (SPWeb web1 = site1.OpenWeb())

                                    {

                                        Console.WriteLine("No of item after deletion: " + web1.Lists["Workflow History"].ItemCount);

                                    }

                                }

                                Console.WriteLine("--------------------------------------------------");

                            }

                        }

                    }

                    Console.WriteLine("Current System Time (End) : " + DateTime.Now.ToString());

                    Console.WriteLine("-----------Program Completed------------");

                }

                catch (Exception ex)

                {

                    Console.WriteLine("Error : " + ex.Message);

                    Console.WriteLine("******Program terminated due to error **** ");

                }

            }

        }

       

          

Workflow Auto clean job property in Sharepoint

You can check Workflow Auto clean job property by using the following stsadm command 

stsadm –o setproperty –pn job-workflow-autoclean –pv “daily at 6:34:00” –url http://siteurl

Delete workflow history items that has no associations in Sharepoint

Whenever the Out of the box timer job "workflow auto clean up" jobs runs, it will break the associations between the list items and workflow history items for those records that are older than 60 days (default is 60 days. this default value can be chagned). This job will not clear the history data, it just removes the association. Now it is up to us either to keep the history details or remove them. 

For those who want to remove those records can use the below sample code. We have used processbatch data method to delete items which will have better performance.

string WorkflowAssocID="";

            try

            {

                using (SPSite site = new SPSite("http://SiteURL/"))

                {

                    using (SPWeb web = site.OpenWeb())

                    {

                        SPList list = web.Lists["List_Name"];//Name of the list where Workflow is associated

                        SPQuery qry = new SPQuery();

                        qry.ViewAttributes = "Scope=\"Recursive\"";

                        SPListItemCollection lstCol = list.GetItems(qry);

                        SPWorkflowAssociationCollection WFCol = list.WorkflowAssociations;

                        foreach (SPWorkflowAssociation wa in list.WorkflowAssociations)

                        {

                            if (wa.Name == "Workflow Name")//name of the Workflow

                                WorkflowAssocID = wa.ParentAssociationId.ToString();

                        }

                       

                        List<string> ootbLib = new List<string>();

                        foreach (SPListItem item in lstCol)

                        {

                            if (item.Workflows.Count == 0)

                            {

                                ootbLib.Add(item.ID.ToString());

                            }

                        }

                        SPList wflist = web.Lists["Workflow History"];//History List Name

                        string wflistID = wflist.ID.ToString();

                        SPQuery query = new SPQuery();

                        query.ViewAttributes = "Scope=\"Recursive\"";

                        SPListItemCollection wflstCol = wflist.GetItems(query);

                       

                        StringBuilder batchString = new StringBuilder();

                        batchString.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch OnError=\"Continue\">");

                        for (int i = 0; i < wflstCol.Count; i++)

                        {

                            if (ootbLib.Exists(delegate(string p) { return p.Trim() == wflstCol[i]["Primary Item ID"].ToString(); }))

                            {

                                if (wflstCol[i]["Workflow Association ID"].ToString() == "{" + WorkflowAssocID + "}")

                                {

                                   

                                    batchString.Append("<Method>");

                                    batchString.Append("<SetList Scope=\"Request\">" + wflistID + "</SetList>");

                                    batchString.Append("<SetVar Name=\"ID\">" + Convert.ToString(wflstCol[i]["ID"]) + "</SetVar>");

                                    batchString.Append("<SetVar Name=\"Cmd\">Delete</SetVar>");

                                    batchString.Append("</Method>");

                                }

                            }

                        }

                        batchString.Append("</Batch>");

                        web.AllowUnsafeUpdates = true;

                        string result = web.ProcessBatchData(batchString.ToString());

                        web.AllowUnsafeUpdates = false;

                    }

                }

               

            }

            catch (Exception ex)

            {

                //Some logging can be done

            }