in

code for eternity !!!

community website for .net freaks ;-)

Technology

April 2008 - Posts

  • T-SQL Query to Find the Second Lowest Column Value in a Table

    After reading my earlier post T-SQL Query to Find the Second Highest Column Value in a Table a lot of users asked me to also help them with writing a query to find the second lowest column value in a table. So here it is: If we had a table named Employee which had a column named Salary and we had to find the second lowest Salary in the Employee table, the query for the same would be:

    SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee ORDER BY Salary ASC) AS E ORDER BY Salary DESC

    If we ran the above query against the Employee table which had the following 5 rows:

    5000
    4000
    3000
    2000
    1000

    The subquery or the inner query would return the top 2 rows in ascending Salary order which would be:

    1000
    2000

    The outer query would then select the top 1 row from the subquery results in descending Salary order which would be:

    2000

    Note that if we had to get the fourth lowest Salary, we could do so by simply changing the subquery from TOP 2 to TOP 4

    Cheers,
    Raj

    ~~~ CODING FOR ETERNITY !!! ~~~

    Posted Apr 30 2008, 09:41 AM by raj with 2 comment(s)
    Filed under:
  • LINQ Last and LastOrDefault

    We can use the Last extension method of LINQ to return the last element in a sequence that satisfies a specified condition. However the Last extension method would throw the System.InvalidOperationException when no element in a sequence satisfies a specified condition. In such a scenario, we can use the LastOrDefault extenion method which would return the default value of a type instead of throwing the System.InvalidOperationException.

    Code example (both in C# and VB.NET) with comments below:

    C#:

        // Create a new generic list of ints
        List<int> l = new List<int>();

        l.Add(1); // Add 1 to the list
        l.Add(5); // Add 5 to the list
        l.Add(3); // Add 3 to the list

        // Returns 1 as only 1 satisfies the condition
        int value = l.Last(i => i == 1);

        // Returns 3 although both 5 and 3 are greater than 1
        // since 3 appears last in the list
        value = l.Last(i => i > 1);

        // Returns the default value of int which is 0
        // since no element in the list equals 4
        value = l.LastOrDefault(i => i == 4);

        // Throws System.InvalidOperationException
        // since no element in the list equals 4
        value = l.Last(i => i == 4);

    VB.NET:

        ' Create a new generic list of ints
        Dim l As New List(Of Integer)

        l.Add(1) ' Add 1 to the list
        l.Add(5) ' Add 5 to the list
        l.Add(3) ' Add 3 to the list

        ' Returns 1 as only 1 satisfies the condition
        Dim value As Integer = l.Last(Function(i) i = 1)

        ' Returns 3 although both 5 and 3 are greater than 1
        ' since 3 appears last in the list
        value = l.Last(Function(i) i > 1)

        ' Returns the default value of int which is 0
        ' since no element in the list equals 4
        value = l.LastOrDefault(Function(i) i = 4)

        ' Throws System.InvalidOperationException
        ' since no element in the list equals 4
        value = l.Last(Function(i) i = 4)

    Cheers,
    Raj

    ~~~ CODING FOR ETERNITY !!! ~~~

    Posted Apr 27 2008, 09:31 AM by raj with 2 comment(s)
    Filed under: ,
  • LINQ First and FirstOrDefault

    We can use the First extension method of LINQ to return the first element in a sequence that satisfies a specified condition. However the First extension method would throw the System.InvalidOperationException when no element in a sequence satisfies a specified condition. In such a scenario, we can use the FirstOrDefault extenion method which would return the default value of a type instead of throwing the System.InvalidOperationException.

    Code example (both in C# and VB.NET) with comments below:

    C#:

        // Create a new generic list of ints
        List<int> l = new List<int>();

        l.Add(1); // Add 1 to the list
        l.Add(5); // Add 5 to the list
        l.Add(3); // Add 3 to the list

        // Returns 1 as only 1 satisfies the condition
        int value = l.First(i => i == 1);

        // Returns 5 although both 5 and 3 are greater than 1
        // since 5 appears first in the list
        value = l.First(i => i > 1);

        // Returns the default value of int which is 0
        // since no element in the list equals 4
        value = l.FirstOrDefault(i => i == 4);
       
        // Throws System.InvalidOperationException
        // since no element in the list equals 4
        value = l.First(i => i == 4);

    VB.NET:

        ' Create a new generic list of ints
        Dim l As New List(Of Integer)

        l.Add(1) ' Add 1 to the list
        l.Add(5) ' Add 5 to the list
        l.Add(3) ' Add 3 to the list

        ' Returns 1 as only 1 satisfies the condition
        Dim value As Integer = l.First(Function(i) i = 1)

        ' Returns 5 although both 5 and 3 are greater than 1
        ' since 5 appears first in the list
        value = l.First(Function(i) i > 1)

        ' Returns the default value of int which is 0
        ' since no element in the list equals 4
        value = l.FirstOrDefault(Function(i) i = 4)

        ' Throws System.InvalidOperationException
        ' since no element in the list equals 4
        value = l.First(Function(i) i = 4)

    Cheers,
    Raj

    ~~~ CODING FOR ETERNITY !!! ~~~

    Posted Apr 25 2008, 06:22 PM by raj with 2 comment(s)
    Filed under: ,
  • Blackle - Energy Saving Search

    Google is the second brain for many coders hungry for information. I as a coder cannot imagine a single day at work without Google. Few days back (22 April to be precise), it was Earth's day and I was wondering how we as coders can do our bit for our planet and fight against Global Warming. So I thought I would blog about Blackle :-)

    The idea behind Blackle: Google uses white background which consumes more power, and considering the huge number of queries Google gets (about 200 million each day), according to calculations, a black version of Google would save 750 mega watts / hour per year.

    So next time you want to search for information, try http://www.blackle.com :-) For more info on Blackle, visit http://www.blackle.com/about

    Also help spread the word about Blackle by telling your coder friends about it, ask them to blog about Blackle incase they have a blog ;-)

    Cheers,
    Raj

    ~~~ CODING FOR ETERNITY !!! ~~~

    Posted Apr 24 2008, 08:45 PM by raj with 2 comment(s)
    Filed under:
  • Cool Extension Methods for IDataReader Interface

    I find it really annoying to use the GetOrdinal method of the DataReader class everytime I have to access data in a strongly typed manner using any of the Get[DataType] methods of the DataReader class. Have a look at below code:

        int employeeID = dr.GetInt32(dr.GetOrdinal("EmployeeID"));
        string firstName = dr.GetString(dr.GetOrdinal("FirstName"));
        bool enabled = dr.GetBoolean(dr.GetOrdinal("Enabled"));

    It would be so cool if I could directly specify the table column names like below:

        int employeeID = dr.GetInt32("EmployeeID");
        string firstName = dr.GetString("FirstName");
        bool enabled = dr.GetBoolean("Enabled");

    This would save me so much typing and make my code look so much cleaner. The power of Extension Methods make this possible :-) I created a new class named IDataReaderHelper which has 18 Extension Methods in all. You can simply pass the table column names instead of table column indexes to all these 18 Extension Methods, and these 18 Extension Methods would take care of the rest by calling the GetOrdinal method of the DataReader class internally :-). Have a look at the screen shot below:

    Note: For readability purpose, above screen shot just highlights 3 of the 18 Extension Methods present in the IDataReaderHelper class. The 18 Extension Methods present in the IDataReaderHelper class are:

    • GetBoolean
    • GetByte
    • GetBytes
    • GetChar
    • GetChars
    • GetData
    • GetDataTypeName
    • GetDateTime
    • GetDecimal
    • GetDouble
    • GetFieldType
    • GetFloat
    • GetGuid
    • GetInt16
    • GetInt32
    • GetInt64
    • GetString
    • GetValue

    You can find the entire source code for the IDataReaderHelper class as an attachment with this post available for download. To see all my posts on Extension Methods click here.

    Update: Guys please dont forget to check out the Chris's comments (2nd from top) regarding performance issues when using the GetOrdinal method of the DataReader class inside a loop. Thanks for your excellent feedback Chris :-)

    Update: Guys please dont forget to check out programatik's post here where he / she has compared performance (using GetOrdinal versus using int inside a loop). His / her post is in Portuguese so you can go to http://www.google.com/translate_t and translate programatik's post from Portuguese to English. It took me 30 minutes to figure out the language of this post ;-) Great work programatik ;-)

    Cheers,
    Raj

    ~~~ CODING FOR ETERNITY !!! ~~~

  • System.Data.DataSetExtensions Assembly Error in Visual Studio 2008 RTM

    After upgrading from Visual Studio 2008 Beta 2 to Visual Studio 2008 RTM, I got the following error when I tried to build my ASP.NET 3.5 project which was initially coded using Visual Studio 2008 Beta 2:

    Could not load file or assembly 'System.Data.DataSetExtensions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.

    This error occurs because with Visual Studio 2008 RTM, a newer version (3.5) of the System.Data.DataSetExtensions assembly is available and the older version (2.0) of the System.Data.DataSetExtensions assembly no longer exists.

    To resolve this error, simply change your web.config file from:

    <add assembly="System.Data.DataSetExtensions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

    to:

    <add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

    Cheers,
    Raj

    ~~~ CODING FOR ETERNITY !!! ~~~

Powered by Community Server (Non-Commercial Edition), by Telligent Systems