Application To Generate Combined Images Of All Image-Categories Possible Combinations

[Update] The application is now updated to avoid "out of memory" exceptions.


On my last project I was working on a tree control which represents system business objects into hierarchical form. Each object has a workflow to go through and for each step in this workflow the object state changes.

So, one of the requirements was to add images beside each tree node for the system user to know the status of each tree node easily with the need to open a properties window or something like that.

This is easy but we have some points to clarify first:

• Each system objects has three categories of status
1. Cat01: is so business related so I will not go into details about it but let's just say that this type of object status should have one of 25 possibilities
2. Cat02: is related to the type of change applied on the system object (added, edited, deleted, unchanged)
3. Cat03: is also so business related but it is mainly about object change approval status. This object status should have one of 7 possibilities
• For each image tag added on the page there will be a request to the server and for sure its related response

So, after some thinking we came up with 2 approaches to choose from:

1. Add three image tags beside each tree node and fill these tags with appropriate images according to the status of each object
2. Add one image tag beside each tree node and fill this tag with only one image which is all the object 3 status images combined into one image. The specific image name will be built by concatenating the keywords for each category status. This way we don't have to switch case or detect each combination from real object status, just concatenate the keywords and voila we have the right image name

After doing the math, and it was so simple, we decided to go with the second approach to eliminate the extra requests the would be sent for each tree node to get all the three status images, so instead of 3 requests it would be only 1.

So, now comes the hard part. We need to generate the combined images. We could have wrote some code to generate the combined image of each tree node on the fly at run-time but we thought that this would not be the best decision as the status images are some static images which we already have at design time. So, there was no reason to go with that much run-time processing for each node when we could already generate all the possible images combinations we need.

That's why we decided to generate all the possible combinations that we could have for each system object status. Someone said that this would be too much images but we replied that it is not a problem as these images will be static and at the end of the days we are talking about a few mega bytes of static hard desk space not RAM.

We thought that doing the combination thing manually would be a shame and actually impossible as we have 25 x 4 x 7 = 700 combinations which means 700 images. Doing all of this work manually is so bad by all means especially when at some point the client decides to replace an image with another one or add a new image.

That's why I wrote a simple windows application which does all the hard work. You just give it each category images and in just seconds you get all your images.

Hint: the code related to generating all the possible combinations is built using the "Possibilities Cube" library I had posted before. If you are interested to read about it you can find it on Possibilities Cube Library - A Library Smart Enough To Calculate All Possibilities With Logical Conditions

Now, let's see some screenshots for the windows application.


Images for the first category (Cat01)

Images for the second category (Cat02)

Images for the third category (Cat03)

Adding images for each category on the application

Finally, the generated images

As you can see it is so easy to use the application and you can apply any modifications on the code to go with your specific business needs. Currently the code is set to export images into png format but you can change it as you wish in the code.

I didn't put much care into the application UI, graphics and so on as it is only for indoor usage to serve a certain need not to be a standalone product or anything like that so don't be turned off by the UI as at the end of the day it may help you save much time and effort.

Finally, you can download the application from here


Wish you will find this useful.
Good luck.

Read More

Forcing ASP.NET Control To Go Through Its Full Life Cycle On Static Methods

A few days ago I needed to use a custom control which I made as a template to generate some HTML and return it as a response for an Ajax call, but, I faced a problem which is that my WebMethod is a static method which cannot access the control instance i dropped on the same page including the WebMethosd on code behind.

To make it more clear, here was the situation. I was working on an ASP.NET tree control which represents some system objects into hierarchical form. When testing the tree on few objects everything went fine, but with increasing the number of objects the tree became heavier and its response was not that appealing.

This made me think to take another approach which is using Ajax calls to hit the database and get the only objects I need per situation and finally attach/bind/update the tree with these objects. This will save me costy round trips back and forth to the server through postbacks and for sure the heavy load of the tree HTML with each request and response.

First, the idea was ideal but then came the difficulties. The tree control I was using is a third party control. So, I didn't actually have the code and pattern on which the nodes were built, so this made me think, how would I write the full HTML of the new nodes I got from the Ajax calls?

After short thinking, an idea hit me...... why don't I use the same tree control to draw me the single nodes I need, so whenever an Ajax call is performed a new instance of the tree control on another page will return me the HTML of the new nodes and then I will just update my main tree control with the new HTML. Luckily, I found that the tree control I was using had a method which would return me the generated HTML by the tree, so I cheered up and said out loud "Found it".

For the second time, this was a promising idea but then came the troubles. I had already implemented a static WebMethod on a page to call by Ajax. This WebMethod should be responsible for getting me the new HTML. So, I created an instance of the tree control inside this method, bound the tree to the new object, called the tree method which returns the generated HTML, but my code blown up with an exception.

After debugging I found that the tree method I was calling to get the generated HTML thrown some serious exceptions as the tree control instance I created inside my WebMethod is not actually fully loaded because the control didn't go through its whole life cycle.

This made me feel bad as the other alternatives are nightmares. So, I tried to search for any way by which I can force a control to go through its life cycle inside a static method. Finally, I found the solution and that is what I am going to show you here.

First, you have to know that the "Page" class has some validation which forces you to add a "Form" tag or you will get a serious exception. Since we will need on our solution to add the control we wish to load inside a static method on a page instance, then it would be nice to know this first.

To create a Page and bypass the Form tag existence we need to override the "VerifyRenderingInServerForm" method on the "Page" class. So, we will create a new class inherited from the "Page" class and override the "VerifyRenderingInServerForm" method as shown below.

public class PageWithoutForm : Page
{
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
}

Now, we get to the last part which is forcing the control to go through its life cycle. This can be done as in the code below.

[WebMethod]
public static string LoadControlInStaticMethod()
{
 CustomControl ctrl = new CustomControl();

 var page = new PageWithoutForm();
 page.Controls.Add(ctrl);
 StringWriter writer = new StringWriter();
 HttpContext.Current.Server.Execute(page, writer, false);
 
 ctrl.CustomProperty = "SomeValue";
 ctrl.FireSomeAction();
}

If you have your control in "ascx" form, you can do the same as in the code above but with just updating the line where you defined the control instance as in the code below.

[WebMethod]
public static string LoadControlInStaticMethod()
{
 CustomControl ctrl = (CustomControl)LoadControl("~/SomeFolder/CustomControl.ascx");

 var page = new PageWithoutForm();
 page.Controls.Add(ctrl);
 StringWriter writer = new StringWriter();
 HttpContext.Current.Server.Execute(page, writer, false);
 
 ctrl.CustomProperty = "SomeValue";
 ctrl.FireSomeAction();
}

That is it all. I hope this will help someone one day.
Good luck.

Read More

Possibilities Cube Library - A Library Smart Enough To Calculate All Possibilities With Logical Conditions

Let's imagine that as a big advertisement campaign Vodafone, Samsung & Nokia co-arranged a lottery. The winner will get two mobile phones in addition to two SIM cards with special numbers.

It is obvious that the SIM cards will be provided by Vodafone while the mobile phones manufacturer will be decided by toss, so the winner may get two phones from Samsung or Nokia or both. Also, the phones specifications will be somehow restricted as in the image below.
 

So, if we try to guess all the possible combinations of any of the phones, we can work it out and get the results as in the image below.

This was somehow easy as the possibilities are not that large. But what about guessing all the possible combinations of the two phones at the same time?

This time it is not that easy due to the large number of possibilities. As we can see there are 64 possibilities and this is because each phone can be one of 8 phone combinations and we have 2 phones, then 8 * 8 = 64

What if I told you that we need to re-visit the phones combinations are there is something not logical. We know that Samsung doesn't produce phones with Symbian as OS. So, we need to cancel the phone combination which includes both Samsung and Symbian.

Also, if the lottery managers said that the two mobile phones can't be identical or exactly the same which means that at least one phone specification should be differ between both phones.

All these logical restrictions should be included in our calculations to finally get all possible combinations we need.

If we try to visualize the whole thing, we can see it into the image below.

Now, how about if I tell you that there is a library which you can use to calculate such calculations and you can define your own logical restrictions to filter out all logically refused combinations, will you like to use this library?

Here come the PossibilitiesCube library.


PossibilitiesCube.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DevelopmentSimplyPut.CommonUtilities
{
    public class PossibilitiesCube
    {
        #region Properties
        public Func<Int64[], bool> AttributesCombinationValidator { set; get; }
        public Func<Int64[], bool> InstancesCombinationValidator { set; get; }
        public Func<Int64[], Int64[,], bool> FinalCombinationValidator { set; get; }
        public Int64 InstancesCombinationsMaxRowIndex
        {
            get
            {
                return instancesCombinations.GetLength(0) - 1;
            }
        }
        public Int64 InstancesCombinationsMaxColumnIndex
        {
            get
            {
                return instancesCombinations.GetLength(1) - 1;
            }
        }
        public Int64 AttributesCombinationsMaxRowIndex
        {
            get
            {
                return attributesCombinations.GetLength(0) - 1;
            }
        }
        public Int64 AttributesCombinationsMaxColumnIndex
        {
            get
            {
                return attributesCombinations.GetLength(1) - 1;
            }
        }

        bool combinationsExist;
        public bool CombinationsExist
        {
            get
            {
                return combinationsExist;
            }
        }
        #endregion Properties

        #region Fields
        Int64 numberOfInstances;
        Int64 instancesCombinationsMaxRowIndex;
        Int64 instancesCombinationsMaxColumnIndex;
        Int64 attributesCombinationsMaxRowIndex;
        Int64 attributesCombinationsMaxColumnIndex;
        Int64[,] attributesCombinations;
        Int64[,] instancesCombinations;
        Int64[] attributesPoolsSizes;
        #endregion

        #region Indexers
        public Int64 this[Int64 instancesCombinationIndex, Int64 instanceIndex, Int64 attributeIndex]
        {
            get
            {
                return GetAttributesCombination(instancesCombinations[instancesCombinationIndex, instanceIndex])[attributeIndex];
            }
        }
        public Int64[] this[Int64 instancesCombinationIndex, Int64 instanceIndex]
        {
            get
            {
                return GetAttributesCombination(instancesCombinations[instancesCombinationIndex, instanceIndex]);
            }
        }
        public Int64[] this[Int64 instancesCombinationIndex]
        {
            get
            {
                Int64[] result = new Int64[instancesCombinations.GetLength(1)];
                for (Int64 i = 0; i <= instancesCombinations.GetLength(1); i++)
                {
                    result[i] = instancesCombinations[instancesCombinationIndex, i];
                }
                return result;
            }
        }
        #endregion

        #region Constructors
        public PossibilitiesCube(Int64 _numberOfInstances, params Int64[] _attributesPoolsSizes)
        {
            if (_numberOfInstances <= 0)
            {
                throw new Exception("NumberOfInstancesPerPossibility must be +ve and greater than 0.");
            }

            numberOfInstances = _numberOfInstances;
            attributesPoolsSizes = _attributesPoolsSizes;

            attributesCombinationsMaxRowIndex = 1;
            foreach (Int64 size in _attributesPoolsSizes)
            {
                attributesCombinationsMaxRowIndex *= size;
            }
            
            attributesCombinationsMaxRowIndex--;
            attributesCombinationsMaxColumnIndex = _attributesPoolsSizes.Length - 1;
        }
        #endregion Constructors

        #region Methods
        public Int64[] GetAttributesCombination(Int64 index)
        {
            Int64[] result = new Int64[attributesCombinations.GetLength(1)];

            for (Int64 i = 0; i < attributesCombinations.GetLength(1); i++)
            {
                result[i] = attributesCombinations[index, i];
            }

            return result;
        }
        private void GetPossibilities()
        {
            Int64[,] result = new Int64[instancesCombinationsMaxRowIndex + 1, instancesCombinationsMaxColumnIndex + 1];
            Int64 numberOfFilteredOutPossibilities = 0;

            for (Int64 i = 0; i <= instancesCombinationsMaxRowIndex; i++)
            {
                Int64[] rowResults = GetPossiblityByIndex(i, instancesCombinationsMaxRowIndex, instancesCombinationsMaxColumnIndex, InstancesCombinationValidator, OperationMode.Instances);

                if (rowResults[0] == -1)
                {
                    numberOfFilteredOutPossibilities++;
                }
                else if(null != FinalCombinationValidator)
                {
                    if(!FinalCombinationValidator(rowResults, attributesCombinations))
                    {
                        rowResults[0] = -1;
                        numberOfFilteredOutPossibilities++;
                    }
                }

                for (Int64 k = 0; k < rowResults.Length; k++)
                {
                    result[i, k] = rowResults[k];
                }
            }

            Int64[,] finalResult;
            Int64 actualNumberOfPossibilities = instancesCombinationsMaxRowIndex + 1 - numberOfFilteredOutPossibilities;

            if (actualNumberOfPossibilities > 0)
            {
                finalResult = new Int64[actualNumberOfPossibilities, instancesCombinationsMaxColumnIndex + 1];

                Int64 actualRowIndex = 0;
                for (Int64 i = 0; i < instancesCombinationsMaxRowIndex + 1; i++)
                {
                    if (result[i, 0] != -1)
                    {
                        for (Int64 k = 0; k < instancesCombinationsMaxColumnIndex + 1; k++)
                        {
                            finalResult[actualRowIndex, k] = result[i, k];
                        }

                        actualRowIndex++;
                    }
                }

                combinationsExist = true;
            }
            else
            {
                finalResult = new Int64[1, instancesCombinationsMaxColumnIndex + 1];
                for (Int64 k = 0; k < instancesCombinationsMaxColumnIndex + 1; k++)
                {
                    finalResult[0, k] = -1;
                }

                combinationsExist = false;
            }

            instancesCombinations = finalResult;
        }
        public void BuildPossibilitiesMatrix()
        {
            Int64[,] result = new Int64[attributesCombinationsMaxRowIndex + 1, attributesCombinationsMaxColumnIndex + 1];
            Int64 numberOfFilteredOutPossibilities = 0;

            for (Int64 i = 0; i <= attributesCombinationsMaxRowIndex; i++)
            {
                Int64[] rowResults = GetPossiblityByIndex(i, attributesCombinationsMaxRowIndex, attributesCombinationsMaxColumnIndex, AttributesCombinationValidator, OperationMode.Attributes);

                if (rowResults[0] == -1)
                {
                    numberOfFilteredOutPossibilities++;
                }

                for (Int64 k = 0; k < rowResults.Length; k++)
                {
                    result[i, k] = rowResults[k];
                }
            }

            Int64[,] finalResult;
            Int64 actualNumberOfPossibilities = attributesCombinationsMaxRowIndex + 1 - numberOfFilteredOutPossibilities;

            if (actualNumberOfPossibilities > 0)
            {
                finalResult = new Int64[actualNumberOfPossibilities, attributesCombinationsMaxColumnIndex + 1];

                Int64 actualRowIndex = 0;
                for (Int64 i = 0; i < attributesCombinationsMaxRowIndex + 1; i++)
                {
                    if (result[i, 0] != -1)
                    {
                        for (Int64 k = 0; k < attributesCombinationsMaxColumnIndex + 1; k++)
                        {
                            finalResult[actualRowIndex, k] = result[i, k];
                        }

                        actualRowIndex++;
                    }
                }

                instancesCombinationsMaxRowIndex = intPow(actualNumberOfPossibilities, numberOfInstances) - 1;
                instancesCombinationsMaxColumnIndex = numberOfInstances - 1;
            }
            else
            {
                finalResult = new Int64[1, attributesCombinationsMaxColumnIndex + 1];
                for (Int64 k = 0; k < attributesCombinationsMaxColumnIndex + 1; k++)
                {
                    finalResult[0, k] = -1;
                }

                instancesCombinationsMaxRowIndex = 0;
                instancesCombinationsMaxColumnIndex = 0;
            }

            attributesCombinations = finalResult;
            GetPossibilities();
        }
        private Int64[] GetPossiblityByIndex(Int64 rowIndex, Int64 maxRowIndex, Int64 maxColumnIndex, Func<Int64[], bool> validator, OperationMode mode)
        {
            Int64[] result = null;

            if (rowIndex >= 0)
            {
                if (rowIndex <= maxRowIndex)
                {
                    result = new Int64[maxColumnIndex + 1];

                    for (Int64 i = 0; i <= maxColumnIndex; i++)
                    {
                        result[i] = GetPossiblityByIndex(rowIndex, i, maxRowIndex, maxColumnIndex, mode);
                    }

                    if (null != validator)
                    {
                        if (!validator(result))
                        {
                            for (Int64 i = 0; i <= maxColumnIndex; i++)
                            {
                                result[i] = -1;
                            }
                        }
                    }
                }
                else
                {
                    throw new Exception(string.Format("rowIndex can not be greater than {0}", maxRowIndex));
                }
            }
            else
            {
                throw new Exception("rowIndex must be +ve or equal to 0.");
            }

            return result;
        }
        private Int64 GetPossiblityByIndex(Int64 rowIndex, Int64 columnIndex, Int64 maxRowIndex, Int64 maxColumnIndex, OperationMode mode)
        {
            Int64 result = 0;

            if (rowIndex >= 0 && columnIndex >= 0)
            {
                if (rowIndex > maxRowIndex)
                {
                    throw new Exception(string.Format("rowIndex can not be greater than {0}", maxRowIndex));
                }
                else if (columnIndex > maxColumnIndex)
                {
                    throw new Exception(string.Format("columnIndex can not be greater than {0}", maxColumnIndex));
                }
                else
                {
                    Int64 numberOfHops = 1;
                    Int64 numOfItems = 1;

                    switch (mode)
                    {
                        case OperationMode.Attributes:
                            numOfItems = attributesPoolsSizes[columnIndex];
                            if (columnIndex == 0)
                            {
                                numberOfHops = 1;
                            }
                            else
                            {
                                numberOfHops = 1;
                                for (Int64 i = 0; i < columnIndex; i++)
                                {
                                    numberOfHops *= attributesPoolsSizes[i];
                                }
                            }
                            break;
                        case OperationMode.Instances:
                            numOfItems = attributesCombinations.GetLength(0);
                            numberOfHops = intPow(numOfItems, columnIndex);
                            break;
                    }

                    result = GetPossiblityByIndex(numOfItems, numberOfHops, rowIndex);
                }
            }
            else
            {
                throw new Exception("rowIndex and columnIndex must be +ve or equal to 0.");
            }

            return result;
        }
        private Int64 GetPossiblityByIndex(Int64 numberOfItems, Int64 numberOfHops, Int64 rowIndex)
        {
            Int64 result = 0;
            result = rowIndex / numberOfHops;
            result = result % numberOfItems;
            return result;
        }
        private Int64 intPow(Int64 a, Int64 b)
        {
            Int64 result = 0;

            if (0 == b)
            {
                result = 1;
            }
            else if (1 == b)
            {
                result = a;
            }
            else
            {
                result = a;
                for (Int64 i = 0; i < b - 1; i++)
                {
                    result *= a;
                }
            }
            
            return result;
        }
        #endregion Methods
    }

    public enum OperationMode
    {
        Attributes = 0,
        Instances = 1
    }
}

How to use the library?
The library is somehow simple in usage but always keep in mind the complexity of the task it is about to carry out. To see how simple it is you can check the test application below.


MainForm.Designer.cs

namespace TestApp
{
    partial class MainForm
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.btnRun = new System.Windows.Forms.Button();
            this.rtxtOutput = new System.Windows.Forms.RichTextBox();
            this.SuspendLayout();
            // 
            // btnRun
            // 
            this.btnRun.Location = new System.Drawing.Point(86, 312);
            this.btnRun.Name = "btnRun";
            this.btnRun.Size = new System.Drawing.Size(149, 33);
            this.btnRun.TabIndex = 0;
            this.btnRun.Text = "Get All Prizes Combinations";
            this.btnRun.UseVisualStyleBackColor = true;
            this.btnRun.Click += new System.EventHandler(this.btnRun_Click);
            // 
            // rtxtOutput
            // 
            this.rtxtOutput.Location = new System.Drawing.Point(12, 2);
            this.rtxtOutput.Name = "rtxtOutput";
            this.rtxtOutput.Size = new System.Drawing.Size(299, 304);
            this.rtxtOutput.TabIndex = 5;
            this.rtxtOutput.Text = "";
            // 
            // MainForm
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(321, 347);
            this.Controls.Add(this.rtxtOutput);
            this.Controls.Add(this.btnRun);
            this.Name = "MainForm";
            this.Text = "Test Application";
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.Button btnRun;
        private System.Windows.Forms.RichTextBox rtxtOutput;
    }
}

MainForm.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using DevelopmentSimplyPut.CommonUtilities;
using System.Globalization;

namespace TestApp
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void btnRun_Click(object sender, EventArgs e)
        {
            rtxtOutput.Text = string.Empty;

            string[] colors = new string[2] { "White", "Black" };
            string[] brands = new string[2] { "Nokia", "Samsung" };
            string[] os = new string[2] { "Symbian", "Android" };

            Int64[] attributesSizes = new Int64[3];
            attributesSizes[0] = colors.Length;
            attributesSizes[1] = brands.Length;
            attributesSizes[2] = os.Length;

            PossibilitiesCube container = new PossibilitiesCube(2, attributesSizes);
            container.AttributesCombinationValidator = new Func<Int64[], bool>
                    (
                        delegate(Int64[] attributesCombination)
                        {
                            bool result = true;
                            //filter out if the brand is "Samsung" and the os is "Symbian"
                            if (attributesCombination[1] == 1 && attributesCombination[2] == 0)
                            {
                                result = false;
                            }
                            return result;
                        }
                    );

            container.InstancesCombinationValidator = new Func<Int64[], bool>
                    (
                        delegate(Int64[] instanceCombination)
                        {
                            bool result = true;
                            //filter out if both mobile phones are identical
                            if (instanceCombination[0] == instanceCombination[1])
                            {
                                result = false;
                            }
                            return result;
                        }
                    );

            container.BuildPossibilitiesMatrix();

            for (Int64 i = 0; i <= container.InstancesCombinationsMaxRowIndex; i++)
            {
                if (container.CombinationsExist)
                {
                    for (Int64 k = 0; k <= container.InstancesCombinationsMaxColumnIndex; k++)
                    {
                        string color1 = colors[container[i, k, 0]];
                        string brand1 = brands[container[i, k, 1]];
                        string os1 = os[container[i, k, 2]];
                        rtxtOutput.Text += string.Format(CultureInfo.InvariantCulture, "[{0},{1},{2}]", color1, brand1, os1) + ((k != container.InstancesCombinationsMaxColumnIndex) ? "\t" : string.Empty);
                    }

                    rtxtOutput.Text += Environment.NewLine;
                }   
            }

            MessageBox.Show(string.Format(CultureInfo.InvariantCulture, "{0} prize combinations are found.", container.InstancesCombinationsMaxRowIndex + 1));
        }
    }
}

So, after using the library to calculate all possibilities of the problem described above then running the test application, we will get the results as in the image below.

Notes:
Please keep in mind that if the number of attributes and instances are too big this may cause an arithmetic overflow.

[Update] This library is already used on Application To Generate Combined Images Of All Image-Categories Possible Combinations


That's it. You can download the code from here


Hope you find this library useful.
Goodbye.

Read More

How To Copy SQL Hierarchical Data At Run-time While Keeping Valid Internal References And Self Joins

Sometimes when you deal with hierarchical data structures you may need to perform internal copy operations. To imagine what I mean, you can keep up with the scenario illustrated below.

You have a "Departments" table which include all departments in your system. Each department should have a parent department except for the top department which has no parent.

Now, assume that at some point in your system you need to make duplicates of the existing departments and this should happen automatically at certain condition or at certain action triggered by system user. So, you need to write a stored procedure which will copy the existing departments in the "Departments" table and insert them in the same table.

So, you may think that it is just a simple INSERT-SELECT statement operating on the same table; "Departments" table. This will leads you to the result as in the image below.

Now, you should have a look on this image and re-think what you did, is this the result you wish to achieve?
If you don't know or you still think this is the right result, you can have a look on the image below.

As you see in the image above, the newly inserted departments are messed up regarding their parent departments IDs. This is because while copying the old departments and inserting the new ones you didn't calculate the new IDs of the parent departments so now each department has a parent ID referencing the old department not the appropriate newly created one. This is so wrong.

To understand what I mean, you can have a look on the image below.

As you can see the department which had ID equals to "1" should now have ID equals to "5". Also, the department which had ID equals to "2" should now have ID equals to 6" and so on........

So, the valid result you wish to achieve is as in the image below.

So, how to reach this result? This is the main question this article is trying to answer.

Steps

1. Declare a table variable in which we will keep the IDs mapping. Each record in this table will hold the old copied ID and its corresponding newly inserted ID. This way anytime we need to map an old ID to its new one we can use this table as a reference
2. Copy and insert departments one by one and for each insert you just copy the "ParentID" column value as it is and we will deal with it later to be updated with the right value. Also, for each insert, insert a record in the IDs mapping table to hold the old and new IDs
3. Update the "ParentID" column for the newly inserted departments with the new IDs depending on the IDs mapping table which now should be populated with IDs pairs

Now, it is the time for some code.

-- Variable to hold the ID of the department to be copied; the old department ID
DECLARE @OldDeptID INT

-- Variable to hold the ID of the newly copied department; the new department ID
DECLARE @NewDeptID INT

-- A table to hold the departments to be copied from the "Departments" table
-- The idx column is an identity column
DECLARE @DepartmentsToCopy TABLE (idx INT IDENTITY(1,1), ID INT, Name VARCHAR(100), ParentID INT)

-- A table to map each old copied ID to its new inserted ID
DECLARE @IdsMapping TABLE(Old_Id int , New_Id int)

-- A counter to be used in a loop
DECLARE @counter int
SET @counter = 1

-- Inserting the departments to be copied into the @DepartmentsToCopy table
-- Here we selected all records without any filtering but this can be modified
-- according to your business needs
INSERT INTO @DepartmentsToCopy
(
 ID
 , Name
 , ParentID
)
SELECT ID
, Name
, ParentID
FROM Departments

-- Looping on each department record in the @DepartmentsToCopy table to perform
-- the required actions on each record one by one
WHILE @counter <= (select max(idx) from @DepartmentsToCopy)
BEGIN
 -- Inserting a copy of the current department record in the "Departments" table
 -- but with adding the word "New" at the end of the "Name" column
 INSERT INTO Departments
 (
  ID
  , Name
  , ParentID
 )   
 SELECT TOP 1 ID
 , Name + 'New'
 , ParentID
 FROM @DepartmentsToCopy
 WHERE idx = @counter
 
 -- Setting the value of @NewDeptID with the scope identity
 -- in order to hold the ID of the newly inserted department record
 SET @NewDeptID = SCOPE_IDENTITY()
 
 -- Setting the value of @OldDeptID with the old copied ID
 SELECT TOP 1
 @OldDeptID = ID
 FROM @DepartmentsToCopy
 WHERE idx = @counter
 
 -- Inserting a record into the @IdsMapping table to hold the IDs mapping
 -- where the old id is @OldDeptID and the new one is @NewDeptID
 INSERT INTO @IdsMapping
 (
    Old_Id
  , New_Id
 )
 VALUES(@OldDeptID, @NewDeptID)
 
 -- Incrementing the counter to work on the next department record
 SET @counter = @counter + 1
END

-- Updating the ParentID column of the newly inserted departments
-- to match the new IDs using the @IdsMapping table which hold the IDs mapping
UPDATE Departments
SET ParentID = map.New_Id
FROM Departments AS Dept
INNER JOIN @IdsMapping AS newOnly
ON Dept.ID = newOnly.New_Id
INNER JOIN @IdsMapping AS map
ON Dept.ParentID = map.Old_Id

That's it. Hope you will find this helpful someday.

Read More

How To Transform Unsorted Flat Hierarchical Data Structures Into Nested Parent-Child Or Tree Form Objects

Assume that you have hierarchical data structure presented into an SQL database table as in the image below.

As you can see each employee in the "Employees" table above can have a Manager which is an employee himself. In this case when the employee "Tarek" has "ManagerID" whose ID = 1, then "Tarek" has "Ahmed" as his manager. While "Ahmed" doesn't have a manager as he is the top manager.

This leads us to visualize the whole structure into parent-child relation as in the image below.

So, as we can see the data we can get from the "Employees" table is somehow flat because each data row will be represented by an "Employee" entity so at the end we can have a list of employees each preserves the ID of his manager as in the "Employee" entity below.

Employee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DevelopmentSimplyPut.HierarchicalObjectsManagements.Entities
{
    public class Employee
    {
        public int ID { set; get; }
        public string Name { set; get; }
        public int? ManagerID { set; get; }

        public Employee() { }

        public Employee(int id, string name, int? managerID)
        {
            ID = id;
            Name = name;
            ManagerID = managerID;
        }
    }
}

Unfortunately this is not always enough as sometimes we find ourselves in a need to represent this data into a more structured form so that we can bind it with a tree control or whatever. So, we need to write some code to transform this unsorted flat hierarchical data structure into a parent-child or tree form.

To do so, let's first build an entity which will represent our parent-child or tree form to be used later. This leads us to the "EmployeeTreeNode" entity.

EmployeeTreeNode.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DevelopmentSimplyPut.HierarchicalObjectsManagements.Entities
{
    public class EmployeeTreeNode
    {
        public Employee Employee { set; get; }
        public bool IsProcessed { set; get; }
        public int Level { set; get; }

        private List<EmployeeTreeNode> childNodes;
        public List<EmployeeTreeNode> ChildNodes
        {
            get { return childNodes; }
        }

        public EmployeeTreeNode()
        {
            Level = 0;
            childNodes = new List<EmployeeTreeNode>();
        }

        public EmployeeTreeNode(Employee employee, bool isProcessed) : this()
        {
            Level = 0;
            Employee = employee;
            IsProcessed = isProcessed;
        }
    }
}

Now, we need to write the code which will do the transformation part. This is the code where the magic happens.

Utilities.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DevelopmentSimplyPut.HierarchicalObjectsManagements.Entities;

namespace DevelopmentSimplyPut.HierarchicalObjectsManagements.Utilities
{
    public static class Utilities
    {
        public static EmployeeTreeNode GetEmployeeTreeNode(List<Employee> employees)
        {
            EmployeeTreeNode result = new EmployeeTreeNode();
            result.IsProcessed = false;

            List<EmployeeTreeNode> nodes = new List<EmployeeTreeNode>();
            foreach (Employee emp in employees)
            {
                nodes.Add(new EmployeeTreeNode(emp, false));
            }

            foreach (EmployeeTreeNode empNode in nodes)
            {
                if (empNode.IsProcessed)
                {
                    continue;
                }
                else
                {
                    if (null == empNode.Employee.ManagerID)
                    {
                        result = empNode;
                        empNode.IsProcessed = true;
                        empNode.Level = 0;
                    }
                    else
                    {
                        ProcessNode(empNode, nodes);
                    }
                }
            }

            if (result.ChildNodes.Count == 0)
            {
                result.ChildNodes.AddRange(nodes);
            }

            return result;
        }

        private static void ProcessNode(EmployeeTreeNode node, List<EmployeeTreeNode> nodes)
        {
            EmployeeTreeNode parentNode = nodes.DefaultIfEmpty(null).FirstOrDefault(n => n.Employee.ID == node.Employee.ManagerID);
            if (null != parentNode)
            {
                if (!parentNode.IsProcessed)
                {
                    ProcessNode(parentNode, nodes);
                }

                node.IsProcessed = true;
                node.Level = parentNode.Level + 1;
                node.Parent = parentNode;
                parentNode.ChildNodes.Add(node);
            }
            else
            {
                node.IsProcessed = true;
                node.Level = 0;
                node.Parent = null;
            }
        }

        public static string Repeat(this string source, int numOfTimes)
        {
            string result = source;

            if (numOfTimes > 0)
            {
                for (int i = 0; i < numOfTimes - 1; i++)
                {
                    result += source;
                }
            }
            else
            {
                result = string.Empty;
            }

            return result;
        }
    }
}

Now you can use the code above to get your parent-child or tree form from the unsorted flat hierarchical data structure. To validate the code above, here is a demo windows forms application which you can use.

Form1.Designer.cs

namespace HierarchicalObjectsManagements
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.btn_BuildTree = new System.Windows.Forms.Button();
            this.lst_Employees = new System.Windows.Forms.ListBox();
            this.SuspendLayout();
            // 
            // btn_BuildTree
            // 
            this.btn_BuildTree.Location = new System.Drawing.Point(181, 165);
            this.btn_BuildTree.Name = "btn_BuildTree";
            this.btn_BuildTree.Size = new System.Drawing.Size(75, 23);
            this.btn_BuildTree.TabIndex = 0;
            this.btn_BuildTree.Text = "Build Tree";
            this.btn_BuildTree.UseVisualStyleBackColor = true;
            this.btn_BuildTree.Click += new System.EventHandler(this.btn_BuildTree_Click);
            // 
            // lst_Employees
            // 
            this.lst_Employees.FormattingEnabled = true;
            this.lst_Employees.Location = new System.Drawing.Point(12, 12);
            this.lst_Employees.Name = "lst_Employees";
            this.lst_Employees.Size = new System.Drawing.Size(244, 147);
            this.lst_Employees.TabIndex = 1;
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(268, 193);
            this.Controls.Add(this.lst_Employees);
            this.Controls.Add(this.btn_BuildTree);
            this.Name = "Form1";
            this.Text = "Hierarchical Objects Management";
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.Button btn_BuildTree;
        private System.Windows.Forms.ListBox lst_Employees;
    }
}

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevelopmentSimplyPut.HierarchicalObjectsManagements.Entities;
using DevelopmentSimplyPut.HierarchicalObjectsManagements.Utilities;

namespace HierarchicalObjectsManagements
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btn_BuildTree_Click(object sender, EventArgs e)
        {
            lst_Employees.Items.Clear();

            List<Employee> employees = new List<Employee>();
            employees.Add(new Employee(4, "Saleh", 2));
            employees.Add(new Employee(1, "Ahmed", null));
            employees.Add(new Employee(5, "Selim", 4));
            employees.Add(new Employee(2, "Tarek", 1));
            employees.Add(new Employee(6, "Mohamed", 2));
            employees.Add(new Employee(3, "Hasan", 1));

            EmployeeTreeNode employeeTreeTopNode = Utilities.GetEmployeeTreeNode(employees);

            BuildTree(employeeTreeTopNode);
            MessageBox.Show("Done");
        }

        public void BuildTree(EmployeeTreeNode node)
        {
            lst_Employees.Items.Add("-".Repeat(node.Level) + node.Employee.Name);
            foreach (EmployeeTreeNode childNode in node.ChildNodes)
            {
                BuildTree(childNode);
            }
        }
    }
}

After running the windows form application, you will get the result as in the image below.

That's it. This is just a proof of concept but you can tweak it to satisfy your specific business and needs. I hope this helps you someday :)

You can download the code from here


Good Bye.

Read More

Why/How To Drop SQL Entities If They Exist?

Sometimes while writing SQL scripts purposed for fixing production issues you face some unexpected problems. These problems may be due to the existance of discrepancy between client's environments caused by missing some scripts and may be by other reasons.

In these cases you have to make sure that your scripts are somehow adaptable and can be run on all environments without raising errors and without causing any unexpected behaviors. These errors like trying to update a stored procedure which doesn't exist and so on.

The best practice in this situation which works perfectly most of the times is to first drop the entity you wish to modify if it already exists and then fully re-create it as you wish. This allows you to avoid many unexpected issues you can face.

So, below you will find the code which checks for the existance of some entities and accordingly drops these entities.


Tables

--Check if table exists in a database
IF EXISTS
(
 SELECT *
 FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[TableName]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)

DROP TABLE [dbo].[TableName]
GO

Views

--Check if view exists in a database
IF EXISTS
(
 SELECT *
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_NAME = 'ViewName'
)

DROP VIEW [dbo].[ViewName]
GO

Stored Procedures

--Check if stored procedure exists in a database
IF EXISTS
(
 SELECT *
 FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[StoredProcedureName]')
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)

DROP PROCEDURE [dbo].[StoredProcedureName]
GO

User-defined Functions

--Check if user-defined function exists in a database
IF EXISTS
(
 SELECT  *
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME = 'FunctionName'
 AND ROUTINE_SCHEMA = 'dbo'
 AND ROUTINE_TYPE = 'FUNCTION'
)

DROP FUNCTION [dbo].[FunctionName]
GO

User-defined Table Types

--Check if user-defined table type exists in a database
IF EXISTS
(
 SELECT * 
 FROM sys.types 
 WHERE is_table_type = 1 
 AND name = 'TableTypeName'
)

DROP TYPE [dbo].[TableTypeName]
GO

Table Indexes

--Check if table index exists in a database
IF EXISTS
(
 SELECT * 
 FROM sys.indexes 
 WHERE name='IndexName'
 AND object_id = OBJECT_ID('TableName')
)

DROP INDEX [IndexName] ON [dbo].[TableName]
GO

Hope you find this useful.

Read More

How To Apply Recursive SQL Selections On Hierarchical Data

Sometimes we work on systems where a hierarchical data structure exists on some entities like employees and their managers. Both employees and managers can be called employees but there is a self join relation between them as each employee must have a manager. I think we are all familiar with this pattern.

Also, I think we all faced the situation when we need to get the info about each employee and his/her direct manager. At this point we used to join between the employees (child) table and itself (parent) on the condition that the parent id of the child is equal to the id of the parent. This is good.

But, what about if we need to get the hierarchical tree of managers of a certain employee not just his direct manager. It seems as we just need to the same join but more than one time till we are up all the way to the head manager. This is somehow logical but how can we do this number of joins and we don't know the number of levels up to the head manager?!!!

This introduces the problem or the challenge we are going to find a solution to right now. The answer is simply recursive.

Before going into more details, let's simplify the meaning of the expression "Recursive". It means that we have some logic and we want this logic to be repeated more than one time till a certain condition is satisfied (or not satisfied), at this point the execution should be stopped to get the result of the whole process.

So, is this what we really need to find a solution for what we have on hand right now? I think it is so let's now see some code and try to simplify things a little bit.

First, let's assume that we have a hierarchical tree of departments in a certain company. The tree is as in the image below

Now, we need to write a select statement which will be converted to a stored procedure later. This select statement will select all parent departments from a certain department up to the head department which is department #1 as in the picture.

So, if we assume that the certain department we want to investigate is department #6, then we should have a table having the results as follows
6, 5
5, 2
2, 1
1, 1

As explained before this can't be achieved using single join operations, so lets now check the proposed solution.

Note
You can see the code already written and ready for execution on this link


First we will create our "Departments" table. This table includes each department and a reference to its parent (self join).

CREATE TABLE Departments
(
  [ID] [int] NOT NULL
  , Name nvarchar(max)
  , ParentID int
);

Second, let's fill some data into the "Departments" table to work on.

INSERT INTO Departments (ID, Name, ParentID)
VALUES(1, 'Dept1', 1)
,(2, 'Dept2', 1)
,(3, 'Dept3', 1)
,(4, 'Dept4', 1)
,(5, 'Dept5', 2)
,(6, 'Dept6', 5);

Now, we are ready to work on the select statement.

WITH AllDepartments([ChosenDept], [ChildID], [ChildName], [ParentID], [ParentName])
AS
(
 SELECT Child.ID AS [ChosenDept]
 , Child.ID AS [ChildID]
 , Child.Name AS [ChildName]
 , Parent.ID AS [ParentID]
 , Parent.Name AS [ParentName]
 FROM Departments AS Child
 LEFT OUTER JOIN Departments AS Parent
 ON Child.ParentID = Parent.ID
 
 UNION ALL
 
 SELECT AllDepartments.ChosenDept AS [ChosenDept]
 , AllDepartments.ParentID AS [ChildID]
 , AllDepartments.ParentName AS [ChildName]
 , NewParent.ParentID AS [ParentID]
 , NewParentInfo.Name AS [ParentName]
 FROM AllDepartments
 INNER JOIN Departments AS NewParent
 ON AllDepartments.ParentID = NewParent.ID
 AND AllDepartments.ParentID <> AllDepartments.ChildID
 INNER JOIN Departments as NewParentInfo
 ON NewParent.ParentID = NewParentInfo.ID
)

SELECT AllDepartments.[ChildID]
, AllDepartments.[ChildName]
, AllDepartments.[ParentID]
, AllDepartments.[ParentName]
FROM AllDepartments
WHERE ChosenDept = 6;

As you can see in the code above it seems to be a bit complicated but believe me it is not that hard to understand, so let's go through the analysis.

Analysis

1. As you can see in the first line we are using the "with" statement to create what is known as "Common Table Expression" (aka: CTE)
2. The CTE is an expression by which we a create a table using a select statement to be used as a common table on which other later select statements depend
3. CTE is not only used with SQL recursion but it is one of the most important applications
4. CTE is defined as "WITH tableName (columnAlias1, columnAlias2, columnAlias3, .....)
5. Inside the CTE body, there is two select blocks with a "UNION ALL" operator in the middle
6. The select statement on the top, before the "UNION ALL", is a select statement which provides the data to start with as a seed for the recursion process to start with
7. This select statement is only called once at first then the recursion process will depend on its results to work on
8. In our case, we need this select to get all the info we need about each department like
1. Department ID
2. Department Name
3. Parent Department ID
4. Parent Department Name (needs a self join)
9. So, it is a simple self join as we see but you can notice that we added an extra column over the 4 columns above, so what about this column?
10. This column represents the department we choose for investigation;
1. We need the final result (after the recursion is fully executed) to include each department and all parent departments up to the head one
2. So, for each department upon investigation we have multiple records (as in our example above, to investigate department #6, we had records {(6, 5), (5, 2), (2, 1), (1, 1)}, for #5 we had records {(5, 2), (2, 1), (1, 1)}, ............. so to consolidate all these results into one result set, we should add the department to investigate with each result so for #6 it should be {(6, 6, 5), (6, 5, 2), (6, 2, 1), (6, 1, 1)} and for #5 it should be {(5, 5, 2), (5, 2, 1), (5, 1, 1)} and so on)
3. Then at last when we decide to choose certain department to investigate, we can use this extra column to filter with, so if we need to investigate department #6, we can filter the final result set as follows "WHERE ChosenDept = 6"
11. For the second select statement below, this is the select which represents the recursive opertation
12. To understand what is going on let's imagine that we have the results of the first select statement stored int the CTE we created and try to go through the operation manually just using our minds
13. The results will be
1. {ChosenDept, ChildID, ChildName, ParentID, ParentName}
2. {1, 1, 'Dept1', 1, 'Dept1'}
3. {2, 2, 'Dept2', 1, 'Dept1'}
4. {3, 3, 'Dept3', 1, 'Dept1'}
5. {4, 4, 'Dept4', 1, 'Dept1'}
6. {5, 5, 'Dept5', 2, 'Dept2'}
7. {6, 6, 'Dept6', 5, 'Dept5'}
14.  Let's assume that we are going to investigate "Dept6"
15. Then we will check which department is its parent, it is "Dept5"
16. So now we try to find the department which is parent of "Dept5"
17. This piece of info exists in the "Departments" table
18. This means that we need to join the CTE result set above with the "Departments" table (let's call it "NewParent") on the condition that the "ParentName" column of the CTE result set is equal to the "ID" of the "NewParent" table. All of this to be able to get the parent department of "Dept5"
19. So now in the new select the selected columns will be as follows:
1. "ChildID" will be the "ParentID" of the CTE (because now this is the new child we are trying to find its parent, in this example, it is "Dept5")
2. "ChildName" will be the "ParentName" of the CTE
3. "ParentID" will be the "ParentID" of the "NewParent" table (because this is the direct parent of the current child which is "Dept5" in this example)
4. "ParentName" will be the name of the new parent we got in the column above (this required an extra join cause the name of the parent doesn't exist in the same table)
5. "ChosenDept" will be got from the CTE cause this is the same department we are still investigating from the main CTE
20. Now after we have finished the CTE including the recursion, we will write our final select statement which will get only the results when the "ChosenDept" column of the CTE is equal to 6

That's it. I know that it is not easy to understand it the first time but believe me if you read it one more time you will get it. Try to imagine the whole process in your head as if you are going to do it manual. After short time you will get the common sense of the whole operation and you will be able to understand every detail.

I encourage you to repeat reading the code and the steps more than once and for sure you can search the internet for any other tutorials or resources talking about "Recursive SQL using Common Table Expression".


Other Resources

1. Recursive Queries Using Common Table Expressions
2. SQL SERVER – Simple Example of Recursive CTE | Journey to SQL Authority with Pinal Dave
3. SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop | Journey to SQL Authority with Pinal Dave
4. SQL Anywhere: Example: RECURSIVE UNION

Hope you find this post useful someday :)

Read More

The Difference Between SQL Join Conditions Into "ON" And "WHERE" Clauses

Some of us while writing an SQL select statement with a join between two tables or more may get confused with whether to add a certain condition to the "ON" clause of the join or just add it to the "WHERE" clause as if it is just a filtering condition.

Some people may think that both approaches will return the same results every time. This is not completely true. Yes in some cases both approaches will return the same results but in other cases they won't. Let's check the case below and we will get the whole thing at the end.

Assume that we have two tables:

1. Departments table which includes all departments which exist at a certain company
2. Employees table which includes all employees in the same company

The two tables can be as follows

 

As you can see the "DepartmentId" column in the "Employees" table is a foreign key which references the "deptID" column of the "Departments" table.

Now, before going deep into code let's introduce a very useful online tool which is simply a SQL online simulator/compiler. Using this tool you can write some SQL statements, run them and get results as if you are running on an SQL management studio. I used this tool to prepare and test my queries while writing this post and I really encourage you to give it a try.

So, to use this tool:

1. Browse to http://sqlfiddle.com 
2. In the site tool bar on the upper left side there is a drop down list to choose the SQL engine you wish to use, so choose "MS SQL Server 2008" ...... I was so happy to find "MS SQL Server 2012" in the list :)

Now we are ready to start testing. You will find that you have two wide text areas. The one on the left is where you write your schema building code while the one on the right is where you write your SQL selects.

So, first let's build our schema. To do so, paste the code below into the text area on the left and hit the "Build Schema" button.

create table Departments
(
  [deptID] [int] NOT NULL
  , Name nvarchar(max)
  , CONSTRAINT pk_departments_pid PRIMARY KEY(deptID)
);

create table Employees
(
  [empID] [int] IDENTITY(1,1) NOT NULL
  , Name nvarchar(max)
  , DepartmentId int
  , CONSTRAINT pk_employees_pid PRIMARY KEY(empID)
  , CONSTRAINT FK_employees_Department FOREIGN KEY (DepartmentId) 
    REFERENCES Departments (deptID)
);

So now we have our two tables as in the picture

Now, let's test our two approaches and see what happens. Assume that for some reason we only care about all departments except department with deptID = 3.


Adding Condition To The "WHERE" Clause
Copy and paste the code below into the text area on the right, on the "Run SQL" button there is a small arrow pointing down, hit this arrow and choose "Tabular Output" and finally hit "Run SQL".

insert into Departments (deptID, Name)
values(1, 'Dept1'),(2, 'Dept2'),(3, 'Dept3'),(4, 'Dept4'),(5, 'Dept5');

insert into Employees (Name, DepartmentId)
values('Ahmed', 1),('Tarek', 2),('Hasan', 2),('Saleh', 2),('Selim', 3);

select e.Name as empName
, d.Name as deptName
from Employees as e
left outer join Departments as d
on e.DepartmentId = d.deptID
where d.deptID <> 3;

After running the code you will get the results as in the picture below

As you can see there should be the record (Selim, Dept3) but it is filtered out because we added a condition to the "WHERE" clause which states that we only need records matching to all departments except the departments which have the "deptID" column equal to "3".


Adding Condition To The "ON" Clause
Copy and paste the code below into the text area on the right, on the "Run SQL" button there is a small arrow pointing down, hit this arrow and choose "Tabular Output" and finally hit "Run SQL".

insert into Departments (deptID, Name)
values(1, 'Dept1'),(2, 'Dept2'),(3, 'Dept3'),(4, 'Dept4'),(5, 'Dept5');

insert into Employees (Name, DepartmentId)
values('Ahmed', 1),('Tarek', 2),('Hasan', 2),('Saleh', 2),('Selim', 3);

select e.Name as empName
, d.Name as deptName
from Employees as e
left outer join Departments as d
on (e.DepartmentId = d.deptID and d.deptID <> 3);

After running the code you will get the results as in the picture below

As you can see there should be the record (Selim, Dept3) but instead we have the record (Selim, NULL) and this is because we added the condition to the "ON" clause to filter out the departments with "deptID" equal to "3". So, just before the joining the table "Departments" is filtered according to the condition and then the joining is performed. Since the joining is outer left joining (knowing that the "Employees" table is the one on the left), then whatever values in the "Departments" table are all records in the "Employees" table will return. This time the employee "Selim" has no corresponding department in the "Departments" table because it is already filtered out by the condition just before the joining occurred.


Conclusion
When adding the condition to the "WHERE" clause, the joining is executed first and the filtering based on the condition is the last thing to happen. While when adding the condition to the "ON" clause the filtering is applied to the tables first then the joining happens.

So, in which cases do both approaches return the same results? This happens in case of inner joins as in inner joins if a record in the "Employees" table doesn't have a corresponding record in the "Departments" table then this record will be filtered out. So, even if the condition is added to the "ON" clause, the department record is filtered out before joining, joining is applied, then the joining itself will filter out the whole employee record because it has no corresponding department.


That's it. Hope you find this post useful.

Read More

How To Reconstruct String Sections From Concatenated String Format

 

Sometimes you find yourself in a need to pass some values from one module to another beside being restricted to using a string format not a fully qualified object. This restriction may be due to performance purposes or some technical restrictions like using a dictionary object which only provides you with a "key" and "value" pairs.

There is more than one way to deal with such situation like using serialization. But, I think that the simplest way to go with in this situation is using string concatenation but keep in mind that this approach needs that every single value you wish to pass can be represented into a string format.

For sure every object/value can be represented into a string format but not always using the string format is the best choice. If the object is too complicated then trying to construct the object from its string format will be somehow hard, not impossible, but hard.

So, assume that we decided to use the string concatenation approach. This is done as follows:

1. Constructing the string format: concatenate all sections/values into one string while using a certain character or set of characters as a separator between each two sections/values
2. Reconstructing sections/values: split the constructed string by the same character or set of characters you used in the constructing part

Here we have a problem that whatever the character or set of characters we will use as a separator between sections/values there is always a probability that the sections/values may also contain this character or set of characters in the first place. This will cause a problem when splitting the constructed string as the returned sections/values will be deformed.

So, to overcome this problem we will use the same approach but with some extra steps.

Analysis

• Let's say that we will use "#;" as our separator
• Also let's say that the sections/values we want to work on are "Ahm#;ed" and "Tar#ek"
• So, if we just concatenated the sections using the separator the result will be "Ahm#;ed#;Tar#ek"
• Now when splitting to reconstruct values the result will be "Ahm", "ed" and "Tare#ek"
• This is wrong
• So, since that the "#;" found in the "Ahm#;ed" is causing us problems, let's deal with it first
• So, we will first replace "#;" in "Ahm#;ed" to something else so that it will not confuse us while splitting
• But wait, if we replace it to another character or set of characters won't this cause the same problem we are trying to avoid in the first place???
• Say that we will replace "#;" in "Ahm#;ed" to "<>", then this will work because we already know that "Ahm#;ed" doesn't contain "<>". But what if at run-time one of the values we are dealing with already contain "<>"??? Then, we will have the same problem of invalid splitting, right?
• Ok, this is confusing but we have a solution
• Let's get back a few points, we said that the "#;" found in the "Ahm#;ed" is causing us problems
• We tried before to replace "#;" as a whole although we can only replace a part of it
• Also, the something to replace to should not be a completely different thing
• Confused, let's check this example
• We will replace "#" in "Ahm#;ed" to "#&", so the result will be "Ahm#&;ed"
• The same for "Tare#ek", it will be "Tare#&ek"
• So after concatenation it will be "Ahm#&;ed#;Tare#&ek"
• So after splitting the sections will be "Ahm#&;ed" and "Tare#&ek"
• Finally replace the "#&" to "#" in all sections
• Then the sections will be "Ahm#;ed" and "Tare#ek" which are the same sections we started with

Conclusion

1. Decide a separator ("#;")
2. Decide the part to replace in each section given that it is a part of the separator ("#")
3. Decide the string to replace to ("#&")
4. While concatenation, replace each occurrence of "#" in each section to "#&" then concatenate using "#;"
5. While splitting, split by "#;" then replace each occurrence of "#&" in each section to "#"
6. That's it, you now have your sections as they are without any deformation

Now, let's see some code.

The code below represents a class which encapsulates the logic described above.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;

namespace DevelopmentSimplyPut.Utilities
{
    public class EmployeeToken
    {
        #region Properties
  private string firstName;
        public string FirstName
        {
            get
   {
    return firstName;
   }
        }

  private string secondName;
        public string SecondName
        {
            get
   {
    return secondName;
   }
        }
  
  private string concatenatedSections;
        public string ConcatenatedSections
        {
            get
   {
    return concatenatedSections;
   }
        }
  
  public string NamesSeparator
        {
            get
   {
    return "#;";
   }
        }
  
  public string NamesStringToReplace
        {
            get
   {
    return "#";
   }
        }
  
  public string NamesToReplaceTo
        {
            get
   {
    return "#&";
   }
        }
        #endregion Properties

        #region Constructors
        public EmployeeToken(string _firstName, string _lastName)
        {
            firstName = _firstName;
            lastName = _lastName;
   
   concatenatedSections =
    string.Format(CultureInfo.InvariantCulture, "{0}{1}{2}"
                    , Encode(firstName)
                    , NamesSeparator
                    , Encode(lastName));
        }
  
  public EmployeeToken(string _concatenatedSections)
        {
   concatenatedSections = _concatenatedSections;
   
            if (!string.IsNullOrEmpty(id))
            {
                string[] separators = { NamesSeparator };
                stirng[] sections = concatenatedSections.Split(separators, StringSplitOptions.None);

                if (null != sections && sections.Length == 2)
                {
                    firstName = Decode(sections[0]);
                    lastName = Decode(sections[1]);
                }
            }
        }
        #endregion Constructors

        #region Utilities
        private static string Encode(string str)
        {
            return ((str == null) ? string.Empty : str.Replace(NamesStringToReplace, NamesToReplaceTo));
        }
        private static string Decode(string str)
        {
            return ((str == null) ? string.Empty : str.Replace(NamesToReplaceTo, NamesStringToReplace));
        }
        #endregion Utilities
    }
}

So now if you try to use this class you will get your results right

EmployeeToken newEmp = new EmployeeToken("Ahm#;ed", "Tar#ek");
Console.WriteLine(newEmp.ConcatenatedSections);
Console.WriteLine(newEmp.FirstName);
Console.WriteLine(newEmp.SecondName);

EmployeeToken newEmp1 = new EmployeeToken(newEmp.ConcatenatedSections);
Console.WriteLine(newEmp1.ConcatenatedSections);
Console.WriteLine(newEmp1.FirstName);
Console.WriteLine(newEmp1.SecondName);

That's it. Hope you find this useful.

Read More

How To Sell Your Work And Get Paid?

Everyone of us may have some personal work items like software, tutorials, presentations, articles, videos, graphics,....... Now, instead of just keeping these items in the shades on your drive you can sell these items and get paid for your work every time one of your items is sold.

One of the problems is always to find the right market. So, I found a great market where you can sell your work and get paid. This is what this article about.

Freelancer is a great reliable online service for freelancing. You will find that you can benefit from this service in two ways:

1. Freelancing: You will find some people posting requests for software, articles, remote assistance, professional resume writing, technical writing, translations, graphics, ........... and so many things which you can find yourself interested in and capable of providing. Whatever service you provide you will get paid
2. Freemarket: You can post items of your work and provide a price so that every time someone buys one of your items you get paid.

That's it, I wish you find this service useful and profitable.

Read More