Wednesday, 26 November 2014

Create Xml from Excel

        private const string FileName = @"C:\myFile.xls";
        private const string XmlFileName = @"c:\\myFile.xml";
private static void CreateXmlFile()

            DataTable data = new ExcelReader().ReadFirstSheet(FileName);

            var xdoc = new XDocument();

            var root = new XElement("ReportEntitlementMappings");

            foreach (DataRow row in data.Rows)
                var mapping = new XElement("Mapping");
                mapping.SetAttributeValue("From", row[0]);
                mapping.SetAttributeValue("To", row[1]);



 public interface IExcelReader
        DataTable ReadFirstSheet(string fileName);
        DataTableCollection ReadAllSheets(string fileName);

    public class ExcelReader : IExcelReader
        const string ConnectionStringFormat = "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;";

        public DataTable ReadFirstSheet(string fileName)
           return ReadAllSheets(fileName)[0];

        public DataTableCollection ReadAllSheets(string fileName)
            var adapter = new OleDbDataAdapter("SELECT * FROM [Mappings$]", ConnectionStringFormat.FormatWith(fileName));

            var ds = new DataSet();


            return ds.Tables;

EPPLUS tool supports reading from Excel 2007 and 2010 files:

Friday, 21 November 2014

Why you should use ConcurrentBag with Parallel Foreach?

namespace ConsoleApp
    using System;
    using System.Collections.Concurrent;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;

    class Program
        static void Main(string[] args)
            var targetCount = 10000;
            // Shows 7456!

            // Shows 10000


        // The target count is less than actual.
        static void RunThreadUnsafeDemo(int count)
            var sourceList = Enumerable.Range(1, count);
            var targetList = new List();          // Bad idea
            Parallel.ForEach(sourceList, i =>

            Console.WriteLine("Thread Unsafe Count: {0}.", targetList.Count);

        // The target count equals to actual.
        static void RunThreadSafeDemo(int count)
            var sourceList = Enumerable.Range(1, count);
            var targetList = new ConcurrentBag(); // Good one
            Parallel.ForEach(sourceList, i =>

            Console.WriteLine("Thread Safe Count: {0}.", targetList.Count);

Tuesday, 5 August 2014

Command Query Responsibility Segregation (CQRS)

Command Query Responsibility Segregation (CQRS)
  • A design pattern that suggests we should separating methods that change state from those that don't. In which way, we can even separate the databases used for Reads and Writes and therefore being able to optimize such databases for Reads and Writes.
  • Two main behaviours of a model is to be updated and displayed. For instance, you store a product in certain way and present it to the UI in another way. 
  • Query Models are to read data from database to be displayed for the presentation.
  • Command Models are to execute Create/Update/Delete commands from the Presentation to the database.
  • Syncing the separate databases with each other would be an overhead and one way to achieve it would be using Replication models (publisher and subscribers)
  • The fundamental idea is that we should divide an object's methods into two separated categories:
    • Queries: Return a result and do not change the observable state of the system (are free of side effects).
    • Commands: Change the state of a system but do not return a value.

You'd refactor and group the methods of the Product repository into separate classes:
  • ProductQuery
  • ProductSaveCommand
  • ProductDeleteCommand
Further reading:
Event Sourcing

Monday, 28 July 2014


Run PowerShell Prompt


PowerShell Script File

A PowerShell script is simply a text file with a .ps1 extension that contains a list of commands PowerShell should execute.

Host Version

PowerShell Modules

Reusable Functions

Create App Pool in IIS

# create app pool
if(Test-Path IIS:\AppPools\$websiteName)
    echo "App pool exists - removing"
    Remove-WebAppPool $websiteName
    gci IIS:\AppPools
$pool = New-Item IIS:\AppPools\$websiteName
$pool.processModel.identityType = 4 #ApplicationPoolIdentity
$pool | set-item

Write-Host "app pool created"

Create Website in IIS

$iisApp = New-Item iis:\Sites\$websiteName -bindings @{protocol="http";bindingInformation=":80:$websiteName"} -physicalPath $destinationFolder -force

$iisApp | Set-ItemProperty -Name "applicationPool" -Value $websiteName

Copy File Contents

Copy-Item ($PSScriptRoot + "\Ext\*") $destinationExtFolder -recurse -force

Add to AppSettings

$webConfig = ($interactCollectionsFolder + "\web.config")
$doc = (Get-Content $webConfig) -as [Xml]

$appSettings = $doc.SelectSingleNode("//appSettings")

if ($appSettings -eq $null)
    $appSettings = $doc.CreateElement("appSettings")

$key = $appSettings.SelectSingleNode("//add[@name='CsvFilePath']")

if ($key -eq $null)
     $key = $doc.CreateElement("add")
     $key.SetAttribute("name", "CsvFilePath")
$key.SetAttribute("value", $destinationCsvFilePath)



#Notes: To transform a web.config or app.config with a Transform Config file:

$XmlTransformAssemblyPath = $PSScriptRoot + "\Lib\Microsoft.Web.XmlTransform.dll"

function TransformXml($xmlPath, $xdtPath)
write-host 'Transforming...' $xmlPath
write-host 'With...' $xdtPath

    Add-Type -LiteralPath "$XmlTransformAssemblyPath"

    $xmlPathdoc = New-Object Microsoft.Web.XmlTransform.XmlTransformableDocument;  
    $xmlPathdoc.PreserveWhitespace = $true  
    $transf = New-Object Microsoft.Web.XmlTransform.XmlTransformation($xdtPath);  
    if ($transf.Apply($xmlPathdoc) -eq $false)
        throw "Transformation failed."

write-host 'Config transformed and saved.'


#Notes: To execute a script file against a database from web.config or app.config

function ExecuteSqlScripts($connectionString, $sqlScriptsPath)
write-host 'Executing SQL Scripts from ' $sqlScriptsPath

$sqlScripts = [System.IO.File]::ReadAllLines($sqlScriptsPath)

ExecuteNonQuery $connectionString $sqlScripts

write-host 'SQL Scripts executed successfully.'

function ExecuteNonQuery($connectionString, $sqlScripts)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connectionString

foreach($sqlScriptLine in $sqlScripts)
if($sqlScriptLine -ne "GO" -And $sqlScriptLine -ne "go")
#Preparation of SQL packet
$sqlCommandText += $sqlScriptLine + "`n"
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($sqlCommandText, $SQLConnection)
$result = $SQLCommand.ExecuteNonQuery();                  
$sqlCommandText = ""
Catch [System.Exception]
Write-Host 'An error occurred: ' +  $_.Exception


# Gets the connection string value from app.config or web.config based on the name of the connection string.
function GetConnectionString($configPath, $name)
$xml = [xml](Get-Content $configPath)

$connectionString = ($xml.configuration.connectionStrings.add | where {$ -eq $name})

return $connectionString.connectionString


Monday, 7 July 2014


Install-Package NServiceBus
  • By default NServiceBus uses Log4Net for its logging and you can install it using NuGet again:
Install-Package log4net

Thursday, 3 July 2014


Reinstall a Package:

Update-Package –reinstall SpecFlow

Install a Specific Version of a Package

Install-Package SpecFlow -Version 1.9

Uninstall a Package 

Uninstall-Package SpecFlolw -Force

Friday, 20 June 2014

Gherkin Language and BDD

Gherkin Language

A feature or user story is comprised of 1 or more scenarios.


Each Scenario is defined by GIVEN WHEN THEN sections as explained below:
  • GIVEN is about
    • Preconditions
    • Setups
    • Arrangements
    • Preparations
    • All the parameters and preparations needed before the Action can be taken
    • Multiple steps can be combined with each other using AND or OR
    • Preparations such as GIVEN an existing user with name William enters William in the text box
  • WHEN is about
    • Action that is taken by the user to expect a certain behavior
    • Multiple steps can be combined with each other using AND or OR
    • Actions such as Clicking on a Button, Entering a Number, Navigating to a Page

    • THEN is about
      • Expected behaviour
      • How the behavior can be verified
      • Multiple steps can be combined with each other using AND or OR


      Each edge case of a feature should be considered as a separate scenario.


      GIVEN user goes to 
        AND enter "Technology" in the search box
      WHEN the search button is clicked
      THEN a list of latest adverts related to "Technology" should be shown to the use

      Monday, 12 May 2014

      Web API Documentation

      • Install it from:
      • Enable Xml documentation from the Build tab in the Project Properties section so that the Xml files are writen into an Xml file.
      • Add some comments to the Controller or Properties
      • When you build the app then the Xml file

      You can either read the xml comments from an xml file or via the code XML documentation comments.




      Friday, 11 April 2014

      Windows Commands

      c:\Windows\System32\inetsrv\appcmd list wp

      List all processes related to the ASP.NET worker processes

      Monday, 24 March 2014

      Email Delivery Systems

      If you have the skills/time/budget available, you could of course create your own bespoke email delivery system tailored to your needs for example when orders received via your e-commerce website.

      Alternatively you may want to evaluate the potential of using one of the existing systems other Companies use in the industry:

      Wednesday, 29 January 2014

      TSQL IN clause Generator

      The below class, generates TSQL IN Clauses. Basically, you'd need them when you'd have a large list of items and you'd like to pass to a SELECT statement in Oracle, SQL, ODBC or any other database.

      For example, Oracle 10g has got a limitation for 1000 items in the IN clause and here is how you can get around it:

         public interface ISqlInClauseGenerator
              string Generate(string[] items, string columnName, int inClauseLimit = 1000);
          public class SqlInClauseGenerator : ISqlInClauseGenerator
              const string EndClause = ")";
              const string StartClausePattern = "{0} IN (";
              const string ConnectorClausePattern = ") OR " + StartClausePattern;
              public string Generate(string[] items, string columnName, int inClauseLimit = 1000)
                  if (items.IsNullOrEmpty())
                      return string.Empty;
                  string startClause = string.Format(StartClausePattern, columnName);
                  string connectorClause = string.Format(ConnectorClausePattern, columnName);
                  var sb = new StringBuilder(startClause);
                  for (int i = 0; i < items.Length; i++)
                      if (i == 0)
                          // start
                      else if (i % inClauseLimit == 0)
                          // start a new IN clause
                          // continue
                          sb.Append("," + items[i]);
                      if (i == items.Length - 1)
                          // it's the last item
                  return sb.ToString();

      For example, if you have a list of 2000 productIds, this will generate something like this:
      "ProductId IN (1,2,3...1000) OR ProductId IN (1001...2000)"