How can use multiple aggregation in pivot query.

 Syntax for PIVOT.

PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

Below table we have Suppliercode and amount. We want change Row data in column means HP, CBS & GRN as column name also find total number of supplier code in table base of unique supplier.

Another output total amount base of unique supplier code.

ID	SupplierCode	Amount
1	HP	29
2	HP	30
3	CBS	40
4	CBS	50
5	HB	50

In below pivot query using multiple aggregation in pivot query for our output.

select * from cost

SELECT * FROM (

select CBS as CBS_Count,HB as HB_Count,HP as HP_Count from (

select count(SupplierCode)  as Code,SupplierCode  from cost

group by SupplierCode) as t

PIVOT (SUM(CODE) FOR SupplierCode IN([CBS],[HB],[HP])) AS PIB) T1,

(select * from

(

SELECT SupplierCode,SUM(Amount) as Total  from cost group by SupplierCode) as T2

pivot

(

SUM(Total) for SupplierCode in([CBS],[HB],[HP])) AS totalamount) T3

OUTPUT:

CBS_Count	HB_Count	HP_Count	CBS	HB	HP
2	1	2	90	50	59

How can use sqlCacheDependency for fetching data from data base without page refreshing.

Database name : TQueryNotification

Table name : EMP_Detail , cpyjob.

  

select * from cpyjob

no	job
1	SW
2	TT
3	bank

select * from cpy.EMP_Detail

sno	name	address	email	phone	pin
1	chandra	delhi	cp@gmail.com	3455	2344
2	ravi	lucknow	rp@gmail.com	45667	2345
3	rohit	delhi	r@gmail.com	3455	344
4	roli	delhi	roli@gmailo.com	35335	353
5	raj1	kanpur	raJ@gmail.com	5556756	44545

Before uesing sqlCacheDependency to use them we first need to set up our database by executing the following scripts:

CREATE ROLE [cpy] AUTHORIZATION [dbo]

GO

 CREATE SCHEMA [cpy] AUTHORIZATION [cpy]

GO

GRANT CREATE PROCEDURE to [cpy];

GRANT CREATE QUEUE to [cpy];

GRANT CREATE SERVICE to [cpy];

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [cpy];

GRANT VIEW DEFINITION TO [cpy];

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [cpy];

GRANT RECEIVE ON QueryNotificationErrorsQueue TO [cpy];

GO

SELECT  [no],[job]  FROM [cpy].[cpyjob]   no result

SELECT  [Sno],[Name]  FROM [cpy].[EMP_Detail]  result

SELECT  [no],[job]  FROM [dbo].[cpyjob]    result

Have you ever thought of a scenario in an ASP.NET website that if something is inserted, updated or deleted from the database the ASPX page should auto-reflect it without explicitly refreshing the page? You can using an ajax timer and database polling can easily accomplish the mission, right?

Give it a second thought; is it really a good practice to poll the database periodically? No, its not, because every database call will increase the load on the website. What if we put the data into a cache and poll it periodically for a change in data . Sql server provide we have option QueryNotification and SQLCacheDependency for above task.

In the getdata() function I am executing a select command and populating the data table with the results and assigning it to a cache. If you will see I have created an object of sqlCacheDependency and passed a command object into its constructor. Later this dependency object has been passed to the Cache.Insert method. A timer is periodically checking whether the cache is empty or not. If the cache is empty it will be populated back from the database.Query Notifier that we enabled will notify whether the data has changed or not as soon as the data is changed our the cache will be Null and the freshly changed data will be shown in the grid.

There are the following limitations of SQL Cache Dependency:

Works on plain query not SP

No support of * in the query

Query should not contain UNION, INTERSECT, EXCEPT operators

Query should not contain Distinct Keyword

Limited use of AGGREGATE functions

C# code :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Web.Caching; 

public partial class _Default : System.Web.UI.Page

{

    string con = "Data Source=HP11-PC\\SQLEXPRESS;Initial Catalog=TQueryNotification;integrated security=true";

  protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            getdata();

        }

    }

    public void getdata()

    {

        if (HttpRuntime.Cache["Hello1"] == null)

        {

            SqlConnection c = new SqlConnection(con);

            SqlCommand cmd = new SqlCommand("SELECT  [Sno],[Name]  FROM [cpy].[EMP_Detail]", c);

            cmd.Notification = null;

            cmd.NotificationAutoEnlist = true;

            SqlCacheDependency cac1 = new SqlCacheDependency(cmd);

            SqlDependency.Start(con);

            c.Open();

            DataTable dt = new DataTable();

            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            sda.Fill(dt);

            c.Close();

            HttpRuntime.Cache.Insert("Hello1", dt, cac1);

        }

        Load1();

    }

    public void Load1()

    {

        DataTable dt = (DataTable)HttpRuntime.Cache.Get("Hello1");

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

    protected void Timer1_Tick(object sender, EventArgs e)

    {

        getdata();

    }

}

aspx page code:

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

<!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 id="Head1" runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <asp:ScriptManager ID="ScriptManager1" runat="server">

    </asp:ScriptManager>

    <asp:UpdatePanel runat="server" ID="upd" UpdateMode="Conditional">

        <ContentTemplate>

            <asp:Timer ID="Timer1" runat="server" Interval="600" OnTick="Timer1_Tick">

            </asp:Timer>

            <div>

                <asp:GridView ID="GridView1" runat="server">

                </asp:GridView>

            </div>

        </ContentTemplate>

    </asp:UpdatePanel>

    </form>

</body>

</html>

How can match particular word with in string.

How can match particular word with in string.

In below example we have room name string, we want categories our room as per category:

Deluxe,Single,Double etc. We have function roomCategory2()  and room name “Deluxe 1 Bedroom Suite Courtyard or City view” we can provide Deluxe category this room.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        roomCategory2();

    }

        

   //***********************************************************

    public string roomCategory()

    {

        string[] category = new string[] {"Deluxe","Single","Double"};

        string roomname = "Deluxe 1 Bedroom Suite Courtyard or City view";

        string retVal="";

        foreach (string finalname in category)

        {

             retVal = roomname.Split(" \n\r".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).First(p => p.Equals(finalname));

           

        }

        Response.Write(retVal);

       

        return "";

     }

//***********************************************************

    public string roomCategory1()

    {

        string[] category = new string[] { "Deluxe", "Single", "Double" };

        string roomname = "Deluxe 1 Bedroom Suite Courtyard or City view";

        string retVal = "";

      

        foreach (string x in category)

        {

            if (x.Contains(roomname))

            {

                Response.Write("true");

            }

            else

            {

                Response.Write("false");

            }

        }

       

        return "";

    }

//***********************************************************

    public string roomCategory2()

     {

        string[] category = new string[] { "Deluxe", "Single", "Double" };

        string roomname = "Single 1 Bedroom Suite Courtyard or City view";

        string[] splitroomname = roomname.Split(" \n\r".ToCharArray());

        string str="";

        foreach (var item in splitroomname)

        {

            foreach(var item1 in category)

              {

                  if (item == item1)

                      str = item1;

              }

           

        }

        Response.Write(str);

        return str;

       

     }

    //***********************************************************

  

}

Calculate number of days between two days in c#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string checkoutDate = "04/13/2014";//mm/dd/yyyy
        DateTime checkout = Convert.ToDateTime(checkoutDate);
        DateTime currentDate = DateTime.Now;
        Response.Write("<br>");
        Response.Write(currentDate);
        Response.Write("<br>");
        Response.Write(checkout);
        Response.Write("<br>");
        int dayscount = DateDiffInDays(currentDate,checkout);
        Response.Write("<br>");
       
       
        string script = "<script type='text/javascript'>alert('Search not allowed more then 365 days');</script>";
        if (dayscount > 365)
        {
            Response.Write(script);
        }
        else
        {
            Response.Write(dayscount);
        }
    }

    private int DateDiffInDays(DateTime currentDate, DateTime checkout)
    {

        return checkout.Subtract(currentDate).Days;

    }
}

SQL to Select a random row from a database table.

There are many ways to select a random record or row from a database table. We have SQL syntax for this task, we can easily fetch random row from sql database table.

     Table: builder

id	buildername
1	DLF
2	Amerpali
3	vision
4	rampark

SELECT TOP 2 *

FROM builder TABLESAMPLE (100 ROWS) order by NEWID()

OUTPUT:

id	buildername
2	Amerpali
4	rampark