Thursday, 22 November 2012

C# string parameter replacement with simple date arithmetic

[Test]
public void FindAndReplace()
{
  var sql = @"SELECT * FROM Table_{@AsOf} WHERE Date='{@AsOf-1}'";
  Console.WriteLine("BEFORE: {0}", sql);
  var asOf = DateTime.Today;
 
  //(?: )+ means repeat the inner groups
  var regexExpr = @"(?:{@(?<parameter>\w+)(?<operation>\S)?(?<number>\d+)?})+";
  var regex = new Regex(regexExpr);      
  foreach (Match match in regex.Matches(sql))
  {
    sql = sql.Replace(match.Value, Substitute(match.Value, asOf));
  }
 
  Console.WriteLine("AFTER : {0}", sql);
}
 
private string Substitute(string parameterText, DateTime asOf)
{      
  var regexExpr = @"{@(?<parameter>\w+)(?<operation>\S)?(?<number>\d+)?}";
  var regex = new Regex(regexExpr);
  var match = regex.Match(parameterText);
  if (match.Success)
  {
    var parameter = match.Groups["parameter"].Value;
    if (match.Groups["operation"].Success && match.Groups["number"].Success)
    {
      var operation = match.Groups["operation"].Value.Equals("+") ? 1 : -1;
      var number = int.Parse(match.Groups["number"].Value);
      return asOf.AddDays(operation*number).ToString("yyyyMMdd");
    }
    else
    {
      return asOf.ToString("yyyyMMdd");
    }
  }
  return parameterText;
}
//this example doesn't do any validation/exception catching etc..
BEFORE: SELECT * FROM Table_{@AsOf} WHERE Date='{@AsOf-1}'
AFTER : SELECT * FROM Table_20121122 WHERE Date='20121121'

No comments:

Post a Comment