How to collapse and expand rows of a datagrid using C#, ASP.NET, and Javascript.

April 28, 2008 at 7:24 am Leave a comment

The following article will describe the source code, which will allow programmers to create a HGrid. It will allow master/slave or master/child data to be shown. It also includes the features to collapse and expand data on the client side using JavaScript. The original code was done in Visual Studio 2003 using framework 1.1. This is the second version of this grid.

What are the controls needed?

In order to create a HGrid on your own, only a datagrid, and a hidden textbox are needed. In the example I have included the following are the components:

1.    DataGrid (ID: DataGrid1)

The datagrid will hold the data for the master/slave or master/child data. We will also include the feature to expand/collapse (show/hide) on the grid itself. In the example I use, I will be connecting to the NorthWind database of a SQL Database.

2. TextBox (ID: txtExpandedDivs, Width = “0px” or in a div which has style.display: ’none’)

The width is set to 0px so that the users cannot see it, but it exists on the page as a hidden text field. I chose to do it this way, so users can change the width and view the contents of the textbox

3. Button (ID: ButtonSample)

This button is outside of the grid. I created this button, but most likely when users create a page, the page will consist of many controls which will call the page to reload. We have to make sure the contents of the grid are the same, once the page is refreshed.

4. Labels (ID: LabelPostBack)

This label will show the contents of the hidden textbox (txtExpandedDivs) once the (ButtonSample) is clicked.

5. Labels (ID: LabelTitle)

This label is just show the database we are connecting to. e.g.. NorthWind

6. Labels (ID: LabelWhatHappens)

This label is a static label which always shows the message: “What are we storing in the hidden textbox field (txtExpandedDivs Textbox Control)?”

7. Table (ID: Table1)

This table has all the above controls in it.

Details of the DataGrid

Here is the html of the datagrid below. It has paging (10 per page), sorting, and autogeneratedcolumns = false. The columns we have are:

  1. HyperLinkColumn: “+”
  2. BoundColumn: “OrderID”, “CustomerID”, “EmployeeID”, “ShipName”, “ShipAddress”, “ShipCity”, “ShipRegion”, “ShipPostCode”, “ShipCountry”.


<asp:datagrid id=”DataGrid1″ runat=”server” ForeColor=”Black” AllowSorting=”True” AllowPaging=”True” GridLines=”Vertical” CellPadding=”3″ BackColor=”White” BorderWidth=”1px” BorderStyle=”Solid” BorderColor=”#999999″ Width=”100%” AutoGenerateColumns=”
False“>

<SelectedItemStyle Font-Bold=”True” HorizontalAlign=”Left” ForeColor=”White”

VerticalAlign=”Top” BackColor=”#000099″>

</SelectedItemStyle>

<EditItemStyle HorizontalAlign=”Left” VerticalAlign=”Top”></EditItemStyle>

<AlternatingItemStyle HorizontalAlign=”Left” ackColor=”#CCCCCC”></AlternatingItemStyle>

<ItemStyle Font-Size=”8pt” Font-Names=”Tahoma” HorizontalAlign=”Left”

VerticalAlign=”Top”>

</ItemStyle>

<HeaderStyle Font-Bold=”True” ForeColor=”White” BackColor=”Black”></HeaderStyle>

<FooterStyle BackColor=”#CCCCCC”></FooterStyle>

<Columns>

<asp:HyperLinkColumn Text=”+”></asp:HyperLinkColumn>

<asp:BoundColumn Visible=”False” DataField=”OrderID” ReadOnly=”True”

HeaderText=”OrderID”>

</asp:BoundColumn>

<asp:BoundColumn Visible=”False” DataField=”CustomerID

HeaderText=”CustomerID”>

</asp:BoundColumn>

<asp:BoundColumn Visible=”False” DataField=”EmployeeID

HeaderText=”EmployeeID”>

</asp:BoundColumn>

<asp:BoundColumn DataField=”ShipName” SortExpression=”ShipName

HeaderText=”Name”>

</asp:BoundColumn>

<asp:BoundColumn DataField=”ShipAddress” SortExpression=”ShipAddress

HeaderText=”Address”>

</asp:BoundColumn>

<asp:BoundColumn DataField=”ShipCity” SortExpression=”ShipCity

HeaderText=”City”>

</asp:BoundColumn>

<asp:BoundColumn DataField=”ShipRegion” SortExpression=”ShipRegion

HeaderText=”Region”>

</asp:BoundColumn>

<asp:BoundColumn DataField=”ShipPostalCode” SortExpression=”ShipPostalCode

HeaderText=”Postal”>

</asp:BoundColumn>

<asp:BoundColumn DataField=”ShipCountry” SortExpression=”ShipCountry

HeaderText=”Country”>

</asp:BoundColumn>

</Columns>

<PagerStyle HorizontalAlign=”Center” ForeColor=”Black” BackColor=”#999999″

Mode=”NumericPages”>

</PagerStyle>

</asp:datagrid>

Language used for this code is: ASP.NET, C#. There are many tools to convert to VB.NET if needed. The source code is very descriptive and can easily be traced.

Code:

private string connectionstring = “server=SERVERNAME;database=Northwind;uid=USERID;password=PASSWORD;”;

private void Page_Load(object sender, System.EventArgs e)
{

//Clear the contents of the Label
this.LabelPostBack.Text = “”;

if ( !Page.IsPostBack )
{

BindData(); //Bind Master Details
}
else
{

for ( int i = 0; i < this.DataGrid1.Items.Count; i++ )
{
//After Postback ID’s get lost. Javascript will not //work without it, so we must set them back.
this.DataGrid1.Items[i].Cells[0].ID = “CellInfo” + i.ToString();
}

//If it is a postback that is not from the grid, we have to
//expand the rows the user had expanded before. We have to
//check first who called this postback by checking the
//Event Target. The reason we check this, is because we
//don’t need to expand if it is changing the page of the
//datagrid, or sorting, etc…

if ( Request[“__EVENTTARGET”] != null)
{
string strEventTarget = Request[“__EVENTTARGET”].ToString().ToLower();

//datagrid1 is the name of the grid. If you modify
//the grid name, make sure to modify this if
//statement.

if ( strEventTarget.IndexOf(”datagrid1″) == -1)
{

if (!Page.IsStartupScriptRegistered(”ShowDataJS”))
{
Page.RegisterStartupScript( “ShowDataJS”, “<script>ShowExpandedDivInfo(’” + this.txtExpandedDivs.ClientID + “‘,’” +
this.DataGrid1.ClientID + “‘);</script>”);
}
}
}
}
}

private void ButtonSample_Click(object sender, System.EventArgs e)
{

LabelPostBack.ForeColor = System.Drawing.Color.DarkRed;
if ( txtExpandedDivs.Text.Length == 0 )
{
LabelPostBack.Text = “A Postback has occurred. txtExpandedDivs has no content!”;
}
else
{
LabelPostBack.Text = “A Postback has occurred. txtExpandedDivs has contents: <BR/><B>” +
this.txtExpandedDivs.Text + “</B>”;
}
}
#region Database Methods

private void BindData()
{

//======Query For Master Rows=======
string QueryString = “SELECT OrderID, CustomerID, EmployeeID,
ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM Orders”;

if ( ViewState[“sortby”] != null )
{
QueryString = QueryString + ” order by ” + ViewState[“sortby”].ToString();
}

//==============================

System.Data.SqlClient.SqlConnection conn = new
System.Data.SqlClient.SqlConnection();

try
{
conn.ConnectionString = connectionstring;
if ( conn.State == System.Data.ConnectionState.Closed )
{
conn.Open();
}

System.Data.SqlClient.SqlDataAdapter adapter = new
System.Data.SqlClient.SqlDataAdapter( QueryString, conn);

DataSet ds = new DataSet();
adapter.Fill( ds );
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
}
catch( Exception ex1 )
{
Response.Write( “An error has occurred: ” );
Response.Write( ex1.Message.ToString() );
Response.End();
}
finally
{
if ( conn.State == System.Data.ConnectionState.Open )
{
conn.Close();
}
}
}

private DataSet RunQuery(string QueryString)
{
System.Data.SqlClient.SqlConnection conn = new
System.Data.SqlClient.SqlConnection();

try
{
conn.ConnectionString = connectionstring;
if ( conn.State == System.Data.ConnectionState.Closed )
{
conn.Open();
}

System.Data.SqlClient.SqlDataAdapter adapter = new
System.Data.SqlClient.SqlDataAdapter( QueryString, conn);

DataSet ds = new DataSet();
adapter.Fill( ds );
return ds;
}
catch(Exception ex1)
{
Response.Write(”An Error has occurred.<BR />”);
Response.Write(ex1.Message.ToString());
Response.End();

//This line below will never execute.
return null;
}
finally
{
if ( conn.State == System.Data.ConnectionState.Open )
{
conn.Close();
}
}
}

#endregion

#region Datagrid Methods

private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
//If your page size is 10, only 10 sub queries will be done.
if ( e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem )
{

string DetailsQuery = “SELECT C.CompanyName, OD.UnitPrice, ” +
“OD.Quantity, OD.Discount, E.FirstName, ” +
“E.LastName “FROM [Order Details] as OD, ” +
“[Customers] as C, [Orders] as O, ” +
“[Employees] as E ” + ” where OD.OrderID = ” +
“O.OrderID and O.CustomerID = ” +
“C.CustomerID and O.EmployeeID = E.EmployeeID and ” +
“O.OrderID = ‘” + e.Item.Cells[1].Text + “‘ and ” +
“C.CustomerID = ‘” + e.Item.Cells[2].Text + “‘ and ” +
“E.EmployeeID = ‘” + e.Item.Cells[3].Text + “‘”;

//Here I am grabbing the additional data and putting it
//into mini datagrids…
//If you wish to just use labels, or other controls, just
//bind the data as you
//wish, and render to html as I did.

DataSet ds = this.RunQuery(DetailsQuery);
DataGrid NewDg = new DataGrid();
NewDg.AutoGenerateColumns = true;
NewDg.Width = Unit.Percentage(100.00);
NewDg.DataSource = ds;
NewDg.DataBind();

SetProps(NewDg);

System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
NewDg.RenderControl( htw );

string DivStart = “<DIV id=’uniquename” +
e.Item.ItemIndex.ToString() +“‘ style=’DISPLAY: none;’>”;

string DivBody = sw.ToString();
string DivEnd = “</DIV>”;
string FullDIV = DivStart + DivBody + DivEnd;

int LastCellPosition = e.Item.Cells.Count – 1;
int NewCellPosition = e.Item.Cells.Count – 2;

e.Item.Cells[0].ID = “CellInfo” + e.Item.ItemIndex.ToString();

if (e.Item.ItemType == ListItemType.Item)
{
e.Item.Cells[LastCellPosition].Text = e.Item.Cells[LastCellPosition].Text +
“</td><tr><td bgcolor=’f5f5f5′></td><td colspan=’” +
NewCellPosition + “‘>” + FullDIV;
}
else
{
e.Item.Cells[LastCellPosition].Text = e.Item.Cells[LastCellPosition].Text +
“</td><tr><td bgcolor=’d3d3d3′></td><td colspan=’” +
NewCellPosition +”‘>” + FullDIV;
}

e.Item.Cells[0].Attributes[“onclick”] = “HideShowPanel(’uniquename” +
e.Item.ItemIndex.ToString() + “‘); ChangePlusMinusText(’” +
e.Item.Cells[0].ClientID + “‘); SetExpandedDIVInfo(’” +
e.Item.Cells[0].ClientID + “‘,’” + this.txtExpandedDivs.ClientID +
“‘, ‘uniquename” + e.Item.ItemIndex.ToString() + “‘);”;

e.Item.Cells[0].Attributes[“onmouseover”] = “this.style.cursor=’pointer’”;

e.Item.Cells[0].Attributes[“onmouseout”] = “this.style.cursor=’pointer’”;
}
}

private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
//clean up expanded records.
this.txtExpandedDivs.Text = “”;
DataGrid1.CurrentPageIndex = e.NewPageIndex;
BindData();
}

private void DataGrid1_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
this.txtExpandedDivs.Text = “”;
string sortby = “[” + e.SortExpression + “]“;

if ( ViewState[“sortby”] == null )
{
sortby = sortby + ” ASC”;
ViewState[“sortby”] = sortby;
BindData();
}
else
{
if ( ViewState[“sortby”].ToString() == sortby + ” ASC”)
{
sortby = sortby + ” DESC”;
ViewState[“sortby”] = sortby;
BindData();
}
else if ( ViewState[“sortby”].ToString() == sortby + ” DESC”)
{
sortby = sortby + ” ASC”;
ViewState[“sortby”] = sortby;
BindData();
}
else
{
sortby = sortby + ” ASC”;
ViewState[“sortby”] = sortby;
BindData();
}
}
}

public void SetProps(System.Web.UI.WebControls.DataGrid DG)
{
/***************************************************************/
DG.Font.Size = 8;
DG.Font.Bold = false;
DG.Font.Name = “tahoma”;
/*****************Professional*****************/
//Border Props
DG.GridLines = GridLines.Both;
DG.CellPadding = 3;
DG.CellSpacing = 0;
DG.BorderColor = System.Drawing.Color.FromName(”#CCCCCC”);
DG.BorderWidth = System.Web.UI.WebControls.Unit.Pixel(1);

//Header Props
DG.HeaderStyle.BackColor = System.Drawing.Color.SteelBlue;
DG.HeaderStyle.ForeColor = System.Drawing.Color.White;
DG.HeaderStyle.Font.Bold = true;
DG.HeaderStyle.Font.Size = 8;
DG.HeaderStyle.Font.Name = “tahoma”;

DG.ItemStyle.BackColor = System.Drawing.Color.LightSteelBlue;
}
#endregion

Using the code with your database

In order to use the above code, just modify three things, and your all set:

  1. In the BindData() Method, insert your master query to your database.
  2. In the DataGrid Item_DataBound Method, modify the subquery to your database.
  3. If you modified the grid name, change it in the code as well. Just find and replace the old datagrid name: “datagrid1” to your new datagrid id.
  4. Compile, and Run!!!
Advertisements

Entry filed under: c-sharp. Tags: , , , .

Web Browser/Content in a C# Application MySQL JDBC Driver. Connect to MySQL from Java

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


April 2008
M T W T F S S
    Jun »
 123456
78910111213
14151617181920
21222324252627
282930  

%d bloggers like this: