Selecting from a column with possible NULLs in T-SQL

I write blog posts for different reasons. Sometimes it’s because things aren’t well documented elsewhere, sometimes it’s new and interesting and sometimes it’s purely for my own amusement. Occasionally I write things as I kind of penance – things I should know about but bizarrely forgot or misunderstood. This is one of those. It’s how to select from a column with possible NULLs and not omit expected rows from the results set. This has been documented at length elsewhere but that’s not the point of penance.

Test Cases – equals operator

Working through some examples when comparing NULLs with the equals operator.

NULL is equal to ‘test’

False: as expected

NULL is not equal ‘test’

False:  less expected

NULL is not equal to NULL

False: more expected

NULL is equal to NULL

False: odd again

NULL isn’t equal to anything including itself when using the equals operator. I know this really and we see the same kind of thing in JavaScript.

Test cases – IS NULL

Of course it well known that equals doesn’t do the job with NULLS. The IS NULL operator is correct

Null is null

True.

Null is not null

False.

All as expected.

Test case -selecting from a column with possible NULLs

So this is what tripped me up – when you have a column that is nullable and trying to filter as a string.

I naively thought that there are 2 rows that aren’t ‘Test’ so we will get those. But since NULL is not equal to ‘Test’ and not not equal to ‘Test’ then actually we get one less row than we expected

The correct select is

Which converts the NULL to an empty string so then works

Also works and is the same. Penitential blog post over.

Useful Links

https://stackoverflow.com/questions/18828641/sql-difference-between-coalesce-and-isnull
Comparison of ISNULL and COALESCE

http://adripofjavascript.com/blog/drips/equals-equals-null-in-javascript.html
It’s not just T-SQL. Special handling around NULLs in JavaScript

SQL Server restore fails if backups taken in rapid succession

How I (unfairly) think of SQL Server

I don’t spend an awful lot of time thinking about SQL Server. To me it’s a workhorse which I think is a compliment. * It’s steady, reliable and gets the job done. It’s not glamorous though and people don’t talk about it at dinner parties.

I did have cause to give the old SQL Server workhorse a bit more thought recently. I’m having to do a bit of database work and I’m taking lots of backups in rapid succession and restoring them. For some reason the backups didn’t restore the most recent version of the database. The restored DB was weirdly out of date. I got round this by going to options on the backup dialog and selecting ‘Overwrite existing backup sets’

Once I’d blatted out the existing sets then it worked fine. I don’t know why it wouldn’t work if I appended to backup sets and clearly this is a development environment workaround. I’m not advocating people merrily wiping out backup sets on production sets. A better workaround might have been to start using snapshots but once the workhorse was lashed back to the plough then I was happy. That said, I’d be very interested to know why it was doing that if anyone knows.

* On reflection, I wouldn’t like it if called me a workhorse so perhaps it’s not as much of a compliment as I imagine.

LINQ FirstOrDefault implementation in TypeScript

Proust looking of of the window thinking about LINQ operators

Confession: when working in JavaScript languages I kind of miss some C# things such as LINQ. A substantial part of my C# coding seemed to be chaining LINQ operators together. So rather than staring wistfully out of the window  in remembrance of things past – I thought I would just try to write one of my own. I’ve picked FirstOrDefault for this honour.

FirstOrDefault

For the uninitiated FirstOrDefault is a LINQ operator in C# that takes a function which resolves to a boolean – a predicate. It has the following characteristics

  1. Returns the first object in a collection that matches the predicate
  2. If it can’t find one then it will return null
  3. It won’t throw an exception if it can’t make the match – in contrast to it’s harsher sibling SingleOrDefault that will

TypeScript

I’m writing this In TypeScript – simply because that’s what I’m using at the moment. It would actually be easier to do this in plain JavaScript – the prototype will be the same and they would be no steps to hook it into the TypeScript interface. But we will do it in TypeScript

Implementation

Step 1: Extending Array Type

In C#, the LINQ operators are an extension of IEnumerable<T>. In TypeScript we will extend the Array object which is a similar principle. In Typescript type definitions go in .d.ts files. I’m working in Angular and there is handily a definition file already present – typings.d.ts. In that file put the following

This adds a firstOrDefault defintion to the Array type. It takes a function just like the C# first or default method. As we are using TypeScript we can specify a type of function for the parameter so we will get compile errors if we try to supply anything else to this method. Of course there is no implementation yet ….

Step 2: Implementing the LINQ operator

Create a file e.g. Linq.ts and enter the following

This method is going to do the work – it’s the meat and potatoes. We are leveraging the reduce operator. Reduce flattens a collection into a single value – a simple example would be totalling all the numeric values in an array i.e.

For us flattening the array means returning the first value that matches the predicate. So we iterate through until predicate(currentValue) evaluates to true then we stash the value in the accumulator object and once there we never write over it.

An interesting quirk to this is what happens on the first loop – if we call it like this

i.e. reduce with a single argument. In the first iteration the accumulator has the value of the first object in the array; the currentValue has the second. The first item is already stashed in the accumulator and the predicate is never checked – the first item is always returned no matter what the filter is. This is exactly what we don’t want. We need to supply a second argument to reduce …..

By supplying a second argument we specify what the initial value of the accumulator is. We want a initial value of null so our LINQ operator will attempt the match each time until it succeeds.

As a side note – I’m aware that there are any number of ways to achieve this but I’ve a theory that all of the LINQ operators can be done with JavaScript reduce, map or filter methods so this is my first step to prove that theory out. It can be done and it will be done (if I get the time).

Step 3. Consuming the LINQ operator

Import the linq prototype and use thus

This returns the first object in the array with an Id of 1. This compares quite nicely with the C# FirstOrDefault method which is

Which looks pretty similar to me and works in the same way. It doesn’t do a great job with type checking though and since we are using TypeScript we can do a bit better.

Implementation with Generics

We can use generics in TypeScript to get type checking in the predicate and get a typed returned value. An improvement I think.

Step 1: Extending Array Type

Go to the d.ts file again and use this

Which defines a function that takes type T and returns a boolean – our filtering predicate.

And a generic array extension which uses the typed predicate instead of the more generic Function type.

Step 2: Implementing the LINQ operator

Change the implementation to this

Which uses the generic types throughout.

Step 3. Consuming the LINQ operator

If we were using a collection of persons, the new generic powered firstOrDefault would be called like this

We can now get decent intellisense when filling in the filter conditions and type checking so we don’t compare the wrong types. It’s slightly more verbose than the first example but safer to use. I prefer it, but use whichever floats your TypeScript boat – both work.

Useful Links

https://codepunk.io/extending-strings-and-other-data-types-in-typescript/
More detail about extending built in types in TypeScript

https://danmartensen.svbtle.com/javascripts-map-reduce-and-filter
Nice comparison of the map, filter and reduce methods in JavaScript and guidance about which to use when.

https://www.tutorialspoint.com/linq/linq_query_operators.htm
Overview of LINQ operators. There’s more than you think.

https://stackoverflow.com/questions/7192040/linq-to-entities-vs-linq-to-objects-are-they-the-same
When I’m talking about LINQ I’m really talking about LINQ to objects. There is LINQ to SQL, LINQ to entities, LINQ to XML etc.. This SO question points out the difference.

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/query-syntax-and-method-syntax-in-linq
There are two styles of operators in LINQ – lambda and query syntax. Query syntax is

And the equivalent lamdba is

I’m emulating the lamdba syntax here. I never use the query syntax – I think it was easy route to LINQ for people more familiar with SQL. The lamdba is a more functional approach and nearer to what the code is actually doing. Anyway the query operator was only ever a sub set of the lamdba.

Copying static files into build directory with Angular

This is probably obvious for Angular aficionados and aficionadistas but I found how to copy static files into the output directory of an angular application non-obvious.

To do it go to angular-cli.json in your project root and add the static file to the assets collection thus

And it places it nicely into the output folder which is dist by default. I found this useful when using a web.config file to set up some URL rewriting rules in an IIS hosting scenario i.e..

Just though I would break it out of that post and flag up how to do it when I had a spare 10 mins between cleaning the bathroom and taking the daughter to swimming lessons (too much information – sorry).

Hosting Angular in IIS under an existing website

I recently had cause to put an Angular application a IIS sub application under an existing .Net website so effectively they share the same host url. It took me longer than it should have done – I made heavy weather of it. Some issues were just fiddly and took some working out. Some issues I had were the result of my own sub-optimal performance in this technical area (stupidity). It was Saturday afternoon so I’m allowed sub -optimal performance in my own time.

Tutorial

There is already a good tutorial here. So I’m not proposing to go through it step by step, Instead I’ll quickly outline the basic steps then point out where I went wrong. I suffer so you don’t have to.

Motivation

Just briefly – my motivation for doing this was to have Angular running under the same address as the API it was calling.

So

Is called by an angular app under a virtual directory at

This is to avoid Cross Origin Scripting Issues in my own environment. I know there are other ways to do this – but they either

  1. Never worked
  2. Were too invasive to implement on the API side (for a spare time demo project)
  3. Did work then frustrating stopped working

So I bit the bullet and moved Angular under the API project where it is always going to work for me i.e. they use the same host url so are no longer cross origin.

Create the Sub Application

So very briefly – ceate a sub application under main application to get this configuration

Point virtual directory to Angular app and browse. Boom – It doesn’t work!

Error 1: Using a .Net App pool

First issue is this yellow screen of death

The exact error will vary but essentially the angular application complains that it is missing assemblies, handlers, modules etc… It wants to be a .Net application. It doesn’t need to be.

When setting up the new application the default will be a .Net integrated application pool

To stop it behaving like a .Net app – create a new app pool with .Net CLR version of ‘No Managed Code’

Assign this to the new angular application in IIS and the site is no longer a .Net wannbe. The YSOD is gone.

Error 2: Pointing to the wrong folder

No yellow screen of death, instead the application is entirely blank. This is where I was being particularly sub-optimal. So I pointed it to my angular application at

This is never going to work. Angular builds into this directory by default

Point the sub application here. Why I thought this would magically work I’ve no idea. Although IIS had stopped behaving like a .Net site, I clearly carried on thinking it was one and pointed to the root folder as I would a .Net site. 15 minutes of my life working this out that I am never going to get back. Oh well.

Error 3: Correcting Angular script references

So run the app again and sadly it is still blank. This time if you look at chrome developer toolbar or similar you will see a lot of 404 errors

What’s going on here is that Angular is still behaving as if it is loading all scripts from the root website. To correct this

  1. Use base href element in the root index page i.e.

1. Change the ng build command from

to

This will point Angular to the right direction i.e. looking at the dist folder the script references have changed from

to

Which is correct for our sub application.

As a side note – I always forget these kind of command lines. To avoid the need for additional brain friction use the npm start command to run the ng build command with the extra parameters. So go to packages.json in the root directory of your angular app and change the npm start node under scripts to the build command i.e.

Now to build the app use

And the build command runs with the correct params.

Error 4: Refreshing page causes 404

If you are using routing you will find that refreshing a page when on a route (i.e. demo-app/Angular/page1) will give a 404 error, To resolve this one

1. Install Url rewrite

2. Include a web.config page into your src folder with these url rewrite rules

3. Include the web.config file in project assets. Go to .angular-cli.json and add web.config to the asset node like so

If you omit this step then the web.config won’t be copied over to the dist folder on build. If you add it to the dist folder manually then it will be deleted on build so you won’t be any further forward.

Once all these steps are done, refreshing on a route this will reissue the request up to the base url which will resolve correctly.

Finishing up

Now the angular app runs nicely under a sub application in IIS and my Cors problem is no longer an issue. There are a couple of disadvantages to running it this way

  1. Hot reloading of the application on rebuild doesn’t work
  2. Related to this – the application sometimes caches and a hard refresh of the browser (ctrl, F5) is sometimes needed to kick it back to life.
  3. When refreshing a page it will redirect back to the root so you lose whatever route you are on so

Refreshed becomes

Which may or may not be what you want

These are probably resolvable but when I got my development environment to this point, I declared loudly to all with ears to hear – Job done!

Useful Links

https://docs.microsoft.com/en-us/iis/extensions/url-rewrite-module/creating-rewrite-rules-for-the-url-rewrite-module
Url Rewriting module in IIS – official microsoft page

https://blogs.msdn.microsoft.com/webdev/2006/06/30/part-1-of-3-creating-sub-projects-in-iis-with-web-application-projects/
Setting in sub applications in IIS – detailed tutorial

https://en.wikipedia.org/wiki/Cross-origin_resource_sharing
Wiki page about CORS which was causing me all the problem in the first place

 

Yet Another Free JavaScript Book: Angular 5

As part of my unplanned and unasked for blog series – Free JavaScript books, here is another. So introducing ‘Angular 5: From Theory To Practice: Build the web applications of tomorrow using the new Angular web framework from Google’ by Asim Hussain. It’s not exactly vanilla JavaScript as such – it’s Angular 5 and TypeScript but we’re all friends here so I’m lumping it in with the other free JavaScripts books I’ve written about.

And it’s a good one. Pitched at the right level, good quick start chapter so you can see how it all fits together early on and a nice primer on TypeScript in chapter 2. So, how much for hundreds of pages of Angular goodness delivered to a Kindle device? Well to you Madam/Sir, it is zero pounds, shillings and pence or nada dollars if you prefer. Oddly, if you are trying to get this on Australian Amazon then you are out of luck – clearly the bits and bytes can’t travel that far (though a VPN connection might help in that circumstance).

But Amazon Australia not withstanding, this book is worth the money. I’d even go further – this book would be worth the money if Mr Hussain ever decides to charge for it. Thank you Asim.

Angular pipe to truncate text to the nearest whole word

The problem

I want to truncate text in Angular 5 using a pipe. I don’t just want to chop the text up inbetween words. I want to truncate to the nearest whole word. And I want a ellipses on. I want it all. So the following text as an example

Mind precedes all mental states. Mind is their chief; they are all mind-wrought. If with an impure mind a person speaks or acts suffering follows him like the wheel that follows the foot of the ox.

Truncated to a length of not more than 150 would be

Mind precedes all mental states. Mind is their chief; they are all mind-wrought. If with an impure mind a person speaks or acts suffering follows…

I want that – and as luck would have it is actually a nice quote. Purely coincidental of course.

Starting off

First let’s use angular cli scaffolding to get us the base pipe. So open the command line and run

Or if you prefer

And the following js file pops out

Nice start. The pipe metadata tells us that to use it in the html page we will be using the truncatetext tag. It’s pretty obvious where our implementation is going to be.

The Implementation

So filling out the implementation gives us

So a bit at a time. The signature has changed to

Value is the string that is to be truncated. Length is our target (maximum) length. It returns a string which is the truncated text. There is a bit of parameter checking then

I’ve taken the view that the biggest word I’m likely to deal with is

Pneumonoultramicroscopicsilicovolcanoconiosis

So it’s 45 letters plus 5 for any spelling mistakes for a 50 character margin. (I’m ignoring the 189,819 letter protein name that someone unearthed – I guess biochemists need to use this Angular filter with caution).

So I truncate at my desired length plus 50 then nibble backwards at each space

Until I get to my length then glue on the ellipse and return it out. The only other thing Is

A sprinkle of regex to nibble off any comma, question mark etc.. What I don’t want is a result like

Mind precedes all mental states. Mind is their chief;…

It just looks weird.; I want

Mind precedes all mental states. Mind is their chief…

Much better.

Using the pipe

So to actually use the pipe in the component we need to declare it in the app.module e.g.

If you’ve use the angular cli command then that would have been done for you. Then to use in the component it is simply

And then that is it – my text is truncated in my angular app and looks something like this

Lovely filter; lovely quote.

Variant for truncating to a small length

It’s an edge case for me but there is the case where the filter is set to a (stupidly) small value e.g.

Taking into account the ellipses, the logic would be that the entire string evaporates to just the ellipse or perhaps nothing. I don’t really want that so this filter will return the first word with an ellipse as the minimum return value i.e.

If you want the behaviour where the output strictly adheres to the length or less then it would be something like ..

So it will return the length or less in all cases – if you really want that.

Demo App

As ever the code is on my github site here. It’s part of a bigger app and associated API that generates Buddhist quotes from the Pail Canon. All work in progress but I quite like it.

Useful Links

https://angular.io/guide/pipes
Official documentation for angular pipes. Useful

https://en.wikipedia.org/wiki/Longest_word_in_English
Biggest words in English for edge cases and general marvelment

https://www.accesstoinsight.org/tipitaka/kn/dhp/dhp.01.budd.html
The example text is the first verse of the Dhammapada -original translations by Acharya Buddharakkhita. The first verses are all in pairs so the example text with its partner is ..

Mind precedes all mental states. Mind is their chief; they are all mind-wrought. If with an impure mind a person speaks or acts suffering follows him like the wheel that follows the foot of the ox.

Mind precedes all mental states. Mind is their chief; they are all mind-wrought. If with a pure mind a person speaks or acts happiness follows him like his never-departing shadow.

Tremendous – one of my favourites.

.Net Core 2.0: project.assets.json’ doesn’t have a target for ‘.NETStandard,Version=v2.0’

The Problem

Odd error when building a .net core project with Visual Studio Code. This built a few weeks ago – went back it it and I’m getting this

C:\Program Files\dotnet\sdk\2.0.0\Sdks\Microsoft.NET.Sdk\build\Microsoft.PackageDependencyResolution.targets(165,5): error : Assets file ‘myproject\obj\project.assets.json’ doesn’t have a target for ‘.NETStandard,Version=v2.0’. Ensure that restore has run and that you have included ‘netstandard2.0’ in the TargetFrameworks for your project. [myproject\MyProject.csproj]

.Net Core has been installed, it used to work and there is not much on the Internet. Bafflement.

Solution

Turns out the bin and obj folders for each of the project need to be manually deleted. Once done then the command

gave me the green light and normal service has been resumed. No idea what it going on but sorted now – so I thought I would take a minute and post the solution to my small corner of the Internet. Cheers.

Multiple Projects with .Net Core and Visual Studio Code

The challenge is to create a solution in VS Code with multiple projects. There are three projects

  1. A console application
  2. A web api application
  3. A library with data access code that is common to both.

It will look like this when we are finished

Expanded to show detail

We need a solution that can build out all these projects in one click. Since we using VS Code and enjoying it’s lightweightness we’ll be relying completely on the new .Net Core Command Line Interface. I enjoy a challenge so using the command line seems fun (to me). Let’s go.

(TL;DR;)

In case you don’t want to read the detail – the commands used to set up all projects, solutions and references are

From project root

Preliminary

To do this you’ll need VS Code’s C# extension installed into visual studio. Go to extensions and search for C#.

Install and away you go.

Note on command line

All .Net Core CLI commands can be entered into the terminal window in VS Code or into the standard command prompt if you prefer. Navigate to the root folder and enter your first command

Create Console Application

This installs our console application project. Since we are on .Net Core 2.0 we get the csproj file rather than the json project file of earlier versions. This installs the console app in a new folder with the same name as it’s containing folder. If we want a different name to the folder we do that by

But we don’t want that. We are keeping it simple. What I like about .Net Core is that it’s had all the gubbins stripped out. So our proj file for the console app is simply …

Nice. I understand that.

Create Common Library

Next we want to create a C# dll to host some common code like data access.

Creates an empty project called Demo.Common with a nice simple proj file thus

Link Common Library to Console app

Change to the console app direct and run the add reference common

Inserts the following into the console proj file

So the console app can access code in the common project and the two will build out together.

Add Nuget package to Common Library

We commonly want to add some nuget packages to our projects. So lets add the HtmlAgility pack and a C# driver for MongoDB – no reason for them other than I often use them.

Change directories back to common library

Now add the packages

Look at the proj file and you can see the package references inserted

VS Code likes to tell me to restore the project I.e. run dotnet restore. You don’t need to. Dotnet build or dotnet run does it for you.

Build Console Application

Let’s check things are working as they should and build our console app.

Output

  Demo.Common -> C:\development\Demo\ASP.NET Core\MultipleProjects\Demo.Common\bin\Debug\netstandard2.0\Demo.Common.dll

Demo.ConsoleApp -> C:\development\Demo\ASP.NET Core\MultipleProjects\Demo.ConsoleApp\bin\Debug\netcoreapp2.0\Demo.ConsoleApp.dll

Build succeeded.

0 Warning(s)

0 Error(s)

Good – by building the project we get both common and the console application building.

Create API 

Return to root folder

Now add a web api project called Demo.Api

Change to the Api folder

Now add a reference to our common library

The Api proj file looks a bit more complex but I’ll still pretty minimal compared to Visual Studio proj files.

Now let’s build that project. Staying in the Api folder

And the output shows us two projects have been built.

  Demo.Common -> C:\development\Demo\ASP.NET Core\MultipleProjects\Demo.Common\bin\Debug\netstandard2.0\Demo.Common.dll

Demo.Api -> C:\development\Demo\ASP.NET Core\MultipleProjects\Demo.Api\bin\Debug\netcoreapp2.0\Demo.Api.dll

Build succeeded.

0 Warning(s)

0 Error(s)

Joining everything together

Ok , so we want all the projects to build in one click. Because we have two independent applications (an API and a console app) we aren’t going to be able to do the build by navigating to a folder and building the proj file. We need a solution file to bind them all together

(this shamefully baffled me for a while. I was trying all sorts with launch.json etc… The solution is obvious though – embarrassed for not getting it immediately)

Back to root

Now add the solution file. We don’t want it in it’s own directory so omit the -o parameter. Add in the -n otherwise it takes it’s name from the containing folder which we don’t want in this case.

Now include the API and Console app

The solution file now includes the project info

Staying in the solution directory run build

And now all three projects are built.

Demo.Common -> C:\development\Demo\ASP.NET Core\MultipleProjects\Demo.Common\bin\Debug\netstandard2.0\Demo.Common.dll

Demo.ConsoleApp -> C:\development\Demo\ASP.NET Core\MultipleProjects\Demo.ConsoleApp\bin\Debug\netcoreapp2.0\Demo.ConsoleApp.dll

Demo.Api -> C:\development\Demo\ASP.NET Core\MultipleProjects\Demo.Api\bin\Debug\netcoreapp2.0\Demo.Api.dll

Build succeeded.

0 Warning(s)

0 Error(s)

Finished!!!

Demo code

The rather uninspiring skeleton demo app is at
https://github.com/timbrownls20/Demo/tree/master/ASP.NET%20Core/MultipleProjects

More interesting the same pattern is on a .Net Core API for some Buddhist texts I’ve been working on
https://github.com/timbrownls20/Pali-Canon/tree/master/Api

So

https://github.com/timbrownls20/Pali-Canon/tree/master/Api/PaliCanon.Loader
Is the console app that parses HTML and populates a document database with the texts

https://github.com/timbrownls20/Pali-Canon/tree/master/Api/PaliCanon.Api
Is the web api to access the texts

https://github.com/timbrownls20/Pali-Canon/tree/master/Api/PaliCanon.Common
Is the model and repositories common to both.  It’s the same pattern as the simplified demo app and I’m using VS Code for that – just to try it out really.

Useful Links

https://docs.microsoft.com/en-us/dotnet/core/tools/?tabs=netcore2x
Full documentation for .Net Core Command Line Interface

https://code.visualstudio.com/download
Visual Studio Code is freely available here.

Resharper Build and Run

Just a quick shout out for Resharper’s new build and run feature. I’ve become accustomed to Resharper slowing my machine up as it endlessly loads a variety of caches and processes. Speaking as a member of the slower development machine community it is a delight to see a new feature that actually speed things up and increases productivity even on less than optimal machines. Build and run uses some heuristics (i.e. clever workings out) to analyse which projects actually need to be built then only builds these. On first build you don’t notice much of a difference but on subsequent build it makes for dramatic increases of speed. It’s impressive and a real benefit.

I won’t bore too much with my current slow machined troubles but I currently a home based contractor working on a mid range laptop – I anticipated this laptop to be used for some lightweight home development projects. Inside it’s running 2 Visual Studio instances with 35 and 12 projects each respectively. Really it can’t cope – except it suddenly can using Build and Run. 5 minutes build times have become 10 second affairs. It’s not too much to say that it has been a bit of a life (job) saver.

Of course the real solution is to buy a better machine. But as I find myself in a new, more expensive country funds are a little short right now. So until I spring for a new laptop, Resharper Build and Run is keeping me afloat. Thanks guys.

EDIT

I’ve actually now done the decent thing and upgraded my laptop to use a 500GB SSD disc and popped in some extra RAM while I was at it. It runs like a dream. I close down and reopen Visual Studio now just for the fun of it – takes seconds. I still like Resharper Build and Run though, but it’s now less critical to my health and well being.