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

SharePoint DateTimeControl Issue When Put Inside An Update Panel

All credits for this post goes to my friend and colleague Mohamed Gamal


When you add a SharePoint DateTimeControl to a webpart inside an update panel and click on the date picker icon, you will receive an "Object Expected” JavaScript error.

<SharePoint:DateTimeControl ID="dtcBirthDate" runat="server" CalendarImageUrl="/_layouts/images/calendar.gif"
CssClassTextBox="itw-longDate" DateOnly="true" LocaleId="7177" ToolTip="Example: 2000-12-31"
DatePickerFrameUrl="/_layouts/iframe.aspx" />

The solution of this error is to just include the datepicker.js file explicitly on the page inside the update panel content template as follows

<script type="text/javascript" src="/_layouts/datepicker.js"></script>

That's it. Hope this will help someone someday :)

Read More

ASP.NET Tips And Hints

During my work on ASP.NET projects I passed by a number of problems. Some of these problems may be common and others may be somehow rare. Anyway, I decided to focus on these problems and come up with some sort of patterns to overcome these problems in the future. Don't get me wrong when I said patterns, they are not design patterns, they are just some arrangements which work together to help you not fall in the same mistakes every time.

So, you can look at this topic as a list of tips and hints which may be useful for you while working on ASP.NET projects. May be some of them won't work with your specific business but at least they are good to know.

So, let's start with these tips.


Main Design:

1. Centralized logging module: you should spend sometime before going deep into your business code to set some grounds for your project to stand on. These grounds include all the non-business related code which you may use repeatedly throughout your project. One of these grounds/modules is the logging module. To know more about this point, you can check Extensible Logging Library For Sharepoint With ULS Logging Support
2. Centralized settings module: the same concept applies here on the settings module. You should prepare a module which handles all aspects of your code related to saving and retrieving business and non-business settings. For sure you can do this by just using key and value pairs through you code but this may cause you troubles and difficulties when you face a change request or a new security constrain. To know more about this point, you can check How To Centralize Your Web Application Settings And Decouple Your Code From Back-End Dependent Logic & Code
3. Exposing business related settings: try to expose business related settings and avoid hard-coded values as far as you can because these settings will be subject to frequent change according to the client's needs
4. Centralized error handling module: one of the basic and frequently used modules is the error handling module. You will find that at many points in your code you need to direct the system user to an error page with a descriptive message beside adding a record in the logging back-end for further investigation and tracking. For sure you can do this on spot every time you need but this is not a good thing as you will need to keep track of every spot you used this code to apply any further changes. So, the best way to do this is to write a single class with the methods you usually use when you handle an error ans start using this class whenever you need.
5. Centralized constants class: throughout your code you will find yourself use some constants which are related to the environment (ie.: hostname, port number,......) or some internal constants which are used in your code and the client doesn't need to manage or some computed values. Instead of hard-coding these values in more than one place, you can centralize these constants into one class which you can use anywhere in your code when you need to use any of these constants. This way, when you need to change any of these values you will have only one place to visit
6. Common utilities class: the same applies on utility methods. You will find yourself use some logic repeatedly through your code like converting from string to int with some validation or something like that. In this case, it would be nice to isolate this common logic into one place/class to be used whenever needed
7. Always use a masterpage: sometimes you feel like you don't need a masterpage but believe me you may regret this. Always use a master page even if it will be just a dummy parent page. This will save you a lot of time and effort when you need to apply some common logic or UI beside it will be painful to re-visit all your pages to apply changes to connect these pages with a masterpage, so, let's do it at the early beginning
8. Take care of static constructors and try to use "Double Locking": We use static constructors to do some initialization or logic the first time our static class is used. This is very useful and already used in may applications and even design patterns like "Singleton". But, you need to be careful when using static constructors when the code inside deals with an external source or by any mean is subject to exceptions and errors because once an exception raises inside the static constructor you will have to live with the consequences till the application is reset. If you want to know more you can check How To Handle Exceptions Inside Static Constructors - Double Locking Concept
9. Use separate folder for third party dlls: if you use external third party dlls in your application, create a folder for these dlls other than the "Bin" folder and always add references from this folder
10. Use automated javascript and css minifying: the js and css files you use may include some comments, spaces, empty lines, ....... these things enlarges the js and css files which consumes more bandwidth and affects performance. You can help reduce this waste of bandwidth by minifying these files before deployment on production environments. For sure you will not do it yourself but you can automate it using some techniques and tools. Here is a useful post which explains one technique to achieve this

DataBound Controls:

1. One bind method: write only one method which is responsible for binding the control and handling the paging and sorting. This way you will avoid repeated logic and handling the same cases more than once.
2. Avoid binding when postback: don't bind your control in the page load outside the "!IsPostback" because this will cause some errors and misleading logic. You should keep in mind that every time you click on a button on the page or any other control which causes postback the page load event is executed first before going into the the postback event handler. So, for example if you click on a page number on the pager control, then the page load will be executed first then the "PageIndexChanged" event handler will be executed. So, if you re-bind the control every time you go into the page load event, you will face an error which is telling you you may need to stop the event validation. This is happening because ASP.NET was trying to change the page index in the control as per your request, but before reaching the page index changed event handler you re-bound the control which resets the control and at this point ASP.NET is confused. So, always make sure to not bind the control unless you really need it
3. Always bind when data is changed: sometimes you think that you don't need to re-bind your control as the change is already done and the control doesn't need to know more. This is not right, every time the data in the datasource change you will need to re-bind the control. You can trust me on this or you can try it yourself
4. Keep current page index in hidden field: the common behavior between some data-bound controls is that they reset the page index on sorting so that when you change the sorting expression the control returns back to the first page. If you need to keep the page index upon sorting you can keep the page index in a hidden field and always use this field, only update this field when an action happens which updates the page index

General Tips:

1. Get search values from hidden fields not controls: if you apply searching with some search criteria, then when retrieving data don't get your search criteria values from their corresponding controls directly but get them from hidden fields. This helps you avoid retrieving data upon dummy search criteria unless these criteria are confirmed by hitting the search button. To understand what I am saying, let's say that you have a data grid with paging, user enters value "1" in your search criteria textbox, hits "search", results appear matching the "1", then enters "2" in textbox, hits the arrow on the grid to browse to the second page, then your code will get results matching to the search criteria "2" not "1". This is not logical. So, the best practice is to create a hidden field for every search criteria and update the hidden field value upon clicking the search button. Any time in your code you want to get the value of a search criteria get it from the corresponding hidden field. This way, the criteria will only change when system user hits the search button.
2. Use URL encoding when needed: whenever you are passing values in a URL use URL encoding and for sure decoding on the other side
3. Use ClientID: whenever you try to access a server side control from client-side make sure to get its id using ClientID property
4. Use Encryption/Decryption when needed: whenever you need to pass valuable data between sever and client side think of encrypting and decrypting this data to keep it secure
5. Validate inputs on both client and server side: don't depend on the client side validation alone and always re-validate inputs on server side. As you know javascript could be disabled on the client browser and this will stop your client side validation. Also, system user can tamper your page using tools like IEDev and this way he can bypass your client side validation logic
6. Always try to use code generation: always try to use a code generation tool to generate your layers or any other standard code. This makes your code more stable and reliable as these code generation tools are tested and widely used.

That's what I have on mind right now and I will try to keep this list updated. Hope you find something useful in this list.

Read More

How To Handle Exceptions Inside Static Constrcutors - Double Locking Concept

Static constructors are very useful but sometimes dangerous in case of depending on an external source or asset.

We use static constructors to do some initialization or logic the first time our static class is used. This is very useful and already used in may applications and even design patterns like "Singleton". But, you need to be careful when using static constructors when the code inside deals with an external source or by any mean is subject to exceptions and errors because once an exception raises inside the static constructor you will have to live with the consequences till the application is reset.

This happened to me once as my application used a static "ConnectionManager" class for my "DAL" and inside the static constructor I wrote some code to get the connection string from an external settings provider (SharePoint list).

This was working fine till by accident someone tampered with the SharePoint list and the static constructor code raised an exception because it couldn't communicate with the list as it should. Then, anytime I try to browse to any page I get a strange error page with an ambiguous stack trace.

So, after some debugging I found the problem and started to think of a way to handle this exception. Sometimes you can do this by just providing a default value to your settings but this time I can't do this because it is a connection string.

So, I found a solution for this problem but before jumping into code let's share some info. The strategy we follow when using the static constructor aims to running some logic only once. The same strategy can be achieved using what we all know as "Singleton" design pattern.

The "Singleton" design pattern itself uses another concept which is known as "Lazy Loading", it is all about running some logic only once. This comes in two flavors, the static constructor and the static property. The .NET framework already makes sure that the logic inside the static constructor runs only once, but on the other hand, the logic inside a static property will run every time the property is called, so this time we have to handle the "only once" part ourselves.

So, let's now see some code.

using System;
using System.Collections.Specialized;
using DevelopmentSimplyPut.CommonUtilities;
using DevelopmentSimplyPut.CommonUtilities.Settings;

namespace DevelopmentSimplyPut.DAL
{
    public static class ConnectionManager
    {
        private static object syncRoot = new Object();

        private static StringDictionary connectionDictionary = new StringDictionary();
        public static StringDictionary ConnectionDictionary
        {
            get
            {
                StringDictionary result = null;

                if (null == connectionDictionary)
                {
                    lock (syncRoot)
                    {
                        if (null == connectionDictionary)
                        {
                            result = new StringDictionary();
                        }
                    }
                }
                
                result = connectionDictionary;
                return result;
            }
            set
            {
                connectionDictionary = value;
            }
        }

  private static string defaultConnectionkey;
        public static string DefaultConnectionkey
        {
            get
            {
                string result = null;

                if (string.IsNullOrEmpty(defaultConnectionkey))
                {
                    lock (syncRoot)
                    {
                        if (string.IsNullOrEmpty(defaultConnectionkey))
                        {
                            try
                            {
                                result = SystemSettingsProvider.GetSettingValue<string>(BusinessSetting.DBConnectionString);
                                ConnectionDictionary.Add("DevelopmentSimplyPutConnectionString", result);
                                DefaultConnectionkey = "DevelopmentSimplyPutConnectionString";
                            }
                            catch (Exception ex)
                            {
                                SystemErrorHandler.HandleError(ex);
                            }
                        }
                    }
                }

                result = defaultConnectionkey;
                return result;
            }
            set
            {
                defaultConnectionkey = value;
            }
        }
    }
}

In the code above you will notice that I am using "lock(){}". This is very important as if your application is multi-threaded then it is probable that more than one thread could be accessing the same static property at the same time and though the logic inside could be applied more than once. So, to avoid this we apply locking so that the first thread reaches the static property will lock and prevent other threads from doing the same till it finishes its work. This is good, but another thing, why do I check if the connection string is equal to null two times one before locking and one after?!!!

What we are asking about now is known as "Double Locking" concept. Since locking is somehow expensive from performance and processing point of view, so we need to try to use it less frequent as much as we can. So, we asked ourselves, when do we need locking??? We need it when we need to prevent other threads from accessing some code. Ok, this is good but don't we already know that all threads will only run the code when the connection string is equal to null and otherwise the static property will return the old connection string? So, the racing between threads will only cause us problems if the connection string is equal to null, otherwise we don't care. So, to save some processing and postpone the locking as much as we can, we first check on the equal to null condition to make sure that we really need to apply locking. So, this justifies the check before the locking. Regarding the check inside the locking, this is required for applying the main lazy loading logic.


That's it. I wish you find this post somehow helpful.

Read More