Author: Andrew Huey

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

Evernote privacy, revisited

This is a follow-up to my post from a few days ago about Evernote’s privacy policy changes. They got so much negative feedback about the changes that they’ve decided not to implement them, and to review and revise their policy to “address our customers’ concerns, reinforce that their data remains private by default, and confirm the trust they have placed in Evernote is well founded.” That quote is from their new blog post on the subject. I’m fine with that, and it’s nice to see them reacting quickly to this. I still don’t consider Evernote to be a great place to store seriously confidential information, but I wouldn’t consider most note-taking services to be trustworthy for that.

At lot of people have looked at OneNote as a good alternative to Evernote, but their privacy statement is fairly opaque. There’s nothing terribly alarming in there, but the statement is mostly a bunch of boilerplate legalese.

If I was looking at alternatives, and I didn’t need a Windows client, only macOS and iOS clients, I’d seriously consider Bear. It’s gotten some very good reviews. And it uses CloudKit to sync data, so it’s all encrypted by default.

Another one I’d look at, if I only needed macOS support, is Quiver, which is billed as “a programmer’s notebook.” One of the issues I have with both Evernote and OneNote is that they’re not great for plain text, specifically program source code. But I really need something I can use on iOS and Windows, so a macOS-only program wouldn’t do me much good.

Healthcare in America right now

Here’s an interesting article on Obamacare, which unfortunately turns out to be largely a waste of time to read, due to a couple of key sentences near the end:

There’s one significant problem with all these ideas, of course: They’d need to pass the Republican Congress and be signed into law by Mr. Trump.

Source: Politics Aside, We Know How to Fix Obamacare

So, it’s a good thought exercise, but it isn’t going to happen.

And here’s another article that doesn’t leave me feeling good about the current state of the healthcare system in America:

To put it in very, very blunt terms: This is the health equivalent of a carjacking.

Source: Surprise! Insurance Paid the E.R. but Not the Doctor – The New York Times

I already knew about this one, of course. I’ve read about it before, and was actually a bit worried about it, when I went in for hernia surgery last year.

Installing DD-WRT on my Netgear router

After last week’s Netgear vulnerability scare, I started thinking that maybe it was time to install DD-WRT on my router. I’ve used DD-WRT in the past, on a Linksys router, but I never tried installing it on my Netgear. I think I looked into it when I first got the router, and either it wasn’t yet available for the router, or it was, but there were some issues, and I was afraid of bricking it.

Well, I’ve now overcome any lingering fear and went ahead and installed the DD-WRT firmware. So far, it’s working great. It installed easily, using the old Netgear web interface. It took about five minutes to load. After it came up, I spent about ten minutes configuring everything, and double-checking stuff. (The version I installed was updated about a month ago; the Netgear firmware hadn’t been updated in years.)

For the wireless setup, I just used the same names and passwords that I’d used on the original Netgear interface. All of my devices seem to have connected to it with no issues. This is a far cry from some of the grief I’ve had in the past with wireless setup. (When I look at the available wireless networks from my apartment right now, by the way, I’m seeing about 40. That’s a far cry from when I set up my first Apple Airport Base Station, back in 1999 or so. I was the only person in range with a wireless network back then. I’m amazed this stuff works at all, with so many devices competing with each other.)

I have to admit that I’ve kind of lost track of the various wireless security modes. I used to understand this stuff really well, but I haven’t had to keep up with it recently. I set my networks to “WPA2 Personal Mixed” and that’s working, so I guess that’s good enough.

I haven’t enabled any fancy advanced features in DD-WRT yet. One thing I might play around with is the NAS support. The router has a USB port that you can plug a hard drive into. I had a drive hooked up to it for a while, but gave up on it because it was too slow to really be useful. But maybe it’ll work better with DD-WRT than with the Netgear firmware. I’ll have to try that at some point.

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:

Evernote privacy

There was a bit of a brouhaha earlier this week, when Evernote made some changes to their privacy policy. I’ve always known that my Evernote data isn’t encrypted, and can be seen by Evernote employees and processed by Evernote’s servers, so this doesn’t seem like that big a change (or that big a deal) to me. I generally store more sensitive stuff in 1Password, which is encrypted locally, and would be inaccessible to the folks at AgileBits.

The new wrinkle here is that Evernote is going to be doing some fancy machine learning stuff, so they needed to clarify how that would work. They posted a blog entry on this stuff today, and I’m reasonably satisfied with it, so I’m not going to be jumping ship over this.

Still, I should probably do a quick pass through my Evernote notebooks, and make sure I don’t have anything sensitive in there. If I do, I can move it to 1Password or just encrypt it in-place in Evernote. The encryption feature in Evernote is not great: you need to encrypt single notes, one at a time, and you can only do it on Windows and Mac, not on iOS. I think it would be great if you could designate an entire notebook as encrypted, and just put all your sensitive stuff in that one notebook.

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!

 

scary router vulnerabilities

It seems like consumer-level routers get hacked at an alarming rate. There was a lot of buzz today about a new Netgear vulnerability. I have a Netgear router, but not one of the models that’s affected by this particular problem. I bought mine back in 2010, and it’s been working fine. The firmware for it hasn’t been updated in a few years though. As far as I know, there are no unpatched vulnerabilities for it, but I’m not really confident about that.

I should probably install DD-WRT on it. I had DD-WRT on my last router, and it worked well. I’ve thought about installing it on the Netgear, but haven’t gotten around to it. Maybe that’s a project for this weekend, since it looks like it’ll be cold and snowy out.

Reinventing Local News

Here’s an interesting effort to help “reinvent” local news. The gist of it seems to be that we should put aside a certain amount of money from FCC auctions of local TV licenses to help set up new “cutting edge” local news sources. I’m not too optimistic that this will happen, or that it would result in quality, long-term, local news for New Jersey if it does. But, hey, it’s worth a shot. The local news situation in NJ is pretty weak.

We’re focusing first on New Jersey. Sandwiched between the New York and Philadelphia media markets, New Jersey receives little to no coverage of its state and local governments. New Jersey owns four public TV licenses, which the FCC estimates could fetch as much as $2.3 billion at auction.

Source: Our Last, Best Chance to Reinvent Local News