Wednesday, 28 November 2012

C# Generic Lambda Comparer

public class LambdaComparer<T> : IComparer<T>
{
  public enum Direction
  {
    Asc = 1,
    Desc = -1
  }
 
  private readonly Comparison<T> _comparison;
 
  public LambdaComparer(Comparison<T> comparison)
  {
    _comparison = comparison;
  }
 
  int IComparer<T>.Compare(T x, T y)
  {
    return _comparison(x, y);
  }
 
  public static LambdaComparer<T> IgnoreSortComparer()
  {
    return IgnoreSortComparer(Direction.Asc);
  }
 
  public static LambdaComparer<T> IgnoreSortComparer(Direction direction)
  {
    return new LambdaComparer<T>((x, y) => x.Equals(y) ? 0 : (int)direction);
  }
}
public class Data
{
  public int X;
  public int Y;
}
 
[Test]
public void TestLambdaComparer()
{
  var data = new Data[]
  {
  new Data() { X = 1, Y = 1 }, 
  new Data() { X = 3, Y = 2 }, 
  new Data() { X = 5, Y = 3 }, 
  new Data() { X = 2, Y = 4 }, 
  new Data() { X = 4, Y = 5 }   
  };
 
  Console.WriteLine("BEFORE: "+string.Join(",",data.Select(x=>string.Format("{0}={1}",x.X,x.Y))));
 
  Array.Sort(data, new LambdaComparer<Data>((a,b)=>a.X.CompareTo(b.X)));
 
  Console.WriteLine("AFTER : " + string.Join(",", data.Select(x => string.Format("{0}={1}", x.X, x.Y))));   
 
  //BEFORE: 1=1,3=2,5=3,2=4,4=5
  //AFTER : 1=1,2=4,3=2,4=5,5=3
}
[Test]
public void TestIgnoreSortComparer()
{
  var defaultSortedSet = new SortedSet<int>();
  defaultSortedSet.Add(2);
  defaultSortedSet.Add(3);
  defaultSortedSet.Add(1);
 
  Console.WriteLine("DEFAULT  : " + string.Join(",", defaultSortedSet));
 
  var unSortedSet = new SortedSet<int>(LambdaComparer<int>.IgnoreSortComparer());
  unSortedSet.Add(2);
  unSortedSet.Add(3);
  unSortedSet.Add(1);
 
  Console.WriteLine("UNSORTED : " + string.Join(",", unSortedSet));
 
  var unSortedList = new SortedList<int,int>(
    LambdaComparer<int>.IgnoreSortComparer(LambdaComparer<int>.Direction.Desc));
  unSortedList.Add(2,1);
  unSortedList.Add(3,2);
  unSortedList.Add(1,3);
 
  Console.WriteLine("UNSORTED : " + string.Join(",", unSortedList));
 
  //DEFAULT  : 1,2,3
  //UNSORTED : 2,3,1
  //UNSORTED : [2, 1],[3, 2],[1, 3]
}

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'

Monday, 19 November 2012

Script to create SqlServer Table, Stored Procedure and View

PRINT 'CREATE TABLE'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [Table1]
GO
 
CREATE TABLE [dbo].[Table1](
  [Id] [uniqueidentifier] NOT NULL,
  [Name] [varchar](50) NULL,
  [DateUpdated] [datetime] NULL,
  CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
  (
    [Id]
  )
)
GO
 
GRANT SELECT, INSERT, UPDATE, DELETE ON [Table1] TO [SampleUser]
GO
 
 
PRINT 'CREATE STORED PROCEDURE'
GO 
 
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[StoredProcedure1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[StoredProcedure1]  
GO
 
CREATE PROCEDURE [dbo].[StoredProcedure1]  
AS
 
SET NOCOUNT ON
 
UPDATE dbo.Table1 
SET [DateUpdated]=GETDATE()
 
GO
 
GRANT EXECUTE ON [StoredProcedure1] TO [SampleUser]
GO
 
 
PRINT 'CREATE VIEW'
GO  
 
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[View1]') and OBJECTPROPERTY(id, N'IsView') = 1)
  DROP VIEW [View1]
GO
 
CREATE VIEW [dbo].[View1] AS 
  SELECT * from Table1
GO
GRANT SELECT ON [View1] TO [SampleUser]
GO
 
  
-- SIMPLE TEST -- 
 
DELETE FROM Table1
 
INSERT INTO Table1
SELECT NEWID(),'Test',GETDATE()
 
SELECT 'BEFORE',* FROM View1
 
--wait for 1s
WAITFOR DELAY '00:00:01'
 
EXEC StoredProcedure1
 
SELECT 'AFTER',* FROM View1