.Net – Type and Search ListBox

The Type and Search feature in not available out of the box for System.Windows.Forms.CheckedListBox or System.Windows.Forms.ListBox controls in .net. So, I thought to just share a simple trick with which this could be easily managed.

We can take two approaches to achieve this -

1. Either we can go for creating a Custom control for this.
2. And if this feature is not required at a general level, we can implement the functionality at the form/user control level.

Now, without talking much on this, I will take you straight away to the implementation -

The below is the code generated by the designer followed by the code to be written to make the type and search work -

partial class FrmTypeAndSearchListBox

  {

    /// <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.lstBxTypeNSearch = new System.Windows.Forms.ListBox();

      this.SuspendLayout();

      // 

      // lstBxTypeNSearch

      // 

      this.lstBxTypeNSearch.FormattingEnabled = true;

      this.lstBxTypeNSearch.Items.AddRange(new object[] {

            "Aashay",

            "Aashish",

            "Abdul-Azeez",

            "Abdul-Baari",

            "Abdullah",

            "Abeer",

            "Achal",

            "Achalendra",

            "Achintya",

            "Achyut",

            "Acyutaraya",

            "Adalarasu",

            "Adarsh",

            "Agrim",

            "Agriya",

            "Ahmad",

            "Ahsan",

            "Aja",

            "Ajaat",

            "Ajaatshatru",

            "Ajamil",

            "Akhil",

            "Akhilesh",

            "Akmal",

            "Akram",

            "Akroor",

            "Akshan",

            "Aloke",

            "Amal",

            "Amalendu",

            "Angad",

            "Angamuthu",

            "Anil",

            "Arivunambi",

            "Avinash",

            "Azhagar",

            "Azhar",

            "Azzam"});

      this.lstBxTypeNSearch.Location = new System.Drawing.Point(52, 47);

      this.lstBxTypeNSearch.Name = "lstBxTypeNSearch";

      this.lstBxTypeNSearch.Size = new System.Drawing.Size(146, 108);

      this.lstBxTypeNSearch.TabIndex = 14;

      this.lstBxTypeNSearch.KeyDown += new System.Windows.Forms.KeyEventHandler(this.lstBxTypeNSearch_KeyDown);

      // 

      // FrmTypeAndSearchListBox

      // 

      this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);

      this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;

      this.ClientSize = new System.Drawing.Size(292, 266);

      this.Controls.Add(this.lstBxTypeNSearch);

      this.Name = "FrmTypeAndSearchListBox";

      this.Text = "FrmTypeAndSearchListBox";

      this.ResumeLayout(false);

    }

    #endregion

    private System.Windows.Forms.ListBox lstBxTypeNSearch;

  }

public partial class FrmTypeAndSearchListBox : Form

  {

    DateTime lastKeyDownOnListBox; //DateTime of the last KeyDown on the ListBox.

    StringBuilder listBoxFindString; //String to find in the ListBox.

    int resetListBoxFindStringAfter; //Milliseconds after which the FindString should be reset.

    int findStartIndex; //Starting index from where to find from.

    public FrmTypeAndSearchListBox()

    {

      InitializeComponent();

      //Initializing with current DateTime.

      lastKeyDownOnListBox = DateTime.Now;

      //Initialising the FindString with Empty.

      listBoxFindString = new StringBuilder(string.Empty);

      //Setting 250ms as the reset interval.

      resetListBoxFindStringAfter = 250;

      //Setting to -1 to start from the begining.

      findStartIndex = -1;

    }

    private void lstBxTypeNSearch_KeyDown(object sender, KeyEventArgs e)

    {

      //Just incase the same handler is bound to multiple controls

      //inheriting from ListBox class.

      ListBox senderListBox = sender as ListBox;

      if (senderListBox != null)

      {

        switch (e.KeyData.ToString())

        {

          case "Down":

          case "Up":

          case "Right":

          case "Left":

            //Add on the cases for the keys which we do not want to handle.

            break;

          default:

            //Reset the FindString if the gap between two key strokes exceeds the specified limit.

            if (DateTime.Now.Subtract(lastKeyDownOnListBox).Milliseconds > resetListBoxFindStringAfter)

            { listBoxFindString.Clear(); }

            //Set the DateTime of the current key stroke.

            lastKeyDownOnListBox = DateTime.Now;

            //Prepare the Find String.

            listBoxFindString.Append(e.KeyData.ToString());

            //Find the matching index if any.

            int matchedIndex = senderListBox.FindString(listBoxFindString.ToString(),

                                                        findStartIndex);

            //Select the Item if a match is found.

            if (matchedIndex > 0)

            { senderListBox.SelectedIndex = matchedIndex; }

            break;

        } 

      }

    }

  }

The above code is well commented. Still, if I have missed anything please feel free to revert with your comment. Hope, this small trick will prove to be helpful.

SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL

Introduction

Today, I would like to explain one way in which we can store the HIERARCHICAL data in SQL tables. A general table structure which people come up to store this kind of data is -

Where, EmployeeID id the UniqueID alloted to every new employee record inserted into the table and ManagerID is the EmployeeID of the immediate manager of the employee. Keeping in mind that Manager is also an employee.

Problem Statement

This table structure very well serves the purpose as long as we have 1-Level hierarchy. However, if the hierarchy is of n'th level, the SELECT statement to fetch the records becomes more complex with this kind of table structure. Suppose, we want to fetch the complete TREE of a particular employee, i.e. list of all the employees who are directly or indirectly managed by a particular employee. How to do it……..?

Thanks to CTE’s for making the life a bit easier – as using them in a recursive manner, we can get the work done. Please follow this msdn link to see an implementation using recursive CTE.

Suggested Table Structure

Here, I have just included a new column [PATH]. It is of VARCHAR(MAX) type. I have taken it as VARCHAR(MAX) just to make sure the field is long enough to store the complete path. But one can assign appropriate size as per their system’s requirement.

The basic idea of the [path] column is to store the complete hierarchical path of any employee separated by a delimiter as under -

Calculating the new path is very simple. It’s just, {New Path} = {Parent Path} + {Self ID} + {Delimiter}

Now, suppose if I want to fetch all the employees who are directly or indirectly working under EmployeeID = 2, I can use the below tsql -

;WITH CTE AS (

SELECT 1 EmployeeID,NULL ManagerID, '\1\' [Path]

UNION ALL    

SELECT 2 EmployeeID,1 ManagerID, '\1\2\' [Path]

UNION ALL    

SELECT 3 EmployeeID,1 ManagerID, '\1\3\' [Path]

UNION ALL    

SELECT 4 EmployeeID,2 ManagerID, '\1\2\4\' [Path]

UNION ALL    

SELECT 5 EmployeeID,4 ManagerID, '\1\2\4\5\' [Path]

)

SELECT

  *

FROM

  CTE

WHERE

  [Path] LIKE '%\2\%'

We can use a simple logic to even find out the level of the Employee -

SELECT

  *,

  (LEN([Path]) - LEN(REPLACE([Path],'\',''))) - 2 [Level]

FROM

  CTE

WHERE

  [Path] LIKE '%\2\%'

2 is subtracted from the formula as the length of delimiter for Level-0 is 2.

Conclusion

Hope, this simple trick could save a lot of time for the ones who find themselves lost playing with the hierarchical data.

Last Execution Date Time of a Stored Procedure

On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question.

Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. Please note that this view has been introduced from SQL Server 2008.

The important thing to note is that this view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -

USE DBName

GO

SELECT 

  O.name,

  PS.last_execution_time

FROM 

  sys.dm_exec_procedure_stats PS 

INNER JOIN sys.objects O 

  ON O.[object_id] = PS.[object_id] 

P.S. Please replace the DBName with the actual name of the Database.

The above script will return the name of all the cached stored procedure of the current database with their last execution time.

For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)

How to hide Placeholder in SSRS 2008?

Introduction

For the ones who are working with SSRS, Placeholder is not new for them. So, today I will not go into explaining what is Placeholder, How and Where it is used, etc. I will straight away target the issue mentioned and try to explain a possible way to overcome that. However, for the ones who do not know about Placeholder, please visit this MSDN link.

Problem

Let’s first try to understand the problem I am talking about. Please have a look at Diag: 1-

Diag: 1

Here, as you have noticed, 4 different placeholders are used to make up the details for the Attribute column; 2 each for Labels & their respective values. Now, the requirement is such that the Labels & their Values for the Sub Category are to be shown only if the Value exists ELSE even the label should not be shown. This can be easily managed using the below expression for the Label & Value placeholders respectively -

Diag: 2

  Diag: 3

But the main issue here is the BLANK SPACE CREATED refer Diag: 1. There is not direct property available to Hide the Placeholder. Hence, the need arises to find out some work around.

Solution

The expected output is -

Diag: 4

To get the expected output, the main credit goes to the below Placeholder property -

Diag: 5

We just need to select HTML as the Markup type and then set the Value expression as under -

Diag: 6

And yes, we are done !

Conclusion

This was the solution I could manage to find out. There might exist other solutions as well. Please revert back if you are aware of any such solution by adding a comment below.