Mediclaim

Form A
Form B
Vidal Information for your Mediclaim


speedfan
cpu-z
perfmonitor
hwmonitor
openhardwaremonitor


apc-surge-protector
speedfan
anatomy-of-surge-suppressors

Castro

Varagu
Varagu
Varagu

Valaipoo vadai
Valaipoo vadai

Medhu vadai
Medhu vadai

health
health
health


WCF

Sending arrays to SQL Server: Xml vs. Comma Separated Values

sending-arrays-to-sql-server-xml-vs-comma-separated-values

Check or Uncheck all CheckBoxes in an ASP.NET GridView using jQuery

var $headerCheckBox = $('.headerSelectAll input[type="checkbox"]');
        var $childCheckBox = $('.childSelect input[type="checkbox"]');
        $($headerCheckBox).change(function () {
            $childCheckBox.each(function () {
                this.checked = $headerCheckBox[0].checked;
            })
        });

        $($childCheckBox).change(function () {
            // if any of the checkbox is unchecked
            // check all checkbox should be cleared
            if (!$(this).is(':checked')) {
                $headerCheckBox.removeAttr('checked');
            }
            else {
                // if all of the checkbox is checked
                // check all checkbox should be checked
                if ($childCheckBox.length == $childCheckBox.filter(':checked').length) {
                    $headerCheckBox[0].checked = true;
                }
            }
        });

Filter and Search ASP.Net DropDownList items using JavaScript

The post is originally from http://www.aspsnippets.com/Articles/Filter-and-Search-ASP.Net-DropDownList-items-using-JavaScript.aspx
have update to object oriented.
We just have to create object for each filter, and alter the function FilterItemsByText

//http://www.aspsnippets.com/Articles/Filter-and-Search-ASP.Net-DropDownList-items-using-JavaScript.aspx
//https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/String/startsWith
function CacheItems(ddl, lblMessage) {
    this.ddlText = new Array();
    this.ddlValue = new Array();
    this.ddlTempItems = ddl;
    this.lblMesg = lblMessage;
    this.hasDefault = false;
    this.DefaultOption = new Option("", "");

    if (typeof this.ddlTempItems === 'undefined' || typeof this.ddlTempItems.options === 'undefined' || this.ddlTempItems.options.length == 0)
        return;

    //http://jsperf.com/jquery-each-push-vs-filter-map/2
    for (var i = 0; i < this.ddlTempItems.options.length; i++) {
        this.ddlText[this.ddlText.length] = this.ddlTempItems.options[i].text;
        this.ddlValue[this.ddlValue.length] = this.ddlTempItems.options[i].value;
    }
}

CacheItems.prototype.AddItems = function (text, value, index) {
    var options = new Option(text, value);

    if (typeof this.ddlTempItems === 'undefined'
        || typeof this.ddlTempItems.options === 'undefined'
        )
        return;

    if (this.hasDefault && this.DefaultOption.value != "" && !this.HasOption(this.DefaultOption.value)) {
        this.ddlTempItems.options.add(this.DefaultOption);
    }

    if (index === -1) {
        this.ddlTempItems.options.add(options);
        return;
    }

    if (index === 0 && this.ddlTempItems.options[0].text.toLowerCase() != text.toLowerCase()) {
        this.ddlTempItems.options.add(options, this.ddlTempItems[0]);
    }
}

CacheItems.prototype.RemoveItems = function (text, value) {
    var options = new Option(text, value);

    if (typeof this.ddlTempItems === 'undefined'
        || typeof this.ddlTempItems.options === 'undefined'
        )
        return;

    if (this.ddlTempItems.options[0].text.toLowerCase() == text.toLowerCase())
        this.ddlTempItems[0] = null;
}

CacheItems.prototype.HasOption = function (value) {
    for (var i = 0, len = this.ddlTempItems.options.length; i != len; ++i) {
        if (this.ddlTempItems.options[i].value == value) {
            return true;
        }
    }
    return false;
}

function FilterItems(value, objCacheItem) {

    AddProtoTypes();

    objCacheItem.ddlTempItems.options.length = 0;//Clear the dropdown

    for (var i = 0; i < objCacheItem.ddlText.length; i++) {
        //if (objCacheItem.ddlText[i].toLowerCase().indexOf(value) != -1) {//Contains 
        if (objCacheItem.ddlText[i].toLowerCase().StartsWith(value, 0)) {//Contains 
            objCacheItem.AddItems(objCacheItem.ddlText[i], objCacheItem.ddlValue[i], -1);
        }
    }

    //when no element found
    if (objCacheItem.ddlTempItems.options.length == 0 && objCacheItem.hasDefault) {
        objCacheItem.ddlTempItems.options.add(objCacheItem.DefaultOption);
    }

    objCacheItem.lblMesg.innerHTML = value == "" ? "" :
        objCacheItem.ddlTempItems.options.length + (objCacheItem.hasDefault ? -1 : 0) + " items found.";

    objCacheItem.ddlTempItems[0].selected = true;
}

function AddProtoTypes() {
    if (!String.prototype.StartsWith) {
        String.prototype.StartsWith = function (searchString, position) {
            position = position || 0;
            return this.indexOf(searchString, position) === position;
        };
    }
}

    
        
            

                

                    
Customer
                    


                        

                        

                            

                        

                        
                    
Project
                    


                        

                        

                            

                        

                        
                
        
    

Cannot create file 'entityFmTest.mdf' because it already exists. Change the file path or the file name, and retry the operation.

< add connectionstring="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\entityFmTest.mdf; Integrated Security=True;User Instance=True;" name="entityFmContext" providername="System.Data.SqlClient">
< /add>

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Cannot create file 'entityFmTest.mdf' because it already exists. Change the file path or the file name, and retry the operation.
replace the above with

replace the above connection string with below

< add connectionstring="data source=.\SQLEXPRESS;Initial Catalog=entityFmTest; Integrated Security=SSPI;User Instance=true" name="entityFmContext" providername="System.Data.SqlClient">
< /add>

Finding maximum value out of different columns

DECLARE @a INT, @b INT, @c INT 

SET @a = 10 
SET @b = 7 
SET @c = 12 

--sql server 2008 and above
SELECT MAX(v) AS MaxVal 
FROM   ( VALUES (@a), 
                (@b), 
                (@c) ) AS value(v) 

--sql server 2005
SELECT MAX(v) AS MaxVal 
FROM   (SELECT @a UNION 
        SELECT @b UNION 
        SELECT @c) AS value(v)

Ternary Operator in javascript

var eVal = (isNaN(eThis.val())) ? 0 : eThis.val();
var eVal = parseFloat($(eThis).val()) || 0;

GridView Accessible Header

/// 

    /// Adds THeadere TBody tag to gridview
    /// http://dotnetinside.com/en/framework/v4.0.30319/System.Web/TableRow
    /// 
    /// the gridview
public void MakeAccessible(GridView grid)
{
        if (grid == null || grid.Rows.Count <= 0) return;
        //This replaces  with  and adds the scope attribute

grid.UseAccessibleHeader = true;

//This will add the  and  elements

if (grid.HeaderRow != null) grid.HeaderRow.TableSection = TableRowSection.TableHeader;

if (grid.TopPagerRow != null) grid.TopPagerRow.TableSection = TableRowSection.TableHeader;

//This adds the  element. Remove if you don't have a footer row

if (grid.BottomPagerRow != null) grid.BottomPagerRow.TableSection = TableRowSection.TableFooter;

}

Jquery ui datepicker appears off screen fix

/*e.g. $('.ui-datepicker').center();*/

jQuery.fn.center = function () {
    this.css("position", "absolute");
    this.css("top", (($(window).height() - this.outerHeight()) / 2) + $(window).scrollTop() + "px");
    this.css("left", (($(window).width() - this.outerWidth()) / 2) + $(window).scrollLeft() + "px");
    return this;
}

Restricting keyboard input with jQuery and validate decimal precision,scale

keycodechecker Demo
txt.Attributes.Add("class", "numbersonly");

In blur or textbox leaving, or in button click call the below function

function IsDecimal(Salary) {
     var _Salary = $('#' + Salary);
     if (!_Salary.isValidScale(6, 2)) {
         _Salary.focus();
         alert('Invalid decimal points');
         return false;
     }
     return true;
 }

/*Numeric validation starts*/

//Allows decimal places with only one .(decimal point or full stop)
$(".numbersonly").keydown(function (event) {
    // Prevent shift key since its not needed
    if (event.shiftKey == true) {
        event.preventDefault();
    }

    // Allow Only: keyboard 0-9, numpad 0-9
    if ((event.keyCode >= 48 && event.keyCode <= 57) || (event.keyCode >= 96 && event.keyCode <= 105)
        //Allow Only: backspace, tab, left arrow, right arrow
        || event.keyCode == 8 || event.keyCode == 9 || event.keyCode == 37 || event.keyCode == 39
        //Allow Only: delete, home, end
        || event.keyCode == 46 || event.keyCode == 36 || event.keyCode == 35
        //Allow Only: .(full stop [keyboar, numpad]) and check if there is more than one .(full stop)
        || ((event.keyCode == 190 || event.keyCode == 110) && $(this).val().indexOf('.') < 0)
    ) {
        // Allow normal operation
    } else {
        // Prevent the rest
        event.preventDefault();
    }
});

//Validate the count of precision and scale of a decimal value
//ctrlID.isValidScale(6,2);
jQuery.fn.isValidScale = function (precision, scale) {
    //return !Number.isNaN(num) && parseFloat(num).toFixed(scale).toString() === this.val();
    /*          debugger;
         var ina = !isNaN(num);
         var len = decimalPlaces(num)
         var isTrue = decimalPlaces(num) <= parseInt(scale)
         var tr = !isNaN(num) && isTrue;*/

    var num = parseFloat(this.val()) || 0;
    var intPart = parseInt(this.val()).toString(); //Convert to string

    //if input control is empty
    if (num.length == 0) return true;
    if (intPart.length > parseInt(precision)) return false;

    return !isNaN(num) && decimalPlaces(num) <= parseInt(scale);
};

function decimalPlaces(n) {
    var a;
    var len = (a = (n.toString().charAt(0) == '-' ? n - 1 : n + 1).toString().replace(/^-?[0-9]+\.?([0-9]+)$/, '$1').length) >= 1 ? a : 0;
    return parseInt(len);
}

$(".intonly").keydown(function (event) {
    // Prevent shift key since its not needed
    if (event.shiftKey == true) {
        event.preventDefault();
    }
    // Allow Only: keyboard 0-9, numpad 0-9, backspace, tab, left arrow, right arrow, delete, home, end
    if ((event.keyCode >= 48 && event.keyCode <= 57) || (event.keyCode >= 96 && event.keyCode <= 105) || event.keyCode == 8 || event.keyCode == 9 || event.keyCode == 37 || event.keyCode == 39 || event.keyCode == 46 || event.keyCode == 36 || event.keyCode == 35) {
        // Allow normal operation
    } else {
        // Prevent the rest
        event.preventDefault();
    }
});

var isValidCurrency = function (input) {
    var num = parseFloat(input);
    return !Number.isNaN(num) && parseFloat(num).toFixed(2).toString() === input;
};
/*Numeric validation ends*/

How to get the value of checkboxlist in jquery

For the checboxlist create an attribute for each listitem
/*to get the checkbox value in jquery*/
foreach (ListItem li in cbHideColumns.Items)
{
li.Attributes.Add("someValue", li.Value);
}
How to get the value in jquery
$(".cbShowOrHideGvCols input[type=checkbox]").each(function () {
var checkbox = $(this)
if ($(ctrl).is(":checked")) {
var val = checkbox.parent().attr('someValue');
}
});

jquery - Datatables change language dynamically

1. Consider we need both chinese and english
2. Create 2 text files jquery.dataTables.en-US.txt, jquery.dataTables.zh-CN.txt
3. Go to the url http://datatables.net/plug-ins/i18n
Get the language text and paste it in the relative files.
4. In the masterpage we need to have a hidden control.

5. Store the current language in the hidden field.

hdnLang.Value = SessionProxy.Search.Language.ToLower();

6. Get the language in the javascript file

var locale = ($('#ctl00_hdnLang').val() || "en-us");

7. Dynamically change the datatable language file

var langFile = "../Scripts/jquery.dataTables.en-US.txt";
if (locale === "zh-cn") {
langFile = "../Scripts/jquery.dataTables.zh-CN.txt";
}

8. Using it in the script

var oTable = $('.gvDataTable').dataTable({
    "oLanguage": {
        "sUrl": langFile
    },
    "sScrollX": "99%",
    "bStateSave": true, //http://datatables.net/forums/discussion/573/how-to-stay-on-current-page-after-re-draw/p1
    "fnDrawCallback": function (oSettings) {/*Re-Create serial no for the table*/
        /* Need to redo the counters if filtered or sorted */
        if (oSettings.bSorted || oSettings.bFiltered) {
            for (var i = 0, iLen = oSettings.aiDisplay.length; i < iLen; i++) {
                $('td:eq(0)', oSettings.aoData[oSettings.aiDisplay[i]].nTr).html(i + 1);
            }
        }
         /*Put checkboxlist after filter to show/hide columns after excel export*/
        $('.cbShowOrHideGvCols').appendTo('div.DTTT_container'); 
    },
    /*"sDom": 'r<"H"lf><"datatable-scroll"t><"F"ip>',*/
    "sDom": '<"H"lTfr><"datatable-scroll"t><"F"ip>',
    "oTableTools": {
        "sSwfPath": "../Scripts/media/swf/copy_csv_xls_pdf.swf",
        /*"sSwfPath": "http://datatables.net/release-datatables/extras/TableTools/media/swf/copy_csv_xls_pdf.swf",*/
        "aButtons": [{ /*http://datatables.net/extras/tabletools/button_options*/
                "sExtends": "xls",
                "sFileName": "xlsFileName.xls",
                "sButtonText": "",
                "sTitle": "Title of the file"
                /*"fnInit": function (node) { formatTableToolsButton(node, 'DTTT_button_xls'); }*/
            }
            /*, {
"sExtends": "pdf",
//"sButtonText": "",
"sFileName": "PdfFileName.pdf",
"sTitle": "Title of the file"
}*/
        ]
    }
});

/*When we edit the gridview, header and row are zigzag.
Call this to overcome it
*/
setTimeout(function () {
    oTable.fnAdjustColumnSizing();
}, 50);

/*after page load call this event, if there is a postback*/
if ($('.gvShowOrHideGvCols tr').length <= 0) $(".cbShowOrHideGvCols").css("display", "none");
$(".cbShowOrHideGvCols input[type=checkbox]").each(function () {
    ToggleGridViewCol('.gvShowOrHideGvCols', this, '');
});

/*when the checkbox checked changed*/
$(".cbShowOrHideGvCols input[type=checkbox]").change(function () {
    ToggleGridViewCol('.gvShowOrHideGvCols', this, '');
});

/*Remarks: if we want to show or hide gridview columns
  grid: GridView.ClientID
  ctrl: CheckBoxList control(if we are using foreach)
  colIndex: gridview column index we need to show/hide
  someValue: this the attribute added to the checkbox, which holds the checkbox value
  */
function ToggleGridViewCol(grid, ctrl, colIndex) {
    var col = (colIndex === '') ?
        $(ctrl).parent().attr('someValue') : colIndex;

    if (col != '') {
        var show = $(ctrl).is(":checked");
        if ($(grid + " tr").length <= 0) return true; //check gridview loaded/empty
        var oTable = $(grid).dataTable();
        var bVis = oTable.fnSettings().aoColumns[col].bVisible;

        if (show && !bVis)
            oTable.fnSetColumnVis(col, true);
        else if (!show && bVis)
            oTable.fnSetColumnVis(col, false);
    }
}

jQuery DataTables mouseover cursor issue

For the disabled text of the jquery datatable it show hand or pointer.
To show default cursor use the below css at the end of DataTable.css

.paginate_disabled_previous, .paginate_disabled_next,
.paginate_disabled_previous:hover, .paginate_disabled_next:hover
{
cursor: default;
text-decoration:none;
}

Check the below links for the cursor and text decoration demo from w3schools

Cursor
text-decoration

show popup window minimized

var win;

    function openWindow() {
        if (win == null || win.closed == true) {
            win = window.open('Help.htm', 'Help', 'top=150,left=200,width=450,height=250,resizable=yes,scrollbars=yes');
            win.focus();
            return false;
        } else {
            win.target = "Help.htm";
            win.focus();
            return false;
        }
    }

How to call

OnClientClick="javascript:return openWindow();"

Creating multilingual websites

• To create a multilingual application we need .resx (Resource) file.
• The .resx file is an XML file, which contains Name and a value.
• The value in the resource file is substituted at run-time.
• The file name of the resource file should be name of the aspx file.

e.g. if our aspx file name is Test.aspx, then our resource file name should be Test.aspx.resx(Default resource file for the aspx file).
Test.aspx.en-US.resx (aspx file Name.Culture.resx)

To view the code inside the resx file, Right-click -> View Code

Home US

The data tag contains the string and the value.

To set the culture to the controls and to select the correct resource file we have to set the propertis Culture and UICulture

This two properties are available form the class System.Web.UI.Page

How to attach this value to the server controls?

Import the library

• System.Threading
• System.Globalization

There are two types of Asp.Net Folders

• App_LocalResources
• App_GlobalResources

App_LocalResources
Resource files for each aspx file
The resource file is embeded with the dll file.
App_GlobalResources
Global resource can be read from any page or code that is in the application.
If we are having some text, which is repeated in most of the pages, have the key and value.

Naming conversion for the resource file
<pagename>.aspx.<language>.resx

Type 1:LocalResources
Implicit coding for App_LocalResources

in the page directive add
Culture="auto:en-US" UICulture="auto"

meta:resourceKey="Label1"
in resx file, we have it as Label1.Text as the key name.

meta:resourceKey is called as ambient language

By default our default language is English
How change the language in Browser Settings
Internet Options -> Languages -> Add -> Chinese [Simplified]
Change the default order

If we are having a Label control, and how can we apply localization,
<asp:Label ID="Label1" runat="server" Text="Label" ForeColor="green"></asp:Label>
<asp:Label ID="Label1" runat="server" meta:resourceKey="Label1"></asp:Label>

In the resx file the name of each property is noted as ControlID.Property

Type 2:App_GlobalResources
App_GlobalResources

Select control -> Press F4 -> Expression -> Text -> Expression Type -> Resource -> ClassKey=Resource,

ResourceKey = We can do this only for the GlobalResources

Title="<%$ Resources:PageTitle %>"

Type 3:Dynamically change the language
Import the library
System.Threading
System.Globalization

Override the page method called InitializeCulture
This is the place where we have to set the culture.

protected override void InitializeCulture()
    {
        string language = Request.Form["ddlLanguage"];
        if (!String.IsNullOrEmpty(language))
        {
            Culture = UICulture = language;

            //For selecting the resource file for the languate
            Thread.CurrentThread.CurrentCulture = new CultureInfo(language);

            //For date and currencies
            //Language + Location => Locale
            Thread.CurrentThread.CurrentUICulture = CultureInfo.CreateSpecificCulture(language);

            WriteCookie("CultureInfo", language);
        }
        base.InitializeCulture();
    }


public void WriteCookie(string name, string value)
    {
        HttpContext.Current.Response.Cookies.Set(new HttpCookie(name, value));
    }

    public string ReadCookie(string name)
    {
        return Request.Cookies["CultureInfo"] != null ?
            Request.Cookies["CultureInfo"].Value : null;
    }

    //This is not needed, since it will be set in the InitializeCulture()
/*
    protected void ddlLanguage_SelectedIndexChanged(object sender, EventArgs e)
    {
        WriteCookie("CultureInfo", ddlLanguage.SelectedValue);
    }
*/

To get the current culture

System.Globalization.CultureInfo currentCulture = 
System.Threading.Thread.CurrentThread.CurrentCulture;

To change the currency symbol

String.Format("{0:c}", 1000.2);

How to get the currency symbol according to the languagecode
Get Currency Symbol by Language-code

How to get the value from a resource file in code behind

private string GetMessage(string resourceKey)
    {
        return Convert.ToString(this.GetLocalResourceObject(resourceKey));
    }

How to get the value from a resource file in JavaScript

alert('<%= Convert.ToString(this.GetLocalResourceObject
("AlertLocationOrCustomer"))%>');

How to get the value from a resource file in DropDownList/ListBox asp:ListItem

-Select-

Error

//If no any resource file available
Could not find any resources appropriate for the specified culture or the neutral culture. Make sure "Test.aspx.resources" was correctly embedded or linked into assembly "App_LocalResources.root.uhdc5eth" at compile time, or that all the satellite assemblies required are loadable and fully signed.

Title="<%$ Resources:PageTitle %>"
Error parsing attribute 'title': The resource object with key 'PageTitle' was not found.

• If there is no default resx file.
• If there is no resx file for the aspx file.
• If the aspx file in a sub-folder then we have to create a App_LocalResources in side the sub-folder and create the resx file.

How do I localize the column headers in a gridview?
Curtesy
In your bound field add resource key like this:

In your resource file you need to add a key with name

Product.HeaderText and in Value = ????
Similarly you can do the same for footer

Product.FooterText and in Value = ????

How to localize in external javascript file
In the master page add the hidden control.

In the code behind of the master page set the current langauge.

hdnLang.Value = SessionProxy.Search.Language.ToLower();

eg: for, how to localize in external javascript file?

var locale = ($('#ctl00_hdnLang').val() || "en-us");
var show = { "zh-cn": "顯示", "en-us": "Show" };
var hide = { "zh-cn": "隱藏", "en-us": "Hide" };

$('img.image-selector').toggle(
    function () {
        $(this).attr('src', '../images/Show.JPG');
        $(this).attr('title', show[locale]);
    },
    function () {
        $(this).attr('src', '../images/Hide.JPG');
        $(this).attr('title', hide[locale]);
    }
);

Understanding Globalization and Localization in .NET
Using Resources for Localization with ASP.NET
Extending the ASP.NET 2.0 Resource-Provider Model

Asp.net 3.5 Application Architecture and Design- Chapter 9 Localization

Get Currency Symbol by LanguageCode

Setting minDate and maxDate for jquery datepicker

$(function () {
    var dateToday = new Date();
    var startDate = new Date(dateToday.getFullYear(), 
                 dateToday.getMonth(), dateToday.getDate() - 30);
    $("#txtFromDate").datepicker({
        minDate: startDate,
        maxDate: dateToday
    });
    $("#txtToDate").datepicker({
        minDate: startDate,
        maxDate: dateToday
    });
});

Various where types

DECLARE @TEMP TABLE
                    (
                                        Name VARCHAR(50),
                                        Sex  CHAR(1)    ,
                                        Age  INT
                    )

INSERT INTO @Temp
SELECT 'Babu', 'M', 31 UNION ALL
SELECT 'Krishna', 'M', 31 UNION ALL
SELECT 'Kanagu', 'M', 31 UNION ALL
SELECT 'rathinam', 'M', 31 UNION ALL
SELECT 'Kumarasamy', 'M', 31 UNION ALL
SELECT 'Suseela', 'F', 31 UNION ALL
SELECT 'Venkatesh', 'M', 31

DECLARE @Name VARCHAR(50),
        @Sex  CHAR(1),
        @Age  INT,
        @Type VARCHAR(50)

Using boolean logic

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

SET @Name = 'BABU'

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

SET @Name = 'BABU'
SET @Sex = 'BABU'

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

Using boolean logic

SET @Type = 'NAME'

SELECT * FROM @Temp
WHERE (@Type = 'NAME' AND Name = @Name)
OR (@Type = 'SEX' AND Sex = @Sex)
OR (@Type = 'AGE' AND Age = @Age)

Using the Case expression

SET @Name = NULL
SET @Sex = NULL
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

SET @Name = 'BABU'
SET @Sex = 'M'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

SET @Name = 'BABU'
SET @Sex = 'BABU'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

Using the ISNULL and NULLIF

SET @Name = NULL
SET @Sex = NULL
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

SET @Name = 'BABU'
SET @Sex = 'M'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

SET @Name = 'BABU'
SET @Sex = 'BABU'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

Execution plans

Get Currency Symbol by LanguageCode

Tutorial on using CultureInfo and RegionInfo

Creating multilingual websites

Code to set the culture and get the currency symbol for that culture.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Threading;
using System.Globalization;

/// 

/// Set the culture and get the currency symbol
/// 
public sealed class MyCulture
{
    public MyCulture()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public void SetCulture(string CultureCode)
    {
        Thread.CurrentThread.CurrentCulture = 
            CultureInfo.CreateSpecificCulture(CultureCode);

        Thread.CurrentThread.CurrentUICulture = 
            new CultureInfo(CultureCode);
    }

    public string GetCurrencySymbol(string CultureCode)
    {
        SetCulture(CultureCode);

        CultureInfo UsersCulture = Thread.CurrentThread.CurrentCulture;
        RegionInfo myRegion = new RegionInfo(UsersCulture.LCID);

        return myRegion.CurrencySymbol;
    }
}

How to pass the input?

MyCulture obj = new MyCulture();
String symbol = obj.GetCurrencySymbol("ta-IN");

When we are passing the input culture is necessary.
i.e. we have to pass "ta-IN" or "ta" but not "IN".

List of CultureCodes
How to add currency symbol in the gridview?
We can do it both from the server side or from the client side.

//Setting currency symbol from the service side
        protected void grdWithCurrencySign_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow || e.Row.RowType == DataControlRowType.Header)
            {
                MyCulture obj = new MyCulture();
                string symbol = obj.GetCurrencySymbol("ta-in");

                e.Row.Cells[1].Text = symbol + " " + e.Row.Cells[1].Text;
            }
        }

        public string AddCurrency(string amount)
        {
            MyCulture obj = new MyCulture();
            return amount + " " + obj.GetCurrencySymbol("ta-in");
        }

                
                    
                    
                    
                        
                            <%# AddCurrency(Convert.ToString(Eval("Salary")))%>
                        
                    
                
            

GROUP BY and DISTINCT

DECLARE @Team TABLE(EventDate DATETIME, Seconds BIGINT)

INSERT INTO @Team
SELECT '2012-08-25',440 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',1232 UNION ALL
SELECT '2012-08-25',7740 UNION ALL
SELECT '2012-08-25',18640 UNION ALL
SELECT '2012-08-25',18640 UNION ALL
SELECT '2012-08-25',18640 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512 UNION ALL
SELECT '2012-08-25',512

SELECT EventDate, Seconds FROM @Team

Sum the column Seconds for the group EventDate

SELECT EventDate, SUM(Seconds) Seconds
FROM @Team
GROUP BY EventDate

The value in the column Seconds is summed with the duplicate value.

How to sum the column without duplicate

SELECT EventDate, Sum(DISTINCT Seconds) Seconds 
FROM     @Team 
GROUP BY EventDate

SELECT   EventDate, Sum(Seconds) AS Seconds 
FROM     ( SELECT EventDate, Seconds 
                  FROM @Team 
                  GROUP BY EventDate, Seconds ) AS T 
GROUP BY EventDate

SELECT   EventDate, Sum(Seconds) AS Seconds 
FROM     ( SELECT DISTINCT EventDate, Seconds 
           FROM @Team ) AS T 
GROUP BY EventDate

Get only business days in a month in SQL SERVER

DECLARE @Start_Date DATETIME
SET @Start_Date = GETDATE()
SET @Start_Date = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Start_Date)-1),@Start_Date),101)

DECLARE @Holiday TABLE(HoliDay DATETIME)

INSERT INTO @Holiday(HoliDay) VALUES('2012-08-15')
 
;WITH MonthDays(day_num, calendar_dt) AS
     ( SELECT 1                                        AS day_num,
             DATEADD(d, -DAY(@Start_Date)+1, @Start_Date ) AS calendar_dt
     
     UNION ALL
     
     SELECT day_num + 1,
            DATEADD(d, 1, calendar_dt)
     FROM   MonthDays
     WHERE  MONTH(DATEADD(d, 1, calendar_dt)) = MONTH(@Start_Date)
     )
--SELECT * FROM MonthDays 

/*
SELECT *, datename(dw, calendar_dt), (DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, calendar_dt), 0), calendar_dt) +1) as [Week]
FROM MonthDays
WHERE ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7) NOT IN (1)
-- 0 for saturday, 1 for sunday

--WHERE ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7) NOT IN (0, 1)
--http://msdn.microsoft.com/en-us/library/ms174420(v=sql.90).aspx
*/

SELECT day_num    ,
       calendar_dt,
       WeekDayName,
       [Week]
FROM  ( SELECT day_num                                 ,
               calendar_dt                             ,
               DATENAME(dw, calendar_dt)                                                            AS WeekDayName,
               (DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, calendar_dt), 0), calendar_dt) +1) AS [Week]
       FROM    MonthDays
       WHERE   ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7 ) NOT IN (1)
       ) AS T
WHERE  ( WeekDayName != 'Saturday' 
   OR     [Week] NOT IN (1, 3, 5)
       )
       --To remove the holiday
AND    day_num NOT IN
       (SELECT DATEPART(DD, HoliDay)
       FROM    @Holiday
       )