Importing Json File to SQL Server into a variable

A short article today of how to import JSON file to SQL Server into a variable, which can then
be used to insert it into a column of type NVARCHAR(MAX) of a table. The maximum size of NVARCHAR(MAX) is 2 Gb, so you can
import large Json files using this datatype. If the Json is small and below 4000 chars, use for example NVARCHAR(4000) instead. Here is a SQL script to import the json file using OPENROWSET and Bulk import. We also pass in the path to the folder where the json file is. It is put in the same folder as the .sql file script. Note that the variable $(FullScriptDir) is passed in via a .bat file (shown further below) and we expect the .json file to be in the same folder as the .bat file. You can provide a full path to a .json file instead and skip the .bat file here and import a json file, but it is nice to load the .json file from the same folder as the .sql file in case you want to copy the .sql and .json file to another server and not having to provide and possibly having to adjust the full path. Sql-script import_json_file_openrowset.sql:

DECLARE @JSONFILE VARCHAR(MAX); 

SELECT @JSONFILE = BulkColumn
FROM OPENROWSET (BULK '$(FullScriptDir)\top-posts.json', SINGLE_CLOB) AS j;

PRINT 'JsonFile contents: ' + @JSONFILE

IF (ISJSON(@JSONFILE)=1) PRINT 'It is valid Json';

The .bat file here passes the current folder as a variable to the sql script runsqlscript.bat

@set FullScriptDir=%CD%
sqlcmd -S .\SQLEXPRESS  -i import_json_file_openrowset.sql

This outputs:

sqlcmd -S .\SQLEXPRESS  -i import_json_file_openrowset.sql
JsonFile contents: [
   {
      "Id":6107,
      "Score":176,
      "ViewCount":155988,
      "Title":"What are deconvolutional layers?",
      "OwnerUserId":8820
   },
   {
      "Id":155,
      "Score":164,
      "ViewCount":25822,
      "Title":"Publicly Available Datasets",
      "OwnerUserId":227
   }
]
It is valid Json

With the variable JSONFILE you can do whatever with it such as inserting it to a column in a new row of a table for example.
Importing json from a string directly using OPENJSON

It is also possible to directly just import the JSON from a string variable like this:

DECLARE @JSONSTRINGSAMPLE VARCHAR(MAX) 

SET @JSONSTRINGSAMPLE = N'[
 {
    "Id": 2334,
    "Score": 4.3,
    "Title": "Python - Used as scientific tool for graphing"
 },
{
    "Id": 2335,
    "Score": 5.2,
    "Title": "C# : Math and physics programming"
 }
]';

SELECT * FROM OPENJSON (@JSONSTRINGSAMPLE) WITH (
    Id INT,
    Score REAL,
    Title NVARCHAR(100)
)

Functional programming - Fork combinator in C# to combine results from parts

This article will discuss a wellknown combinator called Fork which allows you to combine the mapped result. Consider the following extension methods to fork on an object. Fork here means to operate on parts of the object such as
different properties and apply functions on these parts and then recombine the results into a combined result via a specified combinator function, sometimes called a 'join function'.

public static class FunctionalExtensions {

	public static TOutput Map<TInput, TOutput>(
		this TInput @this,
		Func<TInput, TOutput> func) => func(@this);

	public static TOutput Fork<TInput, TMiddle, TOutput>(
		this TInput @this,
		Func<IEnumerable<TMiddle>, TOutput> combineFunc,
		params Func<TInput, TMiddle>[] parts)
	{
		var intermediateResults = parts.Select(p => p(@this));
		var result = combineFunc(intermediateResults);
		return result;
    }

	public static TOutput Fork<TInput, TMiddle, TOutput>(
		this TInput @this,
		Func<TInput, TMiddle> leftFunc,
		Func<TInput, TMiddle> rightFunc,
		Func<TMiddle, TMiddle, TOutput> combineFunc)
	{
		var leftResult = leftFunc(@this); // @this.Map(leftFunc);
		var rightResult = rightFunc(@this); // @this.Map(rightFunc);
		var combineResult = combineFunc(leftResult, rightResult);
		return combineResult;
	}

}

Let's take a familiar mathematical example, calculating the Hypotenuse in a triangle using Pythagorean theorem. This states that the length of the longest side A of a 'right triangle' is the square root of the sum of the squares of the shorter sides B and C : A = √(B² + C²) Consider this class:

  
  
  public class Triangle {
	public double CathetusA { get; set; }
	public double CathetusB { get; set; }	
	public double Hypotenuse { get; set; }
  }
  
    

Let's test the first Fork helper extension method accepting two functions for specifying the left and right components:

  
  
  	var triangle = new Triangle
	{
		CathetusA = 3,
		CathetusB = 4
	};
	
	triangle.Hypotenuse = triangle.Fork(	
		t => t.CathetusA * t.CathetusA, 
		t => t.CathetusB * t.CathetusB, 
		(l,r) => Math.Sqrt(l+r));
		
	Console.WriteLine(triangle.Hypotenuse);
  
  
  

This yields '5' as the answer via the forked result above. A simple example, but this allows us to create a simple combinatory logic example on an object of any type using functional programming (FP). Let's look at a simpler example just combining multiple properties of an object with a simple string-join, but using the Fork version supporting arbitrary number of parts / components:

 


public class Person {
	public string JobTitle { get; set; }
	public string FirstName { get; set; }
	public IEnumerable<string> MiddleNames { get; set; }
	public string LastName { get; set; }
}

var person = new Person{
		JobTitle = "Detective",
		FirstName = "Alexander",
		MiddleNames = new[] { "James", "Axel" },
		LastName = "Foley"
	};
	
string contactCardText = person.Fork(parts => string.Join(" ", parts), p => p.FirstName, p => string.Join(" ", p.MiddleNames), p => p.LastName);
Console.WriteLine(contactCardText);

This yields: Alexander James Axel Foley Fork can be very useful in many cases you need to 'branch off' on an object and recombine parts of the object with some specific function, either two parts or multiple parts and either continue to work on the results or retrieve the results.

Functional programming - the Tee function to inspect current state in a chained expression

In this article we will look at helper extension methods of StringBuilder first to better support chaining StringBuilder. We will work on the same StringBuilder instance and add support for appending lines or character to the StringBuilder given a condition. Also example showing how to aggregate lines from a sequence is shown and appending formatted lines. Since C# interpolation has become more easy to use, I would suggest you keep using AppendLine instead. Here is the helper methods in the extension class :

public static class StringBuilderExtensions {

	public static StringBuilder AppendSequence<T>(this StringBuilder @this, IEnumerable<T> sequence, Func<StringBuilder, T, StringBuilder> fn)
	{
		var sb = sequence.Aggregate(@this, fn);
		return sb;
	}
	
	public static StringBuilder AppendWhen(this StringBuilder @this, Func<bool> condition, Func<StringBuilder, StringBuilder> fn) => 
		condition() ? fn(@this) : @this;
		
    public static StringBuilder AppendFormattedLine(
		this StringBuilder @this,
		string format,
		params object[] args) => 
			@this.AppendFormat(format, args).AppendLine();
	
}

Now consider this example usage:

void Main()
{
	var countries = new Dictionary<int, string>{
		{ 1, "Norway" },
		{ 2, "France" },
		{ 3, "Austria" },
		{ 4, "Sweden" },
		{ 5, "Finland" },
		{ 6, "Netherlands" }
	};
	string options = BuildSelectBox(countries, "countriesSelect", true);
	options.Dump("Countries"); //dump is a method available in Linqpad to output objects 
	
}

private static string BuildSelectBox(IDictionary<int, string> options, string id, bool includeUnknown) =>
		new StringBuilder()
			.AppendFormattedLine($"<select id=\"{id}\" name=\"{id}\">")
			.AppendWhen(() => includeUnknown, sb => sb.AppendLine("\t<option value=\"0\">Unknown</option>"))
			.AppendSequence(options, (sb, item) => sb.AppendFormattedLine("\t<option value=\"{0}\">{1}</option>", item.Key, item.Value))
			.AppendLine($"</select>").ToString();   

What if we wanted to inspect the state of the stringbuilder in the middle of these chained expression. Is it possible to output state in such lengthy chained functional expressions? Yes, that is called the Tee method inside functional programming patterns. Other might call it for Tap such as used in Rx languages. The Tee method looks like this:

 
 
public static class FunctionalExtensions {

	public static T Tee<T>(this T @this, Action<T> act) {
		act(@this);
		return @this;
	}
	
}

 

We can now inspect state in the middle of chained expressions in functional expressions.

 
 
 
private static string BuildSelectBox(IDictionary<int, string> options, string id, bool includeUnknown) =>
		new StringBuilder()
			.AppendFormattedLine($"<select id=\"{id}\" name=\"{id}\">")
			.AppendWhen(() => includeUnknown, sb => sb.AppendLine("\t<option value=\"0\">Unknown</option>"))
            .Tee(Console.WriteLine)
			.AppendSequence(options, (sb, item) => sb.AppendFormattedLine("\t<option value=\"{0}\">{1}</option>", item.Key, item.Value))
			.AppendLine($"</select>").ToString();   
 
 

The picture below shows the output:

So there you have it, if you have lengthy chained functional expressions, make such a Tee helper method to peek into the state this far. The name Tee stems from the Unix Command by the same name. It copies contents from STDIN to STDOUT. More about Tee Unix command here:

https://shapeshed.com/unix-tee/

Functional programming - looking up current time and encapsulating usings

I looked at encapsulating Using statements today for functional programming and how to look up the current time with API available on the Internet.

public static class Disposable {
	
	public static TResult Using<TDisposable,TResult>(
		Func<TDisposable> factory,
		Func<TDisposable, TResult> map)		
		where TDisposable : IDisposable
	{
		using (var disposable = factory()){
			return map(disposable);
		}
		
	}	
}

void Main()
{
	var currentTime = EpochTime.AddSeconds(Disposable
			  .Using(() => new HttpClient(),
					client => JsonDocument.Parse(client.GetStringAsync(@"http://worldtimeapi.org/api/timezone/europe/oslo").Result))
			  .RootElement
			  .GetProperty("unixtime")
			 .GetInt64()).ToLocalTime(); //list of time zones available here: http://worldtimeapi.org/api/timezone
	currentTime.Dump("CurrentTime");	
}

public static DateTime EpochTime => new DateTime(1970, 1, 1);

The Disposable is abstracted away in the helper method called Using accepting a factory function to create a TDisposable that accepts an IDisposable. We look up the current time using the WorldTimeApi and make use of extracting the UnixTime which is measured from Epoch as the number of seconds elapsed from 1st January 1970. We make use of System.Text.Json here, which is part of .NET to parse the json retrieved.

Currying functions in C#

This article will look into helper methods for currying functions in C#. The definition of Currying consists of splitting up a function with multiple arguments into multiple functions accepting one argument. But you can also have some of the arguments provided via smaller functions, so be aware also of this alternative. What is in the name currying? The name has nothing to do with cooking from India, but comes from the mathematician Haskell Brooks Curry (!)

https://en.wikipedia.org/wiki/Haskell_Curry

A reason for introducing support for currying is that you can build complex functions from simpler functions as building blocks. Currying is explained great here:
https://www.c-sharpcorner.com/UploadFile/rmcochran/functional-programming-in-C-Sharp-currying/

We will see in the examples that we can provide multiple arguments at once and the syntax will look a bit special compared to other C# code. Curryings benefits is to allow a more flexible way to call a method. You can store into variables calls to a function providing a subset of argument and use that variable to either specify an intermediate other call or get the final result. Note - The function will be called when ALL arguments are provided ONCE ! This helps a lot of avoiding surprising side effects. Let's first look at a sample set of methods we want to support currying.

int FooFourArgs(string st, float x, int j, int k)
{
	Console.WriteLine($"Inside method FooFourArgs. Got parameters: st={st}, x={x}, j={j}, k={k}");
	return 42;
}

int FooThreeArgs(string st, float x, int j)
{
	Console.WriteLine($"Inside method FooThreeArgs. Got parameters: st={st}, x={x}, j={j}");
	return 42;
}

int FooTwoArgs(string st, float x)
{
	Console.WriteLine($"Inside method FooTwoArgs. Got parameters: st={st}, x={x}");
	return 41;
}

int FooOneArgs(string st)
{
	Console.WriteLine($"Inside method FooOneArgs. Got parameters: st={st}");
	return 40;
}

We want to call the sample methods above in a more flexible way by splitting the number of arguments we provide. Let's see the extension methods to call up to four arguments to a function. Note the use of chaining the lambda operator (=>) to provide the support for currying.

public static class FunctionExtensions
{
	public static Func<T1, TResult> Curried<T1, TResult>(this Func<T1, TResult> func)
	{
		return x1 => func(x1);
	}
	
	public static Func<T1, Func<T2, TResult>> Curried<T1, T2, TResult>(this Func<T1, T2, TResult> func)
	{
		return x1 => x2 => func(x1, x2);
	}

	public static Func<T1, Func<T2, Func<T3, TResult>>> Curried<T1, T2, T3, TResult>(this Func<T1, T2, T3, TResult> func)
	{
		return x1 => x2 => x3 => func(x1, x2, x3);
	}

	public static Func<T1, Func<T2, Func<T3, Func<T4, TResult>>>> Curried<T1, T2, T3, T4, TResult>(this Func<T1, T2, T3, T4, TResult> func)
	{
		return x1 => x2 => x3 => x4 => func(x1, x2, x3,x4);
	}
}

The following main method shows how to use these curry helper methods:

void Main()
{
	var curryOneArgsDelegate = new Func<string, int>((st) => FooOneArgs(st)).Curried();
	var curryOneArgsPhaseOne = curryOneArgsDelegate("hello");

	var curryTwoArgsDelegate = new Func<string, float, int>((st, x) => FooTwoArgs(st,x)).Curried();
	var curryTwoArgsPhaseOne = curryTwoArgsDelegate("hello");
	var curryTwoArgsPhaseTwo = curryTwoArgsPhaseOne(3.14f);

	var curryThreeArgsDelegate = new Func<string, float, int, int>((st, x, j) => FooThreeArgs(st, x, j)).Curried();
	var curryThreeArgsPhaseOne = curryThreeArgsDelegate("hello");
	var curryThreeArgsPhaseTwo = curryThreeArgsPhaseOne(3.14f);
	var curryThreeArgsPhaseThree = curryThreeArgsPhaseTwo(123);	
	//Or call currying in a single call passing in two or more parametres
	var curryThreeArgsPhaseOneToThree = curryThreeArgsDelegate("hello")(3.14f)(123);

	var curryFourArgsDelegate = new Func<string, float, int, int, int>((st, x, j, k) => FooFourArgs(st, x, j, k)).Curried();
	var curryFourArgsPhaseOne = curryFourArgsDelegate("hello");
	var curryFourArgsNextPhases = curryFourArgsPhaseOne(3.14f)(123)(456); //just pass in the last arguments if they are known at this stage
	curryFourArgsDelegate("hello")(3.14f)(123)(456); //you can pass in 1-4 parameters to FooFourArgs method - all in a single call for example or one by one
}

The output we get is this. Note that we only call the methods we defined when all parameters are sent in. The function call which had partial argument list provided did not result into a function call.

Inside method FooOneArgs. Got parameters: st=hello
Inside method FooTwoArgs. Got parameters: st=hello, x=3,14
Inside method FooThreeArgs. Got parameters: st=hello, x=3,14, j=123
Inside method FooThreeArgs. Got parameters: st=hello, x=3,14, j=123
Inside method FooFourArgs. Got parameters: st=hello, x=3,14, j=123, k=456

So from a higher level, currying a function f(x,y,z) means adding support that you could call the function like this:
f(x,g(y,z)) or f(x,g(y,h(z))) - there more arguments you get there is more variations of number of parameters and methods you can pass in. Here is another example how you can build up a calculation uing simpler methods.

void Main()
{
	Func Area = (x,y) => x*y;
	Func CubicArea = (x,y,z) => Area.Curried()(Area(x,y))(z);	
	CubicArea(3,2,4); //supplying all arguments manully is okay
}

CubicArea expects THREE arguments. The implementation allows us to use the Area function and via currying we can use that method and provide the last third argument avoiding compilation error. Currying makes your functions allow more flexible ways of being called.