Posts in category .net - page 23

Parameterising the IN clause of an SQL SELECT in .NET

I’m a fan of parameterized queries with a strong dislike for building SQL (or other magic strings). Encoding, escaping errors, localization formatting problems and injection can run rampant when you think everything is a string.

Problem

Today I found myself a quandary as I needed to SELECT records based on a list of values I had. e.g.

SELECT * FROM Products WHERE ProductCode IN ('ABC123', 'DEF456', 'GHI789')

At first glance the relevant parameterized version might look like:

SELECT * FROM Products WHERE ProductCode IN (@productlist)

The problem here however is that if you put a comma-separate list of items into a string parameter named @productlist then it sends this to the database server:

SELECT * FROM Products WHERE ProductCode IN ('ABC123, DEF456, GHI789')

That’s not what we want at all.

This hack isn’t pretty and it has some limitations:
  • Only works with named parameters
  • Could upset some DB providers that don’t like having command parameters removed or command text modified
  • Parameter name to replace must be totally unique – i.e. not exist as a subset of another parameter name
  • Only preserves the basic IDbCommand declared properties

It should however work across DB providers and types.

Usage

The previous example would mean we use exactly the expected parameterized version with @productlist in place.

Add the parameter as you’d expect but instead of assigning a string/numeric to it assign something IEnumerable.

Finally call this method against the command and parameter before you execute it for the ‘magic’ to happen:

The ‘magic’

public void ExpandDbArrayParameter(IDbCommand cmd, IDbDataParameter parameter) {
  if (parameter.Value is IEnumerable) {
    int index = 0;
    StringBuilder newParameterSQL = new StringBuilder();
    foreach(Object value in (IEnumerable) parameter.Value) {
      String valueParameterName = String.Format("{0}{1}", parameter.ParameterName, ++index);
      IDataParameter valueParameter = cmd.CreateParameter();
      valueParameter.DbType = parameter.DbType;
      valueParameter.Direction = parameter.Direction;
      valueParameter.ParameterName = valueParameterName;
      valueParameter.SourceColumn = parameter.SourceColumn;
      valueParameter.SourceVersion = parameter.SourceVersion;
      valueParameter.Value = value;
      cmd.Parameters.Add(valueParameter);

      if (index == 1)
        newParameterSQL.Append(valueParameterName);
      else
        newParameterSQL.Append("," + valueParameterName);
      }
      cmd.Parameters.Remove(parameter);
      cmd.CommandText = cmd.CommandText.Replace(parameter.ParameterName, newParameterSQL.ToString());
    }
}

[)amien

Switching from Boot Camp to Parallels

A few weeks ago I managed to screw up my Windows XP installation on my MacBook using some low-level tools and driver related stuff.

I’d already run out of space on the 30GB partition I’d allocated, I was missing the OS X side and not running any 3D applications so I took the plunge to remove the partition entirely and switch over to using the Parallels VM product I’d purchase instead.

Installation was a breeze and I soon had a clean XP install with Visual Studio 2005, SQL Server 2005, .NET Reflector, IE7 and a bunch of other useful tools for work operational again.

Whilst the speed isn’t as nippy as the raw Boot Camp option was it’s fast enough – certainly faster than the Pentium 4 box my client provided for development although having 2GB of RAM certainly helps.

With XP just running the dev tools this meant I could access my iTunes library on OS X whilst I work and get back to using Colloquay and Adium.

Safari crashes every time I try to blog post even though it doesn’t even try to support HTML editing abilities so Firefox and Camino are my staple on the Mac side.

I use a dual-monitor configuration during the day and Parallels works like a breeze with OS X on one display and Windows on the other. The mouse just glides between the two seamlessly – no clicking in/out or awkward keys to press to jump between the two although Parallels did need to be manually told what the resolution was.

It seems I’m not alone in choosing this set-up.

All Parallels need to do now is to enable the virtual machine to utilize multiple cores and 3D acceleration and it would be perfect.

Well, switching over to a Core 2 powered MacBook Pro with 4-8GB of RAM might be perfection… and a bigger hard-disk…

[)amien

.NET quick samples: Up-times, ages, rounding to n places

Just a few quick .NET samples for performing some common tasks that the .NET Framework doesn’t do for you:

System uptime

using System.Diagnostics;

public TimeSpan GetUptime() {
    var systemUpTime = new PerformanceCounter("System", "System Up Time");
    systemUpTime.NextValue(); // Required to work!
    return TimeSpan.FromSeconds(systemUpTime.NextValue())));
}

Calculating age

public int GetAge(DateTime birthday) { 
    int years = DateTime.Now.Year - birthday.Year;
    return (birthday.DayOfYear >= DateTime.Now.DayOfYear) ? years : years - 1;
}

Rounding to n decimal places

public decimal ArithmeticRound(decimal d, int decimals) {
    var power = (decimal)Math.Pow(10, decimals);
    return (decimal.Floor((Math.Abs(d) * power) + 0.4m) / power) * Math.Sign(d);
}

[)amien

AnkhSVN & TortoiseSVN

DamienG joins AnkhSVN team!

I’m proud to announce that I’ve joined the AnkhSVN team, albeit in a rather minor capacity.

For the last few weeks I’ve been working on a set of new icons to add a bit of sparkle to the user interface for the forthcoming AnkhSVN 1.0 release. There is a good chance they will make the next release candidate that’s on it’s way to address some issues with delete/remove functionality.

A couple of nights ago I checked in the current icon set as it stands although there is a little more work left to do. Specifically there are a couple more commands buried in the Repository and Working Copy Explorers.

Maid, spray, sparkle-folder and glowing t-shirt iconsOnce 1.0 goes gold I’ll post a couple of screen-shots and icons here. In the mean time here are a few of the rejects including my 16×12 pixel French maid. Yes, they’re Firefox style and not XP/Visual Studio style, sorry.

TortoiseSVN ignore patterns

Occasionally you may use TortoiseSVN against projects and find it trying to add all sorts of junk to your repository. This is where the global ignore pattern list fits in and rather than loose mine again I’ll post it here for future reference although it is applicable to more than just the Tortoise…

*/bin */obj */Release */Debug *.suo *.err *.log *.obj *.bin *.dll *.exe *.LOG *.user *.pdb [tT]emp [tT]empPE Ankh.Load thumbs.db

[)amien