Second Life of a Hungarian SharePoint Geek

November 29, 2017

How to detect if your DbConext is already disposed

Filed under: Tips & Tricks, Entity Framework — Tags: , — Peter Holpar @ 23:34

A little background (or let’s say context) to the story. Recently I had to create a tool (of course it was SharePoint-related) that needs to import a quite large number of entries into a SQL database for further analysis. I chose Entity Framework (DB-first) as a wrapper to our database entities. Having the knowledge that I already have now, I should admit, it was not the perfect choice, but rather a great opportunity to learn from our failures. In the development environment, where we have a smaller amount of data, the tool was quite quick, so I anticipated, it would be quick enough in the test and productive environment either. Just to have an idea about the order of magnitude, in these environments we have around 0,5 Mio. entries to export. It is a large number of entries, but I don’t think it is extreme large. After the first run in the test environment I knew, I was completely wrong with my assumption.

After letting the tool run on the weekend, and even a few day after, we stopped it, because it simply have not finished, and we haven’t seen the end of the tunnel. I analyzed the log data and found, that at the beginning the tool saved a batch of 100 entries in approximately 50 milliseconds. But later it was slower and slower, and after having saved already about 15.000 entries, the saving of the same batch of 100 entries took more than 7 seconds. What was the problem, and how have we solved it? You can read more about the issue here and here, and about the solution we applied here.

As part of the solution, we disposed and re-created our data context after saving every batch of items. However, this solution has introduced a new problem.

In the original version we have called the SaveChanges method at the end of our processing in an outer code block to ensure saving any remaining items that do not completed an entire batch. But in the new version we got this exception at this point:

InvalidOperationException: The operation cannot be completed because the DbContext has been disposed.
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.LazyInternalContext.get_ObjectContext()
at System.Data.Entity.Internal.InternalContext.DetectChanges(Boolean force)
at System.Data.Entity.Internal.InternalContext.GetStateEntries(Func`2 predicate)
at System.Data.Entity.Infrastructure.DbChangeTracker.Entries()
at System.Data.Entity.DbContext.GetValidationErrors()
at System.Data.Entity.Internal.InternalContext.SaveChanges()

Note: you have this error on a disposed DbContext object only if there were something to save. If no object has been changed, no exception is thrown on calling SaveChanges on a disposed DbContext object.

This exception was due to disposing of the data context we created in a using block. We passed the data context to a method, that disposed (and re-created it) as part of the pattern suggested for performance tuning for importing of large number of items via the Entity Framework. See the sample code with Exporter class and it static DoSomething method further down. The data context was disposed in the DoSomething method. In the original version we tried to invoke dbContext.SaveChanges after returning from the DoSomething method, although the data context was already disposed in the DoSomething method, and it resulted the exception above.

Although some disposable object types might provide a kind of check if the object has been already disposed (see the IsDisposed property of the System.Windows.Forms.Control class, for example), it is definitely not part of the IDisposable interface. If the object belongs to your solution, you can implement a similar property yourself. If you can’t change the source code of the class (it is a class in the .NET libraries or any 3rd party class), you can derive a subclass from it, and extend it by the functionality as shown in this answer. This method works of course only as long as the base class is not marked as sealed.

In the case of DbContext I found a private field of type System.Data.Entity.Internal.InternalContext called _internalContext. InternalContext is an abstract internal class, you can find references for it and for its derived class LazyInternalContext in the error stack trace above as well. The InternalContext class has a public property called IsDisposed. The LazyInternalContext class invokes in its InitializeContext method the CheckContextNotDisposed method of the base class, that throws the InvalidOperationException above if the context has been already disposed.

I created the extension method below using Reflection to read the value of the IsDisposed property of the _internalContext field.

  1. public static bool IsDisposed(this DbContext context)
  2. {
  3.     var result = true;
  4.  
  5.     var typeDbContext = typeof(DbContext);
  6.     var typeInternalContext = typeDbContext.Assembly.GetType("System.Data.Entity.Internal.InternalContext");
  7.  
  8.     var fi_InternalContext = typeDbContext.GetField("_internalContext", BindingFlags.NonPublic | BindingFlags.Instance);
  9.     var pi_IsDisposed = typeInternalContext.GetProperty("IsDisposed");
  10.  
  11.     var ic = fi_InternalContext.GetValue(context);
  12.  
  13.     if (ic != null)
  14.     {
  15.         result = (bool)pi_IsDisposed.GetValue(ic);
  16.     }
  17.  
  18.     return result;
  19. }

You can test the functionality in a code block like this one:

  1. using (dbContext = new YourDbContext())
  2. {
  3.     Console.WriteLine("Disposed: {0}", dbContext.IsDisposed());
  4.     Exporter.DoSomething(dbContext);
  5.     Console.WriteLine("Disposed: {0}", dbContext.IsDisposed());
  6. }
  7. Console.WriteLine("Disposed: {0}", dbContext.IsDisposed());

The Exporter class and its static DoSomething method in the code above are just some arbitrary functionality you would like to perform.

Having the IsDisposed method defined, you can check if the context is disposed before calling SaveChanges (or any other method that are dangerous to be invoked on a disposed context):

  1. if ((dbContext != null) && (!dbContext.IsDisposed()))
  2. {
  3.     dbContext.SaveChanges();
  4. }

Note: The code in this post was tested with Entity Framework 6.1.3. Other versions might behave in another way, so there is no guarantee that the same code works with those versions too.

Advertisements

November 23, 2017

Displaying Filtered or Sorted SharePoint Web Properties from PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 22:47

Specific SharePoint applications and services as well as tools, like SharePoint Designer tend to store their own settings in the property bag of web sites. Especially root webs of site collection have a lot of properties.

You can display the web properties from PowerShell like:

$web = Get-SPWeb http://YourSharePointSite
$web.AllProperties

The problem, that the above script displays really all of the properties, and the list is not alphabetically sorted, so it might be challenging  to find a specific property.

The properties and their values are stored as key-value pairs in a Hashtable object, you can filter these entries as described in this thread. For example, the script below displays the search related entries, the ones whose names begin with SRCH.

$web.AllProperties.GetEnumerator() | ? Key -like ‘SRCH*’

image

Although it is not so common, you can filter the entries based on its values as well. For example, this script display all properties having a value of True:

$web.AllProperties.GetEnumerator() | ? Value -eq ‘True’

If all you want is to display the properties sorted alphabetically by their names, it is easy to achieve either:

$web.AllProperties.GetEnumerator() | Sort-Object -Property Key

image

Of course, all of these possibilities apply not only to the property bags of web objects (SPWeb.AllProperties), but to other property bags as well, like the properties of the folders (SPFolder.Properties) and files (SPFile.Properties) or lists (SPLists.Properties) and list items (SPListItem.Properties).

October 16, 2017

How to query your working hours from Windows Event Log via PowerShell

Filed under: PowerShell, Tips & Tricks — Tags: , — Peter Holpar @ 19:49

At my company we have a kind of time reporting application. If I book the activities the same day, there is no problem. But after a week, it is not always straightforward to remember what I exactly did on a given day. To have a rough estimate, how many hours I overall and separated for projects worked, I usually make use of data sources like Event Viewer (first and last entries daily in the Windows Logs / System), Exchange (mails sent and receive), Internet Explore (sites visited in Browser History) and TFS (check-ins and task history).

To be able to query the Event Viewer Logs without starting the application and browsing through the entries, I wrote a PowerShell script that perform these tasks automatically for me. It’s nothing extra, but I thought it might be useful for others as well:

$startDay = Get-Date -Date ‘2017/09/01’
$endDay = Get-Date -Date ‘2017/09/11’

$days = New-Object System.Collections.Generic.List“1[System.DateTime]
For ($today = $startDay; $today -le $endDay; $today = $today.AddDays(1)) {
  $days.Add($today)
}

$days | % {
  $day = $_
  $events = Get-EventLog -Log System | ? { $_.TimeGenerated.Date -eq $day.Date }
  $maxDate = ($events | Measure-Object -Property TimeGenerated -Maximum).Maximum
  $minDate = ($events | Measure-Object -Property TimeGenerated -Minimum).Minimum
  select -Input $_ -Prop `
    @{ Name=’Day’; Expression={$day.ToShortDateString()} },
    @{ Name=’From’; Expression={ $minDate.ToLongTimeString() } },
    @{ Name=’To’; Expression={ $maxDate.ToLongTimeString() } },
    @{ Name=’Working Hours’; Expression={ $maxDate – $minDate } }
} | Export-Csv -Path C:\Temp\TimeReport.csv -Delimiter ";" -Encoding UTF8 -NoTypeInformation

The script writes the results in a .csv file, but without the last part (Export-Csv) you can direct the output to the screen as well.

July 20, 2017

How to Export a SharePoint List View to Excel Automatically Using PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 21:17

Note: This post is actually only a minor modification of the post I wrote recently about the URL of the Edit View page.

We can easily export the content of a SharePoint List View via the UI, simply by clicking the Export to Excel button on the ribbon:

image

You can achieve that automatically as well, for example from PowerShell.

Note: In the text below I describe the solution for a situation if you work locally on the server, but it is possible to apply the same technique to a remote solution as well, one should only transfer the code to the Managed Client Object Model.

Assume you have a list called YourList in a SharePoint site with URL http://YourSharePoint/Web/SubWeb.

It is easy to find out (for example, by monitoring the network traffic by Fiddler) that the URL generated when you click the Export to Excel button is like this:

http://YourSharePoint/Web/SubWeb/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=%7B5315A0C9%2DAA6A%2D4598%2DA1D4%2D99B1BBCBF8C7%7D&View=%7B8E449E17%2D593C%2D4218%2DA4A4%2D43A8B47382BC%7D&RootFolder=%2FWeb%2FSubWeb%2FLists%2FYourList&CacheControl=1

The values of the List and View query string parameters are the encoded IDs of your list and list view respectively.

The following code generates the same URL from PowerShell:

$web = Get-SPWeb http://YourSharePoint/Web/SubWeb
$list = $web.Lists[‘YourList’]

# get the default view of the list
$view = $list.DefaultView
# or get an arbitrary view by its name
# $view = $list.Views[‘All Items’]
$viewId = $view.ID

function EscapeGuid($guid)
{
  return "{$guid}".ToUpper().Replace(‘-‘, ‘%2D’).Replace(‘{‘, ‘%7B’).Replace(‘}’, ‘%7D’)
}

$escapedListId = EscapeGuid $list.ID
$escapedViewId = EscapeGuid $view.ID
$escapedRootFolder = $list.RootFolder.ServerRelativeUrl.Replace(‘/’, ‘%2F’)

$url = $web.Url + "/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=$escapedListId&View=$escapedViewId&RootFolder=$escapedRootFolder&CacheControl=1"

The URL above is actually no URL for the data or its schema, it’s a URL for a descriptor file (with the extension .iqy, see more about that here), that contains the URL for that list data and its schema.

The content of an .iqy file looks like this (you can capture it by Fiddler as well, or have a look at the content of the file we saved in our script further below) :

WEB
1
http://YourSharePoint/Web/SubWeb/_vti_bin/_vti_bin/owssvr.dll?XMLDATA=1&List={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}&View={8E449E17-593C-4218-A4A4-43A8B47382BC}&RowLimit=0&RootFolder=%2fWeb%2fSubWeb%2fLists%2fYourList

Selection={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}-{8E449E17-593C-4218-A4A4-43A8B47382BC}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=http://YourSharePoint/Web/SubWeb/_vti_bin
SharePointListView={8E449E17-593C-4218-A4A4-43A8B47382BC}
SharePointListName={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}
RootFolder=/Web/SubWeb/Lists/YourList

The URL we have in line 3 refers to the endpoint that returns the data schema and the data itself.. Based on this information, Excel can import and display the data of the list view.

Let’s save the file from the URL of the .icq file we have already from the first script, and start Excel to open the list view data. The script below assumes the extension .iqy is associated with Excel in your system:

$path = "C:\temp\owssvr.iqy"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "text/html, application/xhtml+xml, */*"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$writer = [System.IO.StreamWriter] $path
$writer.WriteLine($data)
$writer.Close()

# the .iqy file will be opened by Excel
Invoke-Expression $path
# optionally delete the file
# Remove-Item $path

July 16, 2017

Find Your Scripts in SharePoint within Seconds – the Effective, but Unsupported Way

Filed under: JavaScript, SP 2013, SQL, Tips & Tricks — Tags: , , , — Peter Holpar @ 18:41

The SharePoint environment I’m working on contains hundreds of webs. I create test sites for various tasks (like prototyping JSLink-based solutions) including the necessary lists, and store the .js and .css files typically in the Site Assets library of that web site to keep the solution artifacts (lists / files) together. It is rather common, that after I’ve finished the proof of concept, I don’t need it for months, then suddenly I should return to it, but I don’t find it anymore, as I don’t remember, which site I used for that solution.

For that kind of search I’ve created a simply SQL query to find the script directly in the content database of the site collection. Yes, I know it is officially unsupported to access the SharePoint databases directly, but I’m OK with that in my test system. Use it on your own risk.

SELECT [Id]
      ,[SiteId]
      ,[DirName]
      ,[LeafName]
      ,[TimeLastModified]
      ,[DeleteTransactionId]
  FROM [dbo].[AllDocs]
  WHERE LeafName LIKE ‘%.js’
  AND DirName LIKE ‘%SiteAssets%’
  ORDER BY TimeLastWritten DESC

This script lists the file name (LeadName) and path (DirName) of the scripts stored in various sites in their Site Assets library. The name of script and the date of last modification (TimeLastModified) is usually enough to identify the script I need. Note, that the records, that have a value other that 0x in the DeleteTransactionId column are recycled and located in the Recycle Bin. Of course, this method works only in the case of on-premise installations, and only as long as you have access to the SharePoint databases.

June 25, 2017

How to get the Url of the “Edit View” Page of a Specific SharePoint List View from PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 07:33

There might be cases when you can’t access the Edit View page of a specific list view from the SharePoint UI. For example, there is no such direct link in the case of Survey lists. There is no ribbon including the Manage Views group, and the Views area is missing from the List settings page as well.

You can, however access the Edit View page from your browser if you know its URL. The standard URL of this page has this pattern:

http://YourSharePoint/Web/SubWeb/_layouts/15/ViewEdit.aspx?List=%7BDC913804%2DB28E%2D4F52%2DAF53%2DDEC490A1C83D%7D&View=%7B2E7DF707%2D42BA%2D44EE%2D87C6%2D0919CA38BDF1%7D

As you see, the ViewEdit.aspx page is responsible for this functionality. The encoded Ids (Guid) of the List and the View are passed as query string parameters (List and View respectively).

You can get the URL of the page using this PowerShell script easily:

$web = Get-SPWeb ‘http://YourSharePoint/Web/SubWeb’
$list = $web.Lists[‘YourList’]
# get the default view of the list
$view = $list.DefaultView
# or get an arbitrary view by its name
# $view = $list.Views[‘All Items’]
$viewId = $view.ID

function EscapeGuid($guid)
{
  return "{$guid}".ToUpper().Replace(‘-‘, ‘%2D’).Replace(‘{‘, ‘%7B’).Replace(‘}’, ‘%7D’)
}

$url = $web.Url + ‘/_layouts/15/ViewEdit.aspx?List=’ + (EscapeGuid $list.ID) + ‘&View=’ + (EscapeGuid $view.ID)

You can even start the page in Internet Explorer from PowerShell if you wish:

$ie = New-Object -ComObject InternetExplorer.Application
$ie.Navigate2($url)
$ie.Visible = $true

June 13, 2017

A Quick and Dirty Solution to Create a Blank Site in SharePoint 2013

Filed under: Administration, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 18:36

Recently one of our clients requested a change in a custom-built SharePoint application. The original version of the application was built for SharePoint (MOSS) 2007 using Visual Studio 2008, then upgraded to SharePoint 2010 using Visual Studio 2010. Later the site was upgraded to SharePoint 2013, without any change in the code of the solution.

Now we had to create a replica of the site in our developer environment including the list data. We pulled a backup of the site using the Export-SPWeb cmdlet successfully in the productive system, and created a new team site in the development system as a target of the Import-SPWeb cmdlet. When executing the restore operation we’ve got this exception:

Import-SPWeb : Cannot import site. The exported site is based on the template STS#0 but the destination site is based on the template STS#1. You can import sites only into sites that are based on same template as the exported site.

image

In the error message STS#0 means the Team Site template, and STS#1 stands for the Blank Site template (see SharePoint site template IDs and their description here). Jason Warren suggests in this thread to use the -Force switch of the Import-SPWeb cmdlet to force the overwrite of the existing site, but we had the same issue even using this switch. How could we create a new web site using the Blank Site template? Solutions available using the server side, like using PowerShell or unhiding the Blank Site template are discussed in this thread. But what could we do, if we had no access to the server side, as this site template is not available on the web UI anymore?

We found a simply solution using only a single browser (Internet Explorer in our case) and the F12 Developer Tools.

Load the site creation page in the browser, then start the Developer Tools, and select the list of templates using the DOM Explorer.

 

image

Select an options in the select element, like the Team Site

image

… change its value attribute to STS#1

image

… and finally click the Create button on the web page to create the new blank site.

This solution is quick, but I consider it to be dirty, as users should perform it themselves and each time they need a blank site, so definitely not a user friendly option. But it might be handy if you need a simple way without access to the server side.

March 26, 2017

Generating Pseudo GUIDs for Your Project Server Entities

Filed under: PowerShell, Project Server, Tips & Tricks — Tags: , , — Peter Holpar @ 06:24

As you might have known, since the version 2013, Project Server utilizes pseudo-GUIDs to improve Project Server performance. These ones has the format of a “classical” GUID, but actually generated sequentially. As Microsoft states in this TechNet article:

"We handle GUIDs a little better in Project Server 2013 – and in many places they are sequential GUIDs which cause less index fragmentation"

This topic is quite good described in the Project Conference 2014 presentation Project Worst Practice – Learning from other peoples mistakes by Brian Smith. See the video recording between 6:08-13:54, or the slides 10-14.

One of the main components of the pseudo-GUID generation is the NewSequentialUid method of the Microsoft.Office.Project.Server.Library.PSUtility class:

public static Guid NewSequentialUid() 

  Guid guid; 
  if (NativeMethods.UuidCreateSequential(out guid) != 0) 
    return Guid.NewGuid(); 
  byte[] b = guid.ToByteArray(); 
  Array.Reverse((Array) b, 0, 4); 
  Array.Reverse((Array) b, 4, 2); 
  Array.Reverse((Array) b, 6, 2); 
  return new Guid(b); 
}

So if you want to use the same kind of pseudo-GUIDs for your own custom entities you create from code, you can get the IDs by invoking the method (for example, via PowerShell). The code sample below illustrates, how to get a single ID, or a batch of  IDs (in this case, 5 of them):

# load the necessary assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Shared")
# generate a single sequential ID
[Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid()
# or generate a range of sequential IDs, in this case, five of them
(1..5) | % { [Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid().Guid }

October 1, 2015

The SharePoint Time Machine

Filed under: SP 2013, Tips & Tricks — Tags: , — Peter Holpar @ 22:06

Assume you have a SharePoint list with a lot of items. The list supports versioning and you should provide a snapshot of the items at a given time in the past.

As you know, the Versions property (of type SPListItemVersionCollection) of the SPListItem class contains the item versions. One can access a specific version via the indexer property of the collection, by the ID of the version (where the ID = 512 * major version number + minor version number), or by the version number (a.k.a. label, for example, 2.3), but there is no direct support to get the actual version at a specific time in the past.

To achieve my goal, I’ve implemented the GetVersionFromDate extension method, that iterates through the method, and returns the version we need based on its creation date:

  1. public static SPListItemVersion GetVersionFromDate(this SPListItemVersionCollection versions, DateTime localDate)
  2. {
  3.     SPListItemVersion result = null;
  4.  
  5.     if (versions != null)
  6.     {
  7.         DateTime date = versions.ListItem.Web.RegionalSettings.TimeZone.LocalTimeToUTC(localDate);
  8.  
  9.         SPListItemVersion prevVersion = null;
  10.  
  11.         // versions[0] – current item version
  12.         // versions[versions.Count – 1] – first item version created
  13.         for (int i = versions.Count – 1; i >= 0; i–)
  14.         {
  15.             SPListItemVersion version = versions[i];
  16.             if (version.Created > date)
  17.             {
  18.                 result = prevVersion;
  19.                 break;
  20.             }
  21.             // if it is the last (actual) version and there is no result yet,
  22.             // then the date specified should be greater than the creation date of the last version
  23.             // we take the last version
  24.             else if (i == 0)
  25.             {
  26.                 result = version;
  27.             }
  28.  
  29.             prevVersion = version;
  30.         }                
  31.  
  32.     }
  33.  
  34.     return result;
  35. }

Note, that the Created property stores the creation date as UTC time, that we should convert first.

Using this method accessing the specific version is so simple as:

  1. SPList list = web.Lists["Your List"];
  2. SPListItem item = list.Items.GetItemById(1);
  3.  
  4. DateTime date = DateTime.Parse("2015/06/29 13:40");
  5. SPListItemVersion version = item.Versions.GetVersionFromDate(date);
  6. Console.WriteLine(version["APropertyName"]);

If you go through the items in the list and get the version of the specific time, you already have the required snapshot.

February 9, 2015

“Decoding” SharePoint Error Messages using PowerShell

Filed under: PowerShell, SP 2010, Tips & Tricks — Tags: , , — Peter Holpar @ 22:26

When working with SharePoint errors in ULS logs, you can find the error message near to the stack trace. In case of simple methods the stack trace may be enough to identify the exact conditions under which the exception was thrown. However, if the method is complex, with a lot of conditions and branches, it is not always trivial to find the error source, as we don’t see the exception message itself, as it is stored in language-specific resource files, and you see only a kind of keyword in the code.

For example, let’s see the GetItemById method of the SPList object with this signature:

internal SPListItem GetItemById(string strId, int id, string strRootFolder, bool cacheRowsetAndId, string strViewFields, bool bDatesInUtc)

There is a condition near to the end of the method:

if (this.IsUserInformationList)
{
    throw new ArgumentException(SPResource.GetString("CannotFindUser", new object[0]));
}
throw new ArgumentException(SPResource.GetString("ItemGone", new object[0]));

How could we “decode” this keyword to the real error message? It is easy to achieve using PowerShell.

For example, to get the error message for the “ItemGone”:

[Microsoft.SharePoint.SPResource]::GetString("ItemGone")

is

Item does not exist. It may have been deleted by another user.

Note, that since the second parameter is an empty array, we can simply ignore it when invoking the static GetString method.

If you need the language specific error message (for example, the German one):

$ci = New-Object System.Globalization.CultureInfo("de-de")
[Microsoft.SharePoint.SPResource]::GetString($ci, "ItemGone")

it is

Das Element ist nicht vorhanden. Möglicherweise wurde es von einem anderen Benutzer gelöscht.

Having the error message, it is already obvious most of the time, at which line of code the exception was thrown.

It can also help to translate the localized message to the English one, and use it to look up a solution for the error on the Internet using your favorite search engine, as there are probably more results when you search for the English text.

Older Posts »

Create a free website or blog at WordPress.com.