Attach to Process

One of the best additions to SQL Server 2016 is native support for JSON. There a number of articles already covering how to query JSON in SQL Server, so I won't cover those. What particularly gives me headaches though is querying a JSON Array in SQL Server. Hopefully the scripts below can help someone else.

The SELECT script below can be used when you are working with a very basic JSON array that doesn't even have a Property for the array. In this example, we have an array of OrderIds.

DECLARE @OrderIdsAsJSON VARCHAR(MAX) = '["1", "2", "3"]';

SELECT [value] AS OrderId
FROM OPENJSON(@OrderIdsAsJSON)
WITH
(
       [value] BIGINT '$'
);

The second SELECT script below can be used when you are working with a JSON array that does have a Property defined for the array. In this example, we have an array of OrderIds with a defined OrderId property.

DECLARE @OrderIdsAsJSON VARCHAR(MAX) = '{"OrderId":["4", "5", "6"]}';

SELECT [value] AS OrderId
FROM OPENJSON(@OrderIdsAsJSON, '$.OrderId')
WITH
(
       [value] BIGINT '$'
);

---

Update: 2019-07-17

Adding another example for a JSON array that came from an Int list in C#. This is how you could query it in SQL Server.

DECLARE @OrderIds VARCHAR(MAX) = '{"$type":"System.Int64[], mscorlib","$values":[4, 5, 7, 999]}';

SELECT [value] AS OrderId
FROM OPENJSON(@OrderIds, '$."$values"');

#SqlServer #Scripts #Database #JSON

Discuss... or leave a comment below.

Out of the 5 SOLID principles, the Open/Closed principle was the hardest one for me to grasp. This post from Code Maze explains it really well and the code examples were very helpful. This was a good read.

Link: Open and Closed Principle

Rather than writing my own post about it, I thought I would share this one instead. After all, as programmers we should avoid reinventing the wheel as much as possible. This is also my first post in a new “Bookmarks” category that I've added to this website. The plan is to use that new post category to share interesting articles that I've found online.

#Bookmarks #SolidPrinciples #CSharp

Discuss... or leave a comment below.

Ever since upgrading to Visual Studio 2017, I was always annoyed that it no longer defaulted to opening the Output window whenever I start building the solution. If I remember correctly, opening the Output window or making it visible was the default behavior in Visual Studio 2013 and 2015. So with VS 2017 I've always had to manually open the Output window during or after a build to see the output summary. Turns out the fix for this is a very simple setting in Visual Studio (duh). This saves me a good number of mouse clicks in a day :)

Reference: How to keep output window always open in Visual Studio 2017

#VisualStudio

Discuss... or leave a comment below.

I'm putting this TRY CATCH template on here in case other people might find it helpful. This is what I usually use when wrapping SQL Scripts in a stored procedure. Normally I would have go to Microsoft Docs to get the syntax for using TRY CATCH and RAISERROR. Having a template that I know works and is ready to go is just easier.

BEGIN TRY
   BEGIN TRAN

	-- Add your scripts in here
	
   COMMIT TRAN
END TRY  
BEGIN CATCH  
	WHILE (@@TRANCOUNT > 0)
	BEGIN
		ROLLBACK TRAN;
	END

	DECLARE @ErrorMessage NVARCHAR(4000);  
	DECLARE @ErrorSeverity INT;  
	DECLARE @ErrorState INT;  

	SELECT   
		@ErrorMessage = ERROR_MESSAGE(),  
		@ErrorSeverity = ERROR_SEVERITY(),  
		@ErrorState = ERROR_STATE();  

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
END CATCH;

#SqlServer #Scripts #Database

Discuss... or leave a comment below.

The scripts below will help you find a table in a SQL Server database based on a given “table name” filter. This is helpful if you are not familiar with a rather large database and didn't want to waste time looking for a table manually. In my case, I usually use these scripts to check for an existing table in a database, before I create a new one.

Most of the time when working with a large database, the table that you think you need to add, was already created by someone else. Oftentimes it is named differently than what you would have named it, but the contents and use of the table would have been the same. So it pays to double check before creating an already existing table.

SELECT *
FROM SYS.tables
WHERE NAME LIKE '%tableName%'

/* The script below will give you a result in the "schema.tablename" format, which can help you quickly locate the table. */
SELECT (SELECT [Name] FROM SYS.schemas AS S WHERE S.schema_id = T.schema_id) + '.' +  T.Name AS 'TableName'
FROM SYS.tables AS T
WHERE NAME LIKE '%tableName%'

#SqlServer #Scripts #Database

Discuss... or leave a comment below.

Adding a time to read estimate was something I've always wanted to do when I created my blog. I like the idea of giving readers an idea of how much time they would spend/lose if they read my posts. This is so they can make better use of their time. If they have enough time, they can read the post, if not, they can bookmark it or add to their reading list.

A note on customizing a Wyam blog

One of the confusing things for me when I first started using Wyam to create my blog is how to customize it. After running the command wyam new -r Blog, you will end up with an about.md markdown file, an input folder with a first-post.md markdown file in it and that's it. Well there is a config.wyam file but that's not what you use to customize the whole site. There are no .html, .cshtml or .css files that you can modify to customize your site. Turns out to customize the site, you will have to add new files to the input folder and add your modifications to those files. The next time Wyam builds your site, Wyam will pick up those files and process them.

What confused me was the use of the word “theme”. When I read theme, I think about the design of the site, mainly modifying the CSS files to customize how it looks. For Wyam though, theme means everything about how the site looks, which includes the design and layout of the content on the site. So say if you wanted to modify how the footer looks, like add content on it, you would have to create a _Footer.cshtml file in the input folder and add your customizations to that file. The Wyam documentation actually talks about that here and on the Overriding Theme Files section here.

Now that we've got that out of the way. Here are the steps I took for adding a time to read estimate for posts on this blog.

Step 1: Add a _PostHeader.cshtml file to the input folder

I wanted to add the time to read estimate on the top of a blog post, around where the Title and Published date shows up. So the file to modify is the _PostHeader.cshtml file. To do this, I created an empty _PostHeader.cshtml file in the input folder and copied the contents from this file. Note that the file is from the CleanBlog theme, which is the theme I'm using for this blog.

At this point you can do a Wyam build to make sure it works and that nothing is broken. Your site will not look any different because we have not made any modifications, we basically just copied the default code for the _PostHeader.cshtml file. When you don't have this file in your input folder, Wyam will actually build and process this file behind the scenes when building your site. When you have the same file in the input folder, Wyam knows to take that file and its content as the main source for processing the _PostHeader.cshtml file.

Step 2: Add code to calculate “time to read” in the _PostHeader.cshtml file

To calculate the time to read value for a blog post, we can use Razor, which is one of the reasons why I like using Wyam. At the top of the file I added the following lines of code:

char[] delimiters = new char[] {' ', '\r', '\n' };
var numberOfWords = Document.Content.Split(delimiters, StringSplitOptions.RemoveEmptyEntries).Length;
var TimeToRead = Math.Ceiling(numberOfWords / 200f).ToString();

What we're doing here is getting the content of the current blog post, then splitting it into an array to get how many words are in the blog post. I actually got that code from a StackOverflow answer. Then we divide the number of words by 200 to get a conservative time to read estimate value in minutes, which is then converted to a string.

To display the time to read estimate, I modified the line of code that displays the date the post was published and I ended up with this:

<div class="meta">
    @if (Published != default(DateTime))
    {
        <text>@Published.ToLongDateString(Context) &rarr; @TimeToRead min read</text>
    }
</div>

I basically just appended an arrow that points to the time to read estimate, on the same line that shows date the post was published.

At this point, you can do a Wyam build and preview your site. All your blog posts should now have a time to read estimate at the top, just below the title.

To view the whole content of the _PostHeader.cshtml file for this blog, you can check here.

#Wyam #Blog

Discuss... or leave a comment below.

As I sat down to write the post on how I added a basic Tags page to my previously Jekyll Now powered blog/website, I thought about how I'm playing catch up as far as blogging related features go. I had to add an Archive page. I had to add a basic Tags page. The sitemap and feeds feature didn't seem to work and I haven't spent the time to figure those out yet. So before I write even more blog posts and in the interest of saving myself time, I've switched to using Wyam to generate this blog/website.

I should have gone with Wyam from the start, but I found the Jekyll Now repository last minute and thought it seemed too easy not to give it a try. So I gave it a try and yes, it is very easy to get a blog up and running without touching the command line at all. A lot of people have used it and are happy with their blogs. However I think it's time for me to get back to what I really intended to do in the first place, and that is building this static blog/website using Wyam.

Here are some reasons as to why I want to use Wyam for this blog/website:

• It is a static site generator that is written using .NET Core. Being a .NET software developer myself, using it to generate my blog/website is extremely rewarding in some way. I've also wanted to play with .NET Core for awhile now, so this is just another excuse to try it out.
• It supports building pages/websites using Razor, which I think is awesome.
• The configuration file is written using C# code and I love C#.
• You end up with a fully featured Blog when using the Blog recipe. By that I mean, you have a working Archive and Tags page, as well as automatically generated feeds.
• It is very easy to switch themes, even on an existing live site.

I must point out that creating/maintaining a blog using Wyam is slightly more challenging than using the Jekyll Now approach. You will have to use a command prompt/terminal to build your site and getting it hosted is not as simple as just creating a repository in Github and changing some settings. I like the extra challenge though. And writing blog posts using text editors and building the site using a terminal, kinda makes me feel like I'm blogging like a hacker, except I'm using .NET Core and Wyam instead of Jekyll.

Tags: #Wyam #Blog

Discuss... or leave a comment below.

As I alluded to in my previous post, part of the limitations with creating a blog using the Jekyll Now repository, is that it didn't come with Archive and Tags support out of the box. Considering how easy it was to get a blog up and running with Jekyll Now, we shouldn't hold this against it or its creator/contributors. Instead, we should take this as an opportunity to learn more about Jekyll and start writing some code. So here is a quick guide on how you can add an Archive page to your Jekyll Now blog.

Step 1: Add a new archive.html file inside the _layouts folder in your repository.

The content of this file will be a mixture of HTML and Liquid. Liquid is a template language that you can use with Jekyll to help control the pages that are generated for your static site. Think of it as some programming code that gets processed when your static site is generated.

I actually struggled with this for awhile as I could not reconcile how a static site had, what looked like server side code in its pages. What I didn't realize then was that the Liquid code is only used to generate a static page/site. From a .NET developer's perspective, it is almost like Razor, except it is only used to generate a static site/page. It is not going to run as server side code.

The content of the archive.html file will be as follows:

---
layout: default
---

<article class="page">
  <h1>{{ page.title }}</h1>
  <div class="entry">
    
    {% assign previousYear = "" %}
    {% for post in site.posts %}
      {% capture currentYear %}
        {{ post.date | date: "%Y" }}
      {% endcapture %}
    
      {% if currentYear != previousYear %}
        {% assign previousYear = currentYear %}
        <h3>{{ currentYear }}</h3>
      {% endif %}
    
      {{ post.date | date: '%B %d, %Y' }} - <a style="font-weight: bold" href="{{ post.url }}">{{ post.title }}</a>
      <br />
    {% endfor %}    
  </div>
</article>

All I'm trying to do here is get all the blog posts on this site and list them down by year. *Note that I am relying on the fact that the list of blog posts returned by the Jekyll Site Variable site.posts remains in reverse chronological order. For now, that seems to be the case and I don't have to add Liquid code to correctly sort the entries.*

If you're wondering what the line of code ”| date: '%B %d, %Y'” does, that is what is called a Liquid filter and that simply formats the date value to a format I specify. This is similar to calling the ToString() method on a DateTime object in C# and passing in a custom datetime format string.

Step 2: Add a link to the Archive page by editing the default.html file.

The default.html file is also found in the _layouts folder in your repository. The link we want to add is this:

<a href="{{ site.baseurl }}/archive">Archive</a>

To find where to insert that line, just find the block of code in the default.html file, where it has the links to the About and Blog page. The code below is an example of what you are looking for.

  <nav>
	<a href="{{ site.baseurl }}/about">About</a>
	<a href="{{ site.baseurl }}/">Blog</a>
	<a href="{{ site.baseurl }}/archive">Archive</a>
	<a href="{{ site.baseurl }}/tags">Tags</a>
  </nav>

As you can see in the code above, I have added the link to the Archive page after the link to the Blog. This is also the code to modify if you wanted to change the order that the links show up on your website.

Step 3: At the root of your repository, add an archive.md file.

The content of the archive.md file will be as follows:

---
layout: archive
title: Archive
permalink: /archive/
---

Adding a .md (Markdown file) at the root of your repository will force Jekyll to generate a html page for that .md file. This is also how Jekyll decides to create the About page, because there is an about.md file in the root of the repository.

Some notes on the content of the archive.md file:

• The layout entry simply tells Jekyll to reference the archive.html file that you added to the _layouts folder. So basically when this Archive page is generated, it will use the layout/html that you have specified in the archive.html file.
• The Title entry is simply the Title text that will show up on the Archive page.
• The Permalink entry is used to ensure that the generated Archive page shows up on “yourdomainname.com/archive/”. You can also supposedly set this in the configuration file, but I just chose to do it here.

There you have it, that wasn't too hard I think. For reference, you can view the source code for this website on Github to see how I implemented it.

#Jekyll #Liquid #Blog

Discuss... or leave a comment below.

Today I ran into an issue where I was trying to debug an application and I was getting a “Breakpoint failed to bind” error on Visual Studio 2017. I was trying to attach to the process when I got the error. I was already running in Administrator mode, so it wasn't a permissions issue.

Solution

Turns out I had the solution configuration set to “Release” right from the start. The fix then was to set the solution configuration to “Debug” and then rebuild the solution. After that, I was able to attach to the process with no errors whatsoever.

Reference:

https://stackoverflow.com/questions/31732944/breakpoint-failed-to-bind-visual-studio-2015

#VisualStudio #Debugging

Discuss... or leave a comment below.

If you are a software developer, this is probably the quickest and most fun way to get a blog up and running in like 5 minutes. If you are not a developer and don't have time to tinker with your blog, I think you might be better off creating a blog using a CMS like Wordpress. At the very least, you would need to learn how to write content using Markdown if you are going to follow this approach to creating and maintaining your blog. (Markdown is very easy to learn though, even for non developers.)

Regardless of whether you are a developer or not, this approach is pretty easy to follow and you will end up with a simple blog that just works. You don't even need a custom domain to try this out and your blog will be hosted for free on Github pages.

This is how this blog/site was previously created. I have since updated this blog to be powered by write.as.

There are a number of articles/posts about this topic already so I won't rehash what others have already written. I'll keep this short and simple and just point you to where you need to go to get this done.

1. Sign up for a Github account.
2. Find the Jekyll Now repository on Github and start reading the readme info. Actually, you can jump straight to the Quick Start section and follow the instructions there.
3. That's pretty much it actually. If you follow Step 2 and go through the instructions from the Quick Start section, the end result is your very own blog up and running on the web, hosted on Github Pages for free!

Barry Clark, the creator of the Jekyll Now repository also wrote a more detailed tutorial on how to create a blog using Jekyll Now and Github pages. If you made it this far, I suggest reading that as well.

There are some limitations to creating a blog using this approach, namely, your blog will not have an Archive and Tags pages out of the box. Those are features that I think people would normally expect to be built-in on a blog website. It is not the case here, most likely due to the restrictions Github Pages has on Jekyll plugins. Fear not, as these are easy to add/implement yourself. I will cover adding an Archive and Tags page in a future post.

I hope this post has at least helped you get started on a blog. Good day everyone!

#Jekyll #GithubPages #Blog

Discuss... or leave a comment below.