Powershell: How do you add inline C#?

Powershell is great for admin tasks. Stuff like iterating through files and folders, copying and transforming files are very, very easily done. But inevitably there will always be stuff that are easier to do via a “normal” language such as C#.

Trying to solve a problem I had at work, I needed to transform a CSV file by changing the fields -which is easily done via powershell- and, at the same time, do a “get only the highest record of every group”. This is done with LINQ, which you can use in powershell but it’s cumbersome and will result in many, many lines of code.

So I wanted to do this in a more clean way, in C#. What I came up with, and worked nicely, was the following:

#
# Source: DotJim blog (https://dandraka.wordpress.com)
# Jim Andrakakis, November 2018
#
# The purpose of this script is to read a CSV file with bank data
# and transform it into a different CSV.
#
# 1. The Bank class is a POCO to hold the data which I need 
#    from every line of the CSV file.
# 2. The Add() method of the BankAggregator class adds the 
#    record to the list after checking the data for correctness.
# 3. The Get() methof of the BankAggregator class does a
#    LINQ query to get the 1st (max BankNr) bank record 
#    from every record with the same Country/BIC.
#    It then returns a list of strings, formatted the way 
#    I want for the new (transformed) CSV file.
#
# Here is where I inline the C# code:
Add-Type -Language CSharp @"
using System;
using System.Collections.Generic;
using System.Linq;
namespace DotJim.Powershell
{
    public class Bank
    {
        public int BankNr;
        public string Country;
        public string BIC;
    }
    public static class BankAggregator
    {
        private static List list = new List();
        public static void Add(string country, string bic, string bankNr)
        {
            //For debugging
            //Console.WriteLine(string.Format("{0}{3}{1}{3}{3}{2}", country, bic, bankNr, ";"));
            int mBankNr;
            // Check data for correctness, discard if not ok
            if (string.IsNullOrWhiteSpace(country) 
                || country.Length != 2 
                || string.IsNullOrWhiteSpace(bic) 
                || string.IsNullOrWhiteSpace(bankNr)
                || !int.TryParse(bankNr, out mBankNr)
                || mBankNr <= 0)
            {
                return;
            }
            list.Add(new Bank() { BankNr = mBankNr, Country = country, BIC = bic });
        }
        public static List Get(string delimiter)
        {
            // For every record with the same Country & BIC, keep only 
            // the record with the highest BankNr
            var bankList = from b in list
                           group b by new { b.Country, b.BIC } into bankGrp
                           let maxBankNr = bankGrp.Max(x => x.BankNr)
                           select new Bank
                           {
                               Country = bankGrp.Key.Country,
                               BIC = bankGrp.Key.BIC,
                               BankNr = maxBankNr
                           };
            // Format the list the way I want the new CSV file to look
            return bankList.Select(x => string.Format("{0}{3}{1}{3}{3}{2}",
                x.Country,x.BIC,x.BankNr,delimiter)).ToList();
        }
    }
}
"@;

# Read one or more files with bank data from the same dir 
# where the script is located ($PSScriptRoot)
$srcSearchStr = "source_bankdata*.csv"
$SourcePath = $PSScriptRoot
$destPath = $SourcePath

$fields = @("Country","BIC","EmptyField","BankId")

$filesList = Get-ChildItem -Path $SourcePath -Filter $srcSearchStr

foreach ($file in $filesList) 
{
    Write-Host "Processing" $file.FullName

    # Fields in the source CSV:
    # BANKNUMMER  = BankNr
    # BANKLAND    = Country
    # BANKSWIFT   = BIC
    $data = Import-Csv -Path $file.FullName -Delimiter ";"

    foreach ($item in $data) 
    {
        # Call the C# code to add the CSV lines to the list
        [DotJim.Powershell.BankAggregator]::Add($item.BANKLAND,$item.BANKSWIFT,$item.BANKNUMMER)
    }

        # Call the C# code to get the transformed data
        $list = [DotJim.Powershell.BankAggregator]::Get(";")
    
        Write-Host "Found" $list.Count "valid rows"

        # Now that we have the list, write it in the new CSV    
        Out-File -FilePath "$destPath\transformed_bankdata_$(New-Guid).csv" -Encoding UTF8 -InputObject $list
}

Have fun coding!

Advertisements

My white bread recipe

I recently bought a bread machine, an Unold 8695 Onyx, and I’m very, very happy with it. Simple machine, nothing fancy (whenever I hear of appliances that are “connected”, “internet enabled” or, go forbid, “on the blockchain” I run away) but great value for money and gets the job done, very well.

The manual is excellent, with detailed timing tables and recipes which I fully recommend. That said, I did get the recipe that I liked most -the humble white bread- and customized it a bit.

These are the ingredients, in the order which I put them in the bowl:

Ingredient For 500 gr bread For 800 gr bread
Water 230 ml 300 ml
Salt 3/4 teasp. (4 gr) 1 teasp. (6 gr)
Honey 2 tblsp. (40 gr) 2.5 tblsp. (52 gr)
Wheat semolina (or Corn polenta) 100 gr 126 gr
Whole flour (Ruchmehl, type 1050) 20 gr 30 gr
White flour (Weissmehl, type 550, preferably with vitamins) 280 gr 356 gr
Yeast 5 gr 7 gr (1 package)

I then use the “Quick” (“Schnell”) program, with light or medium crust. 1h 40min later, it’s ready.

Enjoy!

Citrix on Ubuntu 18.04

I recently changed from Win10 to Ubuntu 18.04 as my main OS at home. I still have Windows in a few VMs, as I need to do the occasional development with Visual Studio.

But a problem I had was that needed to connect to the office when doing home office.

Now, at work we have Citrix Netscaler Gateway. And there’s a Linux client available. It worked, but not as smoothly as I hoped 🙂

Here’s what I did:

From Ubuntu’s Software Center, I installed Citrix Receiver.
Then it asked for the server and tried to connect, but I was getting an error: “An SSL connection to the server could not be established because the server’s certificate could not be trusted.”
So I opened a terminal and gave the following commands (source):
sudo ln -s /usr/share/ca-certificates/mozilla/* /opt/Citrix/ICAClient/keystore/cacerts/
sudo c_rehash /opt/Citrix/ICAClient/keystore/cacerts/

After that it connected, but it was still giving an error: “A protocol error occured while communicating with the Authentication Service”

So after some sleuthing, I opened my browser (Chrome) and connected to the my company’s Citrix server address (https://server). When I clicked the apps there, it worked.

Powershell & Microsoft Dynamics CRM: how to get results using a FetchXml

If you’ve used Microsoft CRM as a power user (on-premise or online), chances are you’ve come across the standard way of querying CRM data, FetchXml.

You can run this by hand but of course the real power of it is using it to automate tasks. And another great way to automate tasks in Windows is, naturally, powershell.

So here’s a script I’m using to run a fetch xml and export the results to a csv file:

#
# Source: DotJim blog (https://dandraka.wordpress.com)
# Jim Andrakakis, May 2018
#
# ============ Constants to change ============
# note: create pwd file with the following command:
# read-host -assecurestring | convertfrom-securestring | out-file C:\temp\crmcred.pwd
$pwdFile = "C:\temp\crmcred.pwd"
$username = "myusername@mycompany.com"
$serverurl = "https://my-crm-instance.crm4.dynamics.com"
$fetchXmlFile = "c:\temp\fetch.xml"
$exportfile = "C:\temp\crm_export.csv"
$exportdelimiter = ";"
# =============================================

# ============ Login to MS CRM ============

$password = get-content $pwdFile | convertto-securestring
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username,$password

try
{
 # for on-prem use :
 # $connection = Connect-CrmOnPremDiscovery -Credential $cred -ServerUrl $serverurl
 $connection = Connect-CRMOnline -Credential $cred -ServerUrl $serverurl
 # you can also use interactive mode if you get e.g. problems with multi-factor authentication
 #$connection = Connect-CrmOnlineDiscovery -InteractiveMode -Credential $cred
}
catch
{
 Write-Host $_.Exception.Message
 exit
}

if($connection.IsReady -ne $True)
{
 $errorDescr = $connection.LastCrmError
 Write-Host "Connection not established: $errorDescr"
 exit
}

# ============ Fetch data ============
$fetchXml = [xml](Get-Content $fetchXmlFile)
$result = Get-CrmRecordsByFetch -conn $connection -Fetch $fetchXml.OuterXml

# ============ Write to file ============
# Obviously here, instead of writing to csv directly, you can loop and do whatever suits your needs, e.g. run a db query, call a web service etc etc
$result.CrmRecords | Select -Property lastname, firstname | Export-Csv -Encoding UTF8 -Path $exportfile -NoTypeInformation -Delimiter $exportdelimiter

When you use your own FetchXml, do remember to change the properties in the last line (lastname, firstname).

For a quick test, the example FetchXml I’m using is the following:

<fetch mapping="logical" version="1.0">
 <entity name="account">
 <attribute name="customertypecode" alias="customertypecode" />
 <attribute name="name" alias="company_name" />
 <attribute name="emailaddress1" alias="company_emailaddress1" />
 <link-entity name="contact" from="accountid" to="accountid" link-type="inner">
 <attribute name="lastname" alias="lastname" />
 <attribute name="firstname" alias="firstname" />
 </link-entity>
 </entity>
</fetch>

 

Have fun coding!

Do execution plans change when using different filter values?

(short answer: yes!)

Anyone who develops software that interacts with a database knows (read: should know) how to read a query execution plan, given by “EXPLAIN PLAN”, and how to avoid at least the most common problems like a full table scan.

It is obvious that a plan can change if the database changes. For example if we add an index that is relevant to our query, it will be used to make our query faster. And this will be reflected in the new plan.

Likewise if the query changes. If instead of

SELECT * FROM mytable WHERE somevalue > 5

the query changes to

SELECT * FROM mytable WHERE somevalue IN 
  (SELECT someid FROM anothertable)

the plan will of course change.

So during a database performance tuning seminar at work, we came to the following question: can the execution plan change if we just change the filter value? Like, if instead of

SELECT * FROM mytable WHERE somevalue > 5

the query changes to

SELECT * FROM mytable WHERE somevalue > 10

It’s not obvious why it should. The columns used, both in the SELECT and the WHERE clause, do not change. So if a human would look at these two queries, they would select the same way of executing them (e.g. using an index on somevalue if one is available).

But databases have a knowledge we don’t have. They have statistics.

Let’s do an example. We’ll use Microsoft SQL server here. The edition doesn’t really matter, you can use Express for example. But the idea, and the results, are the same for Oracle or any other major RDBMS.

First off, let’s create a database. Open Management Studio and paste the following (changing the paths as needed):

CREATE DATABASE [PLANTEST]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'PLANTEST', 
FILENAME = N'C:\DATA\PLANTEST.mdf' , 
SIZE = 180MB , FILEGROWTH = 10% )
 LOG ON 
( NAME = N'PLANTEST_log', 
FILENAME = N'C:\DATA\PLANTEST_log.ldf' , 
SIZE = 20MB , FILEGROWTH = 10%)
GO

Note that, by default, I’ve allocated a lot of space, 180MB. There’s a reason for that; We know that we’ll pump in a lot of data, and we want to avoid the delay of the db files growing.

Now let’s create a table to work on:

USE PLANTEST
GO

CREATE TABLE dbo.TESTWORKLOAD
	(
	testid int NOT NULL IDENTITY(1,1),
	testname char(10) NULL,
	testdata nvarchar(36) NULL
	)  ON [PRIMARY]
GO

And let’s fill it (this can take some time, say around 5-10 minutes):

DECLARE @cnt1 INT = 0;
DECLARE @cnt2 INT = 0;

WHILE @cnt1 < 20
BEGIN
	SET @cnt2 = 0;
	WHILE @cnt2 < 100000
	BEGIN
	   insert into TESTWORKLOAD (testname, testdata) 
             values ('COMMON0001', CONVERT(char(36), NEWID()));
	   SET @cnt2 = @cnt2 + 1;
	END;
	insert into TESTWORKLOAD (testname, testdata) 
          values ('SPARSE0002', CONVERT(char(36), NEWID()));
	SET @cnt1 = @cnt1 + 1;
END;
GO

What I did here is, basically, I filled the table with 2 million (20 * 100000) plus 20 rows. Almost all of them (2 million) in the testname field, have the value “COMMON0001”. But a few, only 20, have a different value, “SPARSE0002”.

Essentially the table is our proverbial haystack. The “COMMON0001” rows are the hay, and the “SPARSE0002” rows are the needles 🙂

Let’s examine how the database will execute these two queries:

SELECT * FROM TESTWORKLOAD WHERE testname = 'COMMON0001';
SELECT * FROM TESTWORKLOAD WHERE testname = 'SPARSE0002';

Select both of them and, in management studio, press Control+L or the “Display estimated execution plan” button. What you will see is this:

What you see here is that both queries will do a full table scan. That means that the database will go and grab every single row from the table, look at the rows one by one, and give us only the ones who match (the ones with COMMON0001 or SPARSE0002, respectively).

That’s ok when you don’t have a lot of rows (say, up to 5 or 10 thousand), but it’s terribly slow when you have a lot (like our 2 million).

So let’s create an index for that:

CREATE NONCLUSTERED INDEX [IX_testname] ON [dbo].[TESTWORKLOAD]
(
	[testname] ASC
)
GO

And here’s where you watch the magic happen. Select the same queries as above and press Control+L (or the “Display estimated execution plan” button) again. Voila:

What you see here is that, even though the only difference between the two queries is the filter value, the execution plan changes.

Why does this happen? And how?

Well, here’s where statistics are handy. On the Object Explorer of management studio, expand (the “+”) our database and table, and then the “Statistics” folder.

You can see the statistic for our index, IX_testname. If you open it (double click and then go to “details”) you see the following:

So (I’m simplifying a bit here, but not a lot) the database knows how many rows have the value “COMMON0001” (2 million) and how many the value “SPARSE0002” (just 20).

Knowing this, it concludes (that’s the job of the query optimizer) that the best way to execute the 2 queries is different:

The first one (WHERE testname = ‘COMMON0001’) will return almost all the rows of the table. Knowing this, the optimizer decides that it’s faster to just get everything (aka Full Table Scan) and filter out the very few rows we don’t need.

For the second one (WHERE testname = ‘SPARSE0002’), things are different. The optimizer knows that it’s looking only for a few rows, and it’s smartly using the index to find them as fast as possible.

In plain English, if you want the hay out of a haystack, you just get the whole stack. But if you’re looking for the needles, you go find them one by one.

How to make an espresso / cappuccino freddo

So you went for vacations in Greece or Cyprus or southern Italy and liked the cold coffee they serve there? Or maybe you have a Greek colleague who’s busting your balls non stop about how great cold coffee is, and just want him to shut up? You’re at the right place!

These recipe is for both espresso freddo and cappuccino freddo which are exactly the same thing; you just add cold foam milk on top of the espresso freddo to make the cappuccino version.

Over the years I’ve tried to simplify the recipe a bit. It’s not barista-level good, but anyone who’s tried it tells me it’s pretty decent.

To begin with, here’s the equipment you need:

  • A strong coffee mixer. This is an absolute must, you can’t do without it. Outside of Greece they are called “drink mixers” (you can find them in amazon.de for example). They look like this:

mixer

 

  • One or more suitable tall glasses. You need them to be around 200-250 ml for espresso freddo and 300-350 ml for cappuccino freddo. The ones from IKEA are fine.

Trinkglas

 

  • Two cocktail shakers, one for the milk and one for the coffee. It’s ok if you don’t have shakers though, you can just use normal glasses. But you can also buy them from amazon.de.

shaker

Now let’s see the stuff you need to prepare every time before you make cold coffee.

  • I’m sure you’ll be surprised to learn that you need coffee! Basically you need a double espresso, around 100ml. What I usually do is use the Lungo capsules for my Dolce Gusto machine, and set it to 3 lines instead of 4.
  • You also need straws, medium or thin ones. Don’t get the thick ones, they’re good for smoothies but not cold coffee.
  • You need ice cubes. For every coffee, you need 5-6.
  • If you’re going to make cappuccino (not espresso) freddo, you need milk, and you need it cold. Let me say that again, because it’s really really important: COLD. Ideally it should be 2 degrees. That means that you need to put it at the back of the fridge, not at the door where it’s a bit warmer. I usually put it in the refrigerator about 10min before I start. Keep it in the fridge until the moment you actually need it.
    You also need to experiment a bit with the kind of milk you’ll use. I’ve found that the best one -at least from the ones you find in a regular supermarket- is full fat UHT milk, 3.5%. The one you get at the fridge of the supermarket isn’t as good –no idea why. If you find a “barista milk” get it; they have more proteins so they froth better.
  • One of the shakers, the one to use for milk, has to be really, really cold. Put it in the refrigerator for at least an hour before making the coffee.

The basic idea is that, in order to make the foam milk, the milk has to be cold and stay cold. That’s why you need its container to also be frozen.

Now that we’ve prepared everything, let’s get to work.

  • The first thing you need to do is prepare the coffee. If you also want sugar, you need to add it immediately afterwards, while the coffee is still hot, and stir it a bit with the mixer; that way it will melt nicely and you won’t get the awful crunchy feeling of unmelted sugar.
  • Now we need to get our coffee ice cold. Put 5 or 6 ice cubes in the shaker or glass. Pour the coffee swiftly over the ice cubes. Stir it a bit with the mixer, but too much, you don’t want it to turn into foam. 5-6 seconds should be enough. Then pour everything (coffee+ice cubes) in the glass. 
  • If you want an espresso freddo, you can add a straw and stop here, you’re done. Otherwise you have one more step to prepare the cold foam milk. 
  • Get the milk and the 2nd shaker (or glass) out of the fridge. Fill the shaker just below half full. Stir it with the mixer for some time (at least 30 sec, can be more) until the surface is smooth and free of bubbles. This part is exactly why the shaker has to be cold. If it’s not, it will warm up the milk and it will be impossible to turn into foam.

Pro (well, sort of) tip: when holding the shaker with the milk and stirring, try to grab it from the top, not the middle or the bottom. That way the heat from your hand will affect the milk as little as possible.

The result should, ideally, look like this:

Αποτέλεσμα εικόνας για καπουτσινο φρεντο

The water -always with ice cubes!- is mandatory. The beach isn’t, but it’s a very nice addition 😉

Oracle PL/SQL: how is AND evaluated?

Coders used in C#, Java etc. know there are two ways to evaluate a logical AND. In C# you can do either

if (test1) & (test2)
{
  // whatever
}

or

if (test1) && (test2)
{
  // whatever
}

The difference, of course, is that in the first case (&) BOTH test1 and test2 are evaluated. This doesn’t matter much if test1 and test2 are variables, but it matters a lot if they’re methods. This of the following example:

if (reserveItemsForOrder()) && (sendOrderToErp())
{
  // whatever
}

In this fictional case, && means that the order will be sent to the ERP system only if items can be reserved. If the single & is used, however, it will be sent anyway –even if not enough stock can be found.

This is well known in languages like C, C++, C#, Java etc. But how is AND evaluated in Oracle?

In short, it’s the same as &&. But for a more complete explanation, let’s read it from Oracle itself:

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error. Consider the following OR expression:

DECLARE

on_hand INTEGER;
on_order INTEGER;
BEGIN
..
IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN

END IF;
END;

When the value of on_hand is zero, the left operand yields TRUE, so PL/SQL need not evaluate the right operand. If PL/SQL were to evaluate both operands before applying the OR operator, the right operand would cause a division by zero error. In any case, it is a poor programming practice to rely on short-circuit evaluation.