Linq with Datatable : Asp.net C#

1. Show all data
********************************

 protected void Button1_Click(object sender, EventArgs e)
    {
        string srt = @"select e.EmployeeId,d.Designation,u.username,u.userpwd from Employees e 
                    inner join DesignationMaster d on d.Designationid=e.DesignationID
                    inner join userdet u on u.EmpId=e.EmployeeId";
        SqlDataAdapter adt = new SqlDataAdapter(srt, con);
        DataTable dt = new DataTable();
        adt.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

Output
********************************

2. Show all Asst.Professor
********************************

 protected void Button2_Click(object sender, EventArgs e)
    {
        string srt = @"select e.EmployeeId,d.Designation,u.username,u.userpwd from Employees e 
                    inner join DesignationMaster d on d.Designationid=e.DesignationID
                    inner join userdet u on u.EmpId=e.EmployeeId";
        SqlDataAdapter adt = new SqlDataAdapter(srt, con);
        DataTable dt = new DataTable();
        adt.Fill(dt);
       


        EnumerableRowCollection<DataRow> filter = from row in dt.AsEnumerable() where row.Field<string>("Designation") == "Asst.Professor" select row;
        DataView DV = new DataView();
        DV = filter.AsDataView();
        GridView1.DataSource = DV;
        GridView1.DataBind();
    }

Output
********************************



3. Filer based on user name
********************************

 protected void Button3_Click(object sender, EventArgs e)
    {
        string srt = @"select e.EmployeeId,d.Designation,u.username,u.userpwd from Employees e 
                    inner join DesignationMaster d on d.Designationid=e.DesignationID
                    inner join userdet u on u.EmpId=e.EmployeeId";
        SqlDataAdapter adt = new SqlDataAdapter(srt, con);
        DataTable dt = new DataTable();
        adt.Fill(dt);

        EnumerableRowCollection<DataRow> filter = from row in dt.AsEnumerable() where row.Field<string>("username").Contains(TextBox1.Text) select row;

        DataView dv = new DataView();
        dv = filter.AsDataView();
        GridView1.DataSource = dv;
        GridView1.DataBind();

    }

Output
********************************

Linq Simple Example with Array C#

  protected void Button1_Click(object sender, EventArgs e)
    {
        Label1.Text = "";
        int[] a = { 1, 5, 4, 8, 9, 6 };

        Label1.Text = "Original Array";

        for (int i = 0; i < a.Length; i++)
        {
            Label1.Text += "<br>" + a[i].ToString();
        }

        Label1.Text = "<br>Linq";

        var elements =from element in a where element>5 select element;

        foreach (var x in elements)
        {
            Label1.Text += "<br>" + x;
        }

    }

Output

C# code to convert Number to Words

public static string NumbersToWords(int inputNumber)
    {
        int inputNo = inputNumber;

        if (inputNo == 0)
            return "Zero";

        int[] numbers = new int[4];
        int first = 0;
        int u, h, t;
        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        if (inputNo < 0)
        {
            sb.Append("Minus ");
            inputNo = -inputNo;
        }

        string[] words0 = {"" ,"One ", "Two ", "Three ", "Four ",
            "Five " ,"Six ", "Seven ", "Eight ", "Nine "};
        string[] words1 = {"Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ",
            "Fifteen ","Sixteen ","Seventeen ","Eighteen ", "Nineteen "};
        string[] words2 = {"Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ",
            "Seventy ","Eighty ", "Ninety "};
        string[] words3 = { "Thousand ", "Lakh ", "Crore " };

        numbers[0] = inputNo % 1000; // units
        numbers[1] = inputNo / 1000;
        numbers[2] = inputNo / 100000;
        numbers[1] = numbers[1] - 100 * numbers[2]; // thousands
        numbers[3] = inputNo / 10000000; // crores
        numbers[2] = numbers[2] - 100 * numbers[3]; // lakhs

        for (int i = 3; i > 0; i--)
        {
            if (numbers[i] != 0)
            {
                first = i;
                break;
            }
        }
        for (int i = first; i >= 0; i--)
        {
            if (numbers[i] == 0) continue;
            u = numbers[i] % 10; // ones
            t = numbers[i] / 10;
            h = numbers[i] / 100; // hundreds
            t = t - 10 * h; // tens
            if (h > 0) sb.Append(words0[h] + "Hundred ");
            if (u > 0 || t > 0)
            {
                if (h > 0 || i == 0) 
                    sb.Append("and ");
                if (t == 0)
                    sb.Append(words0[u]);
                else if (t == 1)
                    sb.Append(words1[u]);
                else
                    sb.Append(words2[t - 2] + words0[u]);
            }
            if (i != 0) sb.Append(words3[i - 1]);
        }

        return sb.ToString().TrimEnd();        
    }

Pivot table in SQL Server 2008 - Example

Introduction

A pivot table is a regularly used technique of shortening and displaying particular report information by means of consortium and aggregating values. Pivot tables are easily shaped by office users using Microsoft Excel or MS Access. Sincepivot table enables information builders and BI (Business Intelligence) specialists authorize their appearance of information and amplify the visibility andunderstandabilityof mined information, pivot tables are widespread and favored widely. Pivot tables exhibit information in tabular form. The pivot table formatting is not dissimilar than a tabular report formatting. But the tuples are produced by the statement information itself.

In this example i am using a temp table.

Sample Table

CREATE TABLE #Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)

INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)

INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO #Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)



Select * from #Product order by Cust

Cust                      Product              QTY
------------------------- -------------------- -----------
FRED                      MILK                 3
FRED                      BEER                 24
KATE                      VEG                  3
KATE                      VEG                  2
KATE                      SODA                 6
KATE                      MILK                 1
KATE                      BEER                 12

(7 row(s) affected)

Pivot table based on customer field

SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM #Product) up
PIVOT (  SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)  
) AS pvt
ORDER BY CUST

CUST                      VEG         SODA        MILK        BEER        CHIPS
--------- ----------- ----------- ----------- ----------- ----------- ---------
FRED                      NULL        NULL        3           24          NULL
KATE                      5           6           1           12          NULL

(2 row(s) affected)

Pivot table based on product field

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM #Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

PRODUCT              FRED        KATE
-------------------- ----------- -----------
BEER                 24          12
MILK                 3           1
SODA                 NULL        6
VEG                  NULL        5

(4 row(s) affected)