and has 0 comments
It has been a while since I've blogged something technical. It's just that nothing I did seemed to be worthy of this majestic blog... Well, jokes aside, here is a post detailing my log4net JIRA appender.

log4net is a popular (if not the most popular) logging framework for .NET out there. Its strength lies in its configurability, the possibility to create custom loggers, custom appenders, custom filters, etc. I will be talking about a custom appender, a class that can be loaded by log4net to consume the logged lines and put them somewhere. For example to make an application that uses log4net to write the log to the console all you do is configure it to use the console appender. The JIRA appender takes the log output and creates issues in JIRA, notifying users afterwards. JIRA is a tracker for team planning. It is also very popular.

In order to create an appender, one references the log4net assembly (or NuGet package) and then creates a class that inherits from AppenderSkeleton. We could implement IAppender, but the skeleton class has most of what people want from an appender. The next step is to override the Append method and we are done. We don't want to create an issue with each logged line, though, so we will make it so that it creates the issue after a period of inactivity or when the logger closes. For that we use the CancellationTokenSource class to create delayed actions that we can cancel and recreate. We also need to override OnClose().

For the JIRA Api I used a project called AnotherJiraRestClient, but I guess one can used anything out there. You will see that the notify functionality is not implemented so we have to add it.

Here is the appender source code:
using AnotherJiraRestClient;
using log4net.Appender;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace JiraLog4netAppender
{
public class JiraAppender : AppenderSkeleton // most appender functionality is found in this abstract class
{
private List<string> _notifyUsers;

// properties of the appender, configurable in the log4net.config file as children elements of the appender
public string url { get; set; } // the url of the JIRA site
public string user { get; set; } // the JIRA user
public string password { get; set; } // the JIRA password
public string project { get; set; } // the JIRA project
public string notify // a comma separated list of JIRA users who will be notified of the issue's creation
{
get
{
return string.Join(", ", _notifyUsers);
}
set
{
_notifyUsers.Clear();
if (!string.IsNullOrWhiteSpace(value))
{
_notifyUsers.AddRange(value.Split(',').Select(s => s.Trim()).Where(s => !string.IsNullOrWhiteSpace(s)));
}
}
}

CancellationTokenSource _ts;
StringWriter _sw;
Task _task;
private JiraClient _jc;
private string _priorityId;
private string _issueTypeId;

private object _writerLock=new object();

public JiraAppender()
{
_notifyUsers = new List<string>();
_ts = new CancellationTokenSource(); // use this to cancel the Delay task
_sw = new StringWriter();
}

protected override void Append(log4net.Core.LoggingEvent loggingEvent)
{
this.Layout.Format(_sw, loggingEvent); // use the appender layout to format the log lines (warning: for this you need to override RequiresLayout and return true)
_ts.Cancel(); // cancel the task and create a new one. This means as long as the logger writes, the 5 second delay will be reset
_ts = new CancellationTokenSource();
_task = Task.Delay(5000, _ts.Token).ContinueWith(writeToJira, _ts.Token); // after 5 seconds (I guess you could make this configurable as well) create a JIRA issue
}

protected override bool RequiresLayout
{
get
{
return true;
}
}

/// <summary>
/// write to jira, either when 5 seconds of inactivity passed or the appender is closed.
/// </summary>
/// <param name="task"></param>
private void writeToJira(Task task)
{
string s;
lock (_writerLock) // maybe the method was already in progress when another one was called. We need to clear the StringWriter before we allow access to it again
{
s = _sw.ToString();
var sb = _sw.GetStringBuilder();
sb.Clear();
}
if (!string.IsNullOrWhiteSpace(s))
{
writeTextToJira(s);
}
}

private void writeTextToJira(string text)
{
ensureClientAndValues();
var summary = "Log: " + this.Name; // the issue summary
var labels = new List<string> // some labels
{
this.Name, this.GetType().Name
};
var issue = new AnotherJiraRestClient.JiraModel.CreateIssue(project, summary, text, _issueTypeId, _priorityId, labels); // create the issue with type Issue and priority Trivial
var basicIssue = _jc.CreateIssue(issue);
_jc.Notify(basicIssue, _notifyUsers, "JiraAppender created an issue", null, null); // notify users of the issue's creation
}

/// <summary>
/// Make sure we have a JiraClient and that we know the ids of the Issue type and the Trivial priority
/// </summary>
private void ensureClientAndValues()
{
if (_jc == null)
{
_jc = new JiraClient(new JiraAccount
{
ServerUrl = url,
User = user,
Password = password
});
}
if (_priorityId==null) {
var priority = _jc.GetPriorities().FirstOrDefault(p => p.name == "Trivial");
if (priority == null)
{
throw new Exception("A priority with the name 'Trivial' was not found");
}
_priorityId = priority.id;
}
if (_issueTypeId == null)
{
var meta = _jc.GetProjectMeta(project);
var issue = meta.issuetypes.FirstOrDefault(i => i.name == "Issue");
if (issue == null)
{
throw new Exception("An issue type with the name 'Issue' was not found");
}
_issueTypeId = issue.id;
}
}

protected override void OnClose() //clean what you can and write to jira if there is anything to write
{
_ts.Cancel();
writeToJira(null);
_sw.Dispose();
_ts.Dispose();
_task = null;
base.OnClose();
}

}
}

As I said, AnotherJiraRestClient does not implement the notify API call needed to inform the users of the creation of an issue, so we need to change the project a little bit. Perhaps when you are implementing this, you will find notify already there, with a different format, but just in case you don't:
  • add to JiraClient the following method:
    public void Notify(BasicIssue issue, IEnumerable<string> userList, string subject, string textBody, string htmlBody)
    {
    var request = new RestRequest()
    {
    Method = Method.POST,
    Resource = ResourceUrls.Notify(issue.id),
    RequestFormat = DataFormat.Json
    };
    request.AddBody(new NotifyData
    {
    subject = subject,
    textBody = textBody,
    htmlBody = htmlBody,
    to = new NotifyTo
    {
    users = userList.Select(u => new User
    {
    active = false,
    name = u
    }).ToList()
    }
    });
    var response = client.Execute(request);
    if (response.StatusCode != HttpStatusCode.NoContent)
    {
    throw new JiraApiException("Failed to notify users from issue with id=" + issue.id+"("+response.StatusCode+")");
    }
    }
  • add to ResourceUrls the following method:
    public static string Notify(string issueId)
    {
    return Url(string.Format("issue/{0}/notify", issueId));
    }
  • create the following classes in the JiraModel folder and namespace:
    public class NotifyData
    {
    public string subject { get; set; }
    public string textBody { get; set; }
    public string htmlBody { get; set; }
    public NotifyTo to { get; set; }
    }

    public class NotifyTo
    {
    public List<User> users { get; set; }
    }

    public class User
    {
    public string name { get; set; }
    public bool active { get; set; }
    }

Here is an example configuration. Have fun!
<log4net>
<appender name="JiraAppender" type="JiraLog4netAppender.JiraAppender, JiraLog4netAppender">
<url value="https://my.server.url/jira"/>
<user value="jirauser"/>
<password value="jirapassword"/>
<project value="jiraproject"/>
<notify value="siderite,someotheruser" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
</layout>
<filter type="log4net.Filter.LevelRangeFilter">
<levelMin value="WARN" />
<levelMax value="FATAL" />
</filter>
</appender>
<root>
<level value="DEBUG" />
<appender-ref ref="JiraAppender" />
</root>
</log4net>

and has 1 comment
Intro (click to hide)
Sometimes, after making some software or another and feeling all proud that everything works, I get an annoying exception that the path of the files my program uses is too long, the horrible PathTooLongException. At first I thought it was a filesystem thing or an operating system thing, or even a .Net version thing, but it is not. The exception would appear in .Net 4.5 apps running on Windows 7 systems that used NTFS on modern hardware.

Lazy as any software dev, I googled for it and found a quick and dirty replacement for System.IO called Delimon that mitigated the issue. This library is trying to do most of what System.IO does, but using the so called extended-length paths, stuff that looks like \\?\C:\Windows, with external functions accessed directly from kernel32.dll. All good and nice, but the library is hardly perfect. It has bugs, it doesn't implement all of the System.IO functionality and feels wrong. Why would Microsoft, the great and mighty, allow such a ridiculous problem to persist in their core filesystem library?

And the answer is: because it is a core library. Probably they would have to make an enormous testing effort to change anything there. It is something from a managed code developer nightmare: unsafe access to system libraries, code that spans decades of work and a maze of internal fields, attributes, methods, properties that can be used only from inside the library itself. Not to mention all those people who decided to solve problems in core classes using reflection and stuff. My guess is that they probably want to replace file system usage with another API, like Windows.Storage, which, alas, are only used for Windows Phone.


In this blog post I will discuss the System.IO problems that relate to the total length of a path, what causes them and possible solutions (if any).


Let's start with the exception itself: PathTooLongException. Looking for usages of the exception in the mscorlib.dll assembly of .Net 4.0 we see some interesting things. First of all, there is a direct translation from Windows IO error code 206 to this exception, so that means that, in principle, there should be no managed code throwing this exception at all. The operating system should complain if there is a path length issue. But that is not the case in System.IO.

Most of the other usages of the exception come from the class PathHelper, a helper class used by the System.IO.Path class in a single method: NormalizePath. Wonderful method, that: internal static unsafe. PathHelper is like a multiple personality class, the active one being determined by the field useStackAlloc. If set to true, then it uses memory and speed optimized code, but assumes that the longest path will always be 260. That's a constant, it is not something read from the operating system. If set to false, the max path length is also provided as a parameter. Obviously, useStackAlloc is set to true in most situations. We will talk about NormalizePath a bit later.

The other usages of the PathTooLongException class come from two Directory classes: Directory and LongPathDirectory. If you instantly thought "Oh, God, I didn't know there was a LongPathDirectory class! We can use that and all problems disappear!", I have bad news for you. LongPathDirectory is an internal class. Other than that it seems to be a copy paste clone of Directory that uses Path.LongMaxPath instead of hardcoded constants (248 maximum directory name length, for example) or... Path.MaxPath. If you thought MaxPath and LongMaxPath are properties that can be set to fix long path problems, I have bad news for you: they are internal constants set to 260 and 32000, respectively. Who uses this LongPathDirectory class, though? The System.IO.IsolatedStorage namespace. We'll get back to this in a moment.

Back to Path.NormalizePath. It is a nightmare method that uses a lot of internal constants, system calls, convoluted code; it seems like someone deliberately tried to obfuscate its code. It's an internal method, of course, which makes no sense, as the functionality of path normalization would be useful in a lot of scenarios. Its first parameter is path, then fullCheck, which when true leads to extra character validation. The fourth parameter is expandShortPaths which calls the GetLongPathName function of kernel32.dll. The third parameter is more interesting, it specifies the maximum path length which is sent to PathHelper or makes local checks on the path length. But who uses this parameter?

And now we find a familiar pattern: there is a class (internal of course) called LongPath, which seems to be a clone of Path, only designed to work with long paths. Who uses LongPath? LongPathDirectory, LongPathFile and classes in the System.IO.IsolatedStorage namespace!


So, another idea becomes apparent. Can we use System.IO.IsolatedStorage to have a nice access to the file system? No we can't. For at least two reasons. First of all, the isolated storage paradigm is different from what we want to achieve, it doesn't access the raw file system, instead it isolates files in containers that are accessible to that machine, domain, application or user only. Second, even if we could get an "isolated" store that would represent the file system - which we can't, we would still have to contend with the string based way in which IsolatedStorage works. It is interesting to note that IsolatedStorage is pretty much deprecated by the Windows 8 Windows.Storage API, so forget about it. Yeah, so we have LongPathDirectory, LongPathFile and LongPath classes, but we can't really use them. Besides, what we want is something more akin to DirectoryInfo and FileInfo, which have no LongPath versions.

What can we do about it, then? One solution is to use Delimon. It has some bugs, but they can be avoided or fixed, either by the developers or by getting the source/decompiling the library and fixing the bugs yourself. A limited, but functional solution.
An interesting alternative is to use libraries the BCL team published for long path access: LongPath which seems to contain classes similar to the ones we find in mscorlib, but it's latest release is from 2010 or Zeta long paths which has a more recent release, 2013, but is completely different, using the FileInfo and DirectoryInfo paradigm, too.

Of course, you can always make your own API.

Another solution is to be aware of the places where the length limitation appears and avoid them via other type of development, in other words, a file system best practices compilation that eventually turns into a new file system API.

Both solutions coalesce into using some other library instead of System.IO. That's horrible and I think a stain on .Net's honor!


So let's see where the exception is actually thrown.

I've made some tests. First of all, I used FAR Manager, a file manager, to create folders of various lengths. The longest one was 255, before I got an exception. To my surprise, Windows Explorer could see it, but it could not open or copy/rename/delete it. I reduced the size of its name until the total size of the path was 260, then I could manipulate it in Windows Explorer. So there are external reasons for not creating paths as long, but we see that there are tools that can access files like that. Let's attempt to create some programatically.

System.IO.Directory.CreateDirectory immediately fires the exception. DirectoryInfo has no problem instantiating with the long path as the parameter, but the Create method throws the same exception. Any attempt to create a folder of more than 248 characters, even if the total path was less than 260 characters, failed as well.

However, with reflection to the rescue, I could create paths as long as 32000 characters and folders with names as long as 255 characters using our friend LongPathDirectory:
var longPathDirectoryType = typeof(System.IO.Directory).Assembly.GetTypes().First(t=>t.Name=="LongPathDirectory");
var createDirectoryMethodInfo = longPathDirectoryType.GetMethod("CreateDirectory", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.NonPublic);
createDirectoryMethodInfo.Invoke(null, new object[] { path });

What about files? FAR Manager threw the same errors if I tried to create a filename larger than 255 characters. Let's try to create the same programmatically.

File.Create threw the exception, as well as FileInfo.Create and the FileStream constructors.

So can we use the same method and use LongPathFile? No! Because LongPathFile doesn't have the creating and opening functionalities of File. Instead, FileStream has a constructor that specifies useLongPath. It is internal, of course, and used only by IsolatedStorageFileStream!

Code to create a file:
var fileStreamConstructorInfo = typeof(System.IO.FileStream).GetConstructor(System.Reflection.BindingFlags.NonPublic|System.Reflection.BindingFlags.Instance,null,
new Type[] {
typeof(string) /*path*/, typeof(System.IO.FileMode) /*mode*/, typeof(System.IO.FileAccess) /*access*/,
typeof(System.IO.FileShare) /*share*/, typeof(int) /*bufferSize*/, typeof(System.IO.FileOptions) /*options*/,
typeof(string) /*msgPath*/, typeof(bool) /*bFromProxy*/, typeof(bool) /*useLongPath*/, typeof(bool) /*checkHost*/},null);
var stream = (System.IO.Stream)fileStreamConstructorInfo.Invoke(new object[] {
path, System.IO.FileMode.Create, System.IO.FileAccess.Write,
System.IO.FileShare.None, 4096, System.IO.FileOptions.None,
System.IO.Path.GetFileName(path), false, true, false
});
Horrible, but it works. Again, no filenames bigger than 255 and the exception coming from the file system, as it should. Some info about the parameters: msgPath is the name of the file opened by the stream, if bFromProxy is true the stream doesn't try to demand some security permissions, checkHost... does nothing :) Probably someone wanted to add a piece of code there, but forgot about it.

Why did I use 4096 as the buffer size? Because that is the default value used by .Net when not specifying the value. Kind of low, right?

Now, this is some sort of midway alternative to using a third party file system library: you invoke through reflection code that is done by Microsoft and hidden for no good reason. I don't condone using it, unless you really need it. What I expect from the .Net framework is that it takes care of all this for me. It seems (as detailed in this blog post), that efforts are indeed made. A little late, I'd say, but still.

and has 1 comment
I seem to remember that I blogged about this before, but I can't find it anymore. Probably it was just a missed intention. This is simply a warning on how T-SQL converts FLOAT values to string. Here are some Transact SQL queries and their results:
DECLARE @aFloat FLOAT = 1234.123456789
DECLARE @aDecimal DECIMAL(18,9) = 1234.123456789
DECLARE @aNumeric NUMERIC(18,9) = 1234.123456789
DECLARE @aString NVARCHAR(20) = 1234.123456789

SELECT @aFloat,@aDecimal,@aNumeric,@aString -- result: 1234.123456789 1234.123456789 1234.123456789 1234.123456789
SELECT CAST(@aFloat as NVARCHAR(20)),CAST(@aDecimal as NVARCHAR(20)),CAST(@aNumeric as NVARCHAR(20)),CAST(@aString as NVARCHAR(20)) -- result: 1234.12 1234.123456789 1234.123456789 1234.123456789
Wait! What happened there? The FLOAT was the only numeric format that lost precision to only 2 decimals (it is actually a loss of scale, 12345.123456789 would be converted to 12345.1). The solution is either to either convert to DECIMAL or NUMERIC values before converting to NVARCHAR or to use the STR function, which receives the scale and precision parameters. Like this:
SELECT CAST(@aFloat as NVARCHAR(20)), CAST(CAST(@aFloat as DECIMAL(18,9)) as NVARCHAR(20)), STR(@aFloat,18,9) -- result: 1234.12    1234.123456789        1234.123456789

The first conversion to DECIMAL and the STR function later on are equivalent.

I have looked into SQL options to somehow set the default precision that is used when converting a float to string, but I could not find anything usefule. Neither did settings like
SET ARITHIGNORE OFF
SET NUMERIC_ROUNDABORT ON
SET ARITHABORT ON
have any effect on the queries above. No error and the same result.

You don't want to know what happens with 123456789.123456789!
SELECT @aFloat, CAST(@aFloat as NVARCHAR(20)), CAST(CAST(@aFloat as DECIMAL(30,10)) as NVARCHAR(30)), STR(@aFloat,30,10) -- result: 123456789.123457    1.23457e+008    123456789.1234567900    123456789.1234567900

Not only the digits are cut even when selecting the actual value!!, but the scientific notation rears its ugly head. And look at the beautiful STR function returning ugly extra zeroes! Same issue appears when trying to use XML functions. The resulting XML has really ugly strings of the float values.

Bottom line: as much as I hate it, you probably should not use FLOAT when trying to display values. Ever.

I've heard of for some time now, but never actually tried anything on the site. Today I tried a course that teaches the basics of R, a statistics programming language akin to Matlab, and I thought the site was great. I suspect it all depends on the quality of the course, but at least this one was very nice. You can see my "report card" here, although I doubt I am going to visit the site very often. However, for beginners or people who quickly want to "get" something, it is a good place to start, as it gives one a "hands on" experience, like actually coding to get results, but in a carefully explained step by step tutorial format.

I was working on a pretty nice task that involved translating the text in a page in real time. For this I created a one page function that would do magic on elements that were added or changed in the page. On specific pages it moved with abysmal speed and I had no idea why. So I went to profile the thing and I was shocked to see that the problem did not come from my long piece of code, but from a simple encapsulation of an element in a jQuery object. I was using it only to have a nicer interface for getting the name of the element and changing an attribute. Here is the code:
var j=jQuery(elem);
if (j.is('img[alt]')) {
j.attr('alt',translate(j.attr('alt')));
}

Replaced it with:
if (/^img$/i.test(elem.tagName)) {
var alt=elem.getAttribute('alt');
if (alt) {
elem.setAttribute('alt',translate(alt));
}
}

And it worked very fast indeed. The element might have been body so maybe the encapsulation tries to also parse the children or something like that or perhaps the problem was fixed with later versions of the library. However, think about how many times we used this kind of code without thinking twice about it. Think twice about it! :)

If you read an Angular book or read a howto, you will think that Angular is the greatest discovery since fire. Everything is neatly stacked in modules, controllers, templates, directives, factories, etc. The problem comes when you want to use some code of your own, using simple Javascript that does specific work, and then you want to link it nicely with AngularJS. It is not always easy. My example concerns the simple display of a dialog which edits an object. I want it to work on every page, so I added it to the general layout template. The layout does not have a controller. Even if I add it, the dialog engine I have been using was buggy and I've decided to just use jQuery.dialog.

So here is my conundrum: How to load the content of a dialog from an Angular template, display it with jQuery.dialog, load the information with jQuery.get, then bind its input elements to an Angular scope object. I've tried the obvious: just load the template in the dialog and expect Angular to notice a new DOM element was added and parse it and work its magic. It didn't work. Why can't I just call an angular.refresh(elem); function and get it over with, I thought. There are several other solutions. One is to not create the content dynamically at all, just add it to the layout, mark it with ng-controller="something" and then, in the controller, save the object you are interested in or the scope as some sort of globally accessible object that you instantiate from jQuery.get. The dialog would just move the element around, afterwards. That means you need to create a controller, maybe in another file, to be nice, then load it into your page. Another is to create some sort of directive or script tag that loads the Angular template dynamically and to hope it works.

Long story short, none of these solutions appealed to me. I wanted a simple refresh(elem) function. And there is one. It is called angular.injector. You call it with the names of the modules you need to load ('ng' one of them and usually the main application module the second). The result is a function that can use invoke to get the same results as a controller constructor. And that is saying something: if you can do the work that the controller does in your block of code, you don't need a zillion controllers making your life miserable, nor do you need to mark the HTML uselessly for very simple functionality.

Without further ado, here is a function that takes as parameters an element and a data object. The function will force angular to compile said element like it was part of the angular main application, then bind to the main scope the properties of the data object:

function angularCompile(elem, data) {
// create an injector
var $injector = angular.injector(['ng','app']);

// use the type inference to auto inject arguments, or use implicit injection
$injector.invoke(function($rootScope, $compile, $document){
var compiled = $compile(elem || $document);
compiled($rootScope);
if (data) {
for (var k in data) {
if (data.hasOwnProperty(k)) {
$rootScope[k]=data[k];
}
}
}
$rootScope.$digest();
});
}


Example usage:

angularCompile(dialog[0],{editedObject: obj}); // will take the jQuery dialog element, compile it, and add to the scope the editedObject property with the value of obj.


Full code:

OpenTranslationDialog=function(Rule, onProceed, onCancel) {
jQuery.ajax({
type: 'GET',
url: '/Content/ng-templates/dialogs/Translation.html',
data: Rule,
success: function(data) {
var dialog=jQuery('<div></div>')
.html(data)
.dialog({
resizable:true,
width:700,
modal:true,
buttons: {
"Save": function() {
var url='/some/api/url';
jQuery.ajax({
type:'PUT',
url:url,
data:Rule,
success:function() {
if (onProceed) onProceed();
$(this).dialog( "close" );
},
error:function() {
alert('There was an error saving the rule');
}
});
},
Cancel: function() {
if (onCancel) onCancel();
$(this).dialog( "close" );
}
}
});

angularCompile(dialog[0],{Rule:Rule});
},
error:function() {
alert('There was an error getting the dialog template');
}
});
}


Before you take my word on it, though, beware: I am an Angular noob and my desire here was to hack away at it in order to merge my own code with the nice structured code of my colleagues, who now hate me. Although they liked angular.injector when I showed it to them :)

Update 2015 August 28: I've replaced the function master.sys.fn_varbintohexstr with CONVERT, with the extra parameter 2, which translates a binary field into a hexadecimal string with no leading 0x. In addition to being ugly to use, fn_varbintohexstr is very slow.

Sometimes you need to create a unique identifier for a bunch of values so that you use it as an ID in the database. The immediately obvious choice is the CHECKSUM and BINARYCHECKSUM functions. But beware, the purpose of these functions is to detect changes in a string, not to uniquely identify it. It might seem strange, but the two concepts are very different. The change modification functionality is only meant to generate very different values on small changes. The uniqueness is trying to create a value as distinctive as possible for any string. That is why when you use a checksum you will get a lot of similar values for (very) different strings.

Enter HASHBYTES, another function that has the purpose of creating a cryptographic hash for a string. It is mainly used for password hashing, but it will fit nicely for our purpose. There are some caveats, though. First, CHECKSUM gets a variable number of parameters, HASHBYTES only accepts one, so we must take care of the cumbersome concatenation of multiple values. Unfortunately SQL functions do not have the option of variable parameters, which is truly a shame, so we can't hack it. Also, the value that HASHBYTES returns is a varbinary. We could cast it to NVARCHAR, but it turns into a weird Chinese characters string. In order to turn it into a proper string, we need to use the same function used by SQL Server to display varbinary when selecting it: master.sys.fn_varbintohexstr the CONVERT function with a parameter of 2 (hex string without the leading 0x).

So let's compare the two usages. Suppose we have this nice table that contains company data: company name, contact first name, contact last name, phone, email, yearly value. We need to create a unique ID based on these values.
First CHECKSUM:
SELECT CHECKSUM(companyName, firstName, lastName, phone, email, yearlyValue) FROM OurTable
So easy! Just add the columns, no matter how many or what type they have, and get a value as a result. You can even use * to select all columns in a row. You also have the advantage of getting the same checksum for differently capitalized strings. If you don't want this behaviour, use BINARYCHECSUM, which works even better.

Second HASHBYTES:
SELECT CONVERT(VARCHAR(Max),HASHBYTES('SHA1',companyName+'|'+firstName+'|'+lastName+'|'+phone+'|'+email+'|'+CAST(yearlyValue as NVARCHAR(100))),2) as id,*
FROM OurTable
Ugly! You need to create a string from different types, using ugly casts. Also, this works more like BINARYCHECKSUM. If you want to get the same functionality as CHECKSUM you need to use LOWER(LTRIM(RTRIM(value))). Horrid!
However, it works.

WARNING: using CAST to NVARCHAR from a FLOAT loses precision. You should use STR instead!

A middle solution is to use XCHECKSUM. What is that, you ask? A placeholder that can be replaced with some regular expression search and replace, of course :)

Update: I've created a query that creates the script to update the value of a column called 'ValuesHash', for tables that have it, with the hash of all columns that are not in a list of names, they are not primary keys and they are not foreign keys, plus they are not computed, rowguidcol or filestream.
Imagine the scenario where you have something like this:
  • Table A:
    1. Id: primary identity key
    2. Data1: some data
    3. Data2: some data
    4. CreateTime: the creation time
    5. ValuesHash: a VARBINARY(50) column - only 20 are required normally, but let's make sure :)
  • Table B:
    1. Id: primary identity key
    2. AId: foreign key to A
    3. Data1: some data
    4. Data2: some data
    5. ModifyTime: the modification time
    6. ValuesHash: a VARBINARY(50) column - only 20 are required normally, but let's make sure :)
  • Table C:
    1. Id: primary identity key
    2. AId: foreign key to A
    3. Data1: some data
    4. Data2: some data
The query below will update ValuesHash for A and B (because C doesn't have the ValuesHash column) with a hash constructed from the Data columns. The Id columns will be ignored for being primary keys (and for being in the list of columns to ignore), the AId columns will be ignored for being foreign keys, ValuesHash and CreateTime and ModifyTime will be ignored for being in a list of custom columns)

WARNING: each column data is always truncated to 4000 characters, then the corresponding string is also truncated to 4000 bytes before running HASHBYTES (which only accepts a maximum of 8000 bytes). This hash will help in determining unique records, but it is not 100%.

SELECT * 
FROM (
SELECT t.name,
'UPDATE [' + t.name+ '] SET ValuesHash = HASHBYTES(''SHA1'',SUBSTRING('
+ Stuff(
(SELECT '+ ''|''+ ISNULL('+CASE
WHEN tp.name IN ('float', 'real') THEN 'STR('+c.name+',30,30)'
WHEN tp.name IN ('binary', 'varbinary') THEN 'CONVERT(NVARCHAR(4000),'+c.name+',2)'
ELSE 'CONVERT(NVARCHAR(4000),'+c.name+')' END+','''')'
FROM sys.all_columns c
INNER JOIN sys.types tp
ON c.system_type_id=tp.system_type_id
AND c.user_type_id=tp.user_type_id
LEFT JOIN sys.index_columns ic
ON ic.object_id=t.object_id
AND ic.column_id=c.column_id
LEFT JOIN sys.indexes i
ON ic.object_id=i.object_id
AND ic.index_id=i.index_id
LEFT JOIN sys.foreign_key_columns fc
ON fc.parent_object_id=t.object_id
AND c.column_id=fc.parent_column_id
WHERE t.object_id=c.object_id
AND ISNULL(c.is_identity, 0)=0
AND ISNULL(c.is_computed, 0)=0
AND ISNULL(c.is_filestream, 0)=0
AND ISNULL(c.is_rowguidcol, 0)=0
AND ISNULL(i.is_primary_key, 0)=0
AND fc.parent_column_id IS NULL
AND c.name NOT IN ('Id', 'CreateTime' , 'AcquireTime' , 'IntermediateCreateTime', 'IntermediateModifyTime', 'IntermediateDeleteTime', 'ValuesHash')
ORDER BY Sign(c.max_length) DESC, c.max_length, Lower(c.name)
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
, 1, 7, '')
+ ',0,4000)) WHERE ValuesHash IS NULL' AS computed
FROM sys.tables t
INNER JOIN sys.all_columns c
ON t.object_id = c.object_id
WHERE c.name = 'ValuesHash') x
WHERE computed IS NOT NULL
ORDER BY name

Change it to suit your needs. It is by no means perfect, but it's a start for whatever you need.

Update:

A new FORMAT function was introduced in SQL Server 2012, working somewhat similar to the .NET ToString method. Using that function is slightly more precise:

SELECT * 
FROM (
SELECT t.name,
'UPDATE [' + t.name+ '] SET ValuesHash = HASHBYTES(''SHA1'',SUBSTRING('
+ Stuff(
(SELECT '+ ''|''+ ISNULL('+CASE
WHEN tp.name IN ('float', 'real') THEN 'FORMAT('+c.name+',''R'')'
WHEN tp.name IN ('decimal') THEN 'FORMAT('+c.name+',''G'')'
WHEN tp.name IN ('datetime','datetime2') THEN 'FORMAT('+c.name+',''O'')'
WHEN tp.name IN ('binary', 'varbinary') THEN 'CONVERT(NVARCHAR(4000),'+c.name+',2)'
ELSE 'CONVERT(NVARCHAR(4000),'+c.name+')' END+','''')'
FROM sys.all_columns c
INNER JOIN sys.types tp
ON c.system_type_id=tp.system_type_id
AND c.user_type_id=tp.user_type_id
LEFT JOIN sys.index_columns ic
ON ic.object_id=t.object_id
AND ic.column_id=c.column_id
LEFT JOIN sys.indexes i
ON ic.object_id=i.object_id
AND ic.index_id=i.index_id
LEFT JOIN sys.foreign_key_columns fc
ON fc.parent_object_id=t.object_id
AND c.column_id=fc.parent_column_id
WHERE t.object_id=c.object_id
AND ISNULL(c.is_identity, 0)=0
AND ISNULL(c.is_computed, 0)=0
AND ISNULL(c.is_filestream, 0)=0
AND ISNULL(c.is_rowguidcol, 0)=0
AND ISNULL(i.is_primary_key, 0)=0
AND fc.parent_column_id IS NULL
AND c.name NOT IN ('Id', 'CreateTime' , 'AcquireTime' , 'IntermediateCreateTime', 'IntermediateModifyTime', 'IntermediateDeleteTime', 'ValuesHash')
ORDER BY Sign(c.max_length) DESC, c.max_length, Lower(c.name)
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
, 1, 7, '')
+ ',0,4000)) WHERE ValuesHash IS NULL' AS computed
FROM sys.tables t
INNER JOIN sys.all_columns c
ON t.object_id = c.object_id
WHERE c.name = 'ValuesHash'
) x
WHERE computed IS NOT NULL
ORDER BY name

and has 0 comments
It just happens that I have two different projects that have the need of cluster analysis, applied in two different ways: one has uses on maps, where a large number of items needs to be displayed quickly, while another implies finding clusters of news items, where the distance between them is determined by their content. The most used clustering algorithm and the first to be found by searching the web is the k-means clustering. Its purpose is to categorize a list of items into a number of k clusters, hence the name. Setting aside the use value of the algorithm for my purposes, the biggest problem I see is the complexity: in its general form it is at least O(n2), and most of the time a lot higher. The net abounds with scientific papers investigating the k-means complexity and suggesting improvements, but they are highly mathematical and I didn't have the time to investigate further. So I just built my own algorithm. It is clearly fuzzy, imperfect, may even be wrong in some situations, but at least it is fast. I will certainly investigate this area more, maybe even try to understand the math behind it and analyse my results based on this research. When I do that I will update this post or write others. But until then, let me present my work so far.

The first problem I had was, as I said, complexity. For one million points on the map, any algorithm that takes into account the distance between any two items will have to make at least one trillion comparisons. So my solution was to limit the number of items by grouping them in a grid:
Step 1: find the min and max on each dimension (that means going through the entire item collection once or knowing beforetime the map bounds)
Step 2: determine a number of cells that would be a bit more than what I need in the end. (that's a decision I have to take, no algorithmic complexity)
Example: for my map example I have only two dimensions: X and Y. I want to display an upper bound of 1000 clusters. Therefore I find the minimum and maximum X and Y and then split each dimension into 100 slots. That means I would cluster the items I have into 10000 cells.
Step 3: for each item, find its cell based on X,Y and add the item to the cell. This is done by simple division: (X-minX)/(maxX-minX). (again that means going once through the collection)
Step 4: find the smallest cell (the complexity is reduced now to working with cells)
Step 5: find its smallest neighbour (the complexity of this on the implementation)
Step 6: merge the two cells
Until the number of cells is larger than the desired number of clusters, repeat from Step 4.
In the end, the algorithm is O(n+p*log(p)), I guess, where p is the number of cells chosen at step 2.

Optimizations are the next issue.
  • How does one find the neighbours of a cell? On Step 3 we also create a list of neighbors for each new cluster by looking for a cluster that is at coordinates immediately above, below, left or right. When we merge two clusters, we get a cluster that is a neighbour to all the neighbours of the merged clusters.
  • How does one quickly get the cluster at a certain position? We create a dictionary that has the coordinates as the key. What about when we merge two clusters? Then the new cluster will be accessible by any of the original cluster keys (that implied that each cluster has a list of keys, as well)
  • How does one find the smallest cell in the cluster list? After Step 3 we sort the cluster list by the number of items they contain and each time we perform a merge we find the first item larger than the merged result and we insert it in the list at that location, so that the list always remains sorted.
  • How do we easily find the first item larger than an item? By employing a divide-et-impera method of splitting the list in two at each step and choosing to look into one bucket based on the item count of the cluster at the middle position

Before you use the code note that there are specific scenarios where this type of clustering would look a bit off, like items in a long line or an empty polygon (the cluster will appear to be in its center). But I needed speed and I got it.

Enjoy!

Update: The performance of removing or adding items from a List is very poor, so I created a LinkedList version that seems to be even faster. Here it is. The old List version is at the end

/// <summary>
/// Generic x,y positioned item class
/// </summary>
public class Item
{
public double X { get; set; }
public double Y { get; set; }
}

public class ClusteringDataProcessor
{
/// <summary>
/// the squared root of the initial cell number (100*100 = 10000)
/// </summary>
private const int initialClustersSquareSide = 100;
/// <summary>
/// the desired number of resulting clusters
/// </summary>
private const int numberOfFinalClusters = 1000;
private static Random _rnd = new Random();

/// <summary>
/// In this implementation, the Cluster inherits from Item, so the result is a list of Item
/// In the case of one Item Clusters, we actually return the original Item
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public List<Item> Process(List<Item> list)
{
if (list.Count <= numberOfFinalClusters) return list;
// find bounds. If already known, this can be provided as parameters
double minY = double.MaxValue;
double minX = double.MaxValue;
double maxY = double.MinValue;
double maxX = double.MinValue;
foreach (var item in list)
{
var y = item.Y;
var x = item.X;
minY = Math.Min(minY, y);
maxY = Math.Max(maxY, y);
minX = Math.Min(minX, x);
maxX = Math.Max(maxX, x);
}
// the original list of clusters
var clusterArr = new List<Cluster>();

// the dictionary used to index clusters on their position
var clusterDict = new Dictionary<string, Cluster>();

// the unit for finding the cell position for the initial clusters
var qX = (maxX - minX) / initialClustersSquareSide;
var qY = (maxY - minY) / initialClustersSquareSide;

foreach (var item in list)
{
// compute cell coordinates (integer and the values used as keys in the dictionary)
var cx = Math.Min((int)((item.X - minX) / qX), initialClustersSquareSide - 1);
var cy = Math.Min((int)((item.Y - minY) / qY), initialClustersSquareSide - 1);
var key = getKey(cx, cy);
Cluster cluster;
// if the cluster for this position does not exist, create it
if (!clusterDict.TryGetValue(key, out cluster))
{
cluster = new Cluster
{
Keys = new List<string> { key },
X = minX + cx * qX + qX / 2,
Y = minY + cy * qY + qY / 2,
//Items = new List<Item>(),
Count = 0,
Neighbors = new List<string>()
};
// the neighbours of this cluster are the existing clusters that are below, above, left or right. If they exist, this cluster also is added to their neighbour list
var nkeys = new[] { getKey(cx - 1, cy), getKey(cx + 1, cy), getKey(cx, cy - 1), getKey(cx, cy - 1) };
for (var j = 0; j < 4; j++)
{
Cluster nc;
if (clusterDict.TryGetValue(nkeys[j], out nc))
{
cluster.Neighbors.Add(nkeys[j]);
nc.Neighbors.Add(key);
}
}
clusterDict[key] = cluster;
clusterArr.Add(cluster);
}
// add the item to the cluster (note that the commented lines hold the items in a list, while the current implementation only remember the number of items)
//cluster.Items.Add(item);
cluster.Item = item;
cluster.Count++;
// add the item position to the sums, so that we can compute the final position of the cluster at the Finalize stage without enumerating the items (or having to hold them in an Items list)
cluster.SumX += item.X;
cluster.SumY += item.Y;
}
// if the number of items is already smaller than the desired number of clusters, just return the clusters
if (clusterArr.Count <= numberOfFinalClusters)
{
return clusterArr.Select(c => c.Finalize()).ToList();
}

// sort the cluster list so we can efficiently find the smallest cluster
//clusterArr.Sort(new Comparison<Cluster>((c1, c2) => c1.Items.Count.CompareTo(c2.Items.Count)));
LinkedList<Cluster> clusterLinkedList = new LinkedList<Cluster>(clusterArr.OrderBy(c => c.Count));

// remember last merged cluster, as next merged clusters might have similar sizes
var lastCount = int.MaxValue;
LinkedListNode<Cluster> lastLinkedNode = null;

// do this until we get to the desired number of clusters
while (clusterLinkedList.Count > numberOfFinalClusters)
{
// we need to get the smallest (so first) cluster that has any neighbours
var cluster1 = clusterLinkedList.First(c => c.Neighbors.Any());
Cluster cluster2 = null;
// then find the smallest neighbour
var min = int.MaxValue;
foreach (var nkey in cluster1.Neighbors)
{
var n = clusterDict[nkey];
//var l = n.Items.Count;
var l = n.Count;
if (l < min)
{
min = l;
cluster2 = n;
}
}
// join the clusters
var keys = cluster1.Keys.Union(cluster2.Keys).ToList();
var cluster = new Cluster
{
Keys = keys,
// approximate cluster position, not needed
//X = (cluster1.X + cluster2.X) / 2,
//Y = (cluster1.Y + cluster2.Y) / 2,

// the result holds the count of both clusters
//Items = cluster1.Items.Union(cluster2.Items).ToList(),
Count = cluster1.Count + cluster2.Count,
// the neighbors are in the union of their neighbours that does not contain themselves
Neighbors = cluster1.Neighbors.Union(cluster2.Neighbors)
.Distinct()
.Except(keys)
.ToList(),
// compute the sums for the final position
SumX = cluster1.SumX + cluster2.SumX,
SumY = cluster1.SumY + cluster2.SumY
};
foreach (var key in keys)
{
clusterDict[key] = cluster;
}
// efficiently remove clusters since LinkedList removals are fast
clusterLinkedList.Remove(cluster1);
clusterLinkedList.Remove(cluster2);

// a little bit of magic to make the finding of the insertion point faster (LinkedLists go through the entire list to find an item)
// if the last merged cluster is smaller or equal to the new merged cluster, then start searching from it.
// this halves the insert time operation, but I am sure there are even better implementations, just didn't think it's so important
LinkedListNode<Cluster> start;
if (lastCount <= cluster.Count && lastLinkedNode.Value != cluster1 && lastLinkedNode.Value != cluster2)
{
start = lastLinkedNode;
}
else
{
start = clusterLinkedList.First;
}
var insertionPoint = nextOrDefault(clusterLinkedList, start, c => c.Count >= cluster.Count);
// remember last merged cluster
LinkedListNode<Cluster> link;
if (insertionPoint == null)
{
link = clusterLinkedList.AddLast(cluster);
}
else
{
link = clusterLinkedList.AddBefore(insertionPoint, cluster);
}
lastLinkedNode = link;
lastCount = cluster.Count;
}
return clusterLinkedList.Select(c => c.Finalize()).ToList();
}

private LinkedListNode<T> nextOrDefault<T>(LinkedList<T> list, LinkedListNode<T> start, Func<T, bool> condition)
{
while (start.Next != null)
{
if (condition(start.Value)) return start;
start = start.Next;
}
return null;
}

private string getKey(int cx, int cy)
{
return cx + ":" + cy;
}

private class Cluster : Item
{
public Cluster()
{
SumX = 0;
SumY = 0;
}

public double SumX { get; set; }
public double SumY { get; set; }

public List<string> Keys { get; set; }

//public List<Item> Items { get; set; }
public int Count { get; set; }
public Item Item { get; set; }

public List<string> Neighbors { get; set; }


/// <summary>
/// the function that finalizes the computation of the values or even decides to return the only item in the cluster
/// </summary>
/// <returns></returns>
public Item Finalize()
{
//if (Items.Count == 1) return Items[0];
if (Count == 1) return Item;
/*Y = SumY / Items.Count;
X = SumX / Items.Count;
Count = Items.Count;*/
Y = SumY / Count;
X = SumX / Count;
Count = Count;
return this;
}
}
}

old List based code (click to show)

and has 2 comments
Well, sometimes an admin will try to make the system secure by annoying the people who have to use it. Yeah, that always works. My situation is that I have to login every day into a virtual machine that is on a "secure network". So after using a very restrictive password policy that forces everybody to be creative in the way they write "password" and "123456", he also disallowed the saving credentials in Remote Desktop Connection. So every day I have to enter the damn complicated password. I couldn't have that. Here is a .js script that you execute with WScript and it logs you in automatically:
var shell = WScript.CreateObject("WScript.Shell");
shell.Run("mstsc /v:[remote server] /console");
while (!shell.AppActivate("Windows Security")) {
WScript.Sleep(100);
}
WScript.Sleep(100);
shell.SendKeys("[password]{enter}");

Save this into a Javascript file and replace [remove server] and [password] with your settings and either double click the .js file or create a batch file like this:
@echo off
start "Auto log on!" wscript c:\Batches\autologin.js

Of course, this means your secure password will be stored in a stupid text file somewhere, so be warned.

This is one of those WTF moments. After more than a decade of working in software development I learn something this basic about T-SQL (or rather, any flavour based on SQL-92). What would you think happens when running this script?
IF ''='                 ' SELECT 'WTF?!' -- empty string compared to a bunch of spaces
IF ' '=' ' SELECT 'WTF?!' -- bunch or spaces compared to another bunch of spaces of different length
IF 'x'='x ' SELECT 'WTF?!' -- 'x' compared to 'x' followed by a bunch of spaces
IF 'x'=' x' SELECT 'WTF?!' -- 'x' compared to 'x' preceded by a bunch of spaces

There will be three WTF rows returned, for the first three queries. You don't believe me? Try it yourself. The motive is explained here: INF: How SQL Server Compares Strings with Trailing Spaces. Short story shorter: in order for SQL to compare two strings of different lengths, it first right-pads the shorter one with spaces.

So what can you do to fix it? Easy enough, use LEN ,right? Nope. Read the definition carefully: Returns the number of characters of the specified string expression, excluding trailing blanks. A possible but weird solution is to use DATALENGTH. A string is empty only is it has a datalength of 0. In the case of NVARCHAR you could even divide the resulting number to 2 in order to get the true length of the string. WTF, right?

and has 1 comment
Well, it's pretty obvious, but I wanted to post it here, as well. You see, we have this query that was supposed to filter some values based on a parameter. The query was done like this: SELECT * FROM table WHERE value=(CASE WHEN @filter IS NULL THEN value ELSE @filter). Can you spot the problem? Indeed, if value is NULL, then value is NOT equal to value. Not only is this incorrect, but also bad from the standpoint of the SQL execution plan. It is much faster to do SELECT * FROM table WHERE (@filter is NULL OR value=@filter). If, for whatever reason, you need the first syntax, you need to do it like this: SELECT * FROM table WHERE ISNULL(value,@impossibleValueThatIsNotNull)=COALESCE(@filter, value, @impossibleValueThatIsNotNull). Yeah, a bit of a show off, but when the "no filter" value is null, it's better to use ISNULL and COALESCE wherever possible.

I have at work a very annoying HTTP proxy that requires a basic authentication. This translates in very inconsistent behaviour between applications and the annoying necessity of entering the username and password whenever the system wants it. So I've decided to add another local proxy to the chain that handles the authentication for me forever.

This isn't as easy as it sounds. Sure, proxies are a dime a dozen, but for some reason most of them seem to be coming from the Linux world. That is really bad user interaction, vague documentation and unhelpful forums where any request for help ends up in some version of "read the fucking manual". Hence I've decided to help out by creating this lovely post that explains how you can achieve the purpose described above with very little headache. These are the very easy steps that you have to undertake:
  1. Download and install Privoxy
  2. Go to the Program Files folder and look for Privoxy. You will need to edit two files: config.txt and user.action
  3. Optional: change the listen-address port, otherwise the proxy will function on port 8118
  4. Enter your proxy authentication username and password in the fields below and press Help me configure Privoxy - this is strictly a client base Javascript so don't worry that I am going to steal your proxy credentials...
  5. Edit user.action and add the bit of text that appeared as destined for that file.
  6. Edit config.txt, look for examples of forward and add to it the bit that belongs to config.txt and replace proxy:port and the domains and IP masks with the correct values for you
  7. Restart Privoxy
  8. Configure your internet settings to use a proxy on 127.0.0.1 and the port you configured in step 2 (or the default 8118)

This should be it. Enjoy!

Username:

Password:





I made a function in T-SQL that parsed some string and returned a decimal. It all worked fine until one of my colleagues wanted to use it on the test server. And here there was, a beautiful error message: 'decimal' is not a recognized built-in function name. I isolated the line, executed it, same error. It was like the server did not understand decimals anymore. The line in question was a simple SELECT TRY_CONVERT(DECIMAL(18,6),'10.33'). If I used CONVERT, though, it all worked fine. Another hint was that the function worked perfectly fine on the same server, in another database. The problem was that for that particular database, the defined SQL server version was 2008, not 2012. We changed it and it all worked fine after that. The setting in question is found in the Properties of the database, Options, Compatibility level.

While working on a small personal project, I decided to make a graphical tool that displayed a list of items in a Canvas. After making it work (for details go here), I've decided to make the items animate when changing their position. In my mind it had to be a simple solution, akin to jQuery animate or something like that; it was not.

The final solution was to finally give up on a generic method for this and switch to the trusted attached properties. But if you are curious to see what else I tried and how ugly it got, read it here:
Click here to get ugly!

Well, long story short: attached properties. I created two attached properties CanvasLeft and CanvasTop. When they change, I animate the real properties and, at the end of the animation, I set the value. Here is the code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Media.Animation;

namespace Siderite.AttachedProperties
{
public static class UIElementProperties
{
public static readonly DependencyProperty CanvasLeftProperty = DependencyProperty.RegisterAttached("CanvasLeft", typeof(double), typeof(UIElementProperties), new FrameworkPropertyMetadata(
0.0,
FrameworkPropertyMetadataOptions.OverridesInheritanceBehavior,
CanvasLeftChanged));

[AttachedPropertyBrowsableForType(typeof(UIElement))]
public static double GetCanvasLeft(DependencyObject element)
{
if (element == null)
{
throw new ArgumentNullException("element");
}
return (double)element.GetValue(CanvasLeftProperty);
}

[DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)]
public static void SetCanvasLeft(DependencyObject element, double value)
{
if (element == null)
{
throw new ArgumentNullException("element");
}
element.SetValue(CanvasLeftProperty, value);
}

private static void CanvasLeftChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
{
var sb = new Storyboard();
var oldVal = (double)e.OldValue;
if (double.IsNaN(oldVal)) oldVal = 0;
var newVal = (double)e.NewValue;
if (double.IsNaN(newVal)) newVal = oldVal;
var anim = new DoubleAnimation
{
From = oldVal,
To = newVal,
Duration = new Duration(TimeSpan.FromSeconds(1)),
FillBehavior = FillBehavior.Stop
};
Storyboard.SetTarget(anim, d);
Storyboard.SetTargetProperty(anim, new PropertyPath("(Canvas.Left)"));
sb.Children.Add(anim);
sb.Completed += (s, ev) =>
{
d.SetValue(Canvas.LeftProperty, newVal);
};
var fe = d as FrameworkElement;
if (fe != null)
{
sb.Begin(fe, HandoffBehavior.Compose);
return;
}
var fce = d as FrameworkContentElement;
if (fce != null)
{
sb.Begin(fce, HandoffBehavior.Compose);
return;
}
sb.Begin();
}


public static readonly DependencyProperty CanvasTopProperty = DependencyProperty.RegisterAttached("CanvasTop", typeof(double), typeof(UIElementProperties), new FrameworkPropertyMetadata(
0.0,
FrameworkPropertyMetadataOptions.OverridesInheritanceBehavior,
CanvasTopChanged));

[AttachedPropertyBrowsableForType(typeof(UIElement))]
public static double GetCanvasTop(DependencyObject element)
{
if (element == null)
{
throw new ArgumentNullException("element");
}
return (double)element.GetValue(CanvasTopProperty);
}

[DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)]
public static void SetCanvasTop(DependencyObject element, double value)
{
if (element == null)
{
throw new ArgumentNullException("element");
}
element.SetValue(CanvasTopProperty, value);
}

private static void CanvasTopChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
{
var sb = new Storyboard();
var oldVal = (double)e.OldValue;
if (double.IsNaN(oldVal)) oldVal = 0;
var newVal = (double)e.NewValue;
if (double.IsNaN(newVal)) newVal = oldVal;
var anim = new DoubleAnimation
{
From = oldVal,
To = newVal,
Duration = new Duration(TimeSpan.FromSeconds(1)),
FillBehavior = FillBehavior.Stop
};
Storyboard.SetTarget(anim, d);
Storyboard.SetTargetProperty(anim, new PropertyPath("(Canvas.Top)"));
sb.Children.Add(anim);
sb.Completed += (s, ev) =>
{
d.SetValue(Canvas.TopProperty, newVal);
};
var fe = d as FrameworkElement;
if (fe != null)
{
sb.Begin(fe, HandoffBehavior.Compose);
return;
}
var fce = d as FrameworkContentElement;
if (fce != null)
{
sb.Begin(fce, HandoffBehavior.Compose);
return;
}
sb.Begin();
}
}
}

and this is how you would use them:

<ListView ItemsSource="{Binding KernelItems}" 
SelectedItem="{Binding SelectedItem,Mode=TwoWay}"
SelectionMode="Single"
>
<ListView.ItemsPanel>
<ItemsPanelTemplate>
<Canvas HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Background="Black" />
</ItemsPanelTemplate>
</ListView.ItemsPanel>
<ListView.ItemContainerStyle>
<Style TargetType="{x:Type ListViewItem}">
<Setter Property="FocusVisualStyle" Value="{x:Null}"/>
<Setter Property="Foreground" Value="White"/>
<Setter Property="att:UIElementProperties.CanvasLeft" >
<Setter.Value>
<MultiBinding Converter="{StaticResource CoordinateConverter}">
<Binding Path="X"/>
<Binding Path="ActualWidth" ElementName="lvKernelItems"/>
<Binding Path="DataContext.Zoom" RelativeSource="{RelativeSource AncestorType={x:Type Window}}"/>
</MultiBinding>
</Setter.Value>
</Setter>
<Setter Property="att:UIElementProperties.CanvasTop" >
<Setter.Value>
<MultiBinding Converter="{StaticResource CoordinateConverter}">
<Binding Path="Y"/>
<Binding Path="ActualHeight" ElementName="lvKernelItems"/>
<Binding Path="DataContext.Zoom" RelativeSource="{RelativeSource AncestorType={x:Type Window}}"/>
</MultiBinding>
</Setter.Value>
</Setter>
<Style.Resources>
<SolidColorBrush x:Key="{x:Static SystemColors.HighlightBrushKey}" Color="Transparent" />
<SolidColorBrush x:Key="{x:Static SystemColors.ControlBrushKey}" Color="Transparent" />
<SolidColorBrush x:Key="{x:Static SystemColors.HighlightTextBrushKey}" Color="Black" />
<SolidColorBrush x:Key="{x:Static SystemColors.ControlTextBrushKey}" Color="Black" />
</Style.Resources>
<Style.Triggers>
<Trigger Property="IsSelected" Value="True">
<Setter Property="Foreground" Value="Cyan"/>
<Setter Property="Effect">
<Setter.Value>
<DropShadowEffect ShadowDepth="0" Color="White" Opacity="0.5" BlurRadius="10"/>
</Setter.Value>
</Setter>
<Setter Property="Canvas.ZIndex" Value="1000"/>
</Trigger>
</Style.Triggers>
</Style>
</ListView.ItemContainerStyle>
</ListView>

Hope it helps.

For a WPF project I wanted to create a graphical representation of a list of items. I computed some X,Y coordinates for each item and started changing the XAML of a Listview in order to reflect the position of each item on a Canvas. Well, it is just as easy as you imagine: change the ItemsPanel property to a Canvas and then style each item as whatever you want. The gotcha comes when trying to set the coordinates. The thing is that for each item in a listview a container is constructed and inside the item template is displayed. So here you have all you items displayed exactly as you want them, except the coordinates don't work, since what needs to be placed on the Canvas are the generated containers, not the items. Here is the solution:
<Window.Resources>
<local:CoordinateConverter x:Key="CoordinateConverter"/>
<DataTemplate DataType="{x:Type vm:KernelItem}"> <!-- the template for the data items -->
<Grid>
<Ellipse Fill="{Binding Background}" Width="100" Height="100" Stroke="DarkGray" Name="ellipse"
ToolTip="{Binding Tooltip}"/>
<TextBlock Text="{Binding Text}" MaxWidth="75" MaxHeight="75"
HorizontalAlignment="Center" VerticalAlignment="Center"
TextAlignment="Center"
TextWrapping="Wrap"
ToolTip="{Binding Tooltip}" />
</Grid>
</DataTemplate>
</Window.Resources>
<ListView ItemsSource="{Binding KernelItems}" Name="lvKernelItems"
SelectedItem="{Binding SelectedItem,Mode=TwoWay}"
SelectionMode="Single"
>
<ListView.ItemsPanel>
<ItemsPanelTemplate>
<Canvas HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Background="Black" />
</ItemsPanelTemplate>
</ListView.ItemsPanel>
<ListView.ItemContainerStyle>
<Style TargetType="{x:Type ListViewItem}">
<Setter Property="FocusVisualStyle" Value="{x:Null}"/><!-- no highlight of selected items -->
<Setter Property="Foreground" Value="White"/>
<Setter Property="(Canvas.Left)" >
<Setter.Value>
<MultiBinding Converter="{StaticResource CoordinateConverter}">
<Binding Path="X"/>
<Binding Path="ActualWidth" ElementName="lvKernelItems"/>
<Binding Path="DataContext.Zoom" RelativeSource="{RelativeSource AncestorType={x:Type Window}}"/>
</MultiBinding>
</Setter.Value>
</Setter>
<Setter Property="(Canvas.Top)" >
<Setter.Value>
<MultiBinding Converter="{StaticResource CoordinateConverter}">
<Binding Path="Y"/>
<Binding Path="ActualHeight" ElementName="lvKernelItems"/>
<Binding Path="DataContext.Zoom" RelativeSource="{RelativeSource AncestorType={x:Type Window}}"/>
</MultiBinding>
</Setter.Value>
</Setter>
<Style.Resources><!-- no highlight of selected items -->
<SolidColorBrush x:Key="{x:Static SystemColors.HighlightBrushKey}" Color="Transparent" />
<SolidColorBrush x:Key="{x:Static SystemColors.ControlBrushKey}" Color="Transparent" />
<SolidColorBrush x:Key="{x:Static SystemColors.HighlightTextBrushKey}" Color="Black" />
<SolidColorBrush x:Key="{x:Static SystemColors.ControlTextBrushKey}" Color="Black" />
</Style.Resources>
<Style.Triggers>
<Trigger Property="IsSelected" Value="True"><!-- custom selected item template -->
<Setter Property="Foreground" Value="Cyan"/>
<Setter Property="Effect">
<Setter.Value>
<DropShadowEffect ShadowDepth="0" Color="White" Opacity="0.5" BlurRadius="10"/>
</Setter.Value>
</Setter>
<Setter Property="Canvas.ZIndex" Value="1000"/>
</Trigger>
</Style.Triggers>
</Style>
</ListView.ItemContainerStyle>
</ListView>

As a bonus, you see the way to remove the default selection of an item: the ugly dotted line and the highlighting background.