Tim Van Wassenhove

Passionate geek, interested in Technology. Proud father of two

28 Oct 2011

Say no to primitives in your API.. and make your software more explicit

A while ago I wrote some code like this:

public interface ICanBroadcast
{ 
  public void Broadcast(string message) { ... }  
  public void Broadcast(string message, string author) { ... }
}

A bit later the requirements changed and from now on it was required to specify the topic:

public interface ICanBroadcast  
{
  public void Broadcast(string message, string topic) { ... }  
  public void Broadcast(string message, string author, string topic) { ... } 
}

In case you were using Broadcast(string message) the compiler would rightfully inform you that no such method exists. In case you were using Broadcast(string message, string author) the compiler does not catch the error and incorrectly uses the author as topic. I can only hope that you have a suite of tests that makes you notice that something is wrong when you upgrade to my latest release.

Let’s make the difference between an Author and a Topic more explicit (to our API consumers and the compiler) by creating explicit types to represent the concepts:

public interface ICanBroadcast 
{  
  public void Broadcast(Message message, Topic topic) { ... } 
  public void Broadcast(Message message, Author author, Topic topic) { ... }  
} 

The joy of using a typed language 😉

18 Oct 2011

Force the removal of a file with PowerShell

Last couple of weeks I have been generating a lot of files (and restricting their ACLs) and today I decided to remove all those files. The problem is that my user account did not have permissions on those files. Here is a small script that will first take ownership of the file, then grants FullControl permissions, and finally removes the file 🙂

function RemoveFile 
{	  
	param($FileName)
	
	&takeown /F $FileName
	$User = [System.Security.Principal.WindowsIdentity]::GetCurrent().User
	$Acl = Get-Acl $FileName	  
	$Acl.SetOwner($User)	  
	$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($User, "FullControl", "Allow")	  
	$Acl.SetAccessRule($AccessRule)	  
	Set-Acl $FileName $Acl
	Remove-Item $FileName 
}

Get-ChildItem *.txt -R | % { RemoveFile $_.FullName; }

Edit on 2011-10-19

Resetting the permissions with icacls c:\output /reset /t and then calling Remove-Item c:\output -R does the trick.

function RemoveFiles 
{  
	param($Directory)

	icacls $Directory /reset /t 
	Remove-Item $Directory -R 
}

RemoveFiles c:\output;
22 Sep 2011

Remove all access rules from a directory

A while ago i needed to write some code that removes all (existing/inherited) access rules from a given directory. It was pretty frustrating to notice that all my attempts seemed to fail (RemoveAccessRule, PurgeAccessRule, …)

Finally i found that SetAccessRuleProtection was the method that i needed to invoke.

const string Folder = @"c:\temp\secured";
var directory = new DirectoryInfo(Folder);
var directorySecurity = directory.GetAccessControl();
directorySecurity.SetAccessRuleProtection(true,false);
directory.SetAccessControl(directorySecurity);

There you go 😉

19 Aug 2011

Use SQL Server Profiler to see if a connection is pooled

It took me a couple of websearches to discover how i can see in SQL Server Profiler whether or not a connection is pooled. Apparently you have to check ‘Show all columns’ and then you can check the ‘EventSubClass’ column:

This is how it looks like in your trace window:

18 Aug 2011

SqlConnectionStringBuilder sets the Pooling property to true by default

Here is something that surprised me: SqlConnectionStringBuilder sets the Pooling property to true by default.

01 Aug 2011

Specialized solution for aggregate string concatenation

I have noticed that most people come up with the following solution to build a string in T-SQL:

WITH [Numbers] AS (	  
	SELECT TOP(10) [n]	  
	FROM [Nums] 
)	  
SELECT @message = COALESCE(@message, '') + '' + CAST([n] AS nvarchar(2))	  
FROM [Numbers];

SELECT @message = STUFF(@message, 1, 2, '');
SELECT @message;

Important! Microsoft has no official documentation describing this aggregate concatenation technique that is based on the assignment SELECT syntax. The behavior described here is based on observation alone. The current implementation of the ConcatOrders function doesn’t incorporate an ORDER BY clause and does not guarantee the order of concatenation. According to a blog entry by Microsoft’s Conor Cunningham, it seems that SQL Server will respect an ORDER BY clause if specified (http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx). Conor is a very credible source, but I should stress that besides this blog entry I haven’t found any official documentation describing how a multi-row assignment SELECT should behave—with or without an ORDER BY clause.

With the aid of FOR XML PATH (as mentionned in Inside Microsoft SQL Server 2008: T-SQL Programming we can solve this problem using a documented approach:

WITH [Numbers] AS (	  
	SELECT TOP(10) [n]	  
	FROM [Nums] 
)	  
SELECT @message = (SELECT '' + CAST([n] AS nvarchar(2)) AS [text()]  
FROM [Numbers]	  
FOR XML PATH(''));

SELECT @message = STUFF(@message, 1, 2, '');
SELECT @message;
01 Aug 2011

TryGetResult

I think this entry has been in the pipeline for a couple of years now and today i have decided to finally post it 😉 I got frustrated with the annoying out parameter in TryGet methods so i decided to use a different signature using TryGetResult:

public class TryGetResult<T> 
{
  public TryGetResult()   
  {   
    Success = false;  
  }

  public TryGetResult(T result)  
  {   
    Success = true; 
    Result = result;  
  }

  public bool Success { get; private set; }

  public T Result { get; private set; }
}

And now your TryGet methods can have the following signature:

public TryGetResult<Person> TryGetPersonByName(string name) 
{   
  // person is not available  
  if(name.IsInvalidPersonName()) return new TryGetResult();

  // return the person
  return new TryGetResult(new Person(name));
}
15 Jul 2011

Launch DtExec from PowerShell

Running an SSIS package from PowerShell (using DTExec) can be as simple as:

RunPackage -File 'C:\test.dtsx' -DatabaseHost '.' -DatabaseName 'TEST';

Here are the functions that make it this simple:

function GetDtExecPath {    
  $DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup').SQLPath;    
  $DtExecPath = (Resolve-Path "$DtsPath\Binn\DTExec.exe");    
  $DtExecPath;
}

function GetDtExecPropertyPathValue() {      
  param(
    $PropertyPath = '',
    $Value = '';
  );

  "$PropertyPath;\`"\`"$Value\`"\`"";
}

function RunPackage {
      
  param(        
  $DtExecPath = (GetDtExecPath),       
  $File = 'test.dtsx'  
  );

  $Params = "/FILE $File";
   
  for($i = 0; $i -lt $Args.Length; $i += 2) {       
    $PropertyPath = $Args[$i].SubString(1);       
    $Value = $Args[$i+1];       
    $PropertyPathValue = GetDtExecPropertyPathValue -PropertyPath $PropertyPath -Value $Value;       
    $Params += " /SET $PropertyPathValue";    
  } 

  &"$DtExecPath" $Params;
}
13 Jul 2011

Building a Nums table (quickly)

A while ago i presented my approach to generate a nums table here.

DECLARE @count INT = 1000;

WITH    
  [Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] ) 
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)  
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
  
SELECT * FROM [Numbers];

Because we only use this code once to fill the table we don’t really care that it is not very fast. Today i discovered there is a way to speed it up in this wonderful book: Inside Microsoft® SQL Server® 2008: T-SQL Querying:

WITH
      
  [Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )  
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS [Value] FROM[Nums4] )
  
SELECT * FROM [Numbers];

Yay for features like ORDER BY (SELECT ).

13 Jul 2011

Using User-Defined Table Type with Identity column in ADO.NET

A while ago i wanted to use a User-Defined Table Type to pass in a set of records. Nothing special about this except that the first column of the UDTT was an Identity column:

CREATE TYPE [Star].[example] AS TABLE(  
  [Ordinal] [int] IDENTITY(1,1) NOT NULL,  
  [Name] [nvarchar](200) NOT NULL,
)

After finding a lot of posts saying that this is not supported a colleague of mine, Stephen Horsfield, found a way to do it as following:

var sqlMetaData = new[] 
{  
  new SqlMetaData("Ordinal", SqlDbType.Int, true, false, SortOrder.Unspecified, -1),   
  new SqlMetaData("Name", SqlDbType.NVarChar, 200)
};

sqlRecords = new HashSet<SqlDataRecord>(usersToInclude.Select(user =>
{   
  var record = new SqlDataRecord(sqlMetaData);   
  record.SetString(1, user.Name);   
  return record; 
}));