C# expression trees

We want to treat lambda expressions as expression trees and look inside them instead of executing them. For example, LINQ to SQL gets the expression and converts it to the equivalent SQL statement and submits it to server (rather than executing the lambda).

With Lambda expressions we can write less code, and the code will be more readable.

A lambda expression uses the lambda operator => which means goes to.

arguments => expession

The expression reads: arguments goes to expression.

Let’s start with a common example;

using System;

namespace Lambdas
{
    class Program
    {
        static void Main()
        {
            Console.WriteLine(Square(5));
            Console.ReadLine();
        }

        static int Square(int num)
        {
            return num * num;
        }
    }
}

we declare a method called Square of type int with one input parameter of type int. It multiplies the number and returns the result.

In the Main() method we call our Square method with an int as a parameter.

We can convert this function into a Lambda expression. This is what it would look like:

num => num * num;

This expression would read: num goes to calculation (num * num).

This expression is short and easily readable once we get used to it. The catch though, is that a Lambda expression must be assigned to a delegate.

Resources

https://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct

https://tyrrrz.me/blog/expression-trees

https://thesharperdev.com/an-introduction-to-c-expression-trees/

Remove all spaces from strings using sql

I wanted to remove following spaces and special characters from these strings;

'a. Personnel '
'j.  Indirect Charges '

I tried to use following SQL to get rid of all spaces;

SELECT DISTINCT TRIM(LOWER(REPLACE(BudgetCategories, ' ', ''))) BudgetCategories
FROM [dbo].[MyTable]

Still the trailing spaces were there. I guess there is a special character involved, so i tried this one;

SELECT DISTINCT LTRIM(RTRIM(LOWER((REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BudgetCategories, ' ', ''), CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))))
FROM [dbo].[MyTable]

This did the trick and I was able to get this output;

'a.personnel'
'j.indirectcharges'

References

https://stackoverflow.com/questions/21585914/trim-spaces-in-string-ltrim-rtrim-not-working

SQL Server Transaction block

This is suggested code block for creating stored procedures in SQL Server;

BEGIN TRY
	BEGIN TRANSACTION
	--All your insert/update/delete/merge goes in here
	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber
	,ERROR_MESSAGE() AS ErrorMessage
	 ROLLBACK TRANSACTION
END CATCH

While manipulating multiple tables, the transaction would be roll back if there is any error.

SSIS Foreach Loop Folder as variable

I need to set the Folder in Foreach loop to a variable location. I tried just adding the location to my package configuration, and it worked.

To make Folder dynamic so that it can get values from variable, Create a variable;

Click on Expressions under Foreach Loop Editor

This setting will take precedence to the folder value in Folder text box. Optionally you can set the FileSpec to a user variable pointing to the files (which will take precedence to the Files in Files text box.