<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jonathan Twite on Medium]]></title>
        <description><![CDATA[Stories by Jonathan Twite on Medium]]></description>
        <link>https://medium.com/@jonathantwite?source=rss-23fb1ceadd29------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*aZxuYTooayPFcPq2SUlWeQ.jpeg</url>
            <title>Stories by Jonathan Twite on Medium</title>
            <link>https://medium.com/@jonathantwite?source=rss-23fb1ceadd29------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 15:26:00 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jonathantwite/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Unit testing with file paths across Windows and Linux]]></title>
            <link>https://medium.com/@jonathantwite/unit-testing-with-file-paths-across-windows-and-linux-32dc49460aa7?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/32dc49460aa7</guid>
            <category><![CDATA[unit-testing]]></category>
            <category><![CDATA[c-sharp-programming]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Fri, 07 Nov 2025 17:32:56 GMT</pubDate>
            <atom:updated>2025-11-07T17:32:56.130Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ek9Hub95-ibj6XLc1zcUXQ.png" /></figure><p>Over the last month I have been writing a Windows Service for work. One of the things this service had to do was to interact with the file system, creating and reading files. These files therefore need to be created within a specific folder structure where the user configures the base folder and the files are created and read from specific subfolders.</p><p>This was all fine and good; and I created unit tests to test the logic used in various places to work out the exact full file path of these files. Some methods required looking to see what files exist within folders and determining what to do with this. Rather than just mocking the file-access methods, I decided to use <a href="https://github.com/TestableIO/System.IO.Abstractions">System.IO.Abstractions</a> which essentially mocks out the file system, allowing you to test your code including calls to methods such as Directory.Exists() and Path.GetFileNameWithoutExtension(). These unit tests worked well and all passed.</p><p>I then uploaded my code to our Github repository and created a Github Action to build the project and run the tests before allowing Pull Requests to be merged.</p><p>Suddenly, I was getting a raft of failures. Luckily it was easy to spot why:</p><blockquote>[xUnit.net 00:00:00.86] Assert.Equal() Failure: Strings differ<br>[xUnit.net 00:00:00.86] ↓ (pos 9)<br>[xUnit.net 00:00:00.86] Expected: “<strong>F:\\Folder\\20250515_134217</strong>”<br>[xUnit.net 00:00:00.86] Actual: “<strong>F:\\Folder/20250515_134217</strong>”<br>[xUnit.net 00:00:00.86] ↑ (pos 9)</blockquote><p>The Github Action was obviously running on Linux. All of my application code uses Path.Combine(), however several of my unit test <em>expects </em>were just hard coded strings. I therefore went through all of my unit tests and changed any file paths to use Path.Combine() as well.</p><p>Unfortunately <a href="https://www.davidboike.dev/2020/06/path-combine-isnt-as-cross-platform-as-you-think-it-is/">Path.Combine() isn’t as cross-platform as you think it is</a> and the following failure still kept occurring:</p><blockquote>[xUnit.net 00:00:00.96] Assert.Equal() Failure: Strings differ<br>[xUnit.net 00:00:00.96] ↓ (pos 0)<br>[xUnit.net 00:00:00.96] Expected: “<strong>F:/Folder/20250514_100101/Table1.csv</strong>”<br>[xUnit.net 00:00:00.96] Actual: “<strong>/F:/Folder/20250514_100101/Table1.csv</strong>”<br>[xUnit.net 00:00:00.96] ↑ (pos 0)</blockquote><p>To fix this, I used System.IO.Abstraction’s MockUnixSupport.Path() to convert paths into Linux format. I used a new static TestingPath class to replace calls to Path:</p><pre>public static class TestingPath<br>{<br>    private static readonly IPath _Path = new MockFileSystem().Path;<br><br>    public static string Combine(params string[] paths)<br>    {<br>        if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))<br>        {<br>           return _Path.Combine(paths);<br>        }<br>        else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux) || RuntimeInformation.IsOSPlatform(OSPlatform.OSX))<br>        {<br>            return MockUnixSupport.Path(_Path.Combine(paths));<br>        }<br>        else<br>        {<br>            return Path.Combine(paths);<br>        }<br>    }<br>}</pre><p>This was used just like Path:</p><pre>_basePath = TestingPath.Combine(&quot;F:&quot;, &quot;Folder&quot;);<br>_otherFolder = TestingPath.Combine(_basePath, &quot;20250510_140702&quot;);<br>_lastFolder = TestingPath.Combine(_basePath, &quot;20250514_100101&quot;);<br>_newFolder = TestingPath.Combine(_basePath, _now.ToString(&quot;yyyyMMdd_HHmmss&quot;));<br><br>_fileSystem = new(new Dictionary&lt;string, MockFileData&gt;<br>{<br>    { TestingPath.Combine(_otherFolder, &quot;Table1.csv&quot;),  new MockFileData(&quot;&quot;) },<br>    { TestingPath.Combine(_otherFolder, &quot;Table2.csv&quot;),  new MockFileData(&quot;&quot;) },<br>    { TestingPath.Combine(_lastFolder, &quot;Table1.csv&quot;),  new MockFileData(&quot;&quot;) },<br>    { TestingPath.Combine(_lastFolder, &quot;Table2.csv&quot;),  new MockFileData(&quot;&quot;) }<br>});</pre><p>Now, my tests passed when run on both Linux and Windows.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=32dc49460aa7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A tale of @@Error and its issues]]></title>
            <link>https://medium.com/@jonathantwite/a-tale-of-error-and-its-issues-e020e08c4795?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/e020e08c4795</guid>
            <category><![CDATA[error-handling]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-server]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Fri, 10 Oct 2025 14:50:04 GMT</pubDate>
            <atom:updated>2025-10-10T14:50:04.235Z</atom:updated>
            <content:encoded><![CDATA[<p>I have just taken over a SQL codebase that does its entire error handling by using the functionality provided by the inbuilt @@Error variable. While using this inbuilt functionality can be done, it is tricky to do properly and lead to several bugs in this application. There are newer and easier ways to deal with errors than this.</p><h3>What is @@Error?</h3><p>@@Error returns the error number for the last statement run:</p><pre>SELECT 1/0;<br>PRINT CONCAT(&#39;Error: &#39;, @@ERROR);</pre><blockquote>Msg 8134, Level 16, State 1, Line 6<br>Divide by zero error encountered.<br>Error: 8134</blockquote><p>It is an integer number and is 0 when no error was detected and a positive number if an error was detetected.</p><h3>Debugging statements</h3><p>Error messages are not always the most descriptive, therefore you may be tempted to determine where the code fails by adding some harmless PRINT statements. This however, is actually less than harmless as it will wipe the value of @@ERROR…</p><pre>SELECT 1/0&#39;<br>PRINT &#39;Code reached here&#39;&#39;<br>PRINT CONCAT(&#39;Error: &#39;, @@ERROR);</pre><blockquote>Msg 8134, Level 16, State 1, Line 6<br>Divide by zero error encountered.<br>Code reached here<br><strong>Error: 0</strong></blockquote><p>This is because @@ERROR only considers the previous statement — PRINT &#39;Code reached here&#39; ran fine.</p><h3>SET XACT_ABORT OFF</h3><p>This style of error handling requires XACT_ABORT to be set to OFF in order that the code continues to run past the error. Otherwise the batch terminates and any fancy error handling logic you have is not run…</p><pre>SET XACT_ABORT ON;<br>SELECT 1/0;<br>PRINT CONCAT(&#39;Error: &#39;, @@ERROR);</pre><blockquote>Msg 8134, Level 16, State 1, Line 7<br>Divide by zero error encountered.</blockquote><p>Note that setting XACT_ABORT to OFF can cause further issues if you are not careful:</p><pre>SET XACT_ABORT OFF;<br><br>/* If this fails e.g. due to email already existing */<br>INSERT INTO dbo.User (Email, Username)<br>VALUES (@NewUserEmail, @Username);  <br><br>/* Then this will still run regardless */<br>INSERT INTO dbo.UserOrder (Email, OrderID)<br>VALUES (@NewUserEmail, 15);</pre><p>So error handling must be done after every statement that makes a change that is used by a future statement. This gets tedious:</p><pre>INSERT INTO dbo.User (Email, Username)<br>VALUES (@NewUserEmail, @Username);  <br><br>IF @@ERROR &lt;&gt; 0<br>  GOTO cleanup; -- Seriously - do we have to???<br><br>INSERT INTO dbo.UserOrder (Email, OrderID)<br>VALUES (@NewUserEmail, 15);<br><br>IF @@ERROR &lt;&gt; 0<br>  GOTO cleanup;</pre><p>However, remember that the @@ERROR statement is overwritten by every statement. Assuming the GOTO statement works fine (i.e. it exists) that block of code will not have access to the original error. Now your error handling starts looking complicated:</p><pre>DECLARE @ErrorCode INT = 0;<br><br>INSERT INTO dbo.User (Email, Username)<br>VALUES (@NewUserEmail, @Username);  <br><br>IF @@ERROR &lt;&gt; 0<br>BEGIN<br>  SET @ErrorCode = @@ERROR;<br>  GOTO cleanup;<br>END<br><br>INSERT INTO dbo.UserOrder (Email, OrderID)<br>VALUES (@NewUserEmail, 15);<br><br>IF @@ERROR &lt;&gt; 0<br>BEGIN<br>  SET @ErrorCode = @@ERROR;<br>  GOTO cleanup;<br>END</pre><p><strong>BUT </strong>this still doesn’t work — apparently IF @@ERROR &lt;&gt; 0 is enough of a statement to re-write the @@ERROR value. So we require this:</p><pre>DECLARE @ErrorCode INT = 0;<br><br>INSERT INTO dbo.User (Email, Username)<br>VALUES (@NewUserEmail, @Username);  <br><br>SET @ErrorCode = @@ERROR;<br>IF @ErrorCode &lt;&gt; 0<br>  GOTO cleanup;<br><br>INSERT INTO dbo.UserOrder (Email, OrderID)<br>VALUES (@NewUserEmail, 15);<br><br>SET @ErrorCode = @@ERROR;<br>IF @ErrorCode &lt;&gt; 0<br>  GOTO cleanup;</pre><p>Now you can go and cry — 4 lines of SQL have become 11…</p><h3>Dynamic SQL</h3><p>Dynamic SQL runs in a different batch to the calling code. This and the fact that it is run via a stored procedure means that @@ERROR is not set in the calling batch:</p><pre>DECLARE @Sql NVARCHAR(1000) =<br>    N&#39;SET IDENTITY_INSERT dbo.SomeStuff ON;<br>    INSERT INTO dbo.SomeStuff (Id, SomeData, Action)<br>    VALUES <br>        (1, &#39;&#39;data 1&#39;&#39;, 1),<br>        (1, &#39;&#39;data 2&#39;&#39;, 2),<br>        (1, &#39;&#39;data 3&#39;&#39;, 3);<br>    SET IDENTITY_INSERT dbo.SomeStuff OFF;&#39;;<br><br>EXEC sys.sp_executesql @Sql;<br>PRINT CONCAT(&#39;Error: &#39;, @@ERROR);</pre><blockquote>Msg 2627, Level 14, State 1, Line 24<br>Violation of PRIMARY KEY constraint ‘PK__SomeStuf__3214EC07A5B8499D’. Cannot insert duplicate key in object ‘dbo.SomeStuff’. The duplicate key value is (1).<br>The statement has been terminated.<br><strong>Error: 0</strong></blockquote><h3>Stored Procedures</h3><p>Stored procedures suffer from the same issue as dynamic SQL:</p><pre>CREATE OR ALTER PROCEDURE dbo.BreakInsert<br>AS<br>BEGIN<br>    SET IDENTITY_INSERT dbo.SomeStuff ON;<br>    INSERT INTO dbo.SomeStuff (Id, SomeData, Action)<br>    VALUES <br>        (1, &#39;data 1&#39;, 1),<br>        (1, &#39;data 2&#39;, 2),<br>        (1, &#39;data 3&#39;, 3);<br>    SET IDENTITY_INSERT dbo.SomeStuff OFF;<br>END;<br>GO<br><br>EXEC dbo.BreakInsert;<br>PRINT CONCAT(&#39;Error: &#39;, @@ERROR);</pre><blockquote>Msg 2627, Level 14, State 1, Procedure dbo.BreakInsert, Line 5 [Batch Start Line 49]<br>Violation of PRIMARY KEY constraint ‘PK__SomeStuf__3214EC07A5B8499D’. Cannot insert duplicate key in object ‘dbo.SomeStuff’. The duplicate key value is (1).<br>The statement has been terminated.<br><strong>Error: 0</strong></blockquote><h3>A more modern way</h3><p>SQL Server has a TRY / CATCH mechanism that significantly simplifies error handling and brings it more in line with other languages.</p><pre>SET XACT_ABORT OFF;<br><br>BEGIN TRY<br>    EXEC dbo.BreakInsert;<br>    PRINT &#39;Got here&#39;;<br>END TRY<br>BEGIN CATCH<br>    PRINT &#39;In Error Block&#39;;<br>    PRINT CONCAT(&#39;Error: &#39;, @@ERROR);<br>END CATCH;<br><br>PRINT &#39;Finished&#39;;</pre><blockquote>(0 rows affected)<br>In Error Block<br>Error: 0<br>Finished</blockquote><p>Firstly, even though XACT_ABORT is set to OFF, when the error occurs within the BreakInsert procedure, the code jumps straight to the CATCH block, missing the PRINT &#39;Got here&#39;. This code as written behaves the same whether XACT_ABORT is set to OFF or ON.</p><p>The second point to note is that the @@ERROR value was again re-written and so still needs using carefully.</p><p>Finally, no error was actually reported. This can be important if the application calling is code needs to know if an error happened — the CATCH block is considered to have done all the required handling and the code carries on as if all is well. Use THROW with no arguments if you wish the error to be re-thrown so it can be caught at a higher level.</p><h3>ERROR_NUMBER()</h3><p>The above code can be improved further, and we can finally ditch @@ERROR, by using the ERROR_NUMBER() function instead. There is also a ERROR_MESSAGE() function that returns the error text as well.</p><pre>BEGIN TRY<br>    EXEC dbo.BreakInsert;<br>    PRINT &#39;Got here&#39;;<br>END TRY<br>BEGIN CATCH<br>    PRINT &#39;In Error Block&#39;;<br>    PRINT CONCAT(&#39;Error: &#39;, ERROR_NUMBER());<br>    PRINT CONCAT(&#39;Error: &#39;, ERROR_MESSAGE());<br>END CATCH;<br>PRINT &#39;Finished&#39;;</pre><blockquote>(0 rows affected)<br>In Error Block<br>Error: 2627<br>Error: Violation of PRIMARY KEY constraint ‘PK__SomeStuf__3214EC07A5B8499D’. Cannot insert duplicate key in object ‘dbo.SomeStuff’. The duplicate key value is (1).<br>Finished</blockquote><h3>Final thoughts</h3><p>Using @@ERROR for error handling is difficult to use properly and can restrict how your code is written. Using the more modern TRY / CATCH method is both simpler and easier.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e020e08c4795" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A simple SQL function tester]]></title>
            <link>https://medium.com/@jonathantwite/a-simple-sql-function-tester-eeedaca651fc?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/eeedaca651fc</guid>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[testing]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Mon, 05 May 2025 11:04:55 GMT</pubDate>
            <atom:updated>2025-05-05T11:04:55.561Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Originally written 12 August 2021</em></p><p>Sometimes you need a quick method of testing the output of a function you have written, especially when you have fears about specific edge-cases that you want to check. For simple functions (the best type) this can be done in a simple one-line query utilising inline tables.</p><p>Take for example, this function to determine whether a person is 18 or older on a specific date. The code has been lifted off of <a href="https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate#comment96709945_1572938">StackOverflow</a> and so we should test some edge cases to ensure that it works.</p><pre>CREATE FUNCTION dbo.fnIs18OrOver (@DateOfBirth DATE, @Date DATE)<br>    RETURNS BIT<br>AS<br>BEGIN<br><br>    IF((0 + FORMAT(@Date,&#39;yyyyMMdd&#39;) - FORMAT(CAST(@DateOfBirth AS DATE),&#39;yyyyMMdd&#39;) ) /10000) &gt;= 18 RETURN 1;<br>    RETURN 0;<br><br>END;<br>GO</pre><p>It is possible to define a table inline within a query’s FROM statement using the VALUES keyword (<a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15#c-specifying-multiple-values-as-a-derived-table-in-a-from-clause">MS docs</a>):</p><pre>SELECT *<br>FROM (VALUES (&#39;Hi&#39;, &#39;Peter&#39;), (&#39;Hello&#39;, &#39;Paul&#39;)) AS tbl(Greeting, Name)</pre><pre>|Greeting|Name |<br>|--------|-----|<br>|Hi      |Peter|<br>|Hello   |Paul |</pre><p>Using this, we can create a one-statement testing query. Add the WHERE clause back in to filter to only fails. It is possible to add another column with test description in case you start getting lost as to what the row is testing, but if you are getting that complex, you may want to explore the more complete SQL testing solutions available.</p><pre>SELECT<br>    tbl.TestDoB, <br>    tbl.TestDate, <br>    tbl.ExpectedIs18OrOver,<br>    dbo.fnIs18OrOver(tbl.TestDoB, tbl.TestDate) AS ActualIs18OrOver<br>FROM<br>    (VALUES <br>        /* Test DoB on leap year */<br>        (&#39;2000-02-29&#39;, &#39;2018-02-27&#39;, 0),<br>        (&#39;2000-02-29&#39;, &#39;2018-02-28&#39;, 0),<br>        (&#39;2000-02-29&#39;, &#39;2018-03-01&#39;, 1),<br>        (&#39;2000-02-29&#39;, &#39;2018-03-02&#39;, 1),<br><br>        /* Test target date on leap year */<br>        (&#39;2002-02-27&#39;, &#39;2020-02-29&#39;, 1),<br>        (&#39;2002-02-28&#39;, &#39;2020-02-29&#39;, 1),<br>        (&#39;2002-03-01&#39;, &#39;2020-02-29&#39;, 0),<br>        (&#39;2002-03-02&#39;, &#39;2020-02-29&#39;, 0)<br>    ) AS tbl(TestDoB, TestDate, ExpectedIs18OrOver);<br><br>/* Uncomment next line to only show failures */<br>/*WHERE tbl.ExpectedIs18OrOver &lt;&gt; dbo.fnIs18OrOver(tbl.TestDoB, tbl.TestDate);*/</pre><pre>|TestDoB   |TestDate  |ExpectedIs18OrOver|ActualIs18OrOver|<br>|----------|----------|------------------|----------------|<br>|2000–02–29|2018–02–27|0                 |0               |<br>|2000–02–29|2018–02–28|0                 |0               |<br>|2000–02–29|2018–03–01|1                 |1               |<br>|2000–02–29|2018–03–02|1                 |1               |<br>|2002–02–27|2020–02–29|1                 |1               |<br>|2002–02–28|2020–02–29|1                 |1               |<br>|2002–03–01|2020–02–29|0                 |0               |<br>|2002–03–02|2020–02–29|0                 |0               |</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=eeedaca651fc" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Converting a decimal to a varchar]]></title>
            <link>https://medium.com/@jonathantwite/converting-a-decimal-to-a-varchar-b93e075521e2?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/b93e075521e2</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-server]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Tue, 11 Mar 2025 18:16:26 GMT</pubDate>
            <atom:updated>2025-03-11T18:16:26.261Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Originally written 7 May 2021</em></p><p>An error occurred in a stored procedure yesterday that has never had a problem before:</p><pre>Msg 8115, Level 16, State 5, Line 17<br>Arithmetic overflow error converting numeric to data type varchar.</pre><p>It was pretty obvious where to look, a bit of code that looks similar to</p><pre>DECLARE @val DECIMAL(12,4) = /* */;<br>SELECT CAST(@val AS VARCHAR(12));</pre><p>As long as @val was reasonably small, this worked fine. As @val was imported into the application, this had never caused an issue.</p><p>You should be able to spot the problem straight away: DECIMAL(12,4) allows a number with 12 numerals, four of which are after the decimal point - this decimal point adds an extra character to the string representation of this value. Therefore 12345678.9012 is an allowed value for @val but throws an error when casting to VARCHAR(12) as it is 13 characters long.</p><pre>DECLARE @val DECIMAL(12,4) = /* */;<br>SELECT CAST(@val AS VARCHAR(13));</pre><p>and it worked perfectly on our dev database, only to still be broken on the production data.</p><p>Why? It took us investigating the data being imported to realise that there is a second extra character that could be included — a negative symbol. The value -12345678.9012 is also perfectly legal value for a DECIMAL(12,4), however the string representation is 14 characters long.</p><p>Why did this happen? The data being imported by the client used to be 6 digit positive numbers (codes) with the occasional decimal part, or a small negative number that served as a temporary code until an official code was assigned. The frontend checked that the codes were less than 100,000,000 and the application worked fine for most situations. This was an edge-case that got missed and never showed up until the client changed how they worked.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b93e075521e2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Generating Random Data in SQL Server]]></title>
            <link>https://medium.com/@jonathantwite/generating-random-data-in-sql-server-05dab0df6f90?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/05dab0df6f90</guid>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-generation]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Fri, 07 Mar 2025 21:36:51 GMT</pubDate>
            <atom:updated>2025-03-07T21:36:51.058Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Originally written 28 Jan 2021</em></p><p>A question arose today about generating random data for testing a new site. The developer needed to generate a large number of test identifying codes in a format defined by the client, and stored in a format defined by existing systems. These codes are usually eight-digit numbers (although there are occasionally some other variants) stored in the database as a DECIMAL(18,4). The data is for testing and so does not need to be cryptographically random, nor do we care about any patterns or biases towards certain numbers.</p><p>Generating an eight-digit number is easy enough using RAND(), giving us a number between 0 and 1 (exclusive), multiplying it by 100,000,000, and casting it to INT to remove the decimal places (for this we don&#39;t care about the statistical nuances of truncating random numbers). A final casting puts the data back to the correct format.</p><pre>SELECT CAST(CAST(RAND() * 1e8 AS INT) AS DECIMAL(18, 4));</pre><p>The issue comes with generating multiple random numbers. If this statement is run as part of a SELECT ... FROM statement to generate multiple rows of results, the random number will be the same every row.</p><p>The way around this is to ensure that the RAND() function receives a different seed each row. There are several ways of doing this, it is interesting to see which is fastest.</p><p>First we create a table of numbers, representing a random table of data you may have in your database using a method from <a href="https://stackoverflow.com/a/1407488/4456875">StackOverflow</a> to create 1 million rows.</p><pre>DROP TABLE IF EXISTS dbo.Numbers;<br>SELECT TOP (1000000) IDENTITY(INT, 1, 1) AS Number<br>INTO dbo.Numbers<br>FROM <br>    sys.objects AS O<br>    CROSS JOIN sys.objects AS O2<br>    CROSS JOIN sys.objects AS O3;</pre><p>The cross joins are so that there are enough rows in the query result set to select the top 1 million from.</p><p>We test various methods by creating 1 million codes and inserting them into a table variable. We can time this without worrying about the length of time it takes SSMS to render 1 million result rows — which takes a few seconds. We can then select a few from the top of the results to ensure that we are getting sensible results.</p><h3>Incorrect method</h3><p>This does not work and is why we need to set the seed each time.</p><pre>DECLARE @Result TABLE (Code DECIMAL(18,4))<br>DECLARE @T0 DATETIME2 = GETDATE();<br>SET STATISTICS TIME,IO ON;<br><br>INSERT INTO @Result (Code)<br><br>SELECT CAST(CAST(RAND()*1e8 AS INT) AS DECIMAL(18,4))<br>FROM dbo.Numbers AS NSET STATISTICS TIME,IO OFF;<br>PRINT CONCAT(&#39;Total time:&#39;, DATEDIFF(MILLISECOND, @T0, GETDATE()), &#39; ms&#39;);<br><br>SELECT TOP (100) Code FROM @Result AS R;</pre><pre>|Code         |<br>|-------------|<br>|78288301.0000|<br>|78288301.0000|<br>|78288301.0000|<br>|78288301.0000|<br>|78288301.0000|<br>|78288301.0000|</pre><pre>SQL Server parse and compile time: <br>   CPU time = 0 ms, elapsed time = 0 ms.<br>Table &#39;Numbers&#39;. Scan count 1, logical reads 1608, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<br><br>(1000000 rows affected)<br><br>(1 row affected)<br><br>SQL Server Execution Times:<br>   CPU time = 234 ms,  elapsed time = 234 ms.<br>Total time:233 ms</pre><h3>The GO X method</h3><p>SQL Server Management Studio (SSMS) has the functionality built into it’s batching mechanism to repeat a command multiple times. The GO batch separator can be used with a proceeding number to repeat the batch that number of times. One issue for this is that variable cannot be carried over between batches, and so temporary tables need to be used instead. This is not a particularly fair test then, but is much slower - as the following attempt to make 1000 rows shows:</p><pre>DROP TABLE IF EXISTS #Timing;<br>CREATE TABLE #Result (Code DECIMAL(18,4));<br>CREATE TABLE #Timing (StartTime DATETIME2);<br>INSERT INTO #Timing (StartTime) VALUES (GETDATE());<br>SET STATISTICS TIME,IO ON;<br>GO<br><br>INSERT INTO #Result (Code)<br>SELECT CAST(CAST(RAND()*1e8 AS INT) AS DECIMAL(18,4))<br>GO 1000<br><br>SET STATISTICS TIME,IO OFF;<br>DECLARE @StartTime DATETIME2 = (SELECT TOP (1) StartTime FROM #Timing AS T);<br>PRINT CONCAT(&#39;Total time:&#39;, DATEDIFF(MILLISECOND, @StartTime, GETDATE()), &#39; ms&#39;);<br><br>SELECT TOP (100) Code FROM #Result AS R;</pre><pre>...<br>SQL Server parse and compile time: <br>   CPU time = 0 ms, elapsed time = 0 ms.<br>Table &#39;#Result_____________________________________________________________________________________________________________0000000014EE&#39;. Scan count 0, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<br><br>(1 row affected)<br><br>(1 row affected)<br><br>SQL Server Execution Times:<br>   CPU time = 0 ms,  elapsed time = 0 ms.<br>Batch execution completed 1000 times.<br>SQL Server parse and compile time: <br>   CPU time = 0 ms, elapsed time = 1 ms.<br><br>(1 row affected)<br>Total time:545566 mss</pre><p>That is 9m 05s. With all logging and <em>Include Actual Execution Plan</em> turned off, 1000 batches ran instantaneously, 100000 batches took 18s and 1 million batches 1m 57s as measured by SSMS.</p><pre>DECLARE @Result DECIMAL(18,4)<br>SELECT @Result = CAST(CAST(RAND()*1e8 AS INT) AS DECIMAL(18,4))<br><br>GO 1000000</pre><h3>ROW_NUMBER() on a large table</h3><p>Using a large table, you can use the ROW_NUMBER() function to generate a unique seed for the each RAND() call. This is useful if you do not have any tables with guaranteed unique integers, or not a big enough table. In this case, you can CROSS JOIN the largest table (possibly sys.objects) with itself multiple times to create a table with many rows.</p><pre>DECLARE @Result TABLE (Code DECIMAL(18,4))<br>DECLARE @T0 DATETIME2 = GETDATE();<br>SET STATISTICS TIME,IO ON;<br><br>INSERT INTO @Result (Code)<br>SELECT TOP (1000000) CAST(CAST(RAND(ROW_NUMBER() OVER (ORDER BY O.object_id))*1e8 AS INT) AS DECIMAL(18,4))<br>FROM <br>    sys.objects AS O<br>    CROSS JOIN sys.objects AS O2<br>    CROSS JOIN sys.objects AS O3;<br><br>SET STATISTICS TIME,IO OFF;<br>PRINT CONCAT(&#39;Total time:&#39;, DATEDIFF(MILLISECOND, @T0, GETDATE()), &#39; ms&#39;);<br><br>SELECT TOP (100) Code FROM @Result AS R;</pre><pre>|Code         |<br>|-------------|<br>|18209943.0000|<br>|18211806.0000|<br>|18213670.0000|<br>|18215533.0000|<br>|18217396.0000|<br>|18219260.0000|</pre><pre>SQL Server parse and compile time: <br>   CPU time = 7 ms, elapsed time = 7 ms.<br>Table &#39;sysschobjs&#39;. Scan count 3, logical reads 115, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<br><br>(1000000 rows affected)<br><br>(1 row affected)<br><br>SQL Server Execution Times:<br>   CPU time = 468 ms,  elapsed time = 477 ms.<br>Total time:483 ms</pre><h3>Using a large table of integers</h3><p>If you happen to have a large table with unique numbers (e.g. IDs), you could use these as seeds. Using the Numbers table created above:</p><pre>DECLARE @Result TABLE (Code DECIMAL(18,4))<br>DECLARE @T0 DATETIME2 = GETDATE();<br>SET STATISTICS TIME,IO ON;<br><br>INSERT INTO @Result (Code)<br>SELECT TOP (1000000) CAST(CAST(RAND(N.Number)*1e8 AS INT) AS DECIMAL(18,4))<br>FROM dbo.Numbers AS N<br><br>SET STATISTICS TIME,IO OFF;<br>PRINT CONCAT(&#39;Total time:&#39;, DATEDIFF(MILLISECOND, @T0, GETDATE()), &#39; ms&#39;);<br><br>SELECT TOP (100) Code FROM @Result AS R;</pre><pre>|Code       |<br>|-----------|<br>|103305.0000|<br>|105168.0000|<br>|107032.0000|<br>|108895.0000|<br>|110758.0000|<br>|112622.0000|</pre><pre>SQL Server parse and compile time: <br>   CPU time = 0 ms, elapsed time = 0 ms.<br>Table &#39;Numbers&#39;. Scan count 1, logical reads 1608, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<br><br>(1000000 rows affected)<br><br>(1 row affected)<br><br>SQL Server Execution Times:<br>   CPU time = 407 ms,  elapsed time = 432 ms.<br>Total time:433 ms</pre><p>Adding a clustered index to the table doesn’t effect the speed:</p><pre>CREATE CLUSTERED INDEX PK_Numbers ON dbo.Numbers (Number);<br><br>DECLARE @Result TABLE (Code DECIMAL(18,4))<br>DECLARE @T0 DATETIME2 = GETDATE();<br>SET STATISTICS TIME,IO ON;<br><br>INSERT INTO @Result (Code)<br>SELECT TOP (1000000) CAST(CAST(RAND(N.Number)*1e8 AS INT) AS DECIMAL(18,4))<br>FROM <br>    dbo.Numbers AS N<br><br>SET STATISTICS TIME,IO OFF;<br>PRINT CONCAT(&#39;Total time:&#39;, DATEDIFF(MILLISECOND, @T0, GETDATE()), &#39; ms&#39;);<br><br>SELECT TOP (100) Code FROM @Result AS R;</pre><pre>SQL Server parse and compile time: <br>   CPU time = 0 ms, elapsed time = 0 ms.<br>Table &#39;Numbers&#39;. Scan count 1, logical reads 1620, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<br><br>(1000000 rows affected)<br><br>(1 row affected)<br><br>SQL Server Execution Times:<br>   CPU time = 422 ms,  elapsed time = 427 ms.<br>Total time:430 ms</pre><p>Nor does a non-clustered index. However, if you are using a non-indexed column on a larger table (perhaps an employee table where the primary key employee id is alpha-numeric but there is a unique integer web-login id column), then a non-clustered index might be a good idea on that column.</p><pre>DROP TABLE IF EXISTS dbo.SomeData;<br>SELECT TOP (1000000) <br>    CONCAT(&#39;A&#39;, ROW_NUMBER() OVER (ORDER BY O.object_id)) AS Id, <br>    ROW_NUMBER() OVER (ORDER BY O.object_id) AS Number,<br>    &#39;Data&#39; AS Data1, &#39;Data&#39; AS Data2, &#39;Data&#39; AS Data3, &#39;Data&#39; AS Data4, &#39;Data&#39; AS Data5, &#39;Data&#39; AS Data6<br>INTO dbo.RandomData<br>FROM <br>    sys.objects AS O<br>    CROSS JOIN sys.objects AS O2<br>    CROSS JOIN sys.objects AS O3;<br>ALTER TABLE dbo.SomeData ADD CONSTRAINT PK_SomeData PRIMARY KEY CLUSTERED (Id);<br><br>DECLARE @Result TABLE (Code DECIMAL(18,4))<br>DECLARE @T0 DATETIME2 = GETDATE();<br>SET STATISTICS TIME,IO ON;<br><br>INSERT INTO @Result (Code)<br>SELECT TOP (1000000) CAST(CAST(RAND(SD.Number)*1e8 AS INT) AS DECIMAL(18,4))<br>FROM <br>    dbo.SomeData AS SD<br><br>SET STATISTICS TIME,IO OFF;<br>PRINT CONCAT(&#39;Total time:&#39;, DATEDIFF(MILLISECOND, @T0, GETDATE()), &#39; ms&#39;);</pre><pre>|Code         |<br>|-------------|<br>|99846011.0000|<br>|99847875.0000|<br>|99849738.0000|<br>|99851601.0000|<br>|94206948.0000|<br>|99853465.0000|</pre><pre>SQL Server parse and compile time: <br>   CPU time = 0 ms, elapsed time = 0 ms.<br>Table &#39;SomeData&#39;. Scan count 1, logical reads 7965, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<br><br>(1000000 rows affected)<br><br>(1 row affected)<br><br>SQL Server Execution Times:<br>   CPU time = 547 ms,  elapsed time = 542 ms.<br>Total time:543 ms</pre><pre>CREATE NONCLUSTERED INDEX IX_SomeData_Number ON dbo.SomeData (Number);<br><br>DECLARE @Result TABLE (Code DECIMAL(18,4))<br>DECLARE @T0 DATETIME2 = GETDATE();<br>SET STATISTICS TIME,IO ON;<br><br>INSERT INTO @Result (Code)<br>SELECT TOP (1000000) CAST(CAST(RAND(SD.Number)*1e8 AS INT) AS DECIMAL(18,4))<br>FROM <br>    dbo.SomeData AS SD<br><br>SET STATISTICS TIME,IO OFF;<br>PRINT CONCAT(&#39;Total time:&#39;, DATEDIFF(MILLISECOND, @T0, GETDATE()), &#39; ms&#39;);<br><br>SELECT TOP (100) Code FROM @Result AS R;</pre><pre>|Code         |<br>|-------------|<br>|18209943.0000|<br>|18211806.0000|<br>|18213670.0000|<br>|18215533.0000|<br>|18217396.0000|<br>|18219260.0000|</pre><pre>SQL Server parse and compile time: <br>   CPU time = 0 ms, elapsed time = 0 ms.<br>Table &#39;SomeData&#39;. Scan count 1, logical reads 3104, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<br><br>(1000000 rows affected)<br><br>(1 row affected)<br><br>SQL Server Execution Times:<br>   CPU time = 516 ms,  elapsed time = 525 ms.<br>Total time:524 ms</pre><p>As can be seen, the speed is not necessarily increased, but the <em>logical reads</em> comes down which is then more efficient.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=05dab0df6f90" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why doesn’t C#’s IList contain AddRange?]]></title>
            <link>https://medium.com/@jonathantwite/why-doesnt-c-s-ilist-contain-addrange-5c417a385084?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/5c417a385084</guid>
            <category><![CDATA[interface-design]]></category>
            <category><![CDATA[c-sharp-programming]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Fri, 27 Mar 2020 20:27:09 GMT</pubDate>
            <atom:updated>2020-03-27T20:27:09.042Z</atom:updated>
            <content:encoded><![CDATA[<p>Ok, I’ll admit it. I did a bit of Java back in the day. To be precise, I learn’t a little Java in order to teach it to a student who was doing a Business with Computing degree. I was basically keeping one week ahead of the guy’s lectures so to give him the extra help he needed. From this student’s course, there are two things that will remain with me until the day I die:</p><ul><li>Java loves classes in classes in classes in …,</li><li>Programme to interfaces.</li></ul><p>Having cycled from education to science to IT, I am now a professional C# developer, trying to enact this second piece of advice in my day-to-day work (my co-workers get grumpy if I start using sub classes in C#).</p><p>Although writing</p><pre>IList&lt;string&gt; theList = new List&lt;string&gt;();</pre><p>probably has no real benefits over declaring theList as a List&lt;string&gt;, I personally believe that it is probably a good habit to get into, especially when you start using dependency injection containers. The dependency injection packages I have used all work via the DI container injecting a concrete class into a interface — therefore the developer builds each classes dependencies to be interfaces and programmes against these. I have therefore got used to programming against interfaces rather than concrete implementations.</p><p>This however raises one interesting quirk. Take the following code that looks great, but wont compile:</p><pre>public IEnumerable&lt;string&gt; GetBasketDiscounts()<br>{<br>    IList&lt;string&gt; allDiscounts = new List&lt;string&gt;();<br>    allDiscounts.AddRange(GetItemDiscounts());<br>    allDiscounts.AddRange(GetOrderDiscounts());<br>    allDiscounts.AddRange(GetShippingDiscounts());<br>}</pre><p>This could be used to get a summary box on a checkout page of all discounts applied to an order. It builds a IList&lt;string&gt; as it needs to add to this collection but returns an IEnumerable&lt;string&gt; indicating to the developer that the list is intended to be read, not added to.</p><p>Unfortunately, dear Rosilyn, our friendly C# compiler, throws a wobbly at this point complaining that:</p><pre>Error CS1061 ‘IList&lt;string&gt;’ does not contain a definition for ‘AddRange’ and no accessible extension method ‘AddRange’ accepting a first argument of type ‘IList&lt;string&gt;’ could be found (are you missing a using directive or an assembly reference?)</pre><p>Apparently, IList&lt;&gt; does not contain an AddRange() method, however, List&lt;&gt; does. Why?</p><p>Although I am not an authoritative source, this is my view as to the philosophical reasoning as to why IList&lt;&gt; does not contain an AddRange() method.</p><p>Interfaces can be described as a promise to be able to do certain things. We often come across two distinct types of interface in C#,</p><ul><li>The <strong>full-class interface </strong>— where every method on a class is represented on it’s interface. This allows the class to be injected into another class via a Dependency Injection container, or a mock version used by a class undergoing a Unit Test.</li><li>The <strong>class-functionality interface</strong> — e.g. IDisposable or IEnumerable&lt;&gt; which indicate that a class as specific functionality available. Every IEnumerable&lt;&gt; is guaranteed to to be usable in a foreach loop.</li></ul><p>Contrary to what its name suggests, IList&lt;&gt; is not a full-class interface of List&lt;&gt;, rather a stand-alone functionality interface that has little to do with the specific List&lt;&gt; class. It belongs to the inbuilt hierarchy of C# collection interfaces which provide a “ladder” of functionality:</p><ol><li>IEnumerable&lt;&gt;: Class can be iterated over.</li><li>ICollection&lt;&gt;: As IEnumerable&lt;&gt;, plus class can have items added and removed from it. Also has a Count property and a Contains() method.</li><li>IList&lt;&gt;: As ICollection&lt;&gt;, plus items can be accessed with array-like syntax (myList[2]). Also has a IndexOf() method.</li></ol><p>IDictionary&lt;&gt; sits alongside IList&lt;&gt; and there is a similar hierarchy of IEnumerable&lt;&gt; &gt; IReadOnlyCollection&lt;&gt; &gt; IReadOnlyList&lt;&gt;.</p><p>Now, if rather than considering IList&lt;&gt; to be the interface for List&lt;&gt;, you consider List&lt;&gt; to be a default Microsoft-supplied implementation of the IList&lt;&gt; interface you can start to understand why List&lt;&gt; may have more functionality.</p><p>When creating a custom implementation of a collection of objects, what functionality do you want to “have to” implement? IList&lt;&gt; states that you have to be able to add and remove items — it doesn’t force you to implement a method to add a collection of objects, in the same way that it doesn’t force you to have a method to remove every third item or sort every item into alphabetical order according the the Sanskrit alphabet. There was a line to be drawn at what functionality was deemed necessary for all IList&lt;&gt; implementations and what was “nice-to-have” or “specialised”. The creators of C# drew this line at Add(), InsertAt(), Remove(), RemoveAll(), RemoveAt(), Clear(), Count and Contains().</p><p>However, when creating a generic List&lt;&gt; implementation to be used, they added other helpful methods that, while not needed in every application, save developers from having to build their own versions of fairly common functionality. There are a large number of other methods they also implemented: Reverse() , Sort() , TrimExcess() and all manner of Find… methods.</p><p>There you have it. List&lt;&gt; and IList&lt;&gt; are fundamentally different things, built for different purposes. IList&lt;&gt; could have had a AddRange() method, but the creators decided to draw the line at what was absolutely needed for a list-type construct and save developers creating custom IList&lt;&gt; implementations a few lines of code. The List&lt;&gt; class was created as a fully functioning and functional object container.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5c417a385084" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SASC Website Redesign — The Start]]></title>
            <link>https://medium.com/@jonathantwite/sasc-website-redesign-the-start-be05569714f7?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/be05569714f7</guid>
            <category><![CDATA[umbraco8]]></category>
            <category><![CDATA[sailing]]></category>
            <category><![CDATA[sports]]></category>
            <category><![CDATA[web-development]]></category>
            <category><![CDATA[umbraco]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Tue, 26 Feb 2019 13:29:08 GMT</pubDate>
            <atom:updated>2019-02-26T13:29:08.347Z</atom:updated>
            <content:encoded><![CDATA[<h3>SASC Website Redesign — The Start</h3><p>Today marks the start of the Sutton-In-Ashfield Sailing Club website redesign. “Redesign” is actually a bit of an understatement — the <a href="http://suttoninashfieldsailingclub.co.uk/">original site</a> was put together many years ago and no longer represents the club particularly well and so the whole site is being rebuilt from the ground up.</p><p>The original site is written in plain HTML (with frames…) and although it shows the information required by users, it fails in at least the following ways:</p><ul><li>Very old-fashioned and somewhat boring look, especially compared to many websites used today. Fails to draw people in.</li><li>Being plain HTML, updating is complicated for people with no IT knowledge and slow for those with.</li><li>Fails to encourage a sense of pride in the club. No one sends a link to the site to other people to say “this is where I sail”.</li></ul><h3>What is changing?</h3><p>In a word, everything. We are wanting to show people who visit our site something of our passion for the sport and hobby of sailing. There will be dedicated sections for Racing, Learning, Families and Socials and lots of information about how people can get involved. Above all, there will be lots of pictures, videos exciting content.</p><h3>Why today?</h3><p>Why start today, not yesterday, not tomorrow? Well, today sees the launch of <a href="https://umbraco.com/blog/introducing-umbraco-8/">Umbraco 8</a>, the latest version of the Umbraco Content Management System. Umbraco is a well established, free and open-sourced CMS which powers many websites across the internet.</p><p>Using a CMS will allow people at the club to update the site with ease and needing no technical knowledge — expect new content on a regular basis!</p><p>We will be then, one of the first Umbraco 8 websites built anywhere on the internet.</p><h3>When will I see a change?</h3><p>Soon. There is a lot of background work to do, but rest assured, a new Hosting Provider has been purchased and Visual Studio is ready to go!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bGBOWkSrgw4Y81krQelbGA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=be05569714f7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Require, Knockout, Jasmine and Chutzpah in an adventure with Visual Studio]]></title>
            <link>https://medium.com/@jonathantwite/require-knockout-jasmine-and-chutzpah-in-an-adventure-with-visual-studio-f582d43f2234?source=rss-23fb1ceadd29------2</link>
            <guid isPermaLink="false">https://medium.com/p/f582d43f2234</guid>
            <category><![CDATA[testing]]></category>
            <category><![CDATA[javascript]]></category>
            <dc:creator><![CDATA[Jonathan Twite]]></dc:creator>
            <pubDate>Thu, 26 Jul 2018 16:07:24 GMT</pubDate>
            <atom:updated>2018-07-26T16:07:24.298Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fndiWZKgimiKNgWORP6UTw.jpeg" /></figure><p>I am trying to build some testing into a growing JavaScript application that is built using <a href="http://knockoutjs.com/"><em>Knockout</em></a> and <a href="https://requirejs.org/"><em>Require JS</em></a>. The application is growing and currently contains 13 knockout components on top of a few relatively weighty view models. It would be nice to unit test each of these components to confirm that their logic works and that they communicate with the main view models properly.</p><p>I have previously written unit tests for C# using the <em>Visual Studio</em> Test projects and using the <em>Test Explorer</em> window to discover, build and run tests and so I looked around for a way to integrate JavaScript tests into these inbuilt tools. I cannot be sure that everyone in the department will install the same <em>Visual Studio</em> extensions as I and so I needed something that would not prevent the solution from building on someone else’s machine if they did not have the required setup.</p><p>This hunt lead to <a href="http://mmanela.github.io/chutzpah/"><em>Chutzpah</em></a><em> </em>and it’s <a href="http://visualstudiogallery.msdn.microsoft.com/f8741f04-bae4-4900-81c7-7c9bfb9ed1fe"><em>Unit Test Explorer Test Adapter</em></a> Visual Studio extension as a test runner. <em>Chutzpah</em> works with tests written in <a href="https://qunitjs.com/"><em>qUnit</em></a>, <a href="https://jasmine.github.io/"><em>Jasmine</em></a> and <a href="https://mochajs.org/"><em>Mocha</em></a><em>;</em> a fairly arbitrary decision led to <em>Jasmine</em> being chosen.</p><p>Using <em>Visual Studio 2017</em>, I installed the <em>Chutzpah Test Adapter for the Test Explorer </em>extension and created a new solution to try out it out. The solution contained two projects, a blank ASP.NET MVC web application, <em>JsJasmineTest</em>, and a C# Unit Test Project, <em>JsJasmineTest.Tests</em>. Feeling lazy, I used Nuget to install <em>Knockout 3.4.2</em> to the <em>JsJasmineTest</em> project and <em>Jasmine 2.6.4</em> to the <em>JsJasmineTest.Tests</em> project.</p><h3>Testing Knockout View Models</h3><p>As a starter, I ignored <em>Require JS</em> and created a couple of <em>Knockout </em>View Models. The MVC HomeController‘s index view linked to a script <em>home-index.js</em> which contains the following:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/663af0e25b949e1928289dc9c783ca78/href">https://medium.com/media/663af0e25b949e1928289dc9c783ca78/href</a></iframe><p>Normally I don’t return anything so to keep my code separate from anything anyone else is doing, however it appears not to be possible to test code within an <em>immediately invoked function expression</em> without a return.</p><p>I then wrote some simple <em>Jasmine</em> tests in the <em>JsJasmineTest.Tests</em> project in the folder \Scripts\tests-normal\. In order to get <em>Intellisence</em> working to help with the test, I put the following reference at the top of the JavaScript file.</p><pre>/// &lt;reference path=&quot;../../../JsJasmineTest/Scripts/home-index.js&quot;/&gt;</pre><p>This apparently also helps <em>Chutzpah</em> find the required files to use to run the tests.</p><p>The file with a few initial tests looks like:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/68e06c200f601639d32ad54b1c59ef6f/href">https://medium.com/media/68e06c200f601639d32ad54b1c59ef6f/href</a></iframe><p>After which I waited to see my new tests appear in the <em>Test Explorer</em> alongside the C# test. Firstly, I couldn’t find the <em>Test Explorer</em> window — Apparently it is in the <em>Test</em> menu, not the <em>View</em> menu. So with the correct windows open… no JavaScript tests appeared.</p><p><em>Chutzpah</em> requires a config file, even if it is empty, so I created a file chutzpah.json in the \Scripts\ folder with an empty JSON object and voila, there appeared 6 new tests under a node, un-imaginatively called the full windows file path to the test file. I am yet to find a way to change this to something more usable. All 6 tests then proceeded to fail — due to undefined variable ko. To let <em>Jasmine</em> know where <em>Knockout</em> is, a reference was defined in chutzpah.json as such:</p><pre>{<br>    &quot;References&quot;: [<br>        { &quot;Path&quot;: &quot;../../JsJasmineTest/Scripts/knockout-3.4.2.js&quot; }<br>    ]<br>}</pre><p>and all the tests passed.</p><h3>Adding in Require JS</h3><p>The easy bit was over. To test using <em>Require JS</em> as the AMD (<strong>A</strong>synchronous <strong>M</strong>odule <strong>D</strong>efinition) module loader for <em>Knockout</em>, as the original project that I want to test is, I created a second controller RequireJsController with an index view. A view model module, require-index-viewmodel.js, was created in \Scripts\:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/88e7f91417b223bae48a34700482e1b5/href">https://medium.com/media/88e7f91417b223bae48a34700482e1b5/href</a></iframe><p>and a <em>Require JS</em> config file that also sets-up the page:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1127bb811a8c7d93fa8fb0bd1d94efa3/href">https://medium.com/media/1127bb811a8c7d93fa8fb0bd1d94efa3/href</a></iframe><p>Tests for AMD projects have to also be housed in a define statement and so the first test, requirejs-index.js in the folder \Scripts\tests-amd\ looks like</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c6e0202d935255b54ed4a4b7518f8819/href">https://medium.com/media/c6e0202d935255b54ed4a4b7518f8819/href</a></iframe><p>The /// references do not seem to enable <em>Intellisence</em>, although this is likely to be due to it not understanding the AMD pattern used.</p><p>By this point, the <em>Test Explorer</em> window was starting to have a tendency to crash. The green bar that rotates over the top to show tests are being discovered continuously rotates and the <em>Run All</em> and all of the options under <em>Run…</em> are greyed out. The only solution appears to be restarting <em>Visual Studio</em>.</p><p>The file structure now appears something like</p><p><strong>JsJasmineTest</strong> (ASP.NET)</p><ul><li>Controllers\</li><li>— HomeController.cs</li><li>— RequireJsController.cs</li><li>Scripts\</li><li>— config.js</li><li>— home-index.js</li><li>— knockout-3.4.2.js</li><li>— require.js</li><li>— requirejs-index-viewmodel.js</li><li>Views\</li><li>— Home\</li><li>— — Index.cshtml</li><li>— RequireJs\</li><li>— — Index.cshtml</li></ul><p><strong>JsJasmineTest.Tests</strong> (C# Unit Test)</p><ul><li>Content\</li><li>— jasmine\</li><li>— — jasmine.css</li><li>Scripts\</li><li>— jasmine\</li><li>— — boot.js</li><li>— — console.js</li><li>— — jasmine-html.js</li><li>— — jasmine.js</li><li>— tests-amd\</li><li>— — requirejs-index.js</li><li>— tests-normal\</li><li>— — home-index.js</li><li>— chutzpah.json</li><li>UnitTest1.cs</li></ul><p>I wanted to see if we could have AMD and non-AMD tests in the same project. The setup for AMD tests requires the addition of a few new parameters in the chutzpah.json configuration file and so one file can only do one of the two modes. You can, however, declare multiple chutzpah.json configuration files and it is possible to inherit properties from a configuration file in a parent directory. I therefore decided to use \Scripts\chutzpah.json as the base configuration file and create a new file \Scripts\tests-amd\chutzpah.json as the AMD tests configuration.</p><pre>{<br>    &quot;InheritFromParent&quot;: true,<br>    &quot;TestHarnessReferenceMode&quot;: &quot;AMD&quot;,<br>}</pre><p>Once again, no new tests appeared in the <em>Test Explorer</em>. Looking at the <em>Output</em> window showed no errors coming from the requirejs-index.js file.</p><p>The thought that then occurred was that at no point were we telling <em>Chutzpah</em> or <em>Jasmine</em> that we are using <em>Require JS</em>. It may be that they have an AMD loader inbuilt, or may be not, so I added a reference into the chutzpah.json configuration file:</p><pre>&quot;References: [<br>    { &quot;Path&quot;: &quot;../../../jsjasminetest/scripts/require.js&quot; }<br>]</pre><p>It now became clear that <em>Visual Studio</em> doesn’t always detect new tests, especially when a configuation file is changed. The only way I found to force this was to restart <em>Visual Studio</em>. Luckily, <em>Visual Studio 17</em> is much faster than <em>Visual Studio 15</em> was at starting up.</p><p>Now errors start appearing in the <em>Output</em> window:</p><pre>Error opening c:/[...]/jsjasminetest/JsJasmineTest.Tests/scripts/tests-amd/requirejs-index-viewmodel.js: No such file or directory</pre><pre>Error: Script error for &quot;requirejs-index-viewmodel&quot;, needed by: requirejs-index</pre><pre>Error: Timeout occurred when executing test file<br>While running: c:\[...]\jsjasminetest\jsjasminetest.tests\scripts\tests-amd\requirejs-index.js</pre><p>Apparently these errors stop the tests being detected as tests, rather than as failed tests.</p><p>So now, <em>Require JS</em> is looking in the wrong location (in the test project) for the modules to load. Next up was to try setting the AMD base url.</p><pre>&quot;AMDBaseUrl&quot;: &quot;../../../JsJasmineTest/Scripts/&quot;</pre><p>Now we are back to module not found errors:</p><pre>Error opening c:/[...]/jsjasminetest/JsJasmineTest/Scripts/knockout.js: No such file or directory</pre><pre>Error: Script error for &quot;knockout&quot;, needed by: requirejs-index-viewmodel</pre><pre>Error: TypeError: null is not an object (evaluating &#39;c.nodeType&#39;)</pre><pre>Error: Timeout occurred when executing test file<br>While Running:c:\[...]\jsjasminetest\jsjasminetest.tests\scripts\tests-amd\requirejs-index.js</pre><p>The location for the AMD modules is defined in the <em>Require JS</em> config.js file and so I added another reference to this file in chutzpah.json. The file now looks like</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f45753cbb7ac429134066c726fb330a3/href">https://medium.com/media/f45753cbb7ac429134066c726fb330a3/href</a></iframe><p>And amazingly it worked. And the tests passed.</p><h3>What went wrong getting to this point?</h3><p>Why did it take so long to get it working?</p><p>Firstly, several times a file not found error was caused by not setting enough ../‘s in a file path.</p><p>There is a an option that can be passed to the &quot;References&quot; objects, IsTestFrameworkFile which sounds like it should be declaring that the file is a part of the framework — I initially assumed require.js would need this setting to true. This then lead to the following error:</p><pre>Error: Mismatched anonymous define() module: function (N, O) {[/*... lots of minified code...*/]<br>https://requirejs.org/docs/errors.html#mismatch<br>While Running:c:\[...]\scripts\tests-amd\requirejs-index.js</pre><p>This is caused <em>Require JS</em> loading a module which has already been loaded. Firstly I thought this was due to the inheritance — <em>Chutzpah</em> loading knockout-3.4.2.js from the parent configuration file and <em>Require JS </em>also loading it. Changing &quot;InheritFromParent&quot; to false did fix the issue, however I went back to the <a href="https://github.com/mmanela/chutzpah/wiki/references-setting">documentation</a></p><blockquote>Indicated that this references should be placed directly after the test framework files (like QUnit.js) in the test harness. This ensures that this file is injected into the test harness before almost all other files. Defaults to false.</blockquote><p>which on second reading says that these files are then loaded, as opposed to being just a location reference. Removing &quot;IsTestFrameworkFile&quot; allowed the tests to work, and the configuration file to inherit from a parent file.</p><p>Next, setting the &quot;TestFileTimeout&quot; setting in the configuration file does nothing to stop the timeout errors.</p><p>Finally, it took me until the very last moments of developing to realise that <em>Chutzpah</em> watches files with tests in and forces the <em>Test Explorer</em> to reload tests when they are saved, even if there is no actual change to the file.</p><h3>Conclusion</h3><p>I now had a working set of tests that could be run by the click of a button in <em>Visual Studio</em>. I am now starting to code up tests for my actual project and trying to decide whether I am hoping, or not, to find any bugs.</p><p>I have saved the solution to a GitHub repository, mostly so I can find it again.</p><p><a href="https://github.com/jonathantwite/JsJasmineTest">jonathantwite/JsJasmineTest</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f582d43f2234" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>