September 11, 2013

Configuration Pattern

Easy configuration pattern with typed, stateful, and easy to use manage settings.

Settings are represented by a custom type. Since the type is owned by the user it can be fully tailored with little constraint. For example, in WPF it can support INotifyPropertyChanged and be completely bound to your view. It can have in memory default values, be inherited, and even encapsulated logic.

public class MySettings {
 int Host { get; set;}
 int Port { get; set;}

The settings library itself is a static container that manages the saving (serialization) and loading (deserialization) of your in-memory settings object. It also provides a consistent way of accessing the object as well as maintaining its lifetime.

public static class Config<T> where T : new(){
 private T _instance = new T(); //Can be made lazy
 public T Instance { get { return _instance; } }

  //Instance is created via configurator which 
  //implements the deserialization logic
  public static void Load(IConfigurator conf, string location){
  _instance= conf.Load(location);

 //Instance is persisted via configurator which 
 //implements the serialization logic
 public static void Save(string location){
  conf.Save(Instance , location);

A simple interface allows you to create your own configurators that define how serialization and deserialization will be performed. The configurators can be fed to the static config class, or the static config class can be written with pre-canned configurators.

interface IConfigurator
 T Load<T>(string location);
 void Save<T>(T settings, string location);

Using the configuration class is simple. Since the actual settings object is manged by the encapsulating static config class, all you need to do is just call it. Accessing the settings properties without loading will always yield the default memory values. Calling the Load method will update the internal instance of the settings object.

Config<MySettings>.Load(new XmlConfigurator(), "settings.xml");

Once loaded, the Instance property returns a shared instance of in memory settings.

var host = Config<MySettings>.Instance.Host;
var port = Config<MySettings>.Instance.Port;

The settings can be easily manipulated in memory.
Config<MySettings>.Instance.Host = host;
Config<MySettings>.Instance.Port = port;

The settings can also be saved just as easily.
Config<MySettings>.Save(new DBConfigurator(), sqlConnectionString);

September 6, 2013

Zip to Get Element to Element Delta

Often when manipulating time series, you may want to get difference between adjacent elements in a list.

Take a look at the example below:

"Original" is the original time series list of Date/Value, with the Delta row illustrating the expected dela we are trying to calculate.

Linq can handle this in a pretty simple way (this can be pasted into linqpad):

var original = new [] { 
  Tuple.Create(new DateTime(2010, 1, 1), 100),
  Tuple.Create(new DateTime(2010, 2, 1), 50),
  Tuple.Create(new DateTime(2010, 3, 1), 75)

var anchor = Tuple.Create(new DateTime(2009, 12, 1), 0);

var B = (new []{ anchor }).Concat(original);
var A = original;

var deltas = A.Zip(B, (a, b) => Tuple.Create(a.Item1, a.Item2 - b.Item2));


Note that if you don't really care about the first diff, you can simply Skip and Zip like so:
var B = new [] { 
  Tuple.Create(new DateTime(2010, 1, 1), 100),
  Tuple.Create(new DateTime(2010, 2, 1), 50),
  Tuple.Create(new DateTime(2010, 3, 1), 75)

var A = B.Skip(1);

var deltas = A.Zip(B, (a, b) => Tuple.Create(a.Item1, a.Item2 - b.Item2));


July 3, 2012

Introducing C# Toolkit for loading adhoc csv files into a database: Dfloader

Have you ever needed a simple programmatic API in C# for loading islands of data from csv files into a SQL database? Well we have been asked to do this sort of thing on a couple of different projects. So we wrote a little toolkit to allows us to manage the process of uploading adhoc csv files into a set of highly normalized tables in SQL Server.

The toolkit is open source - please do check it out at

June 27, 2012

Sql Server TRY..CATCH

The other day had to do a giant update script on Microsoft SQL Server (this works in 2005+). And was wondering if something happens in the middle, what would happen to the transaction wrapping the sea of inserts/updates/deletes. Would all of them get rolled back, or only some?

It turns out that if you naively (although it makes sense) try to do:

insert(2)... -- Error here!

contrary to intuition, although insert(2) fails, and you would think the transaction gets aborted at this point, insert(3) still goes through. in order to rollback inserts 1 through 3, you would typically need to check for @@ERROR after each of the inserts, and do some sort of GOTO, which will let you jump to some error handling code section, which will perform ROLLBACK TRANSACTION.

Obviously this gets to be pretty, if not excruciatingly painful, to insert the check for @@ERROR after every single statement!

Thankfully there is a much cleaner way of doing this: TRY..CATCH

     insert(2)... -- Error here!
     insert(3)... -- This never happens.
     -- If we got here there were no errors above.
     print 'Committing transaction...'
     -- On insert(2) we drop through here. Nice!
     print 'Rolling back transaction...'

Hope you find this useful... sure better than copy/pasting IF @@ERROR <> 0 a hundred times!

Excel Substitute function

2 small things that make fairly regular data munging tasks a little easier:


When dealing with lots of adhoc data and relational databases, you want to be able to generate insert/update SQL statements in excel, based on some data in the spreadsheet.

Typically you will have some template SQL statement with placeholders for data that varies from row to row. Here is one approach to accomplishing this in Excel with relative ease.

Put your SQL template in cell A1. Lets say my template looks like this:

insert into some_table (one, two) values (@some_value, '@some_string_value')

The funny looking  @ denote placeholder where we want to insert our variable content.
This turns out to be fairly easy to do in Excel, if you use SUBSTITUTE() function.

=SUBSTITUTE(SUBSTITUTE($A$1, "@some_value", B1), "@some_string_value", B2)