Dot Net - Assembly: August 2012

August 30, 2012

SQL - Concatenate Column Values from Multiple Rows into a Single Column

Use the below query to Concatenating Row Values

CREATE TABLE #PRODUCTS

    CategoryId        INT,

    ProductName        VARCHAR(50)

GO

INSERT INTO #PRODUCTS VALUES(1, 'ONE - ONE')

INSERT INTO #PRODUCTS VALUES(1, 'ONE - TWO')

INSERT INTO #PRODUCTS VALUES(2, 'TWO - ONE')

INSERT INTO #PRODUCTS VALUES(2, 'TWO - TWO')

GO

WITH Ranked ( CategoryId, rnk, ProductName )

AS

    SELECT

        CategoryId,

        ROW_NUMBER() OVER( PARTITION BY CategoryId ORDER BY CategoryId ),

        CAST( ProductName AS VARCHAR(8000) )

    FROM #PRODUCTS

),

AnchorRanked ( CategoryId, rnk, ProductName )

AS

    SELECT

        CategoryId,

        rnk,

        ProductName

    FROM Ranked

    WHERE rnk = 1

),

RecurRanked ( CategoryId, rnk, ProductName )

AS

    SELECT

        CategoryId,

        rnk,

        ProductName

    FROM AnchorRanked

    UNION ALL

    SELECT

        Ranked.CategoryId,

        Ranked.rnk,

        RecurRanked.ProductName + ', ' + Ranked.ProductName

    FROM Ranked

    JOIN RecurRanked ON Ranked.CategoryId = RecurRanked.CategoryId AND Ranked.rnk = RecurRanked.rnk + 1

SELECT CategoryId, MAX( ProductName )

FROM RecurRanked

GROUP BY CategoryId;

ACTUAL TABLE:

CategoryId  ProductName

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

1           ONE - ONE

1           ONE - TWO

2           TWO - ONE

2           TWO - TWO

QUERY OUTPUT:

CategoryId  ProductName

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

1           ONE - ONE, ONE - TWO

2           TWO - ONE, TWO - TWO

Original Source: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

August 29, 2012

ASP.Net - Enable GZip Compression

Enable GZip Compression By using Web.Config File

Past the below code inside the Configuration tag

<!-- Enabling Compression Starts Here -->

<system.webServer>

    <httpCompression directory="%SystemDrive%\inetpub\temp\IIS Temporary Compressed Files">

        <scheme name="gzip" dll="%Windir%\system32\inetsrv\gzip.dll"/>

        <dynamicTypes>

            <add mimeType="text/*" enabled="true"/>

            <add mimeType="message/*" enabled="true"/>

            <add mimeType="application/javascript" enabled="true"/>

            <add mimeType="*/*" enabled="false"/>

        </dynamicTypes>

        <staticTypes>

            <add mimeType="text/*" enabled="true"/>

            <add mimeType="message/*" enabled="true"/>

            <add mimeType="application/javascript" enabled="true"/>

            <add mimeType="*/*" enabled="false"/>

        </staticTypes>

    </httpCompression>

    <urlCompression doStaticCompression="true" doDynamicCompression="true"/>

</system.webServer>

<!-- Compression Ends Here -->

Enable GZip Compression By using Global.asax

Past the below code inside the Global.asax File

private const string GZIP = "gzip";

private const string DEFLATE = "deflate";

void Application_ReleaseRequestState(object sender, EventArgs e)

    HttpApplication app = (HttpApplication)sender;

    if (app.Context.CurrentHandler is Page && app.Request["HTTP_X_MICROSOFTAJAX"] == null)

        if (IsEncodingAccepted(DEFLATE))

            app.Response.Filter = new System.IO.Compression.DeflateStream(app.Response.Filter, System.IO.Compression.CompressionMode.Compress);

            SetEncoding(DEFLATE);

        else if (IsEncodingAccepted(GZIP))

            app.Response.Filter = new System.IO.Compression.GZipStream(app.Response.Filter, System.IO.Compression.CompressionMode.Compress);

            SetEncoding(GZIP);

private static void SetEncoding(string encoding)

    HttpContext.Current.Response.AppendHeader("Content-encoding", encoding);

August 24, 2012

C#/LINQ - Split Number From Strings

Code to Split Number From Strings

/// <summary>

/// This Function used to spilt No from word

/// </summary>

/// <param name="strInput">Given String</param>

/// <returns>Expected No, If not found then returns -1</returns>

public Int64 SpiltNumberFromString(String strInput)

    string[] digits = Regex.Split(strInput, @"\D+", RegexOptions.Compiled);

    string numbers = string.Empty;

    foreach (string value in digits)

        int number;

        if (int.TryParse(value, out number))

            numbers = numbers + number.ToString(CultureInfo.InvariantCulture);

    return numbers.Length == 0 ? -1 : int.Parse(numbers);

LINQ Approach

/// <summary>

/// This Function used to spilt No from word

/// </summary>

/// <param name="strInput">Given String</param>

/// <returns>Expected No, If not found then returns -1</returns>

public Int64 SpiltNumberFromString(String strInput)

    int number = -1;

    string numberString = Regex.Split(strInput, @"\D+", RegexOptions.Compiled)

                            .Where(value => int.TryParse(value, out number))

                            .Aggregate(string.Empty, (current, value) => current + number.ToString(CultureInfo.InvariantCulture));

    return Int64.Parse(numberString);

C# – Convert Numbers into Words

Convert Money Numbers into Strings

public static string NumberToWords(int number)

    if (number == 0)

        return "ZERO";

    if (number < 0)

        return "MINUS " + NumberToWords(Math.Abs(number));

    string words = "";

    if ((number / 1000000) > 0)

        words += NumberToWords(number / 1000000) + " MILLION ";

        number %= 1000000;

    if ((number / 1000) > 0)

        words += NumberToWords(number / 1000) + " THOUSAND ";

        number %= 1000;

    if ((number / 100) > 0)

        words += NumberToWords(number / 100) + " HUNDRED ";

        number %= 100;

    if (number > 0)

        if (words != "")

            words += "AND ";

        var unitsMap = new[] { "ZERO", "ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE", "TEN", "ELEVEN", "TWELVE", "THIRTEEN", "FOURTEEN", "FIFTEEN", "SIXTEEN", "SEVENTEEN", "EIGHTEEN", "NINETEEN" };

        var tensMap = new[] { "ZERO", "TEN", "TWENTY", "THIRTY", "FORTY", "FIFTY", "SIXTY", "SEVENTY", "EIGHTY", "NINETY" };

        if (number < 20)

            words += unitsMap[number];

        else

            words += tensMap[number / 10];

            if ((number % 10) > 0)

                words += "-" + unitsMap[number % 10];

    return words;

SQL SERVER – Query to Convert Numbers into Words

Convert Money Numbers into Strings

CREATE FUNCTION fnNumberToWords

    @Number AS BIGINT

) RETURNS VARCHAR(MAX)

AS

BEGIN

    DECLARE @Below20 TABLE (ID INT IDENTITY(0,1), Word VARCHAR(32))

    DECLARE @Below100 TABLE (ID INT IDENTITY(2,1), Word VARCHAR(32))

    DECLARE @BelowHundred AS VARCHAR(126)

    INSERT @Below20 (Word) VALUES ('ZERO')

    INSERT @Below20 (Word) VALUES ('ONE')

    INSERT @Below20 (Word) VALUES ( 'TWO' )

    INSERT @Below20 (Word) VALUES ( 'THREE')

    INSERT @Below20 (Word) VALUES ( 'FOUR' )

    INSERT @Below20 (Word) VALUES ( 'FIVE' )

    INSERT @Below20 (Word) VALUES ( 'SIX' )

    INSERT @Below20 (Word) VALUES ( 'SEVEN' )

    INSERT @Below20 (Word) VALUES ( 'EIGHT')

    INSERT @Below20 (Word) VALUES ( 'NINE')

    INSERT @Below20 (Word) VALUES ( 'TEN')

    INSERT @Below20 (Word) VALUES ( 'ELEVEN' )

    INSERT @Below20 (Word) VALUES ( 'TWELVE' )

    INSERT @Below20 (Word) VALUES ( 'THIRTEEN' )

    INSERT @Below20 (Word) VALUES ( 'FOURTEEN')

    INSERT @Below20 (Word) VALUES ( 'FIFTEEN' )

    INSERT @Below20 (Word) VALUES ( 'SIXTEEN' )

    INSERT @Below20 (Word) VALUES ( 'SEVENTEEN')

    INSERT @Below20 (Word) VALUES ( 'EIGHTEEN' )

    INSERT @Below20 (Word) VALUES ( 'NINETEEN' )

    INSERT @Below100 VALUES ('TWENTY')

    INSERT @Below100 VALUES ('THIRTY')

    INSERT @Below100 VALUES ('FORTY')

    INSERT @Below100 VALUES ('FIFTY')

    INSERT @Below100 VALUES ('SIXTY')

    INSERT @Below100 VALUES ('SEVENTY')

    INSERT @Below100 VALUES ('EIGHTY')

    INSERT @Below100 VALUES ('NINETY')

    IF @Number > 99

    BEGIN

        SELECT @belowHundred = dbo.fnNumberToWords( @Number % 100)

END

    DECLARE @NumberInWords VARCHAR(MAX)

    SET @NumberInWords  =

      SELECT

        CASE

            WHEN @Number = 0 THEN  ''

            WHEN @Number BETWEEN 1 AND 19

                THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

            WHEN @Number BETWEEN 20 AND 99

                THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' + dbo.fnNumberToWords( @Number % 10)

            WHEN @Number BETWEEN 100 AND 999

                THEN (dbo.fnNumberToWords( @Number / 100)) + ' HUNDRED '+

                        CASE

                            WHEN @belowHundred <> ''

                                THEN 'AND ' + @belowHundred else @belowHundred

END

            WHEN @Number BETWEEN 1000 AND 999999

                THEN (dbo.fnNumberToWords( @Number / 1000))+ ' THOUSAND '+ dbo.fnNumberToWords( @Number % 1000)

            WHEN @Number BETWEEN 1000000 AND 999999999

                THEN (dbo.fnNumberToWords( @Number / 1000000)) + ' MILLION '+ dbo.fnNumberToWords( @Number % 1000000)

            WHEN @Number BETWEEN 1000000000 AND 999999999999

                THEN (dbo.fnNumberToWords( @Number / 1000000000))+' BILLION '+ dbo.fnNumberToWords( @Number % 1000000000)

            ELSE ' INVALID INPUT'

END

    SELECT @NumberInWords = RTRIM(@NumberInWords)

    SELECT @NumberInWords = RTRIM(LEFT(@NumberInWords,LEN(@NumberInWords)-1)) WHERE RIGHT(@NumberInWords,1)='-'

    RETURN (@NumberInWords)

END