Thursday, December 20, 2012

@Html.ActionLink to call action in different controller

@Html.ActionLink has many overloads (at least 17), and so sometimes it becomes confusing if the operation is not so straight-foward. 

For example, if you want to execute an Action of different controller, you must take care to use it like following:

@Html.ActionLink("Link Text", "MyAction", "MyController", new {id = item.ID}, null)

Its important to pass the last parameter (which is, "hostname"),
eventhough if you pass it as "null". Doing this will take up proper overload and will call the action from different controller.

Friday, December 7, 2012

Investigations:Could not load file or assembly or one of its dependencies.An attempt was made to load a program with an incorrect format.

ERROR: Could not load file or assembly or one of its dependencies.An attempt was made to load a program with an incorrect format. 

Developers generally face this issue in production server, or running a web application under IIS.
The site works fine in the integrated development environment, but fails and giving this error while running it in IIS.
The most likely reason of this issue is the incompatibility of target platforms. 
Say, your  application is targettng to only x32, but the dependent assembly targets to "Any CPU", or vice versa.

In order to fix this issue, you need to change settings of relevant Application Pool in IIS, to allow 32-bit applications.

This should resolve the issue.

Tuesday, November 27, 2012

Investigations: Could not load type 'System.Web.Razor.Parser.[TypeName]' from assembly 'System.Web.Razor, Version='

I recently faced this issue while working on my ASP.NET MVC 4 project over .NET Framework 4.5.

System was throwing following error whenever I make a call to any member of RazorEngine.Razor class.
Like Razor.Parse, etc.
In order to investigate the issue, I checked at various places in my project and GAC, and finally I could see following been placed in web.config file of  my project. It seems it is incorrectly creating the binding redirect for System.Web.Razor.

        <assemblyIdentity name="System.Web.Razor" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="" newVersion="" />

Remove this section from web.config file, and the issue should be resolved now.

Wednesday, November 21, 2012

Investigations: How to identify current path in referenced library in MVC application?

Following is the exact scenario in my application:

1) MVC application is referencing one DLL (a Class library project).
2) some code in the class library need to access folders and files from the MVC application.
Say, there is one folder named as "Templates" in root of my MVC application.
How should I able to get the path of that folder in context of execution path from any method of the class library.
If I try "~\templates" or "\templates", it looks for folder in C:\Program Files\... instead of the websites folder.
If I try "templates", it looks for folder in C:\ only.
any idea of how to achieve this

Tuesday, November 13, 2012

Microsoft.Commom.targets Warning# 983 The reference assemblies for Framework ".NET Framework, Version=X.x" were not found

Following is the exact error message:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.Common.targets (983): The reference assemblies for framework ".NETFramework,Version=v4.5" were not found. To resolve this, install the SDK or Targeting Pack for this framework version or retarget your application to a version of the framework for which you have the SDK or Targeting Pack installed. Note that assemblies will be resolved from the Global Assembly Cache (GAC) and will be used in place of reference assemblies. Therefore your assembly may not be correctly targeted for the framework you intend.

This occurs when your Build server does not have Visual Studio 2012 (which ideally should not be anyway), and it has only .NET Framework 4.5, and no Framework 4.5 SDK or .NET Framework Framework Targetting-Pack is not installed.

In order to get rid of these warning messages, you have two options:
1) Install SDK or Targetting-Pack for .NET Framework 4.5. But they are considerably large in size.
2) The other option is to copy following folder from your development machine to your TFS build machine.

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5

Doing this should fix the issue, and the warning messages will be disappeared.

Microsoft.Common.targets Warning/ Error: 1578 There was a mismatch between the processor architecture of the project being built...

We get this sort of errors most probably while using MS Build to deploy project from a development environment to production environment by implementing Continuous Integration Build, etc approach.

The exact error message would look like:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.Common.targets (1578): There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "<<Assembly Name>>", "AMD64". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project 

As such if you intend to deploy your application over x86 platform, then this warning message is just a warning, nothing else, and will not harm execution of your application in any way.

However, perform following steps in order to get rid of this warning:

1) Open project file (.csproj) using some text editor (like, Notepad, Notepad++, etc)
2) Add following in <PropertyGroup> section of the project file.


3) Save changes done to .csproj file.
4) Do the same for all project files within your solution.

Now try build using MS Build, and you will not get those warning messages.

Monday, October 22, 2012

FIX: IE Document Mode changes to IE 7 while opening a remotely hosted ASP.NET (or MVC) website in Internet Explorer 8

A recent issue I faced while I deployed my ASP.NET MVC application on production server.

I hosted my MVC application on a web server having IIS 7.
While I tried to access that website on the server itself using "localhost" in URL within IE 8 browser, it was getting displayed perfectly. But when I tried to access it remotely in IE 8 browser, the layout got disturbed, and it was appearing too messy.

I did a lot of investigations, and could found this is because by default browser was opening the website under "IE 7" document mode.

In order to prevent this, I did following changes <system.webServer> section in web.config file of my application, and that successfully resolved this issue:

        <clear />
        <add name="X-UA-Compatible" value="IE=edge" />

HTML tags to set page layout according to device/ page size and resolutions

Use following HTML tags in <head> section in order to make your web page appearing same on different screen resolutions on different devices. This will resize webpage, and all underlying controls according to the device size and resolution. This is very useful when you aim designing your website targeting range of devices like, Mobile screens, tablet screens, and traditional desktop or laptop screens.

    <meta name="HandheldFriendly" content="true" />
    <meta name="viewport" content="width=device-width" />

Friday, October 5, 2012

Convert string into a date, Add days to a date, Format date in different date format in JQuery

This article aims to explain writing a Javascript method (using JQuery functions) to achieve following:
- Convert a string into a date
- Add days to the date
- Format date into a valid date format

- JQuery library (For example, jquery-1.5.1.min.js, etc)

Source code:
<script type="text/javascript">
function ConvertToDate(strDate, dayPartIdx, monthPartIdx, yearPartIdx) {
//Pass dayPartIdx, monthPartIdx, yearPartIdx parameters depending upon the date format in strDate
//If its dd/mm/yy - pass 0,1,2 respectively.
        var day = strDate.split("/")[dayPartIdx],
        month = strDate.split("/")[monthPartIdx],
        year = strDate.split("/")[yearPartIdx];
        month = month - 1; //Date function considers 0 as January
        var convertedDate = new Date(year, month, day,0,0,0,0);
        return convertedDate;

    function AddDaysToDate(strDate, numDays) {
        var dtDate = ConvertToDate(strDate, 0, 1, 2);
        var ms = dtDate.getTime() + (86400000 * numDays);
        return $.datepicker.formatDate('dd/mm/yy', new Date(ms));

Sample call:
alert(AddDaysToDate('20/09/2012', 30));

P.S. - It is assumed that the passed string "strDate" is a valid date. However, you can add more validations to validate it before attempting to convert into a date.

Thursday, October 4, 2012

Issue Troubleshooting: "HttpPost action of a controller gets hit twice" while using @Html.RenderAction and JQuery

Following is the exact scenario in my application:

And this is an attempt to get assistance from various developers across in order to fix this issue.

I believe the issue is self-descriptive from the image above itself. Still if you have any question regarding understanding the issue, please feel free to post the same.
Thanks for visiting, and for your help


Having posted in StackOverflow and ASP.NET Forums, I got responses that helped me fixing the issue:

Following is the revised JQuery method, that worked:

            $('form').submit(function (event) {
                    url: 'EditEntryInline',
                    type: 'POST',
                    data: $(this).serialize(),
                    success: function (result) {
                        if (result.success) {                       
                        } else {
                            alert('Entry Could not be Saved');

The main issue was that, the event.preventDefault() was missing. 
Also, the suggestions were of not to wireup event for in button click event, though keeping above method inside $('.buttonInlineSave').click(function () { .... }) was also working pretty well.

Hope it helps someone.

Sunday, September 30, 2012

Multiple Checkboxes in MVC 3, and Post selection to Controller Action

This article aims to explain - how to provide a multiple selection checkboxes in MVC 3 application, and the way to fetch the selection in an HttpPost contrller action.

A UI should have checkbox for each weekday (Mon to Sun), and when user submits the form, an HttpPost action should be able to fetch selected Weekdays by user.

We will create an Editor Template for this.
Please note this example is in C#.Net and Razor view, but you should easily be able to convert it in VB.Net and ASPX page if your application demands.

Step - 1: Create a Model (i.e. Type)

namespace MyMVC.Models
    public class WeekDaySelectionModel
        public string WeekdDayName { get; set; }
        public bool Selected { get; set; }

Step - 2: Create a collection of weekday names in your HttpGet action calling your view:

using System.Collections.Generic;
using MyMVC.Models;
public ActionResult EditEntry()
            List<WeekDaySelectionModel> _weekDaySelection = new List<WeekDaySelectionModel>();
            _weekDaySelection.Add(new WeekDaySelectionModel { WeekdDayName = "Mon"});
            _weekDaySelection.Add(new WeekDaySelectionModel { WeekdDayName = "Tue"});
            _weekDaySelection.Add(new WeekDaySelectionModel { WeekdDayName = "Wed"});
            _weekDaySelection.Add(new WeekDaySelectionModel { WeekdDayName = "Thu"});
            _weekDaySelection.Add(new WeekDaySelectionModel { WeekdDayName = "Fri"});
            _weekDaySelection.Add(new WeekDaySelectionModel { WeekdDayName = "Sat"});
            _weekDaySelection.Add(new WeekDaySelectionModel { WeekdDayName = "Sun"});

return View("EditEntry", _weekDaySelection);              

Step - 3: Create an Editor Template in "..\Views\Shared\EditorTemplates" folder.

File name: WeekDaySelectionModel.cshtml (Make sure the file name is matching with the type name we created in Step 1).

@model MyMVC.Models.WeekDaySelectionModel
    @Html.HiddenFor(m => m.WeekdDayName)
    @Html.CheckBoxFor(m => m.Selected)
    @Html.LabelFor(m => m.Selected, Model.WeekdDayName)

Step - 4: Integrate this editor in your view.

File Name: EditEntry.cshtml

@model System.Collections.Generic.List<MyMVC.Models.WeekDaySelectionModel>
@for (int i = 0; i <= Model.SelectedWeekDays.Count - 1; i++)
    @Html.EditorFor(m => m.SelectedWeekDays[i])

Step - 5: Catching the selection in HttpPost controller action.

When user submits the pages, an HttpPost controller action will automatically receive the model having "Selected" property updated against each weekday name.

        public ActionResult EditEntry(System.Collections.Generic.List<WeekDaySelectionModel> _weekDayNames)
            string selectedWeekDayNames = String.Empty;
            for (int i = 0; i <= _weekDayNames.Count - 1; i++)
                if (_weekDayNames[i].Selected == true)
                    if (String.IsNullOrEmpty(selectedWeekDayNames) == false)
                        selectedWeekDayNames += ",";
                    selectedWeekDayNames += _weekDayNames[i].WeekdDayName;

Wednesday, September 12, 2012

Test email functionality in ASP.NET application using "Test Mail Server Tool""

At times we need to test the email functionality in our projects, during development and testing phases, and for that we prefer actual email sending does not take place. 

There are many tools available over internet, I prefer using "Test Mail Server" utility.

You can download it from here.

This article explains the steps to test the email functionality using this utility.

Following is the code extract and steps to check if Test Mail Server tool is working or not.

1) Copy following code in a code-behind (.cs) of your ASPX page in you website application.

System.Net.Mail.SmtpClient _smtpClient = new System.Net.Mail.SmtpClient("localhost", 25);
System.Net.Mail.MailMessage _sampleMail = new System.Net.Mail.MailMessage("", "", "Test email - This is working", "This is a test email to check if Send Test Mail utility is working!");

Also make sure, you configure SMTP settings in web.config file, rather than hard-coding it in compiled code, and by doing that we can avoid supplying "localhost" and port number in source code. Its not done in this walkthrough  just to minimize the steps.

2) Make sure you have "Test Mail Server Tool" running on your machine.
3) Also, confirm the port number, and mail storage directory:
(Right-click on running instance of this utility in system tray, and click "Options")

If you want to change the port number, make sure you also change it in the code. (See, the yellow highlighted in Step 1)
4) Run the website, and execute send test mail code.
P.S. Sometimes it takes a while (say, 3-4 minutes) to generate a test email, save and open it. but it will always happen in background by this utility. If this happens, you can still close the browser after email send code is executed, and email will be shown to you once generated.

Saturday, September 8, 2012

Disable Caching for a Controller Action in ASP.NET MVC

At times, we need to disable the caching for some controller actions in our MVC application.
Say for example, there is an "Add Entry" link on your view, and it has a controller action assigned to it.
Clicking on this "Add Entry" link should call an action, and that will eventually load an Entry fill-up screen/ dialog.
Now when user clicks it for the first time it will call the controller action fine. But for next consecutive clicks, it may or may not call controller action and due to which the target dialog/ entry screen will remain prefilled with old data.
This is because of caching. Under some circumstances, browser caches controller actions, and that is why the cached operation is performed rather than the controller action.

To deal with this problem, we need to disable caching of those controller actions. (Its always recommended to disable caching of controller actions that invoke Add/ Edit of entry).

Following is the walkthrough to achieve the same:

1. Create an action filter. To do that create a new class inherited  ActionFilterAttribute class.

class NoCache : ActionFilterAttribute
public override void OnResultExecuting(ResultExecutingContext filterContext)


2. Decorate your controller action with the action filter class.

public ActionResult EditEntry(int entryId)
..... the controller action code.}

This is what we need to do to disable caching of controller action in ASP.NET  MVC.

Monday, June 4, 2012

XHTML 1.0 Transitional "element is not supported XHTML"

While working with MVC 3 or MVC 4 application, some of your HTML tags may be showing you a document validation warning message saying "Element section is not supported", etc.

First of all, be clear that this validation errors has nothing to do with how the page will be displayed or application will be executed. They will still be shown and executed  You may be even seeing them when you just create a new ASP.NET MVC 3/ MVC 4 project using built-in template, and without writing even single line of code.

This is actually because, they are all HTML 5 tags, and your IDE is not set to use HTML 5 standards.

In order to get rid of those validation warnings, you need to set your IDE to use HTML 5 standards for document validation.
In order to do that -

Make sure your document (_Layout) page is using following: (this is default in case of MVC 3 and MVC 4)

<!DOCTYPE html>

Once done, set your IDE to use HTML 5 standards by going through Tools >> Options menu, and below screen:

 Change the target to "HTML 5" and click OK, and you will see those validation warnings are disappeared.

Create Unique Index with IGNORE_DUP_KEY ON/ OFF on SQL Server column

Unique index/ constraint in SQL Server is used to enforce uniqueness over an SQL Column. That is, if a particular column(s) is defined with UNIQUE constraint/ index, user cannot insert duplicate values in that column.

We can have only one NULL value for that column. means, not more than one row can contain NULL value in that column.

While creating a UNIQUE index, we also can specify how to handle duplicate violation. I.e. whether to throw an error, or silently ignore adding a row having a value getting duplicated for that column.

Try following example -

create table #Countries (Id int, Name varchar(20))
ALTER TABLE #Countries
insert into #Countries (Id, Name)
select 1, 'India'
select 10, NULL
select 11, NULL
select 2, 'Australia'
select 3, 'India'
select * from #Countries

drop table #Countries

This query inserts only 3 rows in the table

10    NULL
2    Australia
1    India

But it does not throw any error, because we have configured to ignore duplicate rows.

In the same query if you set IGNORE_DUP_KEY =OFF, it will give you following message:

Violation of UNIQUE KEY constraint 'IX_Countries'. Cannot insert duplicate key in object 'dbo.#Countries'.

Wednesday, May 30, 2012

HTML Helper for Action image in MVC (that is, img tag with anchor tag within)

This article aims to explain how can we create and use HTML helper method for IMG html tag, to display an image, and to call a controller action while user clicks over it.


Step 1: Create new folder in your MVC structure.

Step 2: Add a new class file "MyHelper.cs" in that folder, and create a "static" class within:
namespace MVC.Infrastructure
    public static class MyHelpers

Step 3: Import System, and System.Web.Mvc namespaces in that class.
using System;
using System.Web.Mvc;

Step 4: Write following helper method in MyHelper.cs class.

public static MvcHtmlString ActionImage(this HtmlHelper html, string imagePath, string alt, string cssClass,
            string action, string controllerName,object routeValues)
            var currentUrl = new UrlHelper(html.ViewContext.RequestContext);
            var imgTagBuilder = new TagBuilder("img"); // build the <img> tag
            imgTagBuilder.MergeAttribute("src", currentUrl.Content(imagePath));
            imgTagBuilder.MergeAttribute("alt", alt);
            imgTagBuilder.MergeAttribute("class", cssClass);
            string imgHtml = imgTagBuilder.ToString(TagRenderMode.SelfClosing);
            var anchorTagBuilder = new TagBuilder("a"); // build the <a> tag
            anchorTagBuilder.MergeAttribute("href", currentUrl.Action(action, controllerName, routeValues));
            anchorTagBuilder.InnerHtml = imgHtml; // include the <img> tag inside
            string anchorHtml = anchorTagBuilder.ToString(TagRenderMode.Normal);
            return MvcHtmlString.Create(anchorHtml);

Step 5: Open your razor/ aspx view. Import namespace of above class in your view. (This example deals with razor syntax, but it will be easy for you to convert into aspx, if you are using aspx engine).

@using MVC.Infrastructure

Step 6: Use the created HTML helper to show Image in view, and to call controller action when user clicks it.

@Html.ActionImage("../../Images/logo.png", "Site Logo", "siteLogo", "About", "Home", null)
Here, "Home" is a controller name, and "About" is an action, and it does not have any routeValues so passed as null.

This is all we need to do.
Following is the HTML generated by using this HTML helper:
<a href="/Home/About"><img alt="Site Logo" class="siteLogo" src="../../Images/logo.png" /></a>

You can take advantage of this HTML helper in any view within your MVC application and it will generate HTML image and anchor tags every time for you!

Monday, May 21, 2012

Remove HTML tags from a text string in ASP.NET using RegularExpression

This article aims to explain a very simple method to remove HTML tags from a text using RegularExpressions.

This will remove all HTML tags, or character references (like, &nbsp, &amp) from a text, and will return plain text.

    public static string RemoveHtmlTags(string htmlText, bool preserveNewLine)
        System.Web.UI.HtmlControls.HtmlGenericControl divNew = new System.Web.UI.HtmlControls.HtmlGenericControl("div");
        divNew.InnerHtml = htmlText;
        if (preserveNewLine)
            divNew.InnerHtml = divNew.InnerHtml.Replace("<br>", "\n");
            divNew.InnerHtml = divNew.InnerHtml.Replace("<br/>", "\n");
            divNew.InnerHtml = divNew.InnerHtml.Replace("<br />", "\n");
        return System.Text.RegularExpressions.Regex.Replace(divNew.InnerText, "<[^>]*>", "");

if there is a requirement to preserve new line, then we need to convert HTML line breaks into new line character (which is "\n" in C#).

Example output using above method:
Input: &nbsp;Take one <strong>notebook</strong>, pen, <u>pencil</u> and <em>eraser</em> with you.
Result:  Take one notebook, pen, pencil and eraser with you.
(In above example, &nbsp; is replaced by a space character in the beginning)

Saturday, May 19, 2012

JavaScript Injection: Dealing with Java Scripts supplied through QueryString in URL while requesting for an ASP.NET or MVC webpage.

QueryStrings are very common in websites.
But they are very much prone to JavaScript injection. And if they are not handled properly, they can easily temper your webpage/ data.

For example, you have a label in your webpage, and you are setting it's text property according to a particular QueryString supplied (say, querystring UserName).

Now, if a user requests following URL in a browser:
http://myWebsite/myPage.aspx?UserName=Sample<script type="text/Javascript">alert('a')</script>

In this case, if you have not taken handled QueryString properly in your page, it will execute this javascript. Though, alert is a harmless script, but a user can really pass some dangerous javascripts and they will get executed.

Following is what we need to avoid this -

1. If your page has ValidateRequest attribute is set to True (which is a default value), it will anyway not allow such querystrings to be processed. Instead it will show user Browser's error page indicating potentially dangerous request.

2. But if you do not want to display Browser's error page (Browser's error page does not give good impression to user) or if you absolutely had to set ValidateRequest to False according to your requirements, then you need to do an HTML encoding of supplied querystring.

lblQueryString.Text = HttpUtility.HtmlEncode(Request.QueryString["UserName"].ToString());

This will treat the querystring as plain text (no execution of any script at all)!

Also, if you are using .NET v4.0, it will show you browser's error page even if you have "ValidateRequest" to False in your page. To avoid that, add following line under "<system.web>" section within your web.config file.

<httpRuntime requestValidationMode="2.0"/>

Friday, May 18, 2012

Execute client-side code/ validation on RadioButton/ CheckBox before executing their server-side CheckedChanged (Click) event.

We very much know if we want to perform client-side validations before the server-side Click event of button, we need to write our javascript in “onclientclick” event, and if it returns False, the server-side Click event will not be executed. As easy as it sounds!
But it’s not that straight-forward for controls like RadioButton and Checkbox.

Our article will take example of RadioButton, and will show you how to achieve this.

Consider a webpage showing user following options:

Now, if a user clicks either “Movie”, or “Dinner Out” option, it should show fire some server-side code directly.
But, if user clicks on “Touring Europe” option, it should first ask for confirmation from user and if user agrees, then only should fire server-side code written in “CheckedChanged” event.
Just like –

At first it may look that the job is only to add “onclick” attribute and to call a javascript method for that. But it requires a little more than that.
If you see HTML source of page at this point, it will show there is already a javascript method which is assigned to execute on click.

Now, if you simply add your own JavaScript code for “onclick” method, your code will be executed before existing code of click method (i.e. before setTimeout(..__doPostBack)… ). And if you have “return” statement in your code (to return True/ False), the PostBack will not be hit, and your “CheckedChanged” event will never be called.

If you say, why this behavior is different from “onclientclick” of button control? It’s simply because Button is a submit/ postback control, and so it does not rely on javascript. So you will never see javascript __doPostBack call in its HTML source

To overcome this, you will need to do the postback from your javascript method
Following is a step-by-step instruction to achieve the same in our example. (You can easily correlate it with your requirements)

            STEP 1
            Assign javascript method to “onclick” attribute of radiobutton in Page_Load.
if (!Page.IsPostBack)
            rbOptEuropeTour.Attributes.Add("onclick", "return checkBudgetForEuropeTour('" + rbOpt3.ClientID + "')");

checkBudgetForEuropeTour is a javascript method which we will write in next step.
Here, rpOpt3 is the radio button for “Touring Europe” option. Here, client id of this control is passed to help the code in identifying which control has caused postback, and using that we will call a corresponding server-side event. (Explained in later steps).

      Write javascript method in your aspx page, or script file (.js) according to your preferences.
function checkBudgetForEuropeTour(rbOptClientId) {
        if (confirm('You do not have enough budget for Europe Tour. Do you still want to go with this option?') == true) {
            __doPostBack(rbOptClientId, 'CheckedChanged');           
        else {
            return false;

We are explicitly doing postback from the javascript method (using __doPostBack).

      Last step, is to identify which control has caused the postback, and to call server-side event accordingly.
It should be written ideally in Page_Load event.
if (Page.IsPostBack)
            if (Request["__EVENTTARGET"].Contains("rbOptEuropeTour") == true && Request["__EVENTARGUMENT"] == "CheckedChanged")
                rbOptEuropeTour_CheckedChanged(sender, e);

“rbOptEuropeTour_CheckedChanged” is the server-side OnCheckedChanged event for radio button “rbOptEuropeTour”.

That’s all we need to do!

Tuesday, May 15, 2012

Find pattern-matched duplicate rows within a group using PARTITION BY in SQL Server.

This article aims to explain how to find duplicate rows within a group matching patterns.

Following is the structure and data of “tblPeople” table:


Our SQL should have input parameters for ForeNames, LastName, and CityName to define search pattern for duplicate items search.
Parameters - @foreNames, @lastName, @cityName
Our objective is as follow:
1.       If none of the above parameters are supplied, find all persons having same ForeNames and LastName within same City.
Expected result:

2.       If @cityName is only supplied, then find all persons having same ForeNames within particular City.
Expected result: (input >> @cityName = ‘%Vad%’)

3.       If @foreNames is supplied (with/ without @cityName supplied), but @lastName is not supplied.
In that case, consider all persons matching @foreNames within same city as duplicates, and return them.
Expected result: (input >> @foreNames=’%Bh%’)

4.       If @lastName is supplied (with /without @cityName supplied), but @foreNames is not supplied.
In that case, consider all persons matching @lastName within same city as duplicates, and return them.
Expected result: (input >> @lastName=’%Sh%’)

5.       If @foreNames and @lastName are supplied (with/without @cityName supplied).
In that case, consider all persons matching @foreNames, and @lastName within same city as duplicates, and return them.
Expected result: (input >> @foreNames=’%A%’, @lastName=’%S%’, @cityName=’%Ahm%’)

First of all, we cannot use GROUP BY here, because we need also need ID, and other details of the rows which are duplicated. GROUP BY simply cannot allow selecting those columns which are not used in grouping or aggregate.

To achieve this, we need to partition the rows in groups of “City”, “Forenames”, and “LastName”. Here, while partitioning the rows we also need to consider pattern-matching for ForeNames and LastName.

This can be done using PARTITION BY in SELECT query, and within single T-SQL statement only.

Following is the T-SQL serving the purpose:

(SELECT ID, City, ForeNames, LastName,
CASE WHEN @foreNames <> '' THEN @foreNames
CASE WHEN @lastName <> '' THEN '' ELSE ForeNames END
CASE WHEN @lastName <> ''  THEN @lastName
CASE WHEN @foreNames <> '' THEN ''
CASE WHEN @cityName <> '' THEN ''
ELSE LastName
END) as MAXCount
FROM tblPeople
WHERE ForeNames LIKE  
CASE WHEN @foreNames <> '' THEN @foreNames ELSE '%' END
AND LastName LIKE CASE WHEN @lastName <> '' THEN @lastName ELSE '%' END
AND City LIKE CASE WHEN @cityName <> '' THEN @cityName ELSE '%' END


Here, I have used Common Table Expression (but you can use temporary table to store the result of SELECT statement) before further narrowing down the resultset to exclude non-duplicates.

The point of interest in above T-SQL is how the field “MAXCount” gets generated.
COUNT(*) – indicates the total number of rows in each partition. This is what ultimately will be stored in “MAXCount” field.

Now, each partition is built using following values:
City >> Because we need to find duplicates within the same city always, so this column is an obvious choice in partition.

ForeNames >> Partition on this column depends upon @foreNames parameter.
·         If it is not supplied, and neither @lastName is supplied it will create partition by grouping rows having exact ForeNames.
·         If it is not supplied, but @lastName is supplied, it will ignore ForeNames column in partitioning.
·         If supplied, it will create partition by grouping all matching rows in a single partition.

LastName >> Partition on this column depends upon @lastName parameter.
·         If it is not supplied, and not even @foreNames, and @cityName, then it will create partition by grouping rows having same LastName.
·         If it is not supplied, but either @foreNames, or @cityName is supplied, it will ignore LastName column in partitioning.
·         If supplied, it will create partition by grouping all matching rows in single partition.

Blog Archive