MySQL: Condition based sorting


AscDescIntrioduction:

Sometimes we need to sort a result set based on some conditions. Let’s take few real time scenarios and than try to resolve these with the help of CASE WHEN.

Scenario-1:

Sometimes we need an element to be placed at bottom of the list. For example, a Dropdownlist containing educational qualifications may contain  an item “Other”. While showing all the educational qualifications from database we may need this particular element (i.e, “Other”) to be moved to the bottom of the list. If we try to sort this list using usual ORDER BY keyword then we may not get the desired sequence.

To get the desired result we need to use CASE WHEN keyword along with the ORDER BY. Let’s illustrate this with an example-

Simple ORDER BY :


SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY Qualification

OUTPUT:

5

 

 

 

 

ORDER BY with CASE WHEN :


SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY CASE WHEN Qualification='Other' THEN 'ZZZZZ' ELSE Qualification END

OUTPUT:

6

 

 

 

 

 

EXPLAINATION:

Here we have set the value of item "Other" as "ZZZZZ" while the sorting is being done. Generally "ZZZZZ" will be the last item in any real world list and hence will move to the last.

Scenario 2:

In some other cases we may need the desired item to be moved to top of the result set. An example for this situation can be, a Dropdownlist containing items for Book Category. The list may have an item with text "General" , which is meant for those books whose category is not clearly known. Let's assume that most of the books are supposed to be of this category. Then we have to place this item in the first index of the Dropdownlist so that it will be easier for user to pick the item. Now, we need a little modification in the ORDER BY clause as follows.


ORDER BY CASE WHEN Qualification='General' THEN NULL ELSE Qualification END

 

Please share your feedback !

Thanks

 

You may also like :

Creating a simple stored procedure in MySQL

CSS: Under-utilized content property


css

#Batman {
background-color: black;
clear: both;
visibility: hidden;
content: “I’m Batman”; }  /* @CSSHumor */

Introduction:

In this post we’ll discuss about

  • (The Theory) use and importance of CSS property – content
  • (The Magic) creating printable links using CSS content and pseudo-elements

If you are not interested in ‘The Theory’ which is available in almost all CSS books and web tutorials like w3schools.com, you can start with ‘The Magic’, the feature which motivated me to write this blog post.

CSS ‘content’ Property:

The primary use of CSS is to describe the presentation semantics of an HTML documents but, we can also use CSS for content generation. With content property we can inject a piece of information to an existing document. We have to use content property along with pseudo-elements like :before, :after etc.

Example:

I feel, the best use of content property is to inject the URL of a hyperlink next to it’s text content.

a:after
{
   content: "(" attr(href) ")";
}

This will inject the URL mentioned within the href tag next to each link and will be decorated with a pair of parenthesis. For detailed list of content property values you may refer w3Schools.com.

Create Printable Links:

Before we start let’s have a look on the below paragraph of an HTML page-

“If you want to start learning web technologies then there is no better place than W3School to start with. For Microsoft technologies you can get comprehensive help from MSDN. My suggestion is to follow blogs like Technology Talks, Daily Dot Net Tips, Dot Net Tricks, Kunal Chowdhury, SQL Authority etc. ”

The HTML code for this is

printable_link_1

Note that this HTML snippet has certain links to some other places. Now let someone wants to print the page containing the above paragraph. He/she will get the print copy as the above paragraph but

  • What about the links?
  • How can the user get the links for Technology Talks, Daily Dot Net Tips, Dot Net Tricks, Kunal Chowdhary, SQL Authority etc. from the print copies?
  • Is there any way that we can show links on the print copy but not in the web page?

Solution

The easiest solution is to use content property of CSS along with pseudo-element :after. And as we want the URL to be injected next to it's corresponding link only in the print page and not in the page that is rendered to the browser, we need to mention media type as 'print'. So code view of the final document may look like

<style media="print">
    a:after {
      content: "(" attr(href) ")";
    }
</style>
<div>If you want to start learning web technologies then there is no better place than
 <a href="http://www.w3schools.com/">W3School</a> to start with.For Microsoft technologies
 you can get comprehensive help from <a href="http://msdn.microsoft.com/en-US/">MSDN</a>.
 My suggestion is to follow blogs like <a href="http://suvendugiri.wordpress.com/">
 Technology Talks</a>, <a href="http://dailydotnettips.com/">Daily Dot Net Tips</a>,
 <a href="http://www.abhisheksur.com/">Dot Net Tricks</a>, <a href="http://www.kunal-chowdhury.com/">
 Kunal Chowdhury</a>, <a href="http://blog.sqlauthority.com/">SQL Authority</a> etc.</div>

Live Demo

You can view the live demo here. To spot the difference just view the print preview.

Conclusion

Now, we are able to do the magic with which we can create better web pages with links for both kind of users i.e, online users and offline users (with printed material).

Hopefully, you have enjoyed reading this post.
I'll love to read a feedback from you.
Thanks !

JQuery: Synchronize two ASP.Net text boxes


jquery1

“The central concept behind jQuery is – find something, do something. More specifically, select DOM element(s) from an HTML document and then do something with them using jQuery methods. This is the big picture concept.” ___________________________Cody Lindley(jQuery Succinctly)

Introduction:

As the title suggests, this post is about interacting with two ASP.Net text boxes. If any change/interaction happens to one of the text boxes then we’ll try to do the same to the other text box, programmatically.

Live Demo & Code

Live demo here
Download code hereDownload

Designing the UI:

Let’s start with a new WebForm. The UI in this case requires only two multiline text boxes separated by some white spaces. To keep things simple we’ll concentrate on ‘how it works’ rather than ‘how it looks’. Now, the above quotation indicates that we need to find something and do something and thus it is a two step process. Here the ‘something’ is the text boxes and we can find them by their ids, class, tag, type etc. Particularly for this example we’ll employ the find by class trick. So, we need to mention same CssClass for both the text boxes and thing to notice is- this class may not exists at all.

<div>
     <asp:TextBox runat="server" ID="TextBox1" CssClass="sync" TextMode="MultiLine" Height="100px" />&nbsp;&nbsp;
     <asp:TextBox runat="server" ID="TextBox2" CssClass="sync" TextMode="MultiLine" Height="100px" />
</div>

Writing the Code:

In the script part, we’ll code for two things-

  • Synchronizing the text typed in to one text box with the other
  • Synchronizing the scrolling in both the text boxes

So, we will write two functions for these two specific actions. The first operation can be done on keyup event and the second with scroll event.

<script>
        $(document).ready(function () {
            $(".sync").keyup(function () {
                $(".sync").val($(this).val());
            });
            $(".sync").scroll(function () {
                $(".sync").scrollTop($(this).scrollTop());
            });
        });
</script>

Result:

As I already have said, we can demonstrate two operations

  • Typing anything in to one of the text box will result in displaying same text in the other text box, simultaneously.
  • Type few lines of code so that the scroll bar is visible. Now when we scroll any of the text boxes using either keyboard or mouse, other one will be scrolled automatically.

Explaination:

We have embedded our code inside $(document).ready() function as this function is executed after the DOM is loaded and before the page contents are rendered in to the browser. We can define a common function for both the textboxes with the use of CssClass name. The first action is defined inside the keyup event so that when we release the key the action will be performed. The action here is nothing but copying the content of the current text box to the other. The val() with no argument returns the value of the text box and when some argument is passed to it, sets the argument as value of that text box. I hope this made the thing very clear and further explanation is not required. Now consider about the second action. The scroll() event is fired when we try to scroll the content of text box either through key board or mouse. Again, scrollTop() with zero argument returns the current scroll position but, sets the scroll positions to a value same as the argument if called with an argument.

Conclusion:

Now we are able to develop a page with two text boxes which are synchronized upon typing text in to them or using the scroll bar. We can use more than two text boxes and it will still work as long as we use a common CssClass for them.

I hope you have enjoyed reading this post. Your feedback is very important to me.

SQL Server: Convert Text to Upper or Lower Case


ConvertUpperCase

This small post is all about converting the text typed in New Query window in SSMS (SQL Server Management Studio)  to upper and lower case. There is nothing much to write except a keyboard shortcut but, I feel this feature deserves a separate post. This is particularly useful when you are formatting an existing SQL query, Stored Procedure, View, Trigger etc. which is not well formatted earlier.

Here are the shortcuts:
Select the text and

Press CTRL + Shift + U to convert to Upper Case
and
Press CTRL + Shift + L to convert to Lower Case

Hope this helps.
Thanks for reading.

JQuery: Consuming Page Method in ASP.Net


jquery1

Introduction: Page Method is an easy way to communicate with the code behind and JQuery functions for simple operations. Although it can be done using web services or WCF but, for simple operations there is no need to complicate things. This post is a simple demo of how we can consume page methods in ASP.Net using AJAX. Note: This example produces summation of two inputted integers as result and I know in real life this logic is not going to help you but, as I already said my intention is to show only a way to consume page methods with JQuey using AJAX so, you can definitely replace your logic here.

Demo:
1. Place two Textbox(ID: txtNum1, txtNum2) and a Button (ID: btnSum) in the page.

<div>Enter two integers and click <strong>SUM</strong> button to get the result
<div style="padding: 5px;">First Number</div>
<div style="padding: 5px;">Second Number</div>
</div>

2. Create a page method (SumIntegers()) in code behind with two integer parameters named num1 and num2. Don't forget to assign [WebMethod()] attribute to it for which you need to add namespace System.Web.Services and to call the page method directly make it static.

using System;
using System.Web.Services;

public partial class WebMethodDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e){}

    [WebMethod()]
    public static int SumIntegers(int num1, int num2)
    {
        return num1 + num2;
    }
}

3. Now the last thing to do is to write the JQuery section where we will be able to call the ajax method to get the desired result.

<script type="text/javascript">// <![CDATA[
        $(document).ready(function () {
            $("#btnSum").click(function (e) {

                var num1 = $("#txtNum1").val();
                var num2 = $("#txtNum2").val();
                $.ajax({
                    type: "POST",
                    url: window.location.href + "/SumIntegers",
                    data: '{"num1":"' + num1 + '","num2":"' + num2 + '"}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (msg) {
                        alert(num1+"+"+num2+"="+msg.d);
                    },
                    error: function () {
                        alert("Error occured. Please check inputs !");
                    }
                });
            });
        });

// ]]></script>

Explanation: As you may know, $(document).ready() function gets executed as soon as DOM is loaded and before the page contents are rendered in to the browser. We need to embed all the JQuery markup inside this function. Define an event handler for click event of button 'btnSum'. Rest of the logic is written inside this event handler as we want to execute our logic on clicking the button. First retrieve the values from the textboxes. Now to perform the asynchronous HTTP(ajax) request, call the .ajax() method of JQuery with the url and necessary settings. If the ajax call succeeds then we can get the returned result using msg.d as the result will always be enclosed within the 'd' (since ASP.Net 3.5, which prevents direct execution of the string as script).

Downloads: Demo Project Download

I hope this is useful.

Thanks for reading and please don't forget to add a comment.

Book: Visual Studio 2012 and .NET 4.5 Expert Development Cookbook


book
Book Title
: Visual Studio 2012 and .Net 4.5 Expert Development Cookbook
Author: Abhishek Sur (www.abhisheksur.com)
About Author: Abhishek Sur is a Microsoft MVP in Client App Dev since 2011. He is an architect in the .NET platform.  He is also a renowned public speaker and owns one of the most active  Microsoft user group named Kolkata Geeks.  His website abhisheksur.com guides both budding and experienced developers to understand the details of languages and latest technology. You may also follow him at Facebook or Twitter .

The very first thing I like about this book is its conciseness. Even though it is a 380 pages book you will find every aspect of the enhancement & new features introduced with Visual Studio 2012 and .Net 4.5 has been  explained extensively with proper examples. Starting with the Visual Studio IDE features, author marches us through the enhancement to ASP.Net, WPF etc. It has a dedicated chapter on memory management too.


I always go for the shortcuts while developing applications and also love to use features provided by my IDE for faster development. This book is a good educator of  IDE features and shortcuts introduced in Visual Studio 2012 which will help you in saving development time.
The key component of the book is, everything comes with a proper example. With this book, one can also learn -

  • In-depth memory management helps to to grasp under the hood happenings.
  • Asynchronous programming has become very important in the last few years and the book explains all about the necessity and usage of Asynchronous Programming in .Net
  • Enhancement to ASP.Net includes support for HTML5, Web Socket, JQuery and been explained beautifully.
  • Enhancement to WPF
  • Building Windows 8 style applications and communication & sharing between apps and devices.

In case you want to check it out before buy, you can download Chapter-1: Introduction to Visual Studio IDE features for free.

You can buy it from packtpub.com or amazon.com

Honestly speaking, I have been immensely benefited from C# internal series by the author on his blog DOT NET TRICKS and this book further helped me to discover the roots.
It is not conclusive by any means and there is a much more to grab. At last, if you are a .Net developer and a technology loving person then you’ll definitely love to add this book to your bookshelf.

Windows 8/8.1: Bring back IE 10/11 to Metro Style mode


IE

After using Windows 8.1 preview for few days I noticed that my Internet Explorer is not opening in metro style mode even though I am clicking on the tile placed on my start screen which was behaving normal, just before few days. If you are facing the same problem then this post will be really helpful to you.Although I mentioned about Windows 8.1 which means I have IE 11 on my PC but, this works also for IE 10 on Windows 8 PCs.

First of all this is not a bug or issue, in fact this is its normal behavior. Now, you may ask why is this happening? OK, answer is quite simple but confusing. Setting a browser as default other than Internet Explorer led to this situation. Now this statement made half of the answer to question “How to bring back IE 10/11 to metro style mode ?” Let’s see detailed steps to do this.
Step-1:
Open Internet Explorer and click on menu ‘Tools’ (if you are unable to view the menu bar, press ‘Alt’ key from your keyboard or you can use shortcut ‘Alt+T’ ) and then click ”Internet Options’. A window pops out with different tabs.

Step-2:ie102

Go to ‘Programs’ tab. Click on ‘Make Internet Explorer the default browser’ as shown in the figure.

Step-3: ie104You’ll be presented with ‘Set Default Programs’ window containing list of programs installed on your PC. Choose ‘Internet Explorer’ from the list and click on ‘Set this program as default’. Finally click ‘OK’.

That’s it !

Thanks for reading.

Update (24 July 2013): Corrected incorrect information that was present in the post. I deeply regret for the same.