Loading raw text data into SQL Server can be done using Flat File import or Data Imports, but I find this is always problematic as it does row scans to predict data types, but invariably fails on large data sets due to bad data types further down the file, or column sizes being predicted too small. Using BULK INSERT also fails in a similar fashion, and requires setting up column mappings.

I found a great way to do this dynamically using the XML data type - this was not my idea, but unfortunately I lost the link of the page where I found the technique.

IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'dbo' 
             AND  TABLE_NAME = 'MyTable'))
BEGIN
DROP TABLE MyTable
END
GO

IF (OBJECT_ID('tempdb..#data') IS NOT NULL) DROP TABLE #data
CREATE TABLE #data (data VARCHAR(MAX))

BULK INSERT #data FROM 'C:\temp\MyDataFile.txt' WITH (FIRSTROW = 2, ROWTERMINATOR = '\n')

IF (OBJECT_ID('tempdb..#dataXml') IS NOT NULL) DROP TABLE #dataXml
CREATE TABLE #dataXml (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, data XML)

INSERT #dataXml (data)
SELECT CAST('<r><d>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(data, 0x1F, ''), '<', '&lt;'), '>', '&gt;'), '&', '&amp;'), '|', '</d><d>') + '</d></r>' AS XML)
FROM #data

SELECT  d.data.value('(/r//d)[1]', 'varchar(max)') AS [Column 1],
    d.data.value('(/r//d)[2]', 'varchar(max)') AS [Column 2],
    d.data.value('(/r//d)[3]', 'datetime2') AS [Another Date Column] INTO MyTable
FROM #dataXml d

DROP TABLE #data
DROP TABLE #dataXml

SELECT TOP 100 * from MyTable
SELECT COUNT(*) AS [RowCount] FROM MyTable

This allows you to create a completely dynamic table, reading from a pipe-delimited text file. You can tweak the data types declared when you read out of the XML as you learn the data in the file.

This article is my personal summary (for future reference) of Robert Martin's book Clean Code (most of it, anyway).

Foreword

Making your code readable is as important as making it executable.

Chapter 1 – Clean Code

• They had rushed the product to market and had made a huge mess in the code. As they added more and more features, the code got worse and worse until they simply could not manage it any longer. It was the bad code that brought the company down.

Chapter 2 – Meaningful Names

• Names are important. Names should be meaningful, contextual, and clearly communicate intent. When editing code, if a name slows you down...rename!

Chapter 3 – Functions

• Functions should be short and only do one thing
• Follow one level of abstraction - you should not have mixed levels of detail in a single function.
• Switch statements - avoid except to create polymorphic objects
• Arguments should almost never exceed two - they take a lot of conceptual power and testing all combinations of 3 or more arguments is tedious
• Use exceptions instead of error codes
• Don't Repeat Yourself (DRY)
• You know you are working on clean code when each routine turns out to be pretty much what you expected

Chapter 4 – Comments

• Comments are not maintained over time, and so should be used sparingly and only when you are unable to express yourself with code

Chapter 5 – Formatting

• Keep line spaces between concepts
• Keep related functions close to each other, and where possible, parent functions before child functions. This minimises the need for future programmers to scroll up and down looking for implementation
• Instance variables at the top of the file, all other variables where they are needed
• Spaces between assignment operators
• Consistent indentation
• Team rules over personal opinions!

Chapter 6 – Objects and Data Structures

• Adding public getters/setters makes for a confusing interface - objects should hide their data behind abstractions
• Law of Demeter - a module should not know about the innards of the objects it manipulates (works hand in hand with the single-responsibility principle when creating objects)
• One needs to make a tradeoff: choose objects when you want the flexibility to add new data types; choose data types and procedures when you want the flexibility to add new behaviours

Chapter 7 – Exception Handling

• use exceptions rather than return codes
• define exception classes in terms of a caller's need, and abstract away the complexity of error handling in the custom exception class
• try not to return null (null returns result in obvious bugs as well as messy code which need to check for the null)

Chapter 8 – Boundaries

• don't pass standard types with too much functionality around your application (e.g. a shared Map with a clear() method could cause trouble) - rather create your own types with a limited interface that enforces correct behaviour and makes for more readable code
• code at boundaries needs clear separation and tests that define expectations - it's a good idea to wrap 3rd party libraries into your own "preferred" API - it makes your code more readable AND means you can switch the 3rd party library out with less impact
• this is also particularly useful with library upgrades (fewer places to fix on breaking changes)

Chapter 9 – Unit Tests

• keep your tests clean - this is production code too! Tests evolve with the changing code, and so need to also be maintainable
• single concept per test (try not to mix too many asserts in a single test)
• FIRST
  • Fast
  • Independent (tests should be able to run in any order and not be dependent on any other test)
  • Repeatable
  • Self-validating (should have a boolean output i.e. should either pass or fail)
  • Timely (should be written slightly before the production code)

Chapter 10 – Classes

• single responsiblity principle (SRP)
• aim for high cohesion - all methods should use at least one of the instance variables
• organise classes for change by minimising the responsibilities - if something changes we should be needing to change one area only
• isolate from change using the Dependency Inversion Principle - classes should depend on concepts rather than implementations, allowing us to easily switch out underlying functionality without changing the implementation of the class itself (use interfaces and abstract classes)

Chapter 11 – Systems

• separate construction from use to decouple the details of how to build items from the application (use Dependency Injection); an object should not take responsibility for instantiating dependencies itself
• cities don't build 6-lane highways from the start, similarly software projects should not overengineer the application until there is a need; implement only today's stories, then refactor and expand the system to implement new stories tomorrow
• use standards when they add demonstrable value (not for the sake of them)
• always use the simplest thing that can possibly work

Chapter 12 – Emergence

Kent Beck's rules of simple design:

• Simple Design Rule #1: Runs all tests - systems that aren't testable aren't verifiable, and systems that cannot be verified should not be deployed. Making our system testable pushes us towards a design where classes are small and have a single purpose!
• Simple Design Rule #2: No duplication - you should never duplicate code, whether it's copy/pasted lines or duplicate implementation. "Reuse in the small" (minor duplication of lines) is essential to acheiving reuse in the large. Eliminating duplication improves the quality of your code and can expose breaking of the SRP.
• Simple Design Rule #3: Code should be express the intent of the programmer - the clearer the author can make the code, the less time others will have to spend understanding it. Use good names, keep functions and classes small, use standard nomenclature, use well-written unit tests.
• Simple Design Rule #4: Keep classes and methods to a minimum - lowest in the order of priority of simple design, but still important to remember - don't create classes/methods just for the sake of it (e.g. an insistence that every class has an interface).

Chapter 13 – Concurrency

• concurrency is hard - keep methods simple and small
• be rigorous with the SRP
• make sure you know your framework - make sure you use thread-safe classes
• lock where appropriate but keep locks as small as possible, as few as possible, and only one per class if possible
• test code that will run concurrently in production rigorously and under varying load and in different environments
• invest time in ensuring your system can shut down gracefully
• consider deadlocks, livelocks and thread starvation scenarios early..and implement strategies to handle these

I use mRemoteNG on a daily basis, but every now and again it crashes, prompting you for a password to load your connections. This is as a result of a corrupt connections files.

Fortunately, the application creates backups of your connections when they change. To fix, navigate to the folder in the error message, in my case this folder is C:\Users\matt\AppData\Roaming\mRemoteNG

In that folder will be a confCons.xml file. This file can be deleted or backed up, and then the last backup can be renamed to confCons.xml. In my case, my last backup file was "confCons.xml.20211006-1534460806.backup" . Restart mRemoteNG, and you should be good to go.

Chrome stores SSL certificate state per host in the browser history, which can become an issue when you generate a new self-signed certifcate and need to refresh it. To clear the browser history:

• Ctrl + Shift + Del
• Deselect all checkboxes except for "Cached images and files"

• Clear data

  

You always want to log errors on your live systems, but sometimes there are errors that occur that you are not interested in and don't want to code around. For example, a client disconnects in the middle of a web request. NLog allows you to add filters to ignore these errors at a logger level. So, you could log all errors to a database AND to email, but specific errors would not get emailed and fill up your mailbox!

<rules>
<logger name="*" minlevel="Error" writeTo="Database" />
<logger name="*" minlevel="Error" writeTo="email">
    <filters>
        <when condition="contains('${aspnet-request:serverVariable=HTTP_URL}','Token') and contains('${exception:format=Message}','task was canceled')" action="Ignore" />
    </filters>
</logger>

At IUA we use Jenkins for CI, and we use Powershell for automation of our builds and deployments. We've always invoked Powershell using batch commands, but I recent implemented the Powershell plugin which makes life a little easier. Some tips on implementation:

Include a file

Prefix the file name with a ". "

. C:\Temp\test.ps1

Use environment variables

When running in the context of Jenkins, you can get and set environment variables. This can be useful when wanting to access a common function used across multiple scripts. For example, if you have many files wanting to use a version number, but the generation of that version number is complex enough to put it in a function, you could move that function to a common file and then store the generated value in an environment variable:

. C:\Temp\version.ps1
$env:version = GetVersion
Write-Host $env:version

Run jobs via the Jenkins API

I got this code from Stack overflow (https://stackoverflow.com/questions/46131903/why-is-powershell-not-able-to-send-proper-crumb), but adapted it to include build parameters:

function RunJenkinsJob
{
    param(
        [string]$userName,
        [string]$password,
        [string]$apiUrl,
        [string]$apiPort,
        [string]$jobName,
        [string]$jobParams      # url-encoded string of parameters e.g. myparam1=val1&myparam2=anotherval
    )

    $h = @{}
    $h.Add('Authorization', 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::UTF8.GetBytes("$(${userName}):$(${password})")))

    $params = @{uri = "${apiUrl}:${apiPort}/crumbIssuer/api/json";
            Method = 'Get';
            Headers = $h;}
    $apiCrumb = Invoke-RestMethod @params

    $uri = "${apiUrl}:${apiPort}/job/${jobName}"
    if ([String]::IsNullOrWhiteSpace($jobParams)) {
        $uri = "$uri/build"
    }
    else {
        $uri = "$uri/buildWithParameters?$jobParams"
    }

    $h.Add('Jenkins-Crumb', $apiCrumb.crumb)
    $params['uri'] = $uri
    $params['Method'] = 'Post'
    $params['Headers'] = $h

    Invoke-RestMethod @params

}

RunJenkinsJob -userName "jenkins" -password "mypassword" -apiUrl "http://buildserver" -apiPort "8080" -jobName "JobWithParams" -jobParams "PARAM1=value1&PARAM2=value2"
RunJenkinsJob -userName "jenkins" -password "mypassword" -apiUrl "http://buildserver" -apiPort "8080" -jobName "JobWithoutParams" 

For every web site created in IIS, a new W3SVCX folder is created in the logging folder configured for IIS. Knowing which folder applies to which site is not obvious, particularly when you have multiple sites serving the same content and it's not easy to determine this information from inspecting the log files.

The configuration can be worked out by opening the applicationHost.Config file in the %WinDir%\System32\Inetsrv\Config folder.

Under the sites element, each site is then listed with a unique id, for example:

<site name="mysite.mydomain.co.za" id="6" serverAutoStart="true">

In this case, the log files for this site will be stored in the W3SVC6 folder.

I've been meaning to move to CloudFlare for a while, and thanks to this amazingly helpful page created by Troy Hunt, I've taken the leap.

I've been battling for time, and this really took all the research requirements right out of it, and I'm up and running with SSL even though my host doesn't support it on the cheap hosted package I'm using.

I've been using MediaWiki for ages for technical documentation, and I always forget how to implement custom CSS in both screen and print, so I'm adding this as a permanent reminder for myself.

To edit the CSS for the site (screen version), navigate to "http://YOURMEDIAWIKISITE/index.php?title=MediaWiki:Common.css" - and edit the CSS declared on that page

To edit the CSS for printing, navigate to "http://YOURMEDIAWIKISITE/index.php?title=MediaWiki:Print.css"

Easy!

I needed to move a WordPress site to HTTPS. The certificate was installed, and the site was being correctly served over HTTPS, but I was getting mixed content warnings, as many of the images were being loaded over http. There are many sites that give the full rundown, but nothing worked for me for the Enfold theme.

This site: https://managewp.com/blog/wordpress-ssl-settings-and-how-to-resolve-mixed-content-warnings - can be used to set up HTTPS from start to finish

This site: https://isabelcastillo.com/mysql-wordpress-http-to-https - led me to the solution

Once the site was set up for https, I had to run the following four queries against the WordPress database:

UPDATE wp_posts 
SET    post_content = ( Replace (post_content, 'src="http://', 'src="//') )
WHERE  Instr(post_content, 'jpeg') > 0 
    OR Instr(post_content, 'jpg') > 0 
    OR Instr(post_content, 'gif') > 0 
    OR Instr(post_content, 'png') > 0;

UPDATE wp_posts
SET   post_content = ( Replace (post_content, "src='http://", "src='//") )
WHERE  Instr(post_content, 'jpeg') > 0 
    OR Instr(post_content, 'jpg') > 0 
    OR Instr(post_content, 'gif') > 0
    OR Instr(post_content, 'png') > 0;

UPDATE wp_postmeta
SET    meta_value = ( Replace (meta_value, 'src="http://', 'src="//') )
WHERE  Instr(meta_value, 'jpeg') > 0 
    OR Instr(meta_value, 'jpg') > 0 
    OR Instr(meta_value, 'gif') > 0 
    OR Instr(meta_value, 'png') > 0;

UPDATE wp_postmeta
SET    meta_value = ( Replace (meta_value, "src='http://", "src='//") )
WHERE  Instr(meta_value, 'jpeg') > 0 
    OR Instr(meta_value, 'jpg') > 0 
    OR Instr(meta_value, 'gif') > 0 
    OR Instr(meta_value, 'png') > 0;