Underestimating The Use of Stored Procedures

It's a shame that I have come quite late in the game when it comes to fully utilising the benefits of stored procedures. I am more than familiar on the general uses, such as CRUD operations, passing and returning parameters. Standard stuff!

When I decided to work on a new project, I made a conscious decision to place all my database queries within stored procedures for the following reasons:

  • To broaden my current knowledge.
  • Remove all queries from web application level for maintainability.
  • Attempt to use logic in my stored procedures to do the brunt of the processing in one database call.
  • Increase speed of execution and performance for when a procedure is run multiple times.

Out all of the points listed above, my main aim was to try and minimise all my database calls and get the data in a format that can be used directly without any further manipulation at application level.

As you can see from the following stored procedure, I am carrying out checks to ensure specific data elements exist prior to carrying out further database operations. Thus, hopefully improving performance an efficiency.

CREATE PROCEDURE spLogTagActivity
(
    @ItemPrimaryHashtag AS nvarchar(100),
    @Hashtag AS nvarchar(100),
    @TagCategoryID AS int
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT OFF;
    
    DECLARE @TagID AS int
    DECLARE @TagIsIgnored AS bit
    DECLARE @ItemID AS int
    DECLARE @ItemTagID AS int
        
    --Get TagID and Tag Ignore value based on the text value
    SELECT @TagID = ID, @TagIsIgnored = IsIgnored FROM Tag WHERE TextLowercase = LOWER(@Hashtag) COLLATE SQL_Latin1_General_CP1_CS_AS
    
    SET @ItemID = (SELECT ID FROM Item WHERE PrimaryHashtag = @ItemPrimaryHashtag)

    --If an Item can be found based on the Primary Hashtag, we will use the data.
    IF (@ItemID IS NOT NULL)
    BEGIN
        IF (@TagID IS NULL AND @TagIsIgnored IS NULL)    
        BEGIN
            --Only insert tags that are not the same as the Item Primary Hashtag
            IF (LOWER(@ItemPrimaryHashtag) <> LOWER(@Hashtag))
            BEGIN
                EXEC spInsertTag @TagCategoryID, @Hashtag, 0, 0, NULL, NULL, NULL, 0, @TagID OUTPUT
            END
                                        
            --Associate new tag to the Item.
            IF (@TagID IS NOT NULL)
                BEGIN
                    EXEC spInsertSingleItemTag @TagID, @ItemID, 1, @ItemTagID OUTPUT
                                                    
                    --Create a new Activity line for the Item.
                    EXEC spInsertTagTracking @ItemTagID
                END
        END    
        ELSE
        BEGIN
            --If a Tag has already been logged, we will create a new Activity line for the Item
            --only if it does not have an ignored status
            IF (@TagID IS NOT NULL AND @TagIsIgnored <> 1)    
            BEGIN
                --Associate the Tag to an Item, if it doesn't already exist.
                SET @ItemTagID = (SELECT ID FROM ItemTag WHERE TagID = @TagID AND ItemID = @ItemID)
                
                IF (@ItemTagID IS NULL)
                BEGIN
                    EXEC spInsertSingleItemTag @TagID, @ItemID, 1, @ItemTagID OUTPUT                                        
                END        
                                    
                EXEC spInsertTagTracking @ItemTagID    
            END
        END
    END
    ELSE
    BEGIN
        IF (@TagID IS NULL AND @TagIsIgnored IS NULL)    
        BEGIN    
            EXEC spInsertTag @TagCategoryID, @Hashtag, 0, 0, NULL, NULL, NULL, 0, @TagID OUTPUT    
        END
    END
END
GO

I won't go into detail on what this sample stored procedure is doing, but I am delivering different outcomes based on the conditional statements. This is something that could have been done at application level, but much nicer in one database call. In other procedures I have utilised CASE and WHILE operators.

The benefits of using stored procedures far outweigh the negatives...Negatives!? Yes negatives...Well I just have one small gripe. If you create a stored procedure that contains quite a bit of meaty logic and find that something is not working (even though syntactically correct), it's quite difficult to debug exactly what point the procedure is failing.

To end the post, here's something surprising I found: The execution time of running a simple SELECT query can be slow when used within stored procedures, even though the query itself is fast. This is something I've encountered myself when wanting to retrieve a single value from one of my tables. The only way to get around this, is by passing you query parameter to a local variable inside the stored procedure. Take a look at this answer on StackOverflow. Strange!

Microsoft Virtual Academy...Something every Microsoft Developer Should Take A Look At!

There are many roads and avenues a tech-head can take to either get a grasp on new technology or prepare for certification. Unfortunately, some methods to get the knowledge on a subject can come at a great cost...especially when it comes to anything Microsoft.

Generally, Microsoft has always had some great forum and blogging communities to enable developers to get the expertise they require. I've always found them to be somewhat divided and looked rough around the edges. Now Microsoft has reworked its community and provided learners with a wide variety of courses freely available to anyone!

While MVA courses are not specifically meant to focus on exam preparation. They should be used as an addition to paid courses, books and online test exams to prepare for a certification. But it definitely helps. It takes more than just learning theory to pass an exam.

So if you require some extra exam training or just want to brush up your skills, give a few topics a go. I myself decided to test my skills by starting right from the beginning and covering courses that relate to my industry. In this case, to name a few:

  • Database Fundamentals
  • Building Web Apps with ASP.NET Jump Start
  • Developing ASP.NET MVC 4 Web Applications Jump Start
  • Programming In C# Jump Start
  • Twenty C# Questions Explained

I can guarantee you'll be stumped by some of the exam questions after covering each topic. Some questions can be quite challenging!

I've been a .NET developer for around 7 years and even I had to go through the learning content more than once. Just because you've been in the technical industry for a lengthy period of time, we are all susceptible to forget things or may not be aware of different coding techniques.

One of the great motivations of using MVA is the ranking system that places you against a leaderboard of other avid learners and seeing yourself progress as you complete each exam. All I can advise is that don't let the ranking system be your sole motivation to just "show-off" your knowledge. The important part is learning. What's the point in making a random attempt to answer each exam without a deep understanding on why you got the answer correct or incorrect.

You can see how far I have progressed by viewing my MVA profile here: http://www.microsoftvirtualacademy.com/Profile.aspx?alias=2181504

All in all: Fantastic resource and fair play to Microsoft for offering some free training!

Get Record Count Grouped By Month and Year

Grouping records by their respective month and year based on a date column can be done really easily by simply running the following SQL query:

SELECT
       DATEPART(MONTH, DownloadDate) AS Month,
       DATEPART(YEAR, DownloadDate) AS Year,
       COUNT(DownloadID) AS NumberOfItems
FROM
       Download
GROUP BY
       DATEPART(YEAR, DownloadDate), DATEPART(MONTH, DownloadDate)
ORDER BY
       Year DESC, NumberOfItems DESC

As you can see from the query (above), I am using the "DownloadDate" column to group my records and to also get a count of how many records belong to that month and year.

Use Your Strings Wisely

When I was first learning to code, I was always told to use my strings in applications wisely. It's very easy to use strings without much thought. I think strings are forgiving compared to any other data type...too forgiving.

I was going to write a full blown blog post on the best way to use the string data type, but I found a good blog post that explains everything and I will use for reference when guiding new developers to my somewhat infinite wisdom. :-)

Zeeshan Umar, wrote a very interesting blog post a few years ago (but still relevant) that I only happened to find, explaining some new interesting ways on how to use strings efficiently. The following approach caught my eye:

string errorCode1 = "ec-1001";

if (String.Compare(errorCode1,"ec-1001",true) == 0)
{
   //Do something...
}

The String.Compare constantly stares me in the face via Visual Studio intellisense but never thought of using it. I will be using this approach if I need to check a string whilst using an conditional statement.

If you happen to find anymore interesting ways to efficiently use strings, please leave a comment.

MVC Custom Extension Methods

I've been toying around with MVC for quite some time now. Initially, I couldn't imagine breaking away from the safety-net that is ASP.NET Web Forms. What many developers are not aware of when moving over to the ASP.NET MVC framework is that you have to write everything from scratch. You will not have the comfort of dragging and dropping event driven controls in a GUI-centric way.

But nowadays, I am itching to build new sites in MVC. I like to be in control of the whole page lifecycle and the mark-up that is generated.

Since there are no pre-built resusable controls, I decided start developing my own library of extensions that I could use in future MVC projects I work on. Ranging from pagination to tag clouds.

Creating custom extensions is really easy. I started off by creating a Category Navigation that returns a IHtmlString (HTML-encoded string that should not be encoded again).

public static IHtmlString CategoryNavigation(this WebViewPage wvp)
{
    StringBuilder navBuilder = new StringBuilder();

    List<CustomCategory> categories = CustomCategoryLogic.GetCategories();

    if (categories.Count > 0)
    {
        navBuilder.Append("<ul class=\"nav\">");
        navBuilder.Append("<li><a href=\"/\">Home</a></li>");

        foreach (CustomCategory cc in categories)
            navBuilder.AppendFormat("<li><a href=\"/{0}\">{1}</a></li>", cc.Slug, cc.Name);

        navBuilder.Append("</ul>");
    }

    return MvcHtmlString.Create(navBuilder.ToString());
}

To display my category navigation in one of my Views, I just need to write:

@this.CategoryNavigation()

How easy is that!?

Some New Custom Form Controls I've Been Working On In Kentico...

Over the last few projects I've been working on, I started to notice that clients are requiring increased integration with social platforms that give them the ability to display key elements from well known social platforms, such as YouTube, Twitter and Instagram. Hence the reason why in the past I created a YouTube Form Control that would easily retrieve all information information about a video along with their screen caps by simply entering the YouTube URL.

Recently, I've been working on two more form controls involving Twitter and Instagram to further enhance social platform integration within Kentico from a user standpoint, which (I think) is quite neat!

Twitter

Using a combination of OAuth and Twitter's GET statuses/show/:id API endpoint, I was able to store within Kentico all JSON data relating to a tweet by just entering the ID. One major advantage to storing JSON data is that we can display the tweet as many times as we want throughout the site without worrying about breaching API limits.

In addition, individual elements of a tweet, such as the embedded image, hash tags and author information can be used when required.

Tweet Custom Form Control

As you can see (above), I am using Twitter's Embedded Tweet plugin to display the tweet in a nice graphical representation so that the site administrator is able to view contents of the tweet prior to publishing live.

Instagram

Like the Twitter control above, details of an Instagram image is retrieved in a similar fashion, whereby I'm storing all the details in JSON format.  In this case, I'm using a combination of two Instagram API endpoint's:

  • /oembed - to get the core information of an image by passing in the full URL of where the image resides. This will return important piece of information: the true ID of the image.
  • /media/media-id - to get all information about the media or video object. This information will be stored in Kentico.

Unlike Twitter, Instagram's API is a breeze to implement. There are no API limits (at time of writing) and all you need to access the endpoints is to generate a one time only access token.

Instagram Custom Form Control

By copying and pasting the link of an Instagram image will return all object information and display the image/video within CMS Desk.

I will at some point write a blog post on how I created these two controls once I have refactored all the code into one single control. At the moment, some key functionality is segregated into separate class libraries.

Kentico Certified Developer

A couple days ago I passed my Kentico exam. If anything, I think I've learnt more about Kentico and just how much the platform has to offer. The exam is filled with a wide range of questions from the very simple and straight-forward to the ones that require a more time for deep thought.

In fact, I found the first few set of questions so simple, it got me second guessing myself. I'll admit, I found the exam a little tricky and there are some questions you have to read very carefully, especially ones around K# syntax.

I dedicate this certification to all the awesome guys at Syndicut. I seriously couldn't have done it without them.

I guess I can now display this:

Kentico Certified Developer Logo

There's Nothing Like a SSD To Breath Life Back Into Your Computer...

Crucial 480GB SSDThe plan originally was to ditch my current laptop, Alienware M11x R3 for something a little more recent with a better build quality. Even though my Alienware is an amazing workhorse for the type of work I do (with a very high spec), I started to get annoyed with the common issue these laptop's have: screen touching the keyboard when closed.

I wanted to get an Apple MacBook Pro Retina. But that got thrown completely out the window when I decided to go on the path of getting a property! Eek! So I had to make do with what I currently have on a very modest budget.

As of late, I started noticing that my laptop was getting very sluggish and kept freezing every so often. This continued even after carrying a full factory format to give my Operating System a fresh install. The only thing I could amount this to was that my hard drive was on its way out.

Luckily for me, at the same time, Amazon had some great offers in their computer components section and managed to get a Crucial 480GB Solid State Drive at a price that was an absolute steal!

I've heard online and from a few friends of mine that once you go SSD, you definitely won't go back...and this truly is the case. I wasn't expecting to see so much of a performance increase. My laptop has never performed this well.

At the same time, I decided to upgrade my current Operating System to Windows 8. I was getting a little bored of Windows 7. Plus, I quite like the new tiles interface. Very pretty! I don't understand the complaints or negativity. Some are obviously scared of change...but that's for a future blog post.

Here are a few things I've noticed since my upgrade:

  • Boot times have minimised substantially.
  • My laptop always used to feel very warm during long-term usage. Now it always feels cool.
  • I'm sure battery times have improved.
  • Virtual Machines load in seconds, rather than minutes.

Upgrading to a SSD is a very cheap and easy way to get more out of your machine. Highly recommended!

Official Google Nexus 7 (2013) Case Review

Nexus 7 Case Google LabelEver since I purchased my Nexus 7 last year. I've been trying to find a nice case for it. Failing, I settled for a cheap and cheerful folio case from eBay, which (still to this day) has served me well. But I was dying to have a case that looked different and oozed some unique design elements.

When I noticed Google selling their own collection of Nexus 5 and 7 cases, I purchased one straight away. The Grey/Blue colour scheme caught my eye. It seemed that Google's offering ticked all the boxes. What could they possibly do wrong? It's an official product designed and manufactured by the very people who made the Nexus 7. If anyone could make a case without fault, it would have to be Google....right?

Sadly no.

For starters, the case lacks magnet technology allowing automatic power on/off feature when opening the case. Secondly, there was no type of latch that would keep the case closed and found that the case opened whilst it was moving around in my backpack. Maybe I just had high expectations since my current offering already had these features.

Yes. These might be small things. But I found myself getting increasingly agitated (maybe an overstatement!) whilst using my Nexus 7, especially for a case that cost four times the price of the case I previously used.

Nexus 7 Case - Outside

Nexus 7 Case - Inside

It wasn't all doom and gloom. There were things I did like about Google's case offering. I loved that the case looked and felt very different to what is available on the market currently. Outside was covered with hard wearing fabric with an inner lining of suede. Definitely high quality stuff! 

Unfortunately, Google just seemed to miss the mark by not including a few key features, mainly being the magnetic sensor.

Back to Google it goes.

Update - 11/02/2014

I was expecting to pay for all postage costs to return the case. But after contacting Google Support regarding the reasons to why I wanted to return the item, they sent me a prepaid shipping package and were very helpful throughout the return process. Quick and easy!

Custom Form Control To Select A Folder In Media Library

I had a need to have the ability to select a folder from within the site's media library. Not a file. A folder. The idea behind this requirement was to allow the site administrator to upload a bunch of images to a single directory in the media library, so that the contents (in this case images) could be output to the page.

Unfortunately, after contacting Kentico support, I was told that such a folder selector control does not exist and I would need to create one myself. So I did exactly that!

Step 1: Create A New User Control

I have created a user control in "/CMSFormControls/Surinder/" of my Kentico installation. I have named the user control: FolderSelector.ascx.

HTML

<table>
        <tr>
            <td class="EditingFormValueCell">
                <asp:TreeView ID="MediaLibraryTree" SelectedNodeStyle-BackColor="LightGray" ExpandDepth="0" ImageSet="Arrows" runat="server"></asp:TreeView>
            </td>
        </tr>
</table>

Code-behind

using CMS.CMSHelper;
using CMS.FormControls;
using CMS.GlobalHelper;
using CMS.MediaLibrary;
using CMS.SettingsProvider;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class CMSFormControls_iSurinder_FolderSelector : FormEngineUserControl
{
    private string _Value;

    public override Object Value
    {
        get
        {
            return MediaLibraryTree.SelectedValue;
        }
        set
        {
            _Value = System.Convert.ToString(value);
        }
    }

    public string MediaLibraryPath
    {
        get
        {
            //Get filter where condition format or default format
            return DataHelper.GetNotEmpty(GetValue("MediaLibraryPath"), String.Empty);
        }
        set
        {
            SetValue("MediaLibraryPath", value);
        }
    }

    public override bool IsValid()
    {
        bool isControlValid = true;

        if ((FieldInfo != null) && !FieldInfo.AllowEmpty)
        {
            this.ValidationError = "Please select an Image Gallery directory";
            isControlValid = false;
        }

        return isControlValid;
    }

    protected void EnsureItems()
    {
        if (MediaLibraryPath != String.Empty)
        {
            string fullPath = Server.MapPath(String.Format("~/{0}", MediaLibraryPath));

            if (Directory.Exists(fullPath))
            {
                DirectoryInfo rootDir = new DirectoryInfo(fullPath);

                TreeNode treeNodes = OutputDirectories(rootDir, null);

                MediaLibraryTree.Nodes.Add(treeNodes);
            }
            else
            {
                this.ValidationError = "Directory path does not exist.";
            }
        }
        else
        {
            this.ValidationError = "Properties for this control have not been set.";
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            EnsureItems();
    }
    
    TreeNode OutputDirectories(DirectoryInfo directory, TreeNode parentNode)
    {
        if (directory == null)
            return null;

        //Create a node for directory
        TreeNode dirNode = new TreeNode(directory.Name, directory.FullName);

        //Get subdirectories of the current directory
        DirectoryInfo[] subDirectories = directory.GetDirectories();

        //Mark node as selected
        if (dirNode.Value == _Value.ToString())
            dirNode.Selected = true;

        //Get all subdirectories
        for (int d = 0; d < subDirectories.Length; d++)
            OutputDirectories(subDirectories[d], dirNode);
                
        //If the parent node is null, return this node
        //otherwise add this node to the parent and return the parent
        if (parentNode == null)
        {
            return dirNode;
        }
        else
        {
            parentNode.ChildNodes.Add(dirNode);

            return parentNode;
        }
    }
}

I'm hoping that the code I've shown above is quite self-explanatory. But the only thing you need to be aware of is the "MediaLibraryPath" public property. You will see in the next steps that we will be using this property to contain a link to where our Media Library resides.

Step 2: Add New Control To Kentico

When creating a custom form control in Kentico, ensure we have the following form control settings:

Kentico Folder Selector Settings

Step 3: Add Form Control Property

Remember, from Step 1, we had a property called "MediaLibraryPath". Now we just need to create this property in our custom control settings.

Kentico Folder Selector Settings

Now, when our Folder Selector control is added to a document, we will need enter a map path to the location of our Media Library. For example, "/Surinder/media/Surinder/".

If this custom control has been implemented successfully, you should see something like this when creating a new page based on a document type:

Kentico Folder Selector Tree