Friday, December 23, 2016

Get current server date time via javascript object model in Sharepoint

Some time ago I wrote post Get Sharepoint current datetime in javascript for Sharepoint Online, where described the way how to get current server datetime in javascript and how it works (or to be more precise almost current date time – see below for explanation). Briefly we use _spPageContextInfo.clientServerTimeDelta standard variable which is returned from server in the following form:

   1: clientServerTimeDelta: new Date("2015-05-21T16:54:00.0000000Z") - new Date()

In this line of code string is populated from DateTime.Now on server side:

   1: sb.Append("\", clientServerTimeDelta: new Date(\"");
   2: sb.Append(DateTime.UtcNow.ToString("o", CultureInfo.InvariantCulture));
   3: sb.Append("\") - new Date()");

So in order to get current server datetime in javascript we need to add current javascript date to _spPageContextInfo.clientServerTimeDelta. In original post I wrote that it can be done like that:

   1: var d = new Date();
   2: var currentServerDateTime = _spPageContextInfo.clientServerTimeDelta + d;

which is not fully correct. In order to have correct datetime object in currentServerDateTime use the following code:

   1: var currentServerDateTime = new Date(new Date().getTime() + _spPageContextInfo.clientServerTimeDelta);

And remember that it will be almost current server date time – difference will be in the time between moments when javascript interprets line with initialization of _spPageContextInfo.clientServerTimeDelta variable (see above) and line where you calculate currentServerDateTime.

Tuesday, December 20, 2016

Inherit global and current navigation settings on the Sharepoint publishing sites from parent web via client object model

Sometimes on Sharepoint publishing site we need to inherit global or current navigation settings from parent site:

In order to do that via client object model the following PowerShell script can be utilized:

   1:  
   2: param(
   3:     [string]$siteUrl,
   4:     [string]$username,
   5:     [string]$password
   6: )
   7:  
   8: $currentDir = Convert-Path(Get-Location)
   9: $dllsDir = resolve-path($currentDir + "\dlls_16_csom_16.1.3912.1204")
  10:  
  11: [System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllsDir,
  12: "Microsoft.SharePoint.Client.Runtime.dll"))
  13: [System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllsDir,
  14: "Microsoft.SharePoint.Client.dll"))
  15: [System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllsDir,
  16: "Microsoft.SharePoint.Client.Publishing.dll"))
  17:  
  18: if (-not $siteUrl)
  19: {
  20:     Write-Host "Specify site url in siteUrl parameter" -foregroundcolor red
  21:     return
  22: }
  23:  
  24: if (-not $username)
  25: {
  26:     Write-Host "Specify user name in username parameter" -foregroundcolor red
  27:     return
  28: }
  29:  
  30: if (-not $password)
  31: {
  32:     Write-Host "Specify user password in password parameter" -foregroundcolor red
  33:     return
  34: }
  35:  
  36: function Inherit-Navigation-For-Web($ctx, $web, $taxSession)
  37: {
  38:     Write-Host "Inherit navigation for" $web.Url -foregroundcolor green
  39:     $ctx.Load($web)
  40:     $ctx.ExecuteQuery()
  41:     
  42:     if ($web.ID -eq $ctx.Site.RootWeb.ID)
  43:     {
  44:         Write-Host "    Skip root web" -foregroundcolor yellow
  45:     }
  46:     else
  47:     {
  48:         $navigationSettings = New-Object Microsoft.SharePoint.Client
  49: .Publishing.Navigation.WebNavigationSettings($ctx, $web)
  50:        
  51:         $navigationSettings.GlovalNavigation.Source = [Microsoft.SharePoint
  52: Publishing.Navigation.StandardNavigationSource]::InheritFromParentWeb 
  53:         $navigationSettings.CurrentNavigation.Source = [Microsoft.SharePoint
  54: .Client.Publishing.Navigation.StandardNavigationSource]::InheritFromParentWeb
  55:         $navigationSettings.Update($taxSession)
  56:         
  57:         Write-Host "    Navigation is updated" -foregroundcolor green
  58:     }
  59:     
  60:     $ctx.Load($web.Webs)
  61:     $ctx.ExecuteQuery()
  62:     $web.Webs | ForEach-Object { Inherit-Navigation-For-Web $ctx $_ $taxSession }
  63: }
  64:  
  65: $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
  66: $ctx.RequestTimeOut = 1000 * 60 * 10;
  67: $ctx.AuthenticationMode =
  68: [Microsoft.SharePoint.Client.ClientAuthenticationMode]::Default
  69: $securePassword = ConvertTo-SecureString $password -AsPlainText -Force
  70: $credentials = New-Object Microsoft.SharePoint.Client
  71: .SharePointOnlineCredentials($username, $securePassword)
  72: $ctx.Credentials = $credentials
  73: $ctx.Load($ctx.Web)
  74: $ctx.Load($ctx.Site)
  75: $ctx.Load($ctx.Site.RootWeb)
  76: $ctx.ExecuteQuery()
  77:  
  78: $taxSession =
  79: [Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($ctx)
  80:  
  81: Inherit-Navigation-For-Web $ctx $ctx.Web $taxSession
Script recursively goes through all sub sites in Sharepoint Online site collection and inherits their navigation settings from parent via Microsoft.SharePoint.Client.Publishing.Navigation.WebNavigationSettings class.

Friday, December 16, 2016

Sharepoint Online slowness when site collection contains many sub sites

Recently on several Sharepoint Online sites which belong to different tenants we encountered with similar problem with slowness. Slowness didn’t appear immediately, i.e. from beginning sites worked fast. But after some time response time grew to 6-14 seconds, which made sites almost unusable. First of all we checked SPRequestDuration response header in browser developer tools which shows server response time (see Diagnosing performance issues with SharePoint Online). It showed that slowness is not caused by client side custom components but instead comes from server side because SPRequestDuration had value from 6000 to 14000 milliseconds. All sub sites used OTB seattle.master master page without customizations. After deeper investigation we found that the problem is caused by structural navigation used for left navigation (for top navigation managed metadata navigation was used). I.e. with time number of sub sites in hierarchy grew up and it made structural left navigation work much slower.

We opened ticket in MS for that, but as workaround for this issue you may change left navigation from structural to managed metadata. After we did it on all sub sites (as in Sharepoint single term set with navigation terms can be used on single site, we set managed metadata left navigation on the root site and then inherited left navigation from the parent sites on all sub sites. This approach also has own problems, because in OTB master page only 2 levels of hierarchy are shown. Better approach will is to create own navigation term set for each sub site, but there you also need to think how to keep navigation terms synced with site structure), server response time was reduced to 1 second. In one of the future posts I will describe how you may keep in sync sites structure and managed metadata navigation terms hierarchy.

Wednesday, December 7, 2016

Calls to Microsoft Graph client library hangs in ASP.Net web forms web application

Recently I faced with strange problem: when tried to use Microsoft Graph client library in ASP.Net web forms web application (tried to run it in Azure web site) code hang and Request timeout exception was thrown after request processing time reached timeout value. E.g. if we will use example which I showed in one of the previous posts (see Work with Azure AD via Microsoft Graph API):

   1: class Program
   2: {
   3:     static void Main(string[] args)
   4:     {
   5:         enumGroups();
   6:         Console.ReadKey();
   7:     }
   8:  
   9:     static async void enumGroups()
  10:     {
  11:         var graph = new GraphServiceClient(new AzureAuthenticationProvider());
  12:         try
  13:         {
  14:             var groups = await graph.Groups.Request().GetAsync();
  15:             foreach (var g in groups)
  16:             {
  17:                 Console.WriteLine(g.DisplayName);
  18:             }
  19:         }
  20:          catch (ServiceException x)
  21:         {
  22:             Console.WriteLine("Exception occured: {0}", x.Error);
  23:         }
  24:     }
  25: }

thread hang on line 16 when we perform actual call to graph API for retrieving groups:

   1: var groups = await graph.Groups.Request().GetAsync();

However the same code worked well in console application even if we would remove async/await from enumGroups method and would call client library synchronously. In ASP.Net web forms it is little bit more tricky. In order to make it work you need to make all methods up in the call stack asynchronous including page itself (see Using Asynchronous Methods in ASP.NET 4.5). I.e. in .aspx file add Async=”true” attribute to the Page directive:

   1: <%@ Page Async="true" ...

After that rewrite page method which calls Graph client library in async way:

   1: protected void Page_Load(object sender, EventArgs e)
   2: {
   3:     RegisterAsyncTask(new PageAsyncTask(Page_LoadAsync));
   4: }
   5:  
   6: private async Task Page_LoadAsync()
   7: {
   8:     await enumGroups();
   9: }

(if you have other methods in call stack between Page_LoadAsync and enumGroups you need to make them async as well). After that call to Microsoft Graph client library shouldn’t stuck anymore and should work as expected. There is also similar issue with ASP.Net MVC web applications: Application hang when executing AuthenticationContext.AcquireTokenAsync. Solution is also similar: you have to change MVC actions from sync to async. Exactly this article pointed me to the right direction for ASP.Net web forms, so big thanks to the author.

Saturday, December 3, 2016

Problem with missing users in group membership page in Sharepoint

Some time ago we faced with strange problem: on one of Sharepoint 2013 sites some users were not shown in their groups’ membership page (Site settings > Users and groups: http://example.com/_layouts/15/people.aspx?MembershipGroupId=groupId). Such users were able to login to Sharepoint and had all necessary permissions, but they didn’t appear on this page. Also need to notice that this site was migrated from SP2007. When I checked groups of the missing user via PowerShell:

   1: $u = Get-SPUser -Id "loginName" -Web http://example.com
   2: $u.Groups

they were properly returned. Also it worked as expected when I checked it in opposite way: check members of the group:

   1: $web = Get-SPWeb http://example.com
   2: $g = $web.SiteGroups["Foo"]
   3: $g.Users

User was shown there as well. And when I checked permissions via the following useful script which shows both permissions granted explicitly to the user and permissions granted via groups:

   1: $url = "http://example.com"
   2: Get-SPUser -Web $url -Limit All | sort-object UserLogin | select UserLogin,
   3:     @{name=”Exlicit given roles”;expression={$_.Roles}},
   4:     @{name=”Roles given via groups”;expression={$_.Groups | %{$_.Roles}}},
   5:     Groups | format-Table -auto

It showed that user has necessary permissions. I.e. the problem was only that user was not shown on the group membership page. In order to fix the issue the following script was used:

   1: $web = Get-SPWeb http://example.com
   2: $g = $web.Groups["Foo"]
   3: $users = $group.Users
   4: foreach ($u in $users)
   5: {
   6:     Write-Host "Fix user" $u.LoginName
   7:     $g.RemoveUser($u)
   8:     $g.AddUser($u)
   9:     $g.Update()
  10: }

It goes through all members of specified groups and then re-add them to this group. After that user appeared on group membership page.

Monday, November 7, 2016

One reason of “The underlying provider failed on Open” Entity Framework exception in Sharepoint

When you use Entity Framework data model in Sharepoint (e.g. in custom web part with server object model) you may face with the following problem: when try to query database the following exception is thrown:

The underlying provider failed on Open

If you will search for solution for this problem most of suggestions in forums will say that you need to grant access for account of IIS application pool to the database. In Sharepoint however it is only half of solution. The problem is that in Sharepoint impersonation is used by default, which mean that if you just grant access to app pool account it still may not work, because actual code will run under account of currently authenticated user (check Thread.CurrentPrincipal property in order to know exact account). As in most cases we don’t want to explicitly grant database access for all users of our web application the simplest solution will be grant access to account of application pool as it is suggested and then switch current context user to account of application pool. The simplest way to do it in Sharepoint context is to use SPSecurity.RunWithElevatedPrivileges:

   1: SPSecurity.RunWithElevatedPrivileges(
   2:     () =>
   3:     {
   4:         // query database via EF
   5:     }
   6: );

In this example we don’t need to reopen SPSite under SPSecurity.RunWithElevatedPrivileges as context user switch will happen anyway. Also note that SPSecurity.RunWithElevatedPrivileges has effect only when code runs in context of Sharepoint web app, but not e.g. in console applications – in last case connection will be done with account of the user under which current console application is running.

After that mentioned error should disappear.

Tuesday, November 1, 2016

How to export OTB List view web part and use it in different sub site in Sharepoint

As you probably know in Sharepoint it is possible to add standard List view web part on the page which is located on the same site with referenced list or doclib. But what if we need to use List view web part for displaying list from other sub site (not the one where web part is added)? By default it is not possible but after several additional steps it become possible. Here are these steps:

  1. Create new test page on the site where referenced doclib is located
  2. Add list view web part for appropriate list and publish the page
  3. Open page in Sharepoint designer
  4. Detach page from page layout
  5. Edit the page and change <ExportControlledProperties> property from False to True
  6. Save the page in Designer
  7. Go back to browser and edit the page. Now Export option will be available for List view web part
  8. Export web part to local file system
  9. In browser open other sub site where you need to add exported web part
  10. Open developer tools panel and in Console tab execute the following javascript code for getting id of the current web:
    SP.ClientContext.get_current().load(SP.ClientContext.get_current().get_web())
    SP.ClientContext.get_current().executeQueryAsync()
    SP.ClientContext.get_current().get_web().get_id()
  11. Copy web id from output
  12. Open exported .webpart file and find <WebId> property. After export it will contain empty guid: 00000000-0000-0000-0000-000000000000
  13. Replace it with guid copied from developer console and save .webpart file
  14. Now again in browser go to sub site where you need to add exported web part and edit the page where web part should be added
  15. Click Insert web part in some web part zone and click Upload web part. Choose modified .webpart file
  16. After upload again click Insert web part on web part zone and choose web part from Uploaded web parts category

In result you will have OTB List view web part working with list from other sub site.

Friday, October 21, 2016

Build connection string for Entity Framework programmatically via code or use Entity Framework in Sharepoint Timer jobs

Before to start I would like to mention that in this post Sharepoint timer jobs are used as example of case when app.config is not available (or it is better to not change it for adding EF configuration). But the same idea can be used also in other places where you don’t want to store EF connection string in configuration file or when it should be built dynamically.

Timer jobs is convenient mechanism when we need to do some actions in Sharepoint by scheduler. They are executed in separate process – Sharepoint Timer Service (it can be found with other services in Control panel > Administrative tools > Services). One of the common problem related with timer jobs is management of connection strings and other configuration data. Of course we can store it in app.config of owstimer.exe which is located here "C:\Program Files\Common Files\Microsoft shared\Web Server Extensions\15\bin". The problem however is that in most cases the same connection strings are also needed in basic web application and already specified in web.config of Sharepoint site. I.e. if we will add them to owstimer.exe.config they will be duplicated which will cause maintenance issues. In this article I will show how to avoid duplication when we need to use Entity Framework inside custom Sharepoint timer job. Described technique allows to avoid changing of owstimer.exe.config.

So suppose that you created simple console application, generated model from existing database (i.e. used Database First approach in terms of Entity Framework), debugged application and now want to move the code to Sharepoint custom job. After you will try to run the same code in Sharepoint job the following exception will be thrown:

Code generated using the T4 templates for Database First and Model First development may not work correctly if used in Code First mode. To continue using Database First or Model First ensure that the Entity Framework connection string is specified in the config file of executing application. To use these classes, that were generated from Database First or Model First, with Code First add any additional configuration using attributes or the DbModelBuilder API and then remove the code that throws this exception.

Message is a bit unclear, but the following article describes in more details reasons of this exception: Don’t use Code First by mistake. In short it happens because when we generated model from existing database, mappings between tables and classes were generated automatically and stored in generated .edmx file. EF DbContext must load it before first use which is done via special Entity Framework connection string. This string was added to app.config of your console application when you generated the model and that’s why it worked there. This EF connection string looks like this:

res://*/MyNamespace.DataModel.csdl|res://*/MyNamespace.DataModel.ssdl|res://*/MyNamespace.DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=MyDb;Integrated Security=True;multipleactiveresultsets=True; App=EntityFramework&quot;

As you can see it differs from regular Sql connection string (last one is included into EF connection string as “provider connection string” part). So we need to provide this special connection string to the EF DbContext. Here is how we can do it:

   1: public partial class MyDataContext
   2: {
   3:     public MyDataContext(string dbConnectionString)
   4:         : base(getEFConnectionString(dbConnectionString))
   5:     {
   6:     }
   7:  
   8:     private static string getEFConnectionString(
   9: string dbConnectionString)
  10:     {
  11:         var builder = new EntityConnectionStringBuilder
  12:             {
  13:                 Metadata = "res://*/MyNamespace.DataModel.csdl|" +
  14:                     "res://*/MyNamespace.DataModel.ssdl|" +
  15:                     "res://*/MyNamespace.DataModel.msl",
  16:                 Provider = "System.Data.SqlClient",
  17:                 ProviderConnectionString = dbConnectionString
  18:             };
  19:         return builder.ToString();
  20:     }
  21: }

I.e. we create partial class for our DbContext (it should be located in the same namespace) and create new constructor with one parameter – Sql database connection string (note that we pass there exactly classic database connection string, not EF connection string). Then we build EF connection string by using EntityConnectionStringBuilder class and include Sql connection string to it.

Why we chose regular Sql database connection string as parameter and how we will get it in custom timer job? The answer is because in web.config of Sharepoint site there is exactly regular connection string:

   1: <connectionStrings>
   2:   <add name="MyConnectionString"
   3: connectionString="Data Source=.;Initial Catalog=MyDb;Integrated Security=True" />
   4: </connectionStrings>

I.e. if we would read connection string from web.config inside the job we would be able to build Entity Framework connection string dynamically and would avoid duplication of configuration data because connection string still would be stored in single place – in web.config. Also in this case no manual changes would be needed in owstimer.exe.config.

This can be achieved by using property bag of custom Sharepoint job: SPJobDefinition.Properties. The idea is that inside feature receiver which provisions the job we read connection string from web.config and store it in job’s property bag:

   1: public class MyEventReceiver : SPFeatureReceiver
   2: {
   3:     public override void FeatureActivated(
   4: SPFeatureReceiverProperties properties)
   5:     {
   6:         var parent = (SPWebApplication)properties.Feature.Parent;
   7:  
   8:         var config =
   9: WebConfigurationManager.OpenWebConfiguration("/", parent.Name);
  10:         if (config.ConnectionStrings
  11:             .ConnectionStrings["MyConnectionString"] == null)
  12:         {
  13:             return;
  14:         }
  15:         string connStr = config.ConnectionStrings
  16:             .ConnectionStrings["MyConnectionString"].ConnectionString;
  17:  
  18:         this.deleteExistingJob("My job", parent);
  19:         this.createJob(parent, connStr);
  20:     }
  21:  
  22:     public override void FeatureDeactivating(
  23: SPFeatureReceiverProperties properties)
  24:     {
  25:         var parent = (SPWebApplication)properties.Feature.Parent;
  26:         this.deleteExistingJob("My job", parent);
  27:     }
  28:  
  29:     private void createJob(SPWebApplication site, string connStr)
  30:     {
  31:         var job = new MyJob("My job", site);
  32:         var schedule = new SPDailySchedule();
  33:         schedule.BeginHour = 1;
  34:         job.Schedule = schedule;
  35:         job.Properties.Add("ConnectionString", connStr);
  36:         job.Update();
  37:     }
  38:  
  39:     private void deleteExistingJob(string jobName, SPWebApplication site)
  40:     {
  41:         foreach (var job in site.JobDefinitions)
  42:         {
  43:             if (job.Name == jobName)
  44:             {
  45:                 job.Delete();
  46:                 return ;
  47:             }
  48:         }
  49:     }
  50: }

In example above we assume that feature has WebApplication scope. Then inside the job we can get connection string from property bag:

   1: public class MyJob : SPJobDefinition
   2: {
   3:     public MyJob() : base() { }
   4:  
   5:     public MyJob(string jobName, SPService service)
   6:         : base(jobName, service, null, SPJobLockType.None)
   7:     {
   8:         this.Title = jobName;
   9:     }
  10:  
  11:     public MyJob(string jobName, SPWebApplication webapp)
  12:         : base(jobName, webapp, null, SPJobLockType.ContentDatabase)
  13:     {
  14:         this.Title = jobName;
  15:     }
  16:  
  17:     public override void Execute(Guid targetInstanceId)
  18:     {
  19:         string connStr = this.Properties["ConnectionString"] as string;
  20:         // connect to database
  21:     }
  22: }

After that we pass this connection string to EF DbContext constructor, it creates EF connection string and connects to database. Described approach allows to use Entity Framework without having EF connection string in app.config e.g. inside Sharepoint custom jobs. Hope it will help someone.