Happy Coding

11 Jul 2013

Create ASP.NET Server Controls from Scratch

Introduction

ASP.NET comes with its own set of server-side controls, so why create our own?

By creating our own controls, we can then build powerful, reusable visual components for our Web application’s user interface.

This tutorial will introduce you to the process of ASP.NET server control development. You’ll also see how creating your own controls can simultaneously improve the quality of your Web applications, make you more productive and improve your user interfaces.
ASP.NET custom controls are more flexible than user controls. We can create a custom control that inherits from another server-side control and then extend that control. We can also share a custom control among projects. Typically, we will create our custom control in a web custom control library that is compiled separately from our web application. As a result, we can add that library to any project in order to use our custom control in that project.

HTML5 Video Overview

Until now, there has never been a native way to display video on a web page. Today, most videos are shown, via the use of a plugin (like Flash or Silverlight). However, not all browsers have the same plugins. HTML5 specifies a standard, native way to include video, with the video element.
Currently, there are two widely supported video formats for the video element: Ogg files [encoded with Theora and Vorbis for video and audio respectively] and MPEG 4 files [encoded with H.264 and AAC].
To show a video in HTML5, this is all we need:

<video width="320" height="240" controls="controls">
<source src="movie.ogg" type="video/ogg" />
<source src="movie.mp4" type="video/mp4" />
</video>

The controls attribute is for adding play, pause and volume controls. Without this attribute, your video would appear to be only an image. It is also always a good idea to include both the width and height attributes. The following table shows all attributes of the <video> element:

autoplay: Specifies that the video will start playing as soon as it is ready
controls: Specifies that controls will be displayed, such as a play button
height: The height of the video player
loop: Specifies that the media file will start over again, every time it is finished
preload: Specifies that the video will be loaded at page load, and ready to run. Ignored if “autoplay” is present
src: The URL of the video to play
width: The width of the video player
poster: The URL of the image to show while no video data is available

Step 0: Getting Started

All that is required to get started is a copy of Visual Studio of Visual Web Developer Express. If you don't have the full version of Visual Studio, you can grab the free Express Edition.
The HTML5 video player that we will create here is only a simple video player that will render whatever native interface the browser provides. Browsers that support HTML5 video have video players built in, including a set of controls (play/pause etc.), so you will see a different interface for each browser when running this control.

The HTML5 video player in Firefox 3.6.8.

Step 1: Creating a Custom Control Project

First, we need to create a new class library project to hold our custom controls. By creating the custom control in a separate class library, we can compile the project into a separate DLL and use the custom control in any application that requires it.
Open your ASP.NET project with Visual Studio or Visual Web Developer. In Solution Explorer, right click the solution name, and select Add New Project from the context menu. In the Add New Project dialog box, choose the project type to be a Web project, and select ASP.NET Server Control as the template, like so:

Name the project CustomControls. Click OK. The new ASP.NET Server Control project is created, and Visual Studio also provides you with a simple Web control to start with. Delete this custom control because we don't need it.

Step 2: Adding a Web Custom Control to the Project

In Solution Explorer, right click the CustomControls project, and select Add New Item from the context menu. In the Add New Item dialog box, choose the category type to be a Web category, and select ASP.NET Server Control in the templates.

Name the new custom control VideoPlayer. Click Add. The new custom control (VideoPlayer.cs) is created and added to the CustomControls project.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CustomControls
{
[DefaultProperty("Text")]
[ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]
public class VideoPlayer : WebControl
{
[Bindable(true)]
[Category("Appearance")]
[DefaultValue("")]
[Localizable(true)]
public string Text
{
get
{
String s = (String)ViewState["Text"];
return ((s == null) ? "[" + this.ID + "]" : s);
}
set
{
ViewState["Text"] = value;
}
}
protected override void RenderContents(HtmlTextWriter output)
{
output.Write(Text);
}
}
}

The code above is the default code generated by Visual Studio for a web control library. To start working with VideoPlayer.cs, we need to modify the code above. The first thing that we should do is delete everything between the class declaration line and the end of the class. That leaves us with this code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CustomControls
{
[DefaultProperty("Text")]
[ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]
public class VideoPlayer : WebControl
{
}
}

As you see above, the VideoPlayer class derives from the System.Web.UI.WebControl class. In fact, all ASP.NET server-side controls derive from the WebControl class.

Step 3: Modifying the Class Declaration Line

The class declaration line in the default code also specifies the default property for the VideoPlayer control as the Text property. The VideoPlayer control that we create here doesn't have a property called Text. So, delete the reference to Text as the default property. After all the modifications, the VideoPlayer.cs code file should look like this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CustomControls
{
[ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]
public class VideoPlayer : WebControl
{
}
}

Step 4: Adding Properties

In this step, we will add some properties to the VideoPlayer control to handle the control's behaviour. The following is the list of properties that we will add to the VideoPlayer.cs code file:

VideoUrl: A string property which specifies the URL of the video to play.
PosterUrl: A string property which specifies the address of an image file to show while no video data is available.
AutoPlay: A boolean property to specify whether the video should automatically start playing or not, when the webpage is opened.
DisplayControlButtons: A boolean property that specifies whether the player navigation buttons are displayed or not.
Loop: A boolean property that specifies whether the video will start over again or not, every time it is finished.

Add the following code to the VideoPlayer class:

private string _Mp4Url;
public string Mp4Url
{
get { return _Mp4Url; }
set { _Mp4Url = value; }
}
private string _OggUrl = null;
public string OggUrl
{
get { return _OggUrl; }
set { _OggUrl = value; }
}
private string _Poster = null;
public string PosterUrl
{
get { return _Poster; }
set { _Poster = value; }
}
private bool _AutoPlay = false;
public bool AutoPlay
{
get { return _AutoPlay; }
set { _AutoPlay = value; }
}
private bool _Controls = true;
public bool DisplayControlButtons
{
get { return _Controls; }
set { _Controls = value; }
}
private bool _Loop = false;
public bool Loop
{
get { return _Loop; }
set { _Loop = value; }
}

After we have added the above properties, the VideoPlayer class should look like

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CustomControls
{
[ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]
public class VideoPlayer : WebControl
{
private string _Mp4Url;
public string Mp4Url
{
get { return _Mp4Url; }
set { _Mp4Url = value; }
}
private string _OggUrl = null;
public string OggUrl
{
get { return _OggUrl; }
set { _OggUrl = value; }
}
private string _Poster = null;
public string PosterUrl
{
get { return _Poster; }
set { _Poster = value; }
}
private bool _AutoPlay = false;
public bool AutoPlay
{
get { return _AutoPlay; }
set { _AutoPlay = value; }
}
private bool _Controls = true;
public bool DisplayControlButtons
{
get { return _Controls; }
set { _Controls = value; }
}
private bool _Loop = false;
public bool Loop
{
get { return _Loop; }
set { _Loop = value; }
}
}
}

Step 5: Creating the RenderContents Method

The primary job of a server control is to render some type of markup language to the HTTP output stream, which is returned to and displayed by the client. It is our responsibility as the control developer to tell the server control what markup to render. The overridden RenderContents method is the primary location where we tell the control what we want to render to the client.
Add the following override RenderContents method to the VideoPlayer class:

protected override void RenderContents(HtmlTextWriter output)
{
}

Notice that the RenderContents method has one method parameter called output. This parameter is an HtmlTextWriter object, which is what the control uses to render HTML to the client. The HtmlTextwriter class has a number of methods you can use to render your HTML, including AddAttribute and RenderBeginTag.

Step 6: Adding Tag Attributes

Before we write the code to render the <video> element, the first thing to do is add some attributes for it. We can use the AddAttribute method of the HtmlTextWriter object to add attributes for HTML tags.
Append the following code into the RenderContents method:

output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);
output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());
output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());
if (DisplayControlButtons == true)
{
output.AddAttribute("controls", "controls");
}
if (PosterUrl != null)
{
output.AddAttribute("poster", PosterUrl);
}
if (AutoPlay == true)
{
output.AddAttribute("autoplay", "autoplay");
}
if (Loop == true)
{
output.AddAttribute("loop", "loop");
}

You can see that, by using the AddAttribute method, we have added seven attributes to the tag. Also notice that we are using an enumeration, HtmlTextWriterAttribute, to select the attribute we want to add to the tag.
After we have added the code above, the RenderContents method should look like so:

protected override void RenderContents(HtmlTextWriter output)
{
output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);
output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());
output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());
if (DisplayControlButtons == true)
{
output.AddAttribute("controls", "controls");
}
if (PosterUrl != null)
{
output.AddAttribute("poster", PosterUrl);
}
if (AutoPlay == true)
{
output.AddAttribute("autoplay", "autoplay");
}
if (Loop == true)
{
output.AddAttribute("loop", "loop");
}
}

Step 7: Rendering the <video> Element

After adding some tag attributes for the video element, it's time to render the <video> tag with its attributes onto the HTML document. Add the following code into the RenderContents method:

output.RenderBeginTag("video");
if (OggUrl != null)
{
output.AddAttribute("src", OggUrl);
output.AddAttribute("type", "video/ogg");
output.RenderBeginTag("source");
output.RenderEndTag();
}
if (Mp4Url != null)
{
output.AddAttribute("src", Mp4Url);
output.AddAttribute("type", "video/mp4");
output.RenderBeginTag("source");
output.RenderEndTag();
}
output.RenderEndTag();

We use the RenderBeginTag method of output object to render the opening tag of the video element, and RenderEndTag to render its closing tag. We also added the <source> element between the <video> element. The video element allows multiple source elements. Source elements can link to different video files. The browser will use the first recognized format.
The RenderContents method should look like this after we have added the code above

protected override void RenderContents(HtmlTextWriter output)
{
output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);
output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());
output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());
if (DisplayControlButtons == true)
{
output.AddAttribute("controls", "controls");
}
if (PosterUrl != null)
{
output.AddAttribute("poster", PosterUrl);
}
if (AutoPlay == true)
{
output.AddAttribute("autoplay", "autoplay");
}
if (Loop == true)
{
output.AddAttribute("loop", "loop");
}
output.RenderBeginTag("video");
if (OggUrl != null)
{
output.AddAttribute("src", OggUrl);
output.AddAttribute("type", "video/ogg");
output.RenderBeginTag("source");
output.RenderEndTag();
}
if (Mp4Url != null)
{
output.AddAttribute("src", Mp4Url);
output.AddAttribute("type", "video/mp4");
output.RenderBeginTag("source");
output.RenderEndTag();
}
output.RenderEndTag();
}

Notice that the order in which we place the AddAttributes methods is important. We place the AddAttributes methods directly before the RenderBeginTag method in the code. The AddAttributes method associates the attributes with the next HTML tag that is rendered by the RenderBeginTag method, in this case the video tag.

Step 8: Removing the Span Tag

By default, ASP.NET will surround the control tag with a <span> element when rendering the control's HTML markup. If we have provided an ID value for our control, then the Span tag will also, by default, render an ID attribute. Having the tags can sometimes be problematic, so if we want to prevent this in ASP.NET, we can simply override the Render method and call the RenderContents method directly. Here's how to do that:

protected override void Render(HtmlTextWriter writer)
{
this.RenderContents(writer);
}

After we have added the code above, the VideoPlayer class should look like this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CustomControls
{
[ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]
public class VideoPlayer : WebControl
{
private string _Mp4Url;
public string Mp4Url
{
get { return _Mp4Url; }
set { _Mp4Url = value; }
}
private string _OggUrl = null;
public string OggUrl
{
get { return _OggUrl; }
set { _OggUrl = value; }
}
private string _Poster = null;
public string PosterUrl
{
get { return _Poster; }
set { _Poster = value; }
}
private bool _AutoPlay = false;
public bool AutoPlay
{
get { return _AutoPlay; }
set { _AutoPlay = value; }
}
private bool _Controls = true;
public bool DisplayControlButtons
{
get { return _Controls; }
set { _Controls = value; }
}
private bool _Loop = false;
public bool Loop
{
get { return _Loop; }
set { _Loop = value; }
}
protected override void RenderContents(HtmlTextWriter output)
{
output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);
output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());
output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());
if (DisplayControlButtons == true)
{
output.AddAttribute("controls", "controls");
}
if (PosterUrl != null)
{
output.AddAttribute("poster", PosterUrl);
}
if (AutoPlay == true)
{
output.AddAttribute("autoplay", "autoplay");
}
if (Loop == true)
{
output.AddAttribute("loop", "loop");
}
output.RenderBeginTag("video");
if (OggUrl != null)
{
output.AddAttribute("src", OggUrl);
output.AddAttribute("type", "video/ogg");
output.RenderBeginTag("source");
output.RenderEndTag();
}
if (Mp4Url != null)
{
output.AddAttribute("src", Mp4Url);
output.AddAttribute("type", "video/mp4");
output.RenderBeginTag("source");
output.RenderEndTag();
}
output.RenderEndTag();
}
protected override void Render(HtmlTextWriter writer)
{
this.RenderContents(writer);
}
}
}

Our control is now finished! All we have left to do is build the project before we use it on a ASP.NET web page.

Step 9: Building the Project

It's time to build the project. Select Build, and then click Build Solution from the main menu.

After building the project, the next step is to add the VideoPlayer control into the Toolbox Explorer.

Step 10: Adding VideoPlayer Control to the Visual Studio Toolbox

To add the VideoPlayer control to the Toolbox, right click in the Toolbox Explorer
Choose Items from the context menu
Click the Browse button in the Choose Toolbox Items dialog box
Navigate to the ASP.NET project directory
Go to the CustomControls directory
Open the Bin\Debug directory (Visual Studio builds debug versions by default.)
Select the CustomControls.DLL assembly and click on the Open button

VideoPlayer will appear in the Choose Toolbox Items dialog box as shown in the image above. The check box will show it as selected. As soon as you click the OK button in the Choose Toolbox Items dialog box, the new VideoPlayer control will appear in the toolbox.

Step 11: Placing the VideoPlayer Control on ASP.NET Web Page

To see how the control works, we need to give it a home. Add a new page to the website. Right click the ASP.NET project from the Solution Explorer. Select Add New Item, and add a Web Form. Name the Web Form VideoPlayerTest.aspx.
To place the control on the page, switch to Design mode. Drag the VideoPlayer control from the Toolbox and drop it onto the VideoPlayerTest.aspx design view.
The following Listing shows how the control is declared on the page:

<cc1:VideoPlayer ID="VideoPlayer1" runat="server" Mp4Url="videos/movie.mp4" OggUrl="videos/movie.ogg" Width="400" Height="300" />

The following line of code is what Visual Studio added to the ASPX file to accommodate the control. You can see it by selecting the Source tab from the bottom of the code window in Visual Studio. The Register directive tells the ASP.NET runtime where to find the custom control (which assembly) and maps it to a tag prefix.

<%@ Register assembly="CustomControls" namespace="CustomControls" tagprefix="cc1" %>

We can now test the control.

VideoPlayer control running on Google Chrome.

Summary

In this tutorial, you learned how to create your own ASP.NET custom server control from scratch. You now know every step of the process – from how to create a web custom control library project, how to add properties to a custom control, how to render the HTML markup of the control to the client, and, finally, how to use the ASP.NET custom control in a web form.
Hopefully, you now have the skills to create custom controls that have all the functionality of the standard ASP.NET server-side controls. Thank you so much for reading!

8 Jul 2013

Remove rows having swapped column values in SQL Server

I have a table like

+ ----------------------- +
| RowID | FromCol | toCol |
+ ----------------------- +
| 1     | a       | b     |
| 2     | b       | c     |
| 3     | c       | d     |
| 4     | c       | b     |
| 5     | b       | a     |
+ ----------------------- +

I would like to remove the rows that has FromCol --> ToCol same value as ToCol --> FromCol For eg. RowID 1 is a-->b and RowID 5 has b-->a so rowID 5 should be removed. Similarly RowID 4 should be removed because it has a swapped value like RowID 2.
My expected result Table is:

+ ----------------------- +
| RowID | FromCol | toCol |
+ ----------------------- +
| 1     | a       | b     |
| 2     | b       | c     |
| 3     | c       | d     |
+ ----------------------- +

Above can be achieved by the below query

select t1.*
FROM dbo.MyTest t1
WHERE (SELECT COUNT(t2.rowid) 
         FROM dbo.MyTest t2 
        WHERE t2.toCol= t1.fromCol
          AND t2.fromCol= t1.toCol
          AND t1.rowid> t2.rowid) = 0

4 Jun 2013

Add line break within tooltips

Just use the entity code  for a line break in a title attribute.

---------------------------------------------------------------------------------------

Well if you are using Jquery Tooltip utility, then in "jquery-ui.js" Javascript file find following text:

tooltip.find(".ui-tooltip-content").html(content);

and put above that

content=content.replace(/\&lt;/g,'<').replace(/\&gt;/g,'>');

I hope this will also work for you.

---------------------------------------------------------------------------------------

<br /> did work if you are using qTip

---------------------------------------------------------------------------------------

it is possible to add linebreaks within native HTML tooltips by simply having the title attribute spread over mutliple lines.

However, I'd recommend using a jQuery tooltip plugin such as Q-Tip:http://craigsworks.com/projects/qtip/.

It is simple to set up and use. Alternatively there are a lot of free javascript tooltip plugins around too.

edit: correction on first statement.

29 May 2013

Cross-domain JSONP with jQuery call step-by-step guide

I’ve been banging my head all day to accomplish this, it’s like a puzzle.. but since i get it to work i thought i could write this post for you and myself.

What we want to accomplish?

Simple way to communicate cross-domain with ASMX .NET 3.5 Web Service

How can we do it?

1. Implement a web service method like the following

   [ScriptService]
   public class JSONP_EndPoint : System.Web.Services.WebService
   {
       [WebMethod]
       [ScriptMethod(UseHttpGet = true,ResponseFormat = ResponseFormat.Json)]
       public string Sum(string x,string y)
       {
           return x + y;
       }
   }

2. Add New class library with a name ContentTypeHttpModule

The reason for this is no matter how you specify the content-type of your ajax call ASP.NET send the request with Content-Type text/xml; charset=utf-8 this is security feature explained here by ScottGu

3. Add the following code to your Class (Code by Jason i just did a simple modification)

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

namespace ContentTypeHttpModule
{
    public class ContentTypeHttpModule : IHttpModule
    {
        private const string JSON_CONTENT_TYPE = "application/json; charset=utf-8";

        #region IHttpModule Members
        public void Dispose()
        {
        }

        public void Init(HttpApplication app)
        {
            app.BeginRequest += OnBeginRequest;
            app.ReleaseRequestState += OnReleaseRequestState;
        }
        #endregion

        public void OnBeginRequest(object sender, EventArgs e)
        {
            HttpApplication app = (HttpApplication)sender;
            HttpRequest resquest = app.Request;
            if (!resquest.Url.AbsolutePath.Contains("JSONP-EndPoint.asmx")) return;

            if (string.IsNullOrEmpty(app.Context.Request.ContentType))
            {
                app.Context.Request.ContentType = JSON_CONTENT_TYPE;
            }
        }

        public void OnReleaseRequestState(object sender, EventArgs e)
        {
            HttpApplication app = (HttpApplication)sender;
            HttpResponse response = app.Response;
            if (app.Context.Request.ContentType != JSON_CONTENT_TYPE) return;

            response.Filter = new JsonResponseFilter(response.Filter);
        }
    }

    public class JsonResponseFilter : Stream
    {
        private readonly Stream _responseStream;
        private long _position;

        public JsonResponseFilter(Stream responseStream)
        {
            _responseStream = responseStream;
        }

        public override bool CanRead { get { return true; } }

        public override bool CanSeek { get { return true; } }

        public override bool CanWrite { get { return true; } }

        public override long Length { get { return 0; } }

        public override long Position { get { return _position; } set { _position = value; } }

        public override void Write(byte[] buffer, int offset, int count)
        {
            string strBuffer = Encoding.UTF8.GetString(buffer, offset, count);
            strBuffer = AppendJsonpCallback(strBuffer, HttpContext.Current.Request);
            byte[] data = Encoding.UTF8.GetBytes(strBuffer);
            _responseStream.Write(data, 0, data.Length);
        }

        private string AppendJsonpCallback(string strBuffer, HttpRequest request)
        {
            return request.Params["callback"] +"(" + strBuffer + ");";
        }

        public override void Close()
        {
            _responseStream.Close();
        }

        public override void Flush()
        {
            _responseStream.Flush();
        }

        public override long Seek(long offset, SeekOrigin origin)
        {
            return _responseStream.Seek(offset, origin);
        }

        public override void SetLength(long length)
        {
            _responseStream.SetLength(length);
        }

        public override int Read(byte[] buffer, int offset, int count)
        {
            return _responseStream.Read(buffer, offset, count);
        }
    }
}

4. Register the HttpModule in the service project

4.1 Add referance to the HttpModule assembly to the service project
4.2 Add this code to web.config to register the module
<add name="ContentTypeHttpModule"
type="ContentTypeHttpModule.ContentTypeHttpModule, ContentTypeHttpModule" />
This goes under system.web / httpmodules section

5. Add a web project for testing the application

5.1 add the following libs
jquery-1.3.1.js
json2.js
5.2 add new script file caller.js

function test() {
    $.ajax({ url: "http://localhost:1690/JSONP-EndPoint.asmx/Sum",
    data: { x: JSON.stringify("Now i am getting jsop string"), y: JSON.stringify("2nd param") },
        dataType: "jsonp",
        success: function(json) {
            alert(json.d);
        },
        error: function() {
            alert("Hit error fn!");
        }
    });
}

5.3 Add referances to jquery-1.3.1.js and json2.js

5.4 Add Default.aspx page with input button that has onclick=”return test();”

6. Remarks

6.1 I use the JSON.stringify function to serialize the string data parameters.
6.2 .d is a security features on ASP.NET 3.5

Download the code

25 May 2013

Report Viewer Control missing Header Icons

Hi,

In IIS 7, we need to make sure we configurate the ReportViewer handler.

You can follow these steps:

Open Internet Information Services (IIS) Manager and select your Web application.
Under IIS area, double-click on Handler Mappings icon.
At the Action pane on your right, click on Add Managed Handler.
At the Add Managed Handler dialog, enter the following:
Request path: Reserved.ReportViewerWebControl.axd
Type: Microsoft.Reporting.WebForms.HttpHandler
Name: Reserved-ReportViewerWebControl-axd
Click OK.

For more information, see http://otkfounder.blogspot.com/2007/11/solving-reportviewer-rendering-issue-on.html

21 May 2013

ROW_NUMBER(), RANK(), and DENSE_RANK() – Flexibility at a Price

One of the most handy features introduced in SQL 2005 were the ranking functions; ROW_NUMBER(), RANK(), and DENSE_RANK(). For anyone who hasn’t been introduced to these syntactic gems, here’s a quick rundown (for those of you who are very familiar with these functions already, feel free to read through, or skip right down to “There’s No Such Thing as a Free Ride” below).
OK – so the general syntax for any one of these commands is more or less the same:

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>) RANK() OVER ([<partition_by_clause>] <order_by_clause>) DENSE_RANK() OVER ([<partition_by_clause>] <order_by_clause>)

So the PARTITION BY part is optional, but everything else is required. An example of a non partitioned, and then a partitioned ROW_NUMBER() clause are listed below:

ROW_NUMBER() OVER (ORDER BY TotalDue DESC) ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC)

The difference between the three functions is best explained using an example. Here’s the data I’m using for this example, in case you want to follow the bouncing ball at home ;-)

CREATE TABLE OrderRanking

   (

   OrderID INT IDENTITY(1,1) NOT NULL,

   CustomerID INT,

   OrderTotal decimal(15,2)

   )

   INSERT OrderRanking (CustomerID, OrderTotal)
SELECT 1, 1000
UNION 
SELECT 1, 500
UNION 
SELECT 1, 650
UNION 
SELECT 1, 3000
UNION 
SELECT 2, 1000
UNION 
SELECT 2, 2000
UNION 
SELECT 2, 500
UNION 
SELECT 2, 500
UNION 
SELECT 3, 500

I’ll use the following (admittedly ugly) query to demonstrate the difference between each function:

SELECT  *,

        ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS RN,

        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RNP,

        RANK() OVER (ORDER BY OrderTotal DESC) AS R,

        RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS RP,

        DENSE_RANK() OVER (ORDER BY OrderTotal DESC) AS DR,

        DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderTotal DESC) AS DRP
FROM    OrderRanking
ORDER BY OrderTotal DESC

Excuse the terrible aliases. Anything longer and the code snippets and output in this blog entry get really, really ugly. When we run the query, this is what we get:

So from the example above, we can see that:

ROW_NUMBER() assigns sequential numbers to each partition in a result set (an unpartitioned result set simply has a single partition), based upon the order of the results as specified in the ORDER BY clause. If you look carefully, you’ll see that the values in column RN are based upon a simple sort of TotalDue, while the values in Column RNP (Row_Number partitioned) are first partitioned or “grouped” by CustomerID, and then numbered by TotalDue, with the row number resetting on change of customer.
Contrary to popular belief, RANK() does not sort rows based upon how bad they smell. RANK() does much the same thing as ROW_NUMBER(), only it acknowledges ties in the columns specified in the ORDER BY clause, and assigns them the same rank. Where a tie occurs (as was the case for orders 6/3, and 1/5/8), the numbers that would otherwise have been “used up” are skipped, and numbering resumes at the next available number. As you can see, RANK() leaves a gap whenever there is a tie.
DENSE_RANK() doesn’t like gaps. It’s more of an Abercrombie & Fitch kind of function (ba-dum-ching!). Ohhhhh…that was terrible. My sense of humour may give me up for lent. You might follow it. Anyway….DENSE_RANK() “fills in the gaps”. It starts from the next number after a tie occurs, so instead of 1, 2, 3, 3, 5 you get 1, 2, 3, 3, 4.

There’s No Such Thing as a Free Ride
Ranking functions are not only useful for simple ranking – they’re also great for solving complex problems. In fact, once you get to know them, you’ll find that you’re using them for waaaaaay more than just ranking. In anything from splitting strings to deleting duplicates, ranking functions are the cat’s meow.
But just like most things in ~~life~~ SQL Server, less is more, when you can get away with it. For example, let’s say that you need to get the top order (by TotalDue) for each Customer in AdventureWorks (AdventureWorks2008 in my examples below). You can definitely use ROW_NUMBER (or any of the other ranking functions, for that matter) to do this:

SELECT soh.*
FROM   (SELECT CustomerID, SalesOrderID, TotalDue, 
               ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC) AS RowNumber

       FROM    Sales.SalesOrderHeader) AS soh
WHERE  soh.RowNumber = 1

The WHERE soh.RowNumber = 1 restricts our results to the top order for each customer. Lovely. And the really beautiful thing about this is, if you need the top 2 orders for each customer, or 3, or 4, or x, all you need to do is replace the = 1 with <=2 (for example), and you’re good to go. Now, with that in mind, let’s look at this query:

SELECT soh.CustomerID, soh.TotalDue
FROM   Sales.SalesOrderHeader soh
JOIN   (SELECT     CustomerID, MAX(TotalDue) AS MaxTotalDue

       FROM        Sales.SalesOrderHeader

       GROUP BY CustomerID) AS ttls   ON soh.CustomerID = ttls.CustomerID

                                       AND soh.TotalDue = ttls.MaxTotalDue

If you plug this bad boy in, and run it, you might be surprised by the outcome. It’s actually about half as expensive as the Row_Number solution – but why? Well, as you may or may not know, sorts can be very, very expensive in SQL Server. If we’re only fetching the highest $ sales order for a given customer, the MAX solution does it without a sort, whereas the ROW_NUMBER solution needs to sort (the ORDER BY clause is mandatory, remember).
But there are some caveats to the MAX solution – most notably, how in the world can we get the top 5 orders for each customer? Well…the short answer is, we can’t. We need to change the query up, and in doing so, we’re once again going to incur a sort. Once we get beyond a query that the MAX or MIN tricks can satisfy – for instance, if we need to fetch the top 5 orders for each customer, we may as well take advantage of the ease of coding, and the improved readability of the Row_Number solution. If we want a solution for the “top 5” problem without invoking a ranking function, we’re going to end up with something like this:

SELECT soh.CustomerID, soh.TotalDue
FROM   Sales.SalesOrderHeader soh
WHERE  soh.SalesOrderID IN  
       (SELECT     TOP 5 SalesOrderID

       FROM        Sales.SalesOrderHeader soh2

       WHERE       soh2.CustomerID = soh.CustomerID

       ORDER BY TotalDue DESC)

Which in this case is a very, very crappy alternative to a ranking function. Not only is it uglier, but the query plan isn’t nearly as efficient, and the execution times were consistently about 20% longer in my tests.
Now that said, my tests are against a single data set only, and based upon the nature of your data, your mileage may vary. As a general rule, I would use aggregate functions if I’m only looking for the highest or lowest data point in a series, and a ranking function for anything that can’t be solved by simple aggregation.

4 May 2013

How to get relationship between two tables in SQL Server 2008

SELECT f.name AS ForeignKey
,SCHEMA_NAME(f.SCHEMA_ID) SchemaName
,Object_name(f.parent_object_id) AS TableName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
,SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName
,Object_name (f.referenced_object_id) AS ReferenceTableName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE Object_name(f.parent_object_id) IN ( 'MTAMODE', 'MTACLASS' )