Category: programming

More SharePoint list view threshold fun

Here’s a quick follow-up to my previous post on dealing with SharePoint list view thresholds. I just bumped up against another case where I had to change some code to deal with it.

To recap the project a bit, I am writing a console app that will import a bunch of data into a SharePoint site. Since I’m human, and I make mistakes, the first step of this importer is to delete any existing data in those lists (which would be leftover from the previous test run).

I had a simple solution for doing that, deleting records in batches of 100, based somewhat on this example. I assumed that would work OK, even with larger lists, but I didn’t take into account that the first step in my process was to get all items in the list. That, of course, fails for a very large list. So I had to change my code to initially get only the first 4000 records in the list. (That can be done with the RowLimit clause in CAML.) Then, I delete from that subset in batches of 100. Then, I just repeat that until there are no more records left.

As far as I can tell, there’s no SharePoint CSOM equivalent to SQL’s “truncate table”, which would have made this much easier. And I feel like I’m probably still not doing this in the most efficient way. If I was creating a process that needed to do this repeatedly, instead of just a few times, I’d dig into it some more. And if I was retrieving items instead of deleting them, I’d probably do something with ListItemCollectionPosition.


private void deleteAllFromList(ClientContext cc, List myList)
{
int queryLimit = 4000;
int batchLimit = 100;
bool moreItems = true;
string viewXml = string.Format(@"
<View>
<Query><Where></Where></Query>
<ViewFields>
<FieldRef Name='ID' />
</ViewFields>
<RowLimit>{0}</RowLimit>
</View>", queryLimit);
var camlQuery = new CamlQuery();
camlQuery.ViewXml = viewXml;
while (moreItems)
{
ListItemCollection listItems = myList.GetItems(camlQuery); // CamlQuery.CreateAllItemsQuery());
cc.Load(listItems,
eachItem => eachItem.Include(
item => item,
item => item["ID"]));
cc.ExecuteQuery();
var totalListItems = listItems.Count;
if (totalListItems > 0)
{
Console.WriteLine("Deleting {0} items from {1}…", totalListItems, myList.Title);
for (var i = totalListItems – 1; i > -1; i–)
{
listItems[i].DeleteObject();
if (i % batchLimit == 0)
cc.ExecuteQuery();
}
cc.ExecuteQuery();
}
else
{
moreItems = false;
}
}
Console.WriteLine("Deletion complete.");
}

view raw

deleteAllFromList.cs

hosted with ❤ by GitHub

Bumping up against the list view threshold in SharePoint 2013

I’ve been learning a lot while working on my current SharePoint project. There’s been a lot of “trial and error” stuff, where I try something, get an error message, Google the message, then spend an hour or two puzzling out what I did wrong.

Today’s issue was related to the default 5000-item list view threshold. I was already aware that it existed, and that I’d need to take certain measures to avoid bumping up against it. The main list for my project is going to have about 20,000 items in it, when it’s in production, so I knew I had to watch out for it.

The list has two fields, company and vendor number, that, combined, form a unique key. In SQL, I would put them together into a single two-field unique index, and that would be sufficient to allow lookups to work well. In SharePoint, it’s a little more complicated. It’s theoretically possible to create a two-column compound index, but I can’t do that on my list, for some reason. (At some point, I’ll have to do some research and figure out why.) So I’ve got two single-column indexes.

One of the things I need to do in my code is pull up a single record from my list, given company and vendor number. I’m using something like the code below to do that. (This is CSOM code.) This code should only ever return a single record, since the combination of the two fields is always unique, and they’re both always specified.


private bool doesVendorRecExist(string companyCode, string vendorNum,
List vendorList, ClientContext cc)
{
CamlQuery spq = new CamlQuery();
spq.ViewXml = string.Format(@"
<View><Query>
<Where><And>
<Eq><FieldRef Name='VendorNo' /><Value Type='Text'>{0}</Value></Eq>
<Eq><FieldRef Name='CompanyName' /><Value Type='Text'>{1}</Value></Eq>
</And></Where>
</Query></View>", vendorNum, companyCode);
ListItemCollection myItems = vendorList.GetItems(spq);
cc.Load(myItems);
try
{
cc.ExecuteQuery();
}
catch (Microsoft.SharePoint.Client.ServerException ex)
{
Console.WriteLine("Error in doesVendorRecExist({0},{1}): {2}", companyCode, vendorNum, ex.Message);
Environment.Exit(-1);
}
return (myItems.Count > 0);
}

view raw

doesVendorRecExist.cs

hosted with ❤ by GitHub

The original version of this code had the ‘CompanyName’ field first, and the ‘VendorNo’ field second. That version caused a crash with the message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” That didn’t make any sense to me, since I was specifying values for both indexed fields, and the result should have always been zero or one records.

Some background first: The distribution of the data in my list is a bit lopsided. There are about a half-dozen company codes, and about 6000 unique vendor numbers. There are more than 5000 vendor numbers in the main company and less than 5000 in the others. In SQL, this wouldn’t matter much. Any query with “where VendorNo=x and CompanyName=y” would work fine, regardless of the ordering of the ‘where’ clause.

In CAML, I’m guessing, the order of fields in the ‘where’ clause DOES matter. My guess is that, with the ‘CompanyName’ field first, SharePoint was first doing a select of all items with ‘CompanyName=x’, which in some cases would return more than 5000 rows. Hence the error. By switching the order, it’s searching on ‘VendorNo’ first, which is never going to return more than a half-dozen items (one for each company, if the vendor exists in all of them). Then, it does a secondary query on the CompanyName which whittles down the result set to 0 or 1 records. I’m not entirely sure if I’ve got this right, but I do know that switching the order of the fields in the CAML fixed things.

So, lesson learned: SharePoint isn’t nearly as smart as SQL Server about query optimization.

Another side-lesson I learned: I initially didn’t have my CAML query specified quite right. (I was missing the “<View><Query>” part.) This did NOT result in an error from SharePoint. Rather, it resulted in the query returning ALL records in the list. (It took a while to figure that out.)

I suspect that I’m going to learn even more lessons about SharePoint’s quirks as I get deeper into the testing phase on this project.

Useful Links:

Copying SharePoint users from one group to another

I recently hit an issue with SharePoint, where I had added a bunch of users to a “visitors” group, but then needed to move them to a “members” group. I figured I could probably do this with PowerShell, so I did some searching, found a couple of scripts that were almost what I needed, and managed to cobble something useful together. So, for future reference, here it is. This script will get a list of users from the source group, then add them to the destination group. (I later deleted the users from the source group manually, but that could probably be done with PowerShell as well.) I’m also filtering the user list, so it only includes individual users with e-mail addresses, not domain groups.

Add-PSSnapin "Microsoft.Sharepoint.Powershell"
$siteURL = "http://SITENAME/sites/SUBSITE/"
$srcGroup = "My Database Visitors"
$destGroup = "My Database Members"
$srcUsers = Get-SPWeb $siteURL |
    Select -ExpandProperty SiteGroups |
    Where { $_.Name -eq $srcGroup } |
    Select -ExpandProperty Users |
    Where {$_.IsDomainGroup -eq $false -and $_.Email -ne ""}
foreach ($user in $srcUsers)
{
    New-SPUser -UserAlias $user.Email -Web $siteURL -Group $destGroup
}

I’m still not great with either SharePoint or PowerShell, but I get by. Here’s a couple of sources that I used in creating this script:

Interactive C# REPLs

I was working on a C# program today, and wanted to test a small code snippet out. I used to use Snippet Compiler for that sort of thing, but it hasn’t been updated in a very long time.

Visual Studio 2015 now has a built-in REPL, but I’m using VS 2013.

I decided to try installing ScriptCS, which I’d read about before, but never actually tried. It was near the end of the work day when I started trying to install it, and I hit a couple of minor snags. So I don’t have it working yet, but I plan on getting it straightened out tomorrow.

In the past, I’ve also looked at CShell, which appears to be similar to ScriptCS, but with an actual lightweight IDE included.

I’ve also looked at CS-Script, which is interesting because it can be used with Notepad++ via a plugin.

And if you just want to try something out in a web browser, there’s .NET Fiddle.

Scott Hanselman has a good blog post on C# REPLs here. He mentions ScriptCS and the VS 2015 C# and F# REPLs.

I don’t have anything terribly useful to say about any of these products, since I haven’t gotten around to using them yet, but I wanted to write this up, just so I’d have a consolidated list of links to all of them.

This whole thing has side-tracked me enough from the actual task I was trying to accomplish, that I almost forgot what it was. I’m pretty sure I was trying to check how WebUtility.HtmlDecode would treat a certain input string, but I could be wrong. Well, tomorrow’s another day!

 

Paying for Pluralsight, and other subscriptions

Pluralsight extended their Black Friday sale by a few days, so I gave in and paid for a year last night. Part of my reason for that is that I noticed that there’s a series on SharePoint 2013 programming with JavaScript by David Mann that looks like it might be useful. (Now I just have to convince myself to watch it.)

I was happy to see that they added the paid subscription to the end of my free period, rather than just start it immediately, so it will last until January 2018. I paid for the subscription with a virtual card number, so it won’t auto-renew, so that will give me a chance to think about whether or not I want to continue with it in 2018.

I’m in the middle of listening to a Mac Power Users episode on managing subscriptions, and there’s a few good ideas in there. For stuff that renews annually, my approach is generally to pay for them with a virtual card number or (where possible) a personal check, so they don’t auto-renew. Then, I have to make a conscious decision to evaluate and renew (or not) each year. I also try to keep track of them in Evernote, and set reminders so I know when they’re coming up for renewal.

I tend to review subscriptions at the end of each year, so I might as well do that now. Looking at a few of the big ones, I see that my Office 365 subscription is good until December 2018, so I don’t have to worry about that one for a long time. But my Evernote subscription is set to renew next month, at the $70 “premium” level. I’m still not happy about their increase from $50 to $70, but I do get quite a lot of use out of Evernote, so I’m pretty sure that I’m going to let that one renew. (Though maybe I should read up some more on their iOS app redesign, before I commit to that.)

And I just checked my NY Times subscription. I originally subscribed at a promotional rate of $10.50 every four weeks, for a year. That was in January, so that promotional rate will be over soon. I don’t mind paying that, but I expect that the renewal will be done at whatever the current “regular” rate is. The Times is kind of sketchy about pricing. My account page doesn’t say at what rate the subscription will renew, so I’m not sure if it’ll still be $10.50, or something else. Looking at their current rates (by going to their site in a private browsing window), I think it would be $15 every 4 weeks. And looking at the cost of subscribing through their iOS app, I think I could get a subscription for $130 per year, which comes out to $10 every 4 weeks. So, to get a good rate, I may need to cancel my existing subscription and resubscribe through iOS.

I could choose to look at supporting the Times as being a little like a charitable donation though. I think we’re going to need a robust, independent, (relatively) unbiased press over the next four years. And the Times seems to have gotten under the president-elect’s skin more than any other media outlet. So they’re worth supporting, for that reason, at very least.

Fun and games with the SharePoint social comment control

I’ve been working on a big SharePoint 2013 project at work, and I’m learning a lot about the ins and outs of developing custom applications with SharePoint. (TL;DR: It’s messy.) There have been a bunch of times when I’ve come across something weird and/or interesting and thought “I should write this up in a blog post,” but I just haven’t gotten around to it. Well, this time, I’m going to at least start writing something up. (We’ll see if I get far enough to have something coherent to post or not.)

My application is a farm-level solution using a couple of application pages. There’s a search page and a detail page, basically. The detail page loads data from a few different sources, based on parameters passed on the query string. So, in some ways, standard ASP.NET stuff.

After having gotten most of the stuff on the detail page done, I wanted to add the ability for users to add comments to the page. I looked at a number of possibilities for this. One option that jumped out is the social comment control. This control can be added to a page as a web part, via “Social Collaboration”, “Note Board”. To add it to an application page in Visual Studio, you need to do the following:

  1. Add a reference to “Microsoft.SharePoint.Portal” to your project.
  2. On your page, register the SharePoint portal controls:
    <%@ Register TagPrefix="SharePointPortalControls" Namespace="Microsoft.SharePoint.Portal.WebControls" Assembly="Microsoft.SharePoint.Portal, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
  3. Drop the social comment control on the page somewhere:
    <SharePointPortalControls:SocialCommentControl ID="CommentControl" runat="server"/>

I picked up the basics on this from this article, which also covers user ratings.

This initially seemed to work well. I was curious about where, exactly, SharePoint was storing these comments though. From this article, I learned that you need to go through central admin to get to them:

  1. Open the Central Admin home page.
  2. Click “Manage service applications” under “Application Management.”
  3. Click “User Profile Service Application”.
  4. Click “Manage Social Tags and Notes” under “My Site Settings”.

This gets you to a page where you can search for notes, by user and/or URL. You cannot do a wildcard search, or simply pull up all notes, so that’s pretty inconvenient. But it was reassuring to see that the notes are indeed stored by URL, with the full URL, including query string. So, for me, each detail page would have its own set of comments, no problem there.

When I did some testing, posting comments to the same page from two different user accounts, I hit a pretty major snag though. The two users could not see each other’s comments. That led me down a rabbit hole that brought me to this StackExchange page. I followed the advice to set “Security Trimming Options” to “Show all links regardless of permission”, and that (eventually) fixed the problem. I also ran the “Social Data Maintenance Job”, as described in this StackExchange page.

I’m not sure if changing that security trimming setting will have any negative effects, if I change it in production. It’s a Central Admin level setting, so it’s something I’ll need to review seriously.

Assuming I stick with this plan, I’m also going to need to be able to create comments in code, as I’m going to be importing a bunch of them from the old site that I’m replacing. It does seem to be possible to do that, based on info from these pages:

I haven’t actually tried writing any code for this yet, so I may stumble across some “gotchas,” as I have with a number of other seemingly-straighforward SharePoint tasks.

And, after going through all this, I see that, for SharePoint Online, the Tags & Notes feature has been retired. We’re not using SharePoint Online, but if we ever migrate to it, I’d have to redo this functionality. So that’s a serious knock against it.

A lot of the stuff I’ve been doing in SharePoint has been working out this way:

  1. Find a SharePoint feature that looks like it solves my problem nicely.
  2. Spend some time setting it up and doing some initial testing. Things look promising.
  3. Stumble across a problem. Spend a bunch of time researching it.
  4. Either:
    • Find that the problem isn’t really solvable and give up.
    • Or find a workaround that’s acceptable, maybe, but not great. (It relies on something undocumented, or a feature that’s deprecated, or it requires changing farm-level settings that I’m not sure I can change.)
  5. Realize that I’ve wasted half the day on this.
  6. Give up and go to lunch.

So my solution for commenting is probably going to be a standard SharePoint list that I’ll read and write in code-behind and show in a standard ASP.NET repeater, or something like that.

Well, I guess I’ve succeeded in writing a semi-coherent post about SharePoint programming. I’m not sure if it will help anyone, but it might be mildly amusing, if nothing else.

Cyber Monday

I couldn’t quite talk myself into signing up for a Pluralsight subscription today, even at the $200 Black Friday rate, but I did convince myself to plunk down $15 for the Humble / O’Reilly Unix bundle that’s running right now. (Not technically a Black Friday or Cyber Monday deal, but it’s in the right date range.)

Over a dozen classic O’Reilly books for $15: not a bad deal. Most of the books are pretty old, but many of them are still useful. I’m not sure if the sed & awk book or the lex & yacc book are of any use at this point, but the vi/Vim and Emacs books could be. And bash is still useful. I have a few of these in hard copy form, but it’ll be nice to have PDF copies of them. I imagine I could actually throw out some of my older O’Reilly books at this point. I think I have a first edition of the Unix Power Tools book floating around here somewhere, for instance. That’s a pretty thick book, and there’s really no reason I need it anymore, except maybe as a doorstop. (Thinking about it a bit more, I guess most of these books are available through the Safari subscription I get through ACM, but it can’t hurt to have offline, DRM-free, copies.)

I also convinced myself to buy a few comics from Dark Horse, from their Black Friday sale (most books for 99 cents), and a few collections from Comixology (from DC’s buy one get one free sale). In both cases, I was buying stuff that I missed from the period where I’d pretty much stopped buying comics (2009-2015, roughly).

I should probably be slightly embarrassed that I only bought stuff for myself this weekend, but I don’t really have any need to do any significant Christmas shopping for anyone else. And that can all get done online, whenever.

Pluralsight Black Friday sale and SharePoint development

I signed up for a free three-month Pluralsight subscription, via Microsoft’s Dev Essentials program, a couple of months ago. The main purpose of the subscription, initially, was to learn more about SharePoint programming, for a project at work. So far, I’ve completed Andrew Connell’s “SharePoint 2013 Developer Ramp-Up” series, and I’m most of the way through Sahil Malik’s “Understanding SharePoint 2013” series. So I’m actually using the subscription, and getting some value out of it.

I’ve been watching the videos largely at work, but now I’m getting a little self-conscious about it. There’s apparently been some political stuff going on at work regarding streaming video. It’s a long story, but apparently we need to be careful about not watching too much streaming video now. I don’t think anybody would look askance at a few hours of Pluralsight per week, but you can’t be too careful, so I guess I might need to stick with watching the videos at home from now on.

But, either way, I’m getting value out of the free Pluralsight subscription, so I’m thinking about getting a paid subscription. Normally, I’d wait until the free one was up, but Pluralsight does a Black Friday sale every year, so now would be a good time to pay for a year-long subscription. Their regular rate of $300 per year is a bit too steep for me. The Black Friday deal is $200 per year, so that’s a bit better, but still not insignificant, so I haven’t quite decided yet. The deal is good through Monday, so I have some time to make up my mind.

On the subject of SharePoint programming in general, I’m pretty deep into a SharePoint 2013 project right now, and I’m learning a lot of stuff by trial and error. I seem to keep finding weird little quirks that I have to work around. And I keep trying to do stuff that, apparently, isn’t easy to do in SharePoint, or at least isn’t obvious. I’ve been thinking about writing up a few SharePoint-related blog posts, but I’ve been too busy to really get my notes together on anything particularly interesting, to the point where I could write a coherent post. But maybe soon.

The cost of a good education

After wringing my hands recently about the cost of a Pluralsight subscription ($300/year), I came across this article about the cost of a master’s degree in computer science. Georgia Tech is currently offering an online master’s in CS for only $7000, which is apparently astonishingly inexpensive. As the article points out, a master’s in CS from USC would cost $57,000. (I just went back and reread the article, and realized that they never mention how much an on-campus degree from Georgia Tech would cost. I’m guessing it’s much less than USC’s cost, so it would have made a less startling contrast. But it would have been a more relevant comparison. Oh well.) Anyway, I guess I shouldn’t complain about the cost of the kind of “continuing professional education” that you get from a service like Pluralsight, when you compare it to an actual college education.

Over the years, I’ve occasionally thought about going back to college and getting my master’s degree. In the past, before online education took off, I considered doing it part-time, locally, at maybe someplace like Rutgers, NYU, NJIT, or Stevens. I could never quite talk myself into it, due to the cost and amount of work that would be required. If this Georgia Tech program had been available ten or twenty years ago, I might have considered doing it, part-time, over several years. Now, I don’t think I’d ever be able to do it while also holding down a full-time job. I just don’t have the energy to spend a couple of hours on the computer every night, programming and reading books and watching lectures, after a full day of work. (And, at this stage of my life & career, I’m not really interested anyway.)

Meanwhile, I’ve been bookmarking even more Pluralsight videos that I want to watch. And I keep seeing interesting stuff in the EdX and Coursera emails that I get every week. Maybe I’ll manage to pick up on some of that stuff soon. There’s so much new stuff I want to learn!

Pluralsight and SharePoint

I recently started working on a new SharePoint project at work. This project is basically replacing an old SharePoint 2003 solution with a new SharePoint 2013 one, making a number of improvements along the way. The requirements for this project are a bit beyond my current level of expertise with SharePoint. (Which is a fancy way of saying that I don’t know what the hell I’m doing on this.)

When I last worked on a major SharePoint project, I’d bought a few books on SharePoint 2010 and 2013, and read through them. (Or at least the parts that were relevant to that project.) That was more than a year ago, though, and I’m pretty rusty now. And the new project is a lot more complex than that previous one. So I went back and reread some sections of those books, and did some typical internet research, and stuff like that.

I also remembered that Andrew Connell had a series of videos available on Pluralsight covering SharePoint 2013 development, and that you can get a 3-month Pluralsight trial account through the Visual Studio Dev Essentials program. So now I’ve got a free Pluralsight account that will last me through to the end of the year, and I’ve been watching the Andrew Connell videos in my spare time. When I’m through with those, Sahil Malik has a bunch of SharePoint 2013 videos on Pluralsight too.

I’ve been watching the SharePoint videos on my desktop PC at work, but Pluralsight also has iOS apps, including one for the Apple TV. So I need to download that, and see if the developer training videos are at all effective when watched on a regular TV, from my couch. (I was going to do that on Sunday, but my migraine intervened.)

I’ve thought about paying for a Pluralsight subscription occasionally in the past, but I’ve always decided against it, due to the cost: $300/year or $30/month. So, a good bit more expensive than Netflix, though maybe that’s not a fair comparison. There’s a lot of other stuff on Pluralsight that I’d love to watch, but it’s so hard to find the time to start learning anything new. So I don’t know know if I’d really get my money’s worth out of the subscription. Maybe if I could talk myself into watching Pluralsight videos instead of NCIS reruns once in a while, I could finally learn AngularJS.