Saturday, November 12, 2016

SharePoint Workflow Manager - Useful Info

Here are some useful links and reminders when setting up or troubleshooting the SharePoint Workflow Manager 1.0.

Suppose the SharePoint environment is made of the following:
Domain Name: contoso.edu
SharePoint Server FQDN: sp.contoso.edu (admin site port = 13311)
SharePoint Server Computer Name: SP13
Workflow Manager FQDN: sqlwopi.contoso.edu

  • Check if Workflow Manager is connected:
    http://sp.contoso.edu:13311/_admin/WorkflowServiceStatus.aspx 
  • Check if Workflow Service is working (Do this from the Sharepoint Server):
    http://sqlwopi.contoso.edu:12291 (if http is used)
    https://sqlwopi.contoso.edu:12290 
  • To check the Workflow Manager Service status (Do this on the Workflow Manager Service Server):
    Get-WFFarmStatus
  • To connect SharePoint Farm to Workflow Manager Service (on SharePoint Server):
    Register-SPWorkflowService -SPSite "https://sp.contoso.edu" -WorkflowHostUri "http://sqlwopi.contoso.edu:12291" -AllowOAuthHttp -Force (when http is used)

Tuesday, July 26, 2016

Provider-Hosted Add-in CRUD example using CSOM

Below is a complete CRUD example of provider-hosted SharePoint Add-In using CSOM. In the controller, the CRUD is performed on a SharePoint list, "Products", which is made of [ID], [Title], [UnitPrice] and [UnitsOnStock] fields.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Microsoft.SharePoint.Client;
using ProviderHostedCloud1Web.Models;

namespace ProviderHostedCloud1Web.Controllers
{
    [SharePointContextFilter]
    public class ProductController : Controller
    {

        // GET: Product
        public ActionResult Index()
        {
            var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
            using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
            {
                if (cc != null)
                {
                    List productList = cc.Web.Lists.GetByTitle("Products");
                    cc.Load(productList);
                    cc.ExecuteQuery();

                    if (productList != null)
                    {
                        CamlQuery query = CamlQuery.CreateAllItemsQuery();
                        ListItemCollection products = productList.GetItems(query);
                        cc.Load(products);
                        cc.ExecuteQuery();

                        List retVal = new List();
                        foreach (var product in products)
                        {
                            retVal.Add(new Product
                            {
                                ID = product.Id,
                                Title = product["Title"].ToString(),
                                UnitPrice = Convert.ToDecimal(product["UnitPrice"]),
                                UnitsOnStock = Convert.ToInt32(product["UnitsOnStock"])
                            });
                        }
                        return View(retVal);
                    }

                    return View();
                }
                else
                {
                    ViewBag.ErrorMessage = "Error: ClientContext is null.";
                    return View();
                }
            }

        }

        // GET: Product/Details/5
        public ActionResult Details(int id)
        {
            try
            {
                var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    if (cc != null)
                    {
                        ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                        cc.Load(item);
                        cc.ExecuteQuery();

                        if (item != null)
                        {
                            Product retVal = new Product
                            {
                                ID = item.Id,
                                Title = item["Title"].ToString(),
                                UnitPrice = item["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(item["UnitPrice"]),
                                UnitsOnStock = item["UnitsOnStock"] == null ? 0 : Convert.ToInt32(item["UnitsOnStock"])
                            };

                            return View(retVal);
                        }
                        else
                        {
                            return View();
                        }
                    }
                    else
                    {
                        ViewBag.ErrorMessage = "Error: ClientContext was null. ";
                        return View();
                    }
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.ErrorMessage = "Error: " + ie.Message;
                return View();
            }
        }

        // GET: Product/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: Product/Create
        [HttpPost]
        public ActionResult Create(System.Web.Mvc.FormCollection collection)
        {
            string title = collection["Title"].ToString();
            decimal unitPrice = collection["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(collection["UnitPrice"]);
            int unitsOnStock = collection["UnitsOnStock"] == null ? 0 : Convert.ToInt32(collection["UnitsOnStock"]);

            try
            {
                var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    List productList = cc.Web.Lists.GetByTitle("Products");
                    ListItemCreationInformation creationInfo = new ListItemCreationInformation();
                    ListItem item = productList.AddItem(creationInfo);
                    item["Title"] = title;
                    item["UnitPrice"] = unitPrice;
                    item["UnitsOnStock"] = unitsOnStock;
                    item.Update();
                    cc.ExecuteQuery();

                    return RedirectToAction("Index", new { SPHostUrl = Request.QueryString["SPHostUrl"] });
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.ErrorMessage = "Error: " + ie.Message;
                Product product = new Product { Title = title, UnitPrice = unitPrice, UnitsOnStock = unitsOnStock };
                return View(product);
            }
        }

        // GET: Product/Edit/5
        public ActionResult Edit(int id)
        {
            var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
            using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
            {
                if (cc != null)
                {
                    ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                    cc.Load(item);
                    cc.ExecuteQuery();
                    if (item != null)
                    {
                        Product product = new Product
                        {
                            ID = item.Id,
                            Title = item["Title"].ToString(),
                            UnitPrice = item["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(item["UnitPrice"]),
                            UnitsOnStock = item["UnitsOnStock"] == null ? 0 : Convert.ToInt32(item["UnitsOnStock"])
                        };
                        return View(product);
                    }
                    else
                    {
                        return View();
                    }
                }
                else
                {
                    ViewBag.ErrorMessage = "Error: ClientContext was null";
                    return View();
                }

            }
        }

        // POST: Product/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, System.Web.Mvc.FormCollection collection)
        {
            try
            {
                string title = collection["Title"].ToString();
                decimal unitPrice = Convert.ToDecimal(collection["UnitPrice"]);
                int unitsOnStock = Convert.ToInt32(collection["UnitsOnStock"]);

                var spContext = SharePointAcsContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    if (cc != null)
                    {
                        ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                        item["Title"] = title;
                        item["UnitPrice"] = unitPrice;
                        item["UnitsOnStock"] = unitsOnStock;
                        item.Update();
                        cc.ExecuteQuery();
                        return RedirectToAction("Index", new { SPHostUrl = Request.QueryString["SPHostUrl"] });
                    }
                    else
                    {
                        ViewBag.ErrorMessage = "ClientContext was null.";
                        Product product = new Product
                        {
                            ID = id,
                            Title = title,
                            UnitPrice = unitPrice,
                            UnitsOnStock = unitsOnStock
                        };
                        return View(product);
                    }
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.Error = "Error: " + ie.Message;
                return View();
            }
        }

        // GET: Product/Delete/5
        public ActionResult Delete(int id)
        {
            var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
            using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
            {
                if (cc != null)
                {
                    ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                    cc.Load(item);
                    cc.ExecuteQuery();

                    if (item != null)
                    {
                        Product product = new Product
                        {
                            ID = item.Id,
                            Title = item["Title"].ToString(),
                            UnitPrice = item["UnitPrice"] == null ? 0.00m : Convert.ToDecimal(item["UnitPrice"]),
                            UnitsOnStock = item["UnitsOnStock"] == null ? 0 : Convert.ToInt32(item["UnitsOnStock"])
                        };
                        return View(product);
                    }
                    else
                    {
                        return View();
                    }
                }
                else
                {
                    ViewBag.Error = "Error: ClientContext was null";
                    return View();
                }
            }

        }

        // POST: Product/Delete/5
        [HttpPost]
        public ActionResult Delete(int id, System.Web.Mvc.FormCollection collection)
        {
            string title = collection["Title"].ToString();
            decimal unitPrice = Convert.ToDecimal(collection["UnitPrice"]);
            int unitsOnStock = Convert.ToInt32(collection["UnitsOnStock"]);
            try
            {
                var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
                using (ClientContext cc = spContext.CreateUserClientContextForSPHost())
                {
                    if (cc != null)
                    {
                        ListItem item = cc.Web.Lists.GetByTitle("Products").GetItemById(id);
                        item.DeleteObject();
                        cc.ExecuteQuery();
                        return RedirectToAction("Index", new { SPHostUrl = Request.QueryString["SPHostUrl"] });
                    }
                    else
                    {
                        Product product = new Product
                        {
                            ID = id,
                            Title = title,
                            UnitPrice = unitPrice,
                            UnitsOnStock = unitsOnStock
                        };
                        ViewBag.Error = "Error: ClientContext was null";
                        return View();
                    }
                }
            }
            catch (Exception ex)
            {
                Exception ie = ex;
                while (ie.InnerException != null)
                {
                    ie = ie.InnerException;
                }
                ViewBag.ErrorMessage = "Error: " + ie.Message;
                return View();
            }
        }
    }
}

Monday, July 25, 2016

Bootstrap Popover() - useful example

Bootstrap popover() example below has a few useful features:
  1. Popover initiates via mouse-hover over <span class="paymentNum">
  2. trigger: 'manual' is used with onmouseenter and onmouseleave event to mimic trigger by hover
  3. User can move the mouse into the popover. Popover dissapears when mouse is completely out of the popover-initiating span tag or the shown popover
  4. Once popover is shown, it stays visible even when mouse moves between the popover-initiating span tag and the popover itself (This would not work if trigger: 'hover' was used. It was possible by using trigger: 'manual' and onmouseenter and onmouseover events).
  5. Popover title is dynamically loaded.
  6. Popover content is dynamically loaded via ajax. Loading... is shown if there is a delay. When ajax get is done, its response dynamically replaces popover content.
<span class="paymentNum">@payment.PaymentNum</span>
<input type="hidden" class="payment-id" value="@payment.PaymentID" />
                       
.....

<script type="text/javascript">
        $(function () {

            $('span.paymentNum').popover({
                trigger: 'manual',
                placement: 'right',                
                title: function(){
                    return "Payment " + $(this).text();
                },
                html: true,
                animation: false,
                container: 'body',  
                template: '<div class="popover" role="tooltip" style="min-width: 400px; background-color: #fff; ">' +
                            '<div class="arrow"></div>' +
                            '<h3 class="popover-title" style="background-color: #dedede"></h3>' +
                            '<div class="popover-content" style="height: 400px; overflow-y: auto; font-size: 0.9em;"></div>' +
                            '</div>',
                content: function () {
                    var div_id = "temp_" + $.now();
                    var payment_id = $(this).parent().find('.payment-id').val();
                    
                    $.ajax({
                        type: 'GET',
                        url: GL.site_root_path + '/Payment/_QuickSummary/' + payment_id,
                        cache: false,
                        beforeSend: function () { },
                        complete: function () { }
                    }).done(function (d) {
                        $('#' + div_id).html(d); // Genius!!!
                    });
                    return '<div id="' + div_id + '"><div style="margin-top: 180px; text-align: center; font-size: 1.2em;">Loading ...<i class="fa fa-refresh fa-spin"></i></div></div>';                    
                }
            }).on('mouseenter', function () {
                var _this = this;
                $(this).popover("show");
                $('.popover').on('mouseleave', function () {
                    $(_this).popover("hide");
                });
            }).on('mouseleave', function () {
                var _this = this;
                setTimeout(function () {
                    if (!$('.popover:hover').length) {
                        $(_this).popover("hide");
                    }
                }, 50);
            });
        });

    </script>

Wednesday, June 29, 2016

SharePoint - unlock a file that is exclusively locked by another user

It appears that both SharePoint 2010 and 2013 still have the bug to potentially lock a file even away from the very person who last checked out the file.

This buggy behavior is described in this Microsoft article.

Basically, when a document in SharePoint is opened by a client program like Word, Excel, SharePoint places a lock on the document on the server. The write lock times out after 10 minutes. Users cannot modify the document during the time when the document is locked.

In a scenario where the program (Word, Excel, etc) that opens the document unexpectedly quits or crashes and you try to open the document again before the write lock times out (10 minutes), you may receive the message that says the document is (exclusively) locked by another user. Even if you are the last person who opened the document, you may receive this message, too.

Now the problem may continue even if you wait for 10 minutes. When the document is coninued to be in locked status, we can use Powershell script to remove the lock as described here.

## To find out about locked info of the file
$web = Get-SPWeb "https://sharepoint.mycompany.com/sites/mysite"
$list = $web.Lists["Shared Documents"]
$item = $list.GetItemById(3)
$file = $item.File
$file

The LockType property from script above may show "None", yet the file may still be locked away from the last person who opened it. Now use Powershell to clear the lock.
$fileTime = New-Object System.TimeSpan(10000)
## Create a new file lock on the file
$file.Lock([Microsoft.SharePoint.SPFile+SPLockType]::Exclusive, "Test Lock", $fileTime)
## Remove the lock from the file
$file.UndoCheckOut()

If the script is actually locked by another user and the lock is not being released for any reason, you can use the following script to remove the lock via impersontion, too.
$web = Get-SPWeb "https://sharepoint.mycompany.com/sites/mysite"
$list = $web.Lists["Shared Documents"]
$item = $list.GetItemById(3)
$file = $item.File
$userID = $file.LockedByUser.ID
$user = $web.AllUsers.GetByID($userID)

$impersonatedSite = New-Object Microsoft.SharePoint.SPSite($web.Url, $user.UserToken)
$impersonatedWeb = $impersonatedSite.OpenWeb();
$impersonatedList = $impersonatedWeb.Lists[$list.Title]
$impersonatedItem = $impersonatedList.GetItemById($item.ID)
$impersonatedFile = $impersonatedItem.File
$impersonatedFile.ReleaseLock($impersonatedFile.LockId)

Tuesday, June 14, 2016

Change Title (linked to item with edit menu) to a different column in SharePoint List

By default, the automatically created Title column of custom SharePoint List functions as the link to item detail view and carries the ellipse button (item edit menu).

In order to hide Title column and associate the "link to item with edit menu" to another column:

  1. Go to List Settings --> Advanced Settings --> "Yes" on Allow management of content types. On the List Settings, go to Content Types --> Item --> Hide "Title" column. Afterwards, "Title" column will not appear in Create, Edit and Details forms.
  2. Go to the View(s) correcponding to List View and hide "Title" column.
  3. Use SharePoint 2013 Designer, open AllItems.aspx page or corresponding list page, look for <ViewFields> tag. Add linkToItem="TRUE" linkToItemAllowed="TRUE" listItemMenu="TRUE" to the column that should function as link to details view and carry the ellipse button (item edit menu). Highlighted column below is an example. Beware that these attributes and their values are case-sensitive!
<View Name="{EDF62A70-F0D9-4B36-B3C4-F35017C57868}" 
    DefaultView="TRUE" 
    MobileView="TRUE" 
    MobileDefaultView="TRUE" 
    Type="HTML" 
    DisplayName="All Items" 
    Url="/developer/Ken/Demo1/Lists/Classroom/AllItems.aspx" Level="1" 
    BaseViewID="1" 
    ContentTypeID="0x" 
    ImageUrl="/_layouts/15/images/generic.png?rev=23" >
    <Query><OrderBy><FieldRef Name="ID"/></OrderBy></Query>
    <ViewFields>
        <FieldRef Name="Title"  linkToItem="TRUE" linkToItemAllowed="TRUE" listItemMenu="TRUE" />
        <FieldRef Name="Subject"/>
        <FieldRef Name="Teacher"/>
    </ViewFields>
    <RowLimit Paged="TRUE">30</RowLimit>
    <JSLink>clienttemplates.js</JSLink>
    <XslLink Default="TRUE">main.xsl</XslLink>
    <Toolbar Type="Standard"/>
</View>

Tuesday, June 7, 2016

Recursive Example of Common Table Expression - GetAcronym

Below is an example of SQL function that takes text with space as separator and returns acronym with first letter of each seperated word.
Select dbo.GetAcronym('Hello World  Whats Up', ' ') 
/* There are 2 spaces between World and Whats */

will return HW WU

The function definition is as follows. It uses Common Table Expression with Union All to run a recursive query.
CREATE function dbo.GetAcronym
(
  @str varchar(4000), @separator char(1) = ' '
)
returns varchar(4000)
as
begin

declare @retVal varchar(4000)
if @separator is null
  begin
  set @separator = ' '
  end

;with tokens(p, a, b)
as
(
  select  1, 1, charindex(@separator, @str)
  union all
  select p + 1, b + 1, charindex(@separator, @str, b + 1)
  from tokens /* Recursive Query via Common Table Expression is used along with Union All */
  where b > 0
)

select @retVal = convert(
  varchar(4000), 
  (select substring (@str, a, 1) from tokens for xml path (''), type ), 
  1
)
return @retVal

end


Recursive example of Common Table Expression - SplitString

Below is an example of Recursive query using SQL Server's Common Table Expression.
declare @s varchar(2000)
set @s = 'Hello World SQL DBAs'

select *
from dbo.SplitString(@s, ' ');
go
will return
indexvalue
0Hello
1World
2SQL
3DBAs


Function definition using recursive query via Common Table Expression and Union All:
CREATE function [dbo].[SplitString] 
(
  @str nvarchar(4000), 
  @separator char(1)
)
returns table
AS
return (
        
  with tokens(p, a, b) AS 
  (
    select  1, 1, charindex(@separator, @str)
           
    union all
            
    select p + 1, b + 1, charindex(@separator, @str, b + 1)
    from tokens /* Recursive use of common table expression */
    where b > 0
    )
        
    select 
      p-1 as [index]
      ,substring(@str, a, case when b > 0 then b-a ELSE 4000 end) AS [value]
    from tokens
)