Sunday, August 29, 2010

CASE Statement Equivalent in LINQ

In this post i will show you how how to generate "Case statement" from linq.Let's say that you want to select rows from [Order Details] table in Northwind database something like this:
  1.  If the product UnitPrice is >10 then you want to display it as "High"
  2. If the product UnitPrice is <5 then you want to display it as "Low" otherwise "Average"

Our query should include the switch case  clause. Something like this:

SELECT
(
CASE
WHEN UnitPrice >10 THEN 'High'
WHEN UnitPrice <5 THEN 'Low'
ELSE 'Average'
END ) AS ProductPrice
FROM [Order Details]




using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace Linq_Tips
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindDataContext dc = new NorthwindDataContext();
            var query = from o in dc.Order_Details
                        select new
                        {
                            //ternary operator of switch case
                            ProductPrice =
                           (
                           o.UnitPrice > 10 ? "High" :
                           o.UnitPrice < 5 ? "Low" :
                           "Average"
                          )
                        };
            Console.WriteLine(query);

        }
    }
}

Output of the above code will look something like this 


Linq and Sql "where in" Operator

In this post i will  show you how to generate "where in " clause in Linq to Sql. Imagine that we want to select rows from Products table in Northwind database where ProductId matches 3,4, 10. Our query should include the Where IN clause. Something like this:  

SELECT *FROM Products WHERE ProductID in (3,4,10)
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace Linq_Tips
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a list
            List<int> list = new List<int>();
            //Add items in list
            list.AddRange(new int[] {3, 4, 10 });
            NorthwindDataContext dc = new NorthwindDataContext();
            //Query database
            var query = from product in dc.Products
                        where list.Contains(product.ProductID)
                        select product;
            Console.WriteLine(query);
          
        }
    }
}

The output of the above code snippet will look like this:

Friday, August 27, 2010

How to use Jquery fullcalender in asp.net

This snippet will show how how to use Jquery fullcalender in asp.net
Open Microsoft Visual Studio .NET. In Visual C# .NET, create a new website  named Fullcalender.Add  following code to Default.aspx page.


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="FullCalender.aspx.cs" Inherits="FullCalender" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
    <script src="Scripts/fullcalendar.js" type="text/javascript"></script>
    <script src="Scripts/gcal.js" type="text/javascript"></script>
    <link href="Styles/fullcalendar.css" rel="stylesheet" type="text/css" />
    <script src="Scripts/json2.js" type="text/javascript"></script>
    <script type="text/javascript" language="javascript">
        $(document).ready(function () {
            $('#calendar').fullCalendar({
                header: {
                    left: 'prev,next,today',
                    center: 'title',
                    right: 'month,basicWeek,basicDay'
                },
                events: "FullCalender.asmx/EventList"
                // events: [{ title: 'event1', start: '2010-06-01' }, { title: 'event2', start: '2010-06-05', end: '2010-06-07'}]
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div id="calendar">
    </div>
    </form>
</body>
</html>
 
 
 Create a WebService in existing project named FullCalender.asmx and add following code to
 webservice
 
 
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

/// <summary>
/// Summary description for FullCalender
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class FullCalender : System.Web.Services.WebService
{

    public FullCalender()
    {

    }

    [WebMethod]
    [System.Web.Script.Services.ScriptMethod]
    public string EventList(double startDate, double endDate)
    {
        var eventList = from e in new Events().GetAll()
                        select new
                        {
                            id = e.Id,
                            title = e.Title,
                            start = e.StartDate.ToString("s"),
                            end = e.EndDate.ToString("s"),
                            url = e.Url

                        };

        System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer();
        return js.Serialize(eventList);
    }

}
public class Events
{

    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public string Url { get; set; }

    public List<Events> GetAll()
    {
        List<Events> lists = new List<Events>()
        {
            new Events{Id=1,StartDate=DateTime.Now,EndDate=DateTime.Now.AddDays(-1),Title="Client Call",Url="http://aspdotnetcodebook.blogspot.com"},
            new Events{Id=2,StartDate=DateTime.Now.AddDays(-5),EndDate=DateTime.Now.AddDays(-4),Title="Meeting with CEO",Url="http://aspdotnetcodebook.blogspot.com"},
            new Events{Id=3,StartDate=DateTime.Now.AddDays(-10),EndDate=DateTime.Now.AddDays(-9),Title="Travel Abroad",Url="http://aspdotnetcodebook.blogspot.com"},
        };
        return lists;


    }


    private static DateTime ConvertFromUnixTimestamp(double timestamp)
    {

        var origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);

        return origin.AddSeconds(timestamp);

    }

}



 

Wednesday, August 25, 2010

Linq and Reflection

This snippet will show how LINQ can be used with reflection to retrieve specific metadata about type that match a specified search criterion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace Linq_Tips
{
    class Program
    {
        static void Main(string[] args)
        {
            Assembly assembly = Assembly.Load("System.Data, Version=3.5.0.0, Culture=neutral, " + "PublicKeyToken= b77a5c561934e089");
            var Query = from type in assembly.GetTypes()
                        where type.IsClass
                        select type;
            foreach (var item in Query)
            {
                Console.WriteLine(item.Name);

            }
            Console.WriteLine("Press any key to exit ... ");
            Console.ReadKey();

        }
    }
} 
 
 
 

How to extract number from string using Linq

Imagine you have a string("ABCDE99F-J74-12-89A") and you want to extract only number from the string. This snippet will show how to extract number from string.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Linq_Tips
{
    class Program
    {
        static void Main(string[] args)
        {
            string aString = "ABCDE99F-J74-12-89A";
            // Select only those characters that are numbers 
            IEnumerable<char> stringQuery = from ch in aString
                                            where Char.IsDigit(ch)
                                            select ch;
            // Execute the query 
            foreach (char c in stringQuery)
                Console.Write(c + " ");

        }
    }
}

How to apply Left Outer join in Linq to Sql

In this post i am going to show you how to apply Left outer join in Linq to sql. Imagine that we want to apply left outer join in Products and Categories table in Northwind database Something like this:

SELECT [t1].[CategoryName] AS [CategoryId]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[Categor
yID])

As an example i will take northwind Products and Categories table.Both these tables contain CategoryId as common field.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Linq_Tips
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindDataContext dc = new NorthwindDataContext();
            var filteredQuery = from p in dc.Products
                                join c in dc.Categories
                                on p.CategoryID equals c.CategoryID into joinData
                                from data in joinData.DefaultIfEmpty()
                                select new
                                {
                                    CategoryId = data.CategoryName
                                };
            Console.WriteLine(filteredQuery);
        }
    }
}
The code above gets translated into this SQL query.
SELECT [t1].[CategoryName] AS [CategoryId]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[Categor
yID])

Saturday, August 7, 2010

How to use flag enuemeration in c#/asp.net

Imagine we have a User class, and one of the user properties is Permission. The Permission enum can be defined as follows:
enum Permission
    {
        None = 0,
        Read = 2,
        Write = 4,
        ReadWrite = 8,
        All = Int16.MaxValue

    }
As you can see -these options could be combined in several ways like an user can have Read and Write permission or user can have only Read permission.Most of us will create seprate properties in user class but efficient way to resolve this problem is use flag enumeration.flag enumeration is an instance of the enumeration type to store any combination of the values that are defined in the enumerator list.You create a bit flags enum by applying the System.FlagsAttribute attribute and defining the values appropriately so that AND, OR, NOT and XOR bitwise operations can be performed on them.
[Flag]
  enum Permission
    {
        None = 0,
        Read = 2,
        Write = 4,
        ReadWrite = 8,
        All = Int16.MaxValue

    }

or

 [Flag]
  enum Permission
    {
        None = 0x,
        Read = 1x,
        Write = 2x,
        ReadWrite = 3x,
        All = Int16.MaxValue

    }

This means we can now use the bitwise OR|AND|NOT|XOR operator to combine these enum values. For instance:How to set a flag on an enum, use the bitwise OR operator as shown in the following example:

Permission permisson = Permission.Read | Permission.Write;
How to reset a flag on an enum

permisson &= ~Permission.Read;
How to filp a flag on an enum

permisson &= ~Permission.Read;
How to clear all flags

permission=Permission.None