Assembly Cache Viewer (Shfusion.dll)

The contents of GAC (Global Assembly Cache) are displayed by using Shfusion.dll

 

[Fusion is the code name for assembly binder in .NET Framework. Fusion binds the appropriate assemblies (from bin folder or from GAC or from any network etc.) at load time or at run time.]

 

Shfusion.dll causes the contents of the assembly folder to appear differently than the contents of normal windows folders. Assembly's  name, assembly's type, version, culture, and public key token are displayed in GAC.

 

We cant directly paste an assembly into assembly folder i.e into GAC. First the assembly must be strongly-named & then it can be deployed into GAC using drag & drop method or by using GACUtil tool. (gacutil -i)

 

 

 

 

 

 

 

 

SQL : Finding Nth highest salary in different ways

Query to find  2nd highest salary:

 

 1. Select max( salary) from emp where salary < (Select max(salary) from emp) 

 

 2. select  max(salary) from emp  where salary not in(select  max(salary) from emp)

 

You can write the query to find the Nth max salary details as follows, (This logic is called co-related sub queries, this acts as a for loop in a for loop.

In case of Sub Queries, the inner query gets executed only once. In co-related sub-queries for each record selected by outer query, inner query completely gets executed. So its time-complexity is O(n square)

 

Select * from Employee E1
where (N-1) = (Select count(distinct(E2.Salary)) From Employee E2
Where E2.salary > E1.Salary) 

 

ex:  to find 3rd highest salary 

Select * from Emp E1
where 2 = (Select count(E2.Salary) From Emp E2
Where E2.salary > E1.Salary)

 

 

The following SQL will also return the top ten employees by salary:

SELECT   EMPNO, LASTNAME, FIRSTNME, SALARY

FROM     

EMP A

WHERE 10 > (SELECT COUNT(*)

            FROM   EMP B

            WHERE  A.SALARY < B.SALARY

            AND    B.SALARY IS NOT NULL)

ORDER BY SALARY DESC;

 

 

another example: 

SELECT   distinct EMPid, SALARY

 

FROM     EMP A

 

WHERE 3 >(SELECT COUNT(*)

 

            FROM   EMP B

 

            WHERE  A.SALARY < B.SALARY

 

            AND    B.SALARY IS NOT NULL)

 

ORDER BY SALARY DESC;

 

Beauty of Code Snippets

Although I am using Visual Studio.NET 2005 from more than an year & although I am aware of code snippets in VS.NET, never used it until recently one of my friends told me to find its beauty.

just type the keyword 'while' in your visual studio.net 2005 editor, you can see this something like this:




after typing the word while press tab twice & You will find its complete syntax on your page.


This is the code that I have got in visual studio after typing the word exception and tab twice. AWESOME!! Right!


--------------------------------------------------------------------------
[global::System.Serializable]
public class MyException : Exception
{
//
// For guidelines regarding the creation of new exception types, see
// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconerrorraisinghandlingguidelines.asp
// and
// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncscol/html/csharp07192001.asp
//

public MyException() { }
public MyException(string message) : base(message) { }
public MyException(string message, Exception inner) : base(message, inner) { }
protected MyException(
System.Runtime.Serialization.SerializationInfo info,
System.Runtime.Serialization.StreamingContext context)
: base(info, context) { }
}


-------------------------------------------------------------------------
code for creating custom attributes (type in attribute and tab twice)
-------------------------------------------------------------------------
[global::System.AttributeUsage(AttributeTargets.All, Inherited = false, AllowMultiple = true)]
sealed class MyAttribute : Attribute
{
// See the attribute guidelines at
// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconusingattributeclasses.asp
readonly string _positionalString;
int _namedInt;

// This is a positional argument.
public MyAttribute(string positionalString)
{
this._positionalString = positionalString;

// TODO: Implement code here.
throw new Exception("The method or operation is not implemented.");
}
public string PositionalString
{
get
{
return this._positionalString;
}
}
// This is a named argument.
public int NamedInt
{
get
{
return this._namedInt;
}
set
{
this._namedInt = value;
}
}
}
--------------------------------------------------------------------------

Similarly we can explore iterators, for, while, interfaces etc...


Thank you my best friend for reminding me of this feature!

Hierarchical Grid View (3-levels)

Recently we had a requirement with one of the customers where we have to design a dashboard that shows sales data.

At a glance we have to show his the sales data of all regions. (He can filter on region any way!)

When he clicks on a region, we should show all the areas in that region and the sales data of each area.

Again he can click on an area, and can see the sales data of individual dealers in that area.

Also when it is completely expanded and when he scrolls down to see the data, headers should remain in the same place. (i.e, just like frozen headers in excel)

Alas! Thats the requirement. Thank god! No further level of hierarchy!!!!

We did the trick with 3 Grid views & some javascript & css.
--------------------------------------------------------------------------

To test this, create a new web page using VS.NET 2005 using C#. Copy the .aspx content of this page into your page & the .aspx.cs into your .aspx.cs file. Also dont forget to copy the style sheet & javascript into .aspx file. Simply run & test this page. For demonistration purpose we have not connected to database here, we are generating data from code behind.
--------------------------------------------------------------------------
: collapse.gif



: expand.gif
--------------------------------------------------------------------------


--------------------------------------------------------------------------
Here is the CSS class that we have used:
-------------------------------------------------------------------------------

<style type="text/css">

table {
/*table-layout: fixed;*/
border-collapse: collapse;
font-size:10px;
font-family:Verdana, Arial, Helvetica, sans-serif;
color:#000000;
}

/*This is for freezing the Header Rows*/
.fixedheadercell
{
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:10px;
color:#FFFFFF;
LINE-HEIGHT: 13px;
font-weight:bold;
/*text-align:center;*/
background-color:#3399FF;

position: relative;
/*top: expression(this.offsetParent.scrollTop);*/
top:expression(document.getElementById('p1').scrollTop-2);

padding-right: 2px;
padding-left: 2px;
padding-bottom: 2px;
padding-top: 2px;
}

/*This is for freezing the columns*/
.fixedcolumn
{
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:10px;
color:#FFFFFF;
LINE-HEIGHT: 13px;
font-weight:bold;
background-color:#3399FF;
position: relative;
/*top: expression(this.offsetParent.scrollTop);*/
left:expression(document.getElementById('p1').scrollLeft-2);
padding-right: 2px;
padding-left: 2px;
padding-bottom: 2px;
padding-top: 2px;
}

.geo_view_reg{
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:9px;
color:#2D469F;
LINE-HEIGHT: 13px;
font-weight:bold;
text-align:left;
background-color:#8DCFF3;
border-top:solid;
border-top-width:1px;
border-top-color:#FFFFFF;
}

.geo_view_col {
font-family:Verdana, Arial, Helvetica, sans-serif;
font-size:9px;
color:#2D469F;
LINE-HEIGHT: 13px;
font-weight:bold;
background-color:#DFEFFF;
border-top:solid;
border-top-width:1px;
border-top-color:#FFFFFF;
}

</style>

-------------------------------------------------------------------------------
Java script
-------------------------------------------------------------------------------
<script type="text/javascript">

function firstlevelexpand(id)
{
//GridView1_ctl02_ChildGridView1
if(getimagename(document.getElementById('GridView1$ctl0'+ id + '$btnRegion').src) == "expand.gif")
{
document.getElementById('GridView1$ctl0'+ id + '$btnRegion').src = "collapse.gif";
document.getElementById('GridView1_ctl0'+ id + '_ChildGridView1').style.display = "none";
}
else
{
document.getElementById('GridView1$ctl0'+ id + '$btnRegion').src = "expand.gif";
document.getElementById('GridView1_ctl0'+ id + '_ChildGridView1').style.display = "";
}
return false;
}


function collapseAll(count)
{
debugger;
var i;
var index;
for(i=2;i<count+2;i++)
{
if(i<10)
index = '0' + i;
else
index = i;
if(eval(document.getElementById('GridView1_ctl'+ index + '_ChildGridView1')))
{
document.getElementById('GridView1$ctl'+ index + '$btnRegion').src = "collapse.gif";
document.getElementById('GridView1_ctl'+ index + '_ChildGridView1').style.display = "none";
}
}
return false;
}

function secondlevelexpand(id,id2)
{

if(eval(document.getElementById(id2)))
{
if(getimagename(document.getElementById(id).src) == "expand.gif")
{
document.getElementById(id).src = "collapse.gif";
document.getElementById(id2).style.display = "none";
}
else
{
document.getElementById(id).src = "expand.gif";
document.getElementById(id2).style.display = "";
}
return false;
}
else
return true;
}

function getimagename(fullpath)
{
var imagename="";
var temp = new Array();
temp = fullpath.split("/");
return temp[temp.length -1];
}

</script>


-------------------------------------------------------------------------------
ASPX CODE
-------------------------------------------------------------------------------

<form id="form1" runat="server">
<table>
<tr>
<td>
<asp:Button ID="btnExpandAll" runat="server" OnClick="Button1_Click" Text="Expand All" />
<asp:Button ID="btnCollapseAll" runat="server" OnClick="Button2_Click" Text="Collapse All" />
</td>
</tr>
</table>
<div>
<%--<asp:SqlDataSource ID="ParentSqlDataSource" runat="server"
ConnectionString="Provider=SQLOLEDB;Data Source=16.138.50.177;Integrated Security=SSPI;Initial Catalog=Maps"
ProviderName="System.Data.OleDb" SelectCommand="select region ,area,dealer,ALS_Target,ALS_MTD,ALS_MTD_Last_Month,ALS_MTD_Last_Year,ALS_YTD,ALS_YTDLY from RegionSales">
</asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Provider=SQLOLEDB;Data Source=16.138.50.177;Integrated Security=SSPI;Initial Catalog=Maps"
ProviderName="System.Data.OleDb">
</asp:SqlDataSource>--%>


--------------------------------------------------------------------------
<asp:Panel ID="p1" runat="server" ScrollBars="Auto" Height="150px" BorderStyle="Groove" Width="555px">
<asp:GridView ID="GridView1" runat="server" BorderStyle="None" GridLines="None"
DataKeyNames="region" AutoGenerateColumns="False"
OnRowCommand="GridView1_RowCommand" OnRowCreated="GridView1_RowCreated" ShowFooter="true"
OnSelectedIndexChanged="GridView1_SelectedIndexChanged" ToolTip="View of Sales">
<Columns>
<asp:TemplateField>
<HeaderStyle CssClass="fixedheadercell" />
<HeaderTemplate>
<table id="tblmain" cellpadding="0" cellspacing="0" border="0" style="border-collapse:collapse" width="100%">
<thead>
<tr>
<th align="left">
<asp:Label ID="lblspace" runat="server" Text="" Width="15px"></asp:Label>
</th>
<th align="left">
<asp:Label ID="lblheadingregion" runat="server" Text="Region" Width="75px"></asp:Label>
</th>
<th align="left">
<asp:Label ID="lblheadingarea" runat="server" Text="Area" Width="80px"></asp:Label>
</th>
<th align="left">
<asp:Label ID="lblheadingdealer" runat="server" Text="Dealer" Width="80px"></asp:Label>
</th>
<th align="center">
<asp:Label ID="Label17" runat="server" Text="Target" Width="45px"></asp:Label>
</th>
<th align="center">
<asp:Label ID="Label18" runat="server" Text="MTD" Width="45px"></asp:Label>
</th>
<th align="center">
<asp:Label ID="Label19" runat="server" Text="MTDLM" Width="45px"></asp:Label>
</th>
<th align="center">
<asp:Label ID="Label20" runat="server" Text="MTDLY" Width="45px"></asp:Label>
</th>
<th align="center">
<asp:Label ID="Label21" runat="server" Text="YTD" Width="45px"></asp:Label>
</th>
<th align="center">
<asp:Label ID="Label22" runat="server" Text="YTDLY" Width="45px"></asp:Label>
</th>

</tr>
</thead>
</table>
</HeaderTemplate>
<ItemTemplate>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse:collapse;width:100%">
<tr>
<td class="geo_view_reg">
<asp:ImageButton Width="15px" ID="btnRegion" runat="server" ImageUrl="collapse.gif" CommandName="Select" />
</td>
<td class="geo_view_reg">
<asp:Label Width="75px" ID="lblRegion" runat="server" Text='<%#Eval("region")%>'></asp:Label>
</td>
<td class="geo_view_reg">
<asp:Label Width="80px" ID="lblArea1" runat="server" Text=" "></asp:Label>
</td>
<td class="geo_view_reg">
<asp:Label Width="80px" ID="lbldealer1" runat="server" Text=" "></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="lblCountry" runat="server" Text='<%# Eval("ALS_Target") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label5" runat="server" Text='<%# Eval("ALS_MTD") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label6" runat="server" Text='<%# Eval("ALS_MTD_Last_Month") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label2" runat="server" Text='<%# Eval("ALS_MTD_Last_Year") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label3" runat="server" Text='<%# Eval("ALS_YTD") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label4" runat="server" Text='<%# Eval("ALS_YTDLY") %>'></asp:Label>
</td>

</tr>

<!-- this row has the child grid and its details view-->
<tr>
<td colspan="20">



<asp:GridView ID="ChildGridView1" runat="server" GridLines="None" DataKeyNames="area" Visible="False"
OnSelectedIndexChanged="ChildGridView1_SelectedIndexChanged"
AutoGenerateColumns="False" BorderStyle="None" ShowFooter="false" ShowHeader="false" Width="100%">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<table border="0" width="100%" cellpadding="0" cellspacing="0" style="border-collapse:collapse">
<tr>
<td class="geo_view_reg">
<asp:Label ID="lblspace" runat="server" Width="75px" Text=""></asp:Label>
</td>
<td class="geo_view_reg">
<asp:ImageButton ID="btnState" runat="server" Width="15px" ImageUrl="collapse.gif" CommandName="Select" />
</td>
<td align="right" class="geo_view_reg">
<asp:Label Width="80px" ID="lblCity11" runat="server" Text='<%#Eval("Area")%>'></asp:Label>
</td>
<td align="right" class="geo_view_reg">
<asp:Label Width="80px" ID="lblCountry11" runat="server" Text=""></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="lblCountry" runat="server" Text='<%# Eval("ALS_Target") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label5" runat="server" Text='<%# Eval("ALS_MTD") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label6" runat="server" Text='<%# Eval("ALS_MTD_Last_Month") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label2" runat="server" Text='<%# Eval("ALS_MTD_Last_Year") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label3" runat="server" Text='<%# Eval("ALS_YTD") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label4" runat="server" Text='<%# Eval("ALS_YTDLY") %>'></asp:Label>
</td>

</tr>
<tr>
<td colspan="20">




-------------------------------------------------------------------------- <asp:GridView id="ChildGridView2" runat="server" GridLines="None" DataKeyNames="dealer" Visible="False"
ShowHeader="false" ShowFooter="false"
AutoGenerateColumns="False" BorderStyle="None" Width="100%">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<table border="0" cellpadding="0" cellspacing="0" width="100%" style="border-collapse:collapse">
<tr>
<td class="geo_view_reg">
<asp:Label ID="lblspace1" runat="server" Text="" Width="170px"></asp:Label>
</td>
<td align="right" class=" geo_view_reg">
<asp:Label Width="80px" ID="Label1" runat="server" Text='<%# Eval("Dealer") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="lblCountry" runat="server" Text='<%# Eval("ALS_Target") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label5" runat="server" Text='<%# Eval("ALS_MTD") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label6" runat="server" Text='<%# Eval("ALS_MTD_Last_Month") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label2" runat="server" Text='<%# Eval("ALS_MTD_Last_Year") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label3" runat="server" Text='<%# Eval("ALS_YTD") %>'></asp:Label>
</td>
<td align="right" class="geo_view_col">
<asp:Label Width="45px" ID="Label4" runat="server" Text='<%# Eval("ALS_YTDLY") %>'></asp:Label>
</td>

</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</ItemTemplate>

</asp:TemplateField>
</Columns>
</asp:GridView>
</asp:Panel>
<br />
<br />
</div>
</form>


-------------------------------------------------------------------------------
.aspx.cs
-------------------------------------------------------------------------------

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
--------------------------------------------------------------------------
public partial class HierarchicalGridView : System.Web.UI.Page
{
public DataSet childDS;
GridView childgv;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string strRegion;
DataTable dtRegion = GetRegionsDT();
GridView1.DataSource = dtRegion.DefaultView;
GridView1.DataBind();
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Label lblRegn = (Label)GridView1.Rows[i].FindControl("lblRegion");
GridView childgv = (GridView)GridView1.Rows[i].FindControl("ChildGridView1");
DataTable dt = new DataTable();

strRegion = lblRegn.Text;
dt = GetAreaTable(strRegion);

childgv.DataSource = dt.DefaultView;
childgv.DataBind();
for (int ii = 0; ii < childgv.Rows.Count; ii++)
{
GridView grndChld = (GridView)childgv.Rows[ii].FindControl("ChildGridView2");
Label lblState = (Label)childgv.Rows[ii].FindControl("lblCity11");

DataTable dt1 = new DataTable();
dt1 = GetCityDT(lblState.Text);

grndChld.DataSource = dt1.DefaultView;
grndChld.DataBind();
}
}
}
btnCollapseAll.Attributes.Add("onclick", "return collapseAll('" + GridView1.Rows.Count + "')");
}

//Row Command is equivalent to datagrid's Item Command
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
}

protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{

if (e.Row.RowType == DataControlRowType.DataRow)
{
// Retrieve the LinkButton control from the first column.
if (e.Row.Cells[0].Controls.Count > 1)
{
ImageButton expandButton = (ImageButton)e.Row.Cells[0].Controls[1];

// Set the LinkButton's CommandArgument property with the
// row's index.
//GridView1$ctl02$btnRegion
expandButton.Attributes.Add("onclick", "javascript:return firstlevelexpand('" + (e.Row.RowIndex + 2).ToString() + "');");
childgv = (GridView) e.Row.Cells[0].FindControl("ChildGridView1");
if (childgv != null)
childgv.RowCreated += ChildGridView1_RowCreated;
}
}
}

--------------------------------------------------------------------------
//To handle child grid events this is used. Dynamically I am adding events to child grid.
--------------------------------------------------------------------------
protected void ChildGridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (e.Row.Cells[0].Controls.Count > 1)
{
ImageButton expandButton = (ImageButton)e.Row.Cells[0].FindControl("btnState");
string index, id2;
id2 = ((System.Web.UI.WebControls.CompositeDataBoundControl)(sender)).Controls[0].Parent.UniqueID.ToString();
id2 = id2.Replace("$", "_");

if (e.Row.RowIndex + 2 < 10)
index = "0" + (e.Row.RowIndex + 2).ToString();
else
index = (e.Row.RowIndex + 2).ToString();
expandButton.Attributes.Add("onclick", "javascript:return secondlevelexpand('" + ((System.Web.UI.WebControls.CompositeDataBoundControl)(sender)).Controls[0].Parent.UniqueID.ToString() + "$ctl" + index + "$btnState','" + id2 + "_ctl" + index + "_ChildGridView2');");
}
}
}

--------------------------------------------------------------------------
protected void Button1_Click(object sender, EventArgs e)
{
string strRegion;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Label lblRegn = (Label)GridView1.Rows[i].FindControl("lblRegion");
ImageButton btnRegion = (ImageButton)GridView1.Rows[i].FindControl("btnRegion");
btnRegion.ImageUrl = "expand.gif";
GridView childgv = (GridView)GridView1.Rows[i].FindControl("ChildGridView1");

DataTable dt = new DataTable();
strRegion = lblRegn.Text;
dt = GetAreaTable(strRegion);

childgv.DataSource = dt.DefaultView;
childgv.DataBind();
for (int ii = 0; ii < childgv.Rows.Count; ii++)
{
GridView grndChld = (GridView)childgv.Rows[ii].FindControl("ChildGridView2");
Label lblState = (Label)childgv.Rows[ii].FindControl("lblCity11");
ImageButton btnState = (ImageButton)childgv.Rows[ii].FindControl("btnState");
btnState.ImageUrl = "expand.gif";
DataTable dt1 = new DataTable();
dt1 = GetCityDT(lblState.Text);

grndChld.DataSource = dt1.DefaultView;
grndChld.DataBind();

if (grndChld != null)
{
grndChld.Visible = true;
}
}

if (childgv != null)
{

childgv.Visible = true;
}
}
}

--------------------------------------------------------------------------
private DataTable GetAreaTable(string strRegion)
{
//DataSet ds = new DataSet();
//SqlConnection cnn = new SqlConnection("Data Source=16.138.50.177;Integrated Security=SSPI;Initial Catalog=Maps");

////Create a second DataAdapter for the Titles table.
//SqlDataAdapter cmd2 = new SqlDataAdapter("select region ,area,dealer,ALS_Target,ALS_MTD,ALS_MTD_Last_Month,ALS_MTD_Last_Year,ALS_YTD,ALS_YTDLY from AreaSales where region = '" + strRegion + "'", cnn);
//cmd2.Fill(ds, "AreaSales");
//return ds.Tables["AreaSales"];
DataSet parentDataSource = new DataSet();
parentDataSource.Tables.Add(new DataTable("AreaSales"));

parentDataSource.Tables[0].Columns.Add("region");
parentDataSource.Tables[0].Columns.Add("Area");
parentDataSource.Tables[0].Columns.Add("Dealer");
parentDataSource.Tables[0].Columns.Add("ALS_Target");
parentDataSource.Tables[0].Columns.Add("ALS_MTD");
parentDataSource.Tables[0].Columns.Add("ALS_MTD_Last_Month");
parentDataSource.Tables[0].Columns.Add("ALS_MTD_Last_Year");
parentDataSource.Tables[0].Columns.Add("ALS_YTD");
parentDataSource.Tables[0].Columns.Add("ALS_YTDLY");

DataRow dr;
if (strRegion == "North")
{
dr = parentDataSource.Tables["AreaSales"].NewRow();
dr[0] = "North";
dr[1] = "Delhi";
dr[2] = "D1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["AreaSales"].Rows.Add(dr);

dr = parentDataSource.Tables["AreaSales"].NewRow();
dr[0] = "North";
dr[1] = "Noida";
dr[2] = "D2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["AreaSales"].Rows.Add(dr);

dr = parentDataSource.Tables["AreaSales"].NewRow();
dr[0] = "North";
dr[1] = "Chandigarh";
dr[2] = "D3";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["AreaSales"].Rows.Add(dr);

}
else if (strRegion=="South")
{
dr = parentDataSource.Tables["AreaSales"].NewRow();
dr[0] = "South";
dr[1] = "Bangalore";
dr[2] = "B1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["AreaSales"].Rows.Add(dr);

dr = parentDataSource.Tables["AreaSales"].NewRow();
dr[0] = "South";
dr[1] = "Hyderabad";
dr[2] = "H2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["AreaSales"].Rows.Add(dr);

dr = parentDataSource.Tables["AreaSales"].NewRow();
dr[0] = "South";
dr[1] = "Madras";
dr[2] = "M3";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["AreaSales"].Rows.Add(dr);

}
return parentDataSource.Tables["AreaSales"];


}


--------------------------------------------------------------------------
private DataTable GetCityDT(string strRegion)
{
//DataSet ds = new DataSet();
//SqlConnection cnn = new SqlConnection("Data Source=16.138.50.177;Integrated Security=SSPI;Initial Catalog=Maps");

////Create a second DataAdapter for the Titles table.
////SqlDataAdapter cmd2 = new SqlDataAdapter("select region ,area,dealer,ALS_Target,ALS_MTD,ALS_MTD_Last_Month,ALS_MTD_Last_Year,ALS_YTD,ALS_YTDLY,MS_Target,MS_YTD,ALD_Target,ALD_YTD,RS_Target,RS_MTD,RS_MTD_Last_Month,RS_MTD_Last_Year,RS_YTD,RS_YTDLY from DealerSales where area='" + strRegion + "'", cnn);
//SqlDataAdapter cmd2 = new SqlDataAdapter("select region ,area,dealer,ALS_Target,ALS_MTD,ALS_MTD_Last_Month,ALS_MTD_Last_Year,ALS_YTD,ALS_YTDLY from DealerSales where area='" + strRegion + "'", cnn);
//cmd2.Fill(ds, "DealerSales");

DataSet parentDataSource = new DataSet();
parentDataSource.Tables.Add(new DataTable("DealerSales"));

parentDataSource.Tables[0].Columns.Add("region");
parentDataSource.Tables[0].Columns.Add("Area");
parentDataSource.Tables[0].Columns.Add("Dealer");
parentDataSource.Tables[0].Columns.Add("ALS_Target");
parentDataSource.Tables[0].Columns.Add("ALS_MTD");
parentDataSource.Tables[0].Columns.Add("ALS_MTD_Last_Month");
parentDataSource.Tables[0].Columns.Add("ALS_MTD_Last_Year");
parentDataSource.Tables[0].Columns.Add("ALS_YTD");
parentDataSource.Tables[0].Columns.Add("ALS_YTDLY");
DataRow dr;
if (strRegion == "Delhi")
{

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "North";
dr[1] = "Delhi";
dr[2] = "D1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "North";
dr[1] = "Delhi";
dr[2] = "D2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "North";
dr[1] = "Delhi";
dr[2] = "D3";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);
}
else if(strRegion=="Noida")
{
dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "North";
dr[1] = "Noida";
dr[2] = "N1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "North";
dr[1] = "Noida";
dr[2] = "N2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);


}
else if (strRegion == "Chandigarh")
{
dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "North";
dr[1] = "Chandigarh";
dr[2] = "C1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "North";
dr[1] = "Chandigarh";
dr[2] = "C2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);
}
else if (strRegion=="Bangalore")
{
dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Bangalore";
dr[2] = "B1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Bangalore";
dr[2] = "B2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Bangalore";
dr[2] = "B3";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

}
else if (strRegion == "Hyderabad")
{
dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Hyderabad";
dr[2] = "H1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Hyderabad";
dr[2] = "H2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Hyderabad";
dr[2] = "H3";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

}
else if (strRegion == "Madras")
{
dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Madras";
dr[2] = "M1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);

dr = parentDataSource.Tables["DealerSales"].NewRow();
dr[0] = "South";
dr[1] = "Madras";
dr[2] = "M2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["DealerSales"].Rows.Add(dr);



}
return parentDataSource.Tables["DealerSales"];
}

//--------------------------------------------------------------------------
//Please note we are using dummy data here (for demonstration purpose)
//--------------------------------------------------------------------------

private DataTable GetRegionsDT()
{
DataSet parentDataSource = new DataSet();
parentDataSource.Tables.Add(new DataTable("RegionSales"));

parentDataSource.Tables[0].Columns.Add("region");
parentDataSource.Tables[0].Columns.Add("Area");
parentDataSource.Tables[0].Columns.Add("Dealer");
parentDataSource.Tables[0].Columns.Add("ALS_Target");
parentDataSource.Tables[0].Columns.Add("ALS_MTD");
parentDataSource.Tables[0].Columns.Add("ALS_MTD_Last_Month");
parentDataSource.Tables[0].Columns.Add("ALS_MTD_Last_Year");
parentDataSource.Tables[0].Columns.Add("ALS_YTD");
parentDataSource.Tables[0].Columns.Add("ALS_YTDLY");
DataRow dr;


dr = parentDataSource.Tables["RegionSales"].NewRow();
dr[0] = "North";
dr[1] = "Delhi";
dr[2] = "D1";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["RegionSales"].Rows.Add(dr);

dr = parentDataSource.Tables["RegionSales"].NewRow();
dr[0] = "South";
dr[1] = "Delhi";
dr[2] = "D2";
dr[3] = "200";
dr[4] = "300";
dr[5] = "300";
dr[6] = "300";
dr[7] = "300";
dr[8] = "300";

parentDataSource.Tables["RegionSales"].Rows.Add(dr);

return parentDataSource.Tables["RegionSales"];

}

--------------------------------------------------------------------------
protected void Button2_Click(object sender, EventArgs e)
{
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridView childgv = (GridView)GridView1.Rows[i].FindControl("ChildGridView1");
ImageButton btnRegion = (ImageButton)GridView1.Rows[i].FindControl("btnRegion");
btnRegion.ImageUrl = "collapse.gif";
if (childgv != null)
{
childgv.Visible = false;
}
}

}

//We can do this in Row Command also. Anyways we are able to fulfil the requirement.
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
GridView gv = (GridView)sender;

Int32 rowIndex = GridView1.SelectedRow.RowIndex;
string strRegion;

Label lblRegn = (Label)gv.Rows[rowIndex].FindControl("lblRegion");
ImageButton btn = (ImageButton)gv.Rows[rowIndex].FindControl("btnRegion");
if (btn.ImageUrl == "collapse.gif")
{
btn.ImageUrl = "expand.gif";
}
else
{
btn.ImageUrl = "collapse.gif";
}
strRegion = lblRegn.Text;


GridView childgv = (GridView)gv.Rows[rowIndex].FindControl("ChildGridView1");

if (childgv != null)
{
childgv.Visible = !childgv.Visible;
}
//to set focus to the row in the gridview in which the expand/collapse button was clicked
Page.SetFocus(gv.Rows[rowIndex]);
}


//-----------------------------------------------------------------------------------
//For the first time it takes the postback, next time onwards we are handling in
//javascript
//-----------------------------------------------------------------------------------
//To handle child grid's expand/collpase event.
protected void ChildGridView1_SelectedIndexChanged(object sender, EventArgs e)
{

GridView gv = (GridView)sender;

Int32 rowIndex = gv.SelectedRow.RowIndex;
string strRegion;

Label lblRegn = (Label)gv.Rows[rowIndex].FindControl("lblCity11");
ImageButton btn = (ImageButton)gv.Rows[rowIndex].FindControl("btnState");
if (btn.ImageUrl == "collapse.gif")
{
btn.ImageUrl = "expand.gif";
}
else
{
btn.ImageUrl = "collapse.gif";
}
strRegion = lblRegn.Text;

GridView childgv = (GridView)gv.Rows[rowIndex].FindControl("ChildGridView2");

if (childgv != null)
{
childgv.Visible = !childgv.Visible;
}
//to set focus to the row in the gridview in which the expand/collapse button was clicked
Page.SetFocus(gv.Rows[rowIndex]);
}

}

--------------------------------------------------------------------------

Points to note:
----------------
We have multiple columns to be displayed for each gridview. We make sure that alignments of all the girdviews are correct. So we have taken only one TemplateField (The data is formatted using Table tags) to display all columns of all the three gridviews.

Some style sheets are created for freezing the header row (class=fixedheadercell) and freezing any columns (class=fixedcolumn) though we have not frozen any columns.

Also, for the Collapse functionality Java scripts are used so that there is no postback.
For expand functionality the postback happens only for the first time. Subsequently Java scripts are used for expanding also.

Note:
Please note that we have used dummy data. So the data at the region level will not be the aggregated one of its areas sales data. The same holds for aggregated data for areas.

Page.SetFocus(Control)

To maintain cursor position during postbacks (when the page has more content & there are scroll bars) we can use Page.SetFocus(controlname) in .NET 2.0

There is also another option to achive this :

set

SmartNavigation = true

for the page attribute in .aspx page (in HTML view).


SmartNavigation sometimes creates problems in production. It doesnt allow pages to be redirected to another page.

SQL SERVER date functions & some common points

1. To know current year

 

    select datepart("yy",getdate())

    select year(getdate())

2. To know current month

    

    select datepart("mm",getdate())

    select MONTH(GETDATE())

 

1. How to clear procedure cache
DBCC FREEPROCCACHE
we can use sp_recompile system procedure to recompile the stored procedure
at the time of creating the sp we can add a tag WITH RECOMPILE. This causes the stored procedure to be compiled every time when it is called.

2. How to view the contents of a stored procedure
sp_helptext

3. Difference between Logins and Users
Loing is to establish a connection to the database where as an user is to access a particular database in a sql server.

4. What is sql server Role
Roles contain specific properties & an user can belong to one or more roles.

5. delete duplicates: emp
ex: ID Name Value
1 a 10
2 b 20
3 c 30
4 b 20
5 d 40

DELETE FROM emp
where
ID NOT IN (SELECT MAX(ID) FROM emp e2 WHERE e2.Name = emp.Name AND e2.Value=emp.Value)

(or)

DELETE FROM emp
WHERE
ID > (
SELECT MIN(ID) FROM emp e2 WEHRE e2.Name = emp.Name AND e2.Value = emp.Value
)

(or)

DELETE FROM emp
WHERE
ID <
(
SELECT MAX(ID) FROM emp e2 WHERE e2.Name = emp.Name and e2.Value = emp.Value
)



case 2: If all the columns have same value for ex:
Name Value
1 10
1 10
2 20
2 20

in this scenario

select * into into #temp
(select distinct Name, Value FROM emp)
as T

delete from emp
insert into emp select * from #temp


solution2: Instead of using temp tables, we can do one more thing here.
create another table with this structure. Now add a new column called Id to this table with identity one.
Insert values from previous table to this new table. Drop the previous table. Now this case became case 1.


6. Retrieving Row number
SELECT ROW
select row_number() over(order by ename asc) as rownum from duptest


7. Retrieving Nth highest salary using co-related subquery:

SELECT * FROM emp e1
WHERE
0 = (select count(distinct sal) from emp e2 where e2.sal> e1.sal)

Inheritance Question

public interface I1
{
 void Method1();
}

 

public class A : I1
{
 public void MethodA()
 {
  Console.WriteLine("In Method A");
 } 

 

 public void Method1()
 {
  Console.WriteLine("I am in interface Method1");
 }
}

 

[STAThread]
static void Main()
{
 I1 obj = new A();
 //Now can I access I1.MethodA();
}

 

Question: Can I access MethodA from obj.

 

Answer: Nop. You can't access it.