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

Wednesday, 3 October 2012

C# Deserialization and constructor initialization

This is an example how to recreate non serialized member of the class on deserialization.

class TestSerialization
{
  [Test]
  public void Test()
  {
    var parent = new Parent(2,3);
 
    // serialize
    byte[] data = SerializationHelper.Serialize(parent);
    // deserialize
    var parentCopy = SerializationHelper.Deserialize(data) as Parent;
 
    Assert.AreEqual(2, parentCopy.Value1);
    //confirm that constructor is not called on deserialization
    Assert.AreEqual(6, parentCopy.Value2);
  }
 
  [Serializable]
  class Parent
  {
    public Parent(int value1, int value2)
    {
      _value1 = value1;
      _value2 = value2 * 2;
     //Child object is not serialized, so needs to be recreated on deserialization (see below)
      _child = new Child() { Value1 = _value1, Value2 = _value2 };
    }
 
    private int _value1;
    private int _value2;
 
    [NonSerialized]
    private Child _child;
 
    public int Value1 { get { return _child.Value1; } }
    public int Value2 { get { return _child.Value2; } }
 
    [OnDeserialized]
    private void Init(StreamingContext context)
    {
      //this is the place where we recreate child object
      _child = new Child() { Value1 = _value1, Value2 = _value2 };
    }
  }
 
  //this object is not Serializable
  class Child
  {
    public int Value1 { get; set; }
    public int Value2 { get; set; }
  }
 
  public class SerializationHelper
  {
    private static readonly BinaryFormatter Fmt = new BinaryFormatter();
 
    public static byte[] Serialize(object obj)
    {
      if (obj == null)
        throw new ArgumentNullException("obj");
      // serialize to memory
      var s = new MemoryStream();
      Fmt.Serialize(s, obj);
      s.Flush();
      return s.ToArray();
    }
 
    public static object Deserialize(byte[] data)
    {
      var os = new MemoryStream(data);
      return Fmt.Deserialize(os);
    }
  }
}

Wednesday, 26 September 2012

C# select data using join to temp table

This is useful if you want to select a lot of data by key and writing IN statement is not efficient (too much data etc..). You load the keys to temp table and then select data using a join.

CREATE TABLE [dbo].[Table_1](
  [Id] [int] NOT NULL,
  [Name] [varchar](50) NULL
)
[TestFixture]
public class TestSelectWithTempTableJoin
{
  [Test]
  public void Test()
  {
 
    const string nbTempCreate = @"
      CREATE TABLE #Ids(
        Id INT
      )
    ";
 
    const string nbTempDrop = @"
      DROP TABLE #Ids
    ";
 
    const string query = @"
      SELECT * FROM Table_1 t
      JOIN #Ids temp ON t.Id = temp.Id
	";
 
    var ids = new List<int>(new int[]{1,3,4});
    var rows = new List<KeyValuePair<int, string>>();
 
    var sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
    sqlConnectionStringBuilder.DataSource = "(local)";
    sqlConnectionStringBuilder.InitialCatalog = "Tests";
    sqlConnectionStringBuilder.ApplicationName = "TestTempTableJoin";
    sqlConnectionStringBuilder.IntegratedSecurity = true;
 
    using (var conn = new SqlConnection(sqlConnectionStringBuilder.ToString()))
    {
        conn.Open();
        var tran = conn.BeginTransaction();        
 
        try
        {
        SqlCommand sqlCommand = null;
 
        //create temp table
        using (sqlCommand = new SqlCommand(nbTempCreate, conn, tran))
        {
            sqlCommand.ExecuteNonQuery();
        }
 
        //load data
        var dt = new DataTable();
        dt.Columns.Add("Id", typeof(int));
 
        foreach (int id in ids)
        {
            var row = dt.NewRow();
            row["Id"] = id;
            dt.Rows.Add(row);
        }
 
        using (SqlBulkCopy bcp = new SqlBulkCopy(conn,new SqlBulkCopyOptions(), tran))
        {
            bcp.DestinationTableName = "#Ids";
            bcp.BulkCopyTimeout = 30;
            bcp.BatchSize = 10;
            bcp.WriteToServer(dt);
        }
 
        //execute query
        using (sqlCommand = new SqlCommand(query, conn, tran))
        {
            using (SqlDataReader reader = sqlCommand.ExecuteReader())
            {
            while (reader.Read())
            {
                rows.Add(new KeyValuePair<int, string>(reader.GetInt32(0), reader.GetString(1)));
            }
            }
        }
 
        //remove temp table
        using (sqlCommand = new SqlCommand(nbTempDrop, conn, tran))
        {
            sqlCommand.ExecuteNonQuery();
        }
 
        tran.Commit();
      }
      catch (Exception)
      {
        tran.Rollback();
      }
      finally
      {            
        conn.Close();
      }
    }
 
    Console.WriteLine("Found {0} rows", rows.Count);
 
    foreach (var row in rows)
    {
        Console.WriteLine("{0},{1}",row.Key,row.Value);
    }
  }
}

Friday, 14 September 2012

Get email address from active directory username in C#

* Add reference to System.DirectoryServices

static string GetMail(string user)
{
  using (var connection = new DirectoryEntry())
  {
    using (var search = new DirectorySearcher(connection)
      {
        Filter = "(samaccountname=" + user + ")",
        PropertiesToLoad = {"mail"},
      })
    {
      return (string) search.FindOne().Properties["mail"][0];
    }
  }
}