Ranking-Query

While working with sql queries , sometimes we need to sort result set and find out the top 10 products , prices etc . we use order by clause to sort the results either ascending or descending.
But what if someone told u to find out the top 10 records and display their ranks too besides.
Here is one demonstration of ranking query . SQL Server provides a special function rank() over that can solve the problem very easily and its is very useful .

Syntax : RANK () OVER ( < order_by_clause > )

For example if in sample DB “Adventure work” we want to find top 10 products according to unit price then we can use the query as

Select Distinct ProductID, UnitPrice , LineTotal, ReceivedQty,RANK() OVER (order by UnitPrice) as [Unit Price Rank]
from Purchasing.PurchaseOrderDetail
Where ProductID between 800 and 900
order by [Unit Price Rank]

It will return all products sorted alongwith their rank like

Databound Controls

While working in asp.net applications there are few template repeating controls available like Gridview , Datalist  and DataRepeater . know if one wants to get a single element for any of the control , e.g i want to get the value of some specific item like in shopping cart how many items in cart etc so here are some methods to go through the elements of these controls.

Method 1
  1. click Event of that control , or selected index change event of that control
ImageButton btnbid = (ImageButton)sender;
        int id = Convert.ToInt16(btnbid.CommandArgument);
Method 2
 2. foreach (GridViewRow row in GridView1.Rows)
    {
        // Selects the text from the TextBox
        // which is inside the GridView control
        string textBoxText = ((TextBox)row.FindControl("TextBox1")).Text;
        Response.Write(textBoxText);
Method 3
 private void ChangeBtn_Click(object sender, EventArgs e)
    {
        Control x = MyRepeater.Items[0].FindControl("Message");
        if (x != null) list = WalkContainers(x);
        MyRepeater.DataSource = list;
        MyRepeater.DataBind();
    }
    private ArrayList WalkContainers(Control ctl)
    {
        ArrayList ret = new ArrayList();
        Control parent = ctl.NamingContainer;
        if (parent != null)
        {
            ArrayList sublist = WalkContainers(parent);
            for (int j = 0; j < sublist.Count; j++) ret.Add(sublist[j]);
        }
        ret.Add(ctl.GetType().Name);
        return ret;
    }
Method 4 :
((RepeaterItem)e.Item.NamingContainer.NamingContainer).DataItem
Method 5 :
GridViewRow gr1 = (GridViewRow)((ImageButton)sender).NamingContainer;
Method 6 :
void AuthorsGridView_RowDataBound (Object sender, GridViewRowEventArgs e)
  {
    // Check for a row in edit mode.
    if(e.Row.RowState == DataControlRowState.Edit)
    {
      // Preselect the DropDownList control with the state value
      // for the current row.
 
      // Retrieve the underlying data item. In this example
      // the underlying data item is a DataRowView object. 
      DataRowView rowView = (DataRowView)e.Row.DataItem;
 
      // Retrieve the state value for the current row. 
      String state = rowView["state"].ToString();
 
      // Retrieve the DropDownList control from the current row. 
      DropDownList list = (DropDownList)e.Row.FindControl("StatesList");
 
      // Find the ListItem object in the DropDownList control with the 
      // state value and select the item.
      ListItem item = list.Items.FindByText(state);
      list.SelectedIndex = list.Items.IndexOf(item);
    }
  }