Tuesday, December 20

GridView within GridView

While building a web project, we may often come across the situation of displaying records or other types of data. We can do it in the best way by using DataGrid/GridView. It's very simple and requires only a knowledge of C#, of web application building and of SQL 2005.
"A picture is worth 1000 words!!" so I have explained a lot of things using pictures. I hope you will find it really easy. This is roughly what the final result looks like...
10.JPG

Design Info

First of all is the MasterGrid. You can design it as...
4.JPG It has two columns, as shown. Here's a description of column zero...
3.JPG Label1 is databound to Emp_dept_name, which I have described after the design info. MasterGrid column[1] can be designed as...
5.JPG In ItemTemplate we have the ChildGrid. All the columns are template columns that contain a label (individually bound to fields in Emp_table, which is also described after the design info, as per the column name) in their ItemTemplate. All the columns have TextBoxes in their FooterTemplate. These are exposed when the New (in the Modify column) button is clicked. A Modify column that will be visible only when the Edit button in the 0th column of MasterGrid is clicked can be designed as...
6.JPG I think that's sufficient to let you design all that is required.
NOTE: The DataKeyNames property of MasterGrid is set to Emp_dept_name.

Data Source

For the data I have used, see the two data tables Emp_table and Emp_dept, which you can create in SQL looking at the picture below...
9.JPG
8.JPG You can execute these queries. In which case, choose the database name first.
30.JPG I have used an SQL procedure...
7.JPG You can save this using the procedure name (BindMasterGrid) itself.
NOTE: I have used two different tables. The databound <Label1> in the Department column is bound to Emp_dept_name in the Emp_dept table. For the rest of the work, Emp_table is used.

Using the Code

Coming to the coding part: don't forget to use the correct database connection string. Put it as a parameter at the time of declaring the SQL connection. Also, make sure that the web.config file contains the correct connection string.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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;

public partial class _Default : System.Web.UI.Page 
{
    SqlConnection con=new SqlConnection ("
        Your database connection string here(
        under which you create Emp_table,Emp_dept tables)");
    DataView dv=new DataView();
    
    //
    //
    //*******************Use of dataview to be noted here*************************
    //
    //
    
    protected void Page_Load(object sender, EventArgs e)
    {
        dv = ((DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty));
        Page.MaintainScrollPositionOnPostBack = true;
        if (!Page.IsPostBack)
        {
            BindMasterGrid();
            BindChildGrid();
            
        }
    }
    
    //
    //To bind MasterGrid the use of strored procedure named-----BindMasterGrid----
    //
    //

    private void BindMasterGrid()
    {
        SqlCommand cmd = new SqlCommand("BindMasterGrid",con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();
        da.Fill(ds);

        MasterGrid.DataSource = ds;
        MasterGrid.DataBind();
        
    }

    //Before the GridView control can be rendered, each row in the control must be 
    //bound to a record in the data source.The RowDataBound event is raised when a
    //data row (represented by a GridViewRow object)is bound to data in the GridView 
    //control.
  
    protected void MasterGrid_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        GridView gr;
        if (e.Row.RowType == DataControlRowType.DataRow)
        {

            gr = ((GridView)e.Row.FindControl("ChildGrid"));
            dv.RowFilter = "Emp_dept=" + Convert.ToString(
                MasterGrid.DataKeys[e.Row.RowIndex].Value);
            gr.DataSource = dv;
            gr.DataBind();
        }
    }

    //
    //Use of Select statement to bind the ChildGrid
    //
    //

    private void BindChildGrid()
    {

        for (int i = 0; i < MasterGrid.Rows.Count; i++)
        {

            ((GridView)MasterGrid.Rows[i].Cells[1].Controls[1]).DataSource = null;
            Label lbl1 = (Label)(MasterGrid.Rows[i].Cells[0].Controls[3]);
            DataSet ds1 = new DataSet();
            SqlCommand cmd = 
                new SqlCommand(
                "SELECT Emp_no, Emp_name, Emp_sal FROM Emp_table where Emp_dept ='" + 
                lbl1.Text + "'", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText, con);
            da.Fill(ds1, "Emp_table");
            ((GridView)MasterGrid.Rows[i].Cells[1].Controls[1]).DataSource = ds1;
            ((GridView)MasterGrid.Rows[i].Cells[1].Controls[1]).AllowSorting = true;
            ((GridView)MasterGrid.Rows[i].Cells[1].Controls[1]).DataBind();

        }
    }

    //
    //The event below is fired when "Edit" button in Department column is clicked
    //
    //The point to be noted here is that----we store the index of the row in which
    //Edit button was clicked----using Sessions.
    //
    //Modify column in the ChildGrid apears
    //

    protected void MasterGrid_RowEditing(object sender, GridViewEditEventArgs e)
    {  
        int indx = e.NewEditIndex;
        Session["ind"] = indx;

        int i = (int)Session["ind"];
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);
        Childgrid.Columns[3].Visible = true;
        MasterGrid.Rows[i].FindControl("CancelMaster").Visible = true;

    }
20.JPG
//
    //The event below is fired when "Edit" button in Modify column of CildGrid is clicked
    //
    //

    protected void ChildGrid_RowEditing(object sender, GridViewEditEventArgs e)
    {
        int i = (int)Session["ind"]; 
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);
        
        Childgrid.EditIndex = e.NewEditIndex;
        BindChildGrid();    
    }
    
    //
    //The event below is fired when "Cancel" button in Department column is clicked
    //
    //

    protected void MasterGrid_RowCancelingEdit(
        object sender, GridViewCancelEditEventArgs e)
    {
        int i = (int)Session["ind"];
        MasterGrid.Rows[i].FindControl("CancelMaster").Visible = false;
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);
        Childgrid.Columns[3].Visible = false;
        Childgrid.EditIndex = -1;
        BindMasterGrid();
        BindChildGrid();

    }

    //
    //The event below is fired when "Cancel" button in 
    //Modify column(ChildGrid) is clicked
    //

    protected void ChildGrid_RowCancelingEdit(object sender, 
        GridViewCancelEditEventArgs e)
    {
        int i = (int)Session["ind"];
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);
        Childgrid.EditIndex = -1;
        BindChildGrid();
        
    }

    //
    //To update the editing done in the ChildGrid 
    //
    //

    protected void ChildGrid_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int i = (int)Session["ind"];
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);
        
        int empno = 
            Convert.ToInt16(((Label)Childgrid.Rows[e.RowIndex].FindControl(
            "Label1")).Text);
        string empname = 
            ((TextBox)Childgrid.Rows[e.RowIndex].FindControl("TextBox2")).Text;
        double salary = 
            Convert.ToDouble(((
            TextBox)Childgrid.Rows[e.RowIndex].FindControl("TextBox3")).Text);
        
        SqlCommand cmd = 
            new SqlCommand("Update Emp_table set Emp_name='" + 
            empname + "',Emp_sal='" + salary +"'where Emp_no='" + empno+"'", con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        Childgrid.EditIndex = -1;
        BindChildGrid();

    }

    //
    //Delete button will fire event below to delete a row in ChildGrid
    //
    //

    protected void ChildGrid_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int i = (int)Session["ind"];
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);
        int empno = 
            Convert.ToInt16(((Label)Childgrid.Rows[e.RowIndex].FindControl(
            "Label1")).Text);
        SqlCommand cmd = 
            new SqlCommand("Delete from Emp_table where Emp_no='" + empno + "'", con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        Childgrid.EditIndex = -1;
        BindChildGrid();

    }
 
    //
    //
    //Add a record in selected department
    //

    protected void Add_Click(object sender, EventArgs e)
    {
        
        int i = (int)Session["ind"];
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);
        int empno = 
            Convert.ToInt16(((TextBox)Childgrid.FooterRow.FindControl("TextBox4")).Text);
        string empname = ((TextBox)Childgrid.FooterRow.FindControl("TextBox5")).Text;
        double salary = 
            Convert.ToDouble(((
            TextBox)Childgrid.FooterRow.FindControl("TextBox6")).Text);
        string deptname = ((Label)MasterGrid.Rows[i].FindControl("Label1")).Text;
        SqlCommand cmd = 
            new SqlCommand("Insert into Emp_table values('" + 
            empno + "','" + empname + "','" + deptname + "','" + salary + "')", con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        BindChildGrid();
        ((TextBox)Childgrid.FooterRow.FindControl("TextBox4")).Visible = false;
        ((TextBox)Childgrid.FooterRow.FindControl("TExtBox5")).Visible = false;
        ((TextBox)Childgrid.FooterRow.FindControl("TextBox6")).Visible = false;

    }

    //
    //New button is used to Expose TextBoxes to Enter new record
    //
    //

    protected void New_Click(object sender, EventArgs e)
    {
        int i = (int)Session["ind"];
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);

        BindChildGrid();
        ((TextBox)Childgrid.FooterRow.FindControl("TextBox4")).Visible = true;
        ((TextBox)Childgrid.FooterRow.FindControl("TExtBox5")).Visible = true;
        ((TextBox)Childgrid.FooterRow.FindControl("TextBox6")).Visible = true;
        ((LinkButton)Childgrid.FooterRow.FindControl("New")).Visible = false;
        ((LinkButton)Childgrid.FooterRow.FindControl("Add")).Visible = true;
        ((LinkButton)Childgrid.FooterRow.FindControl("Cancel")).Visible = true;
    }
    
    //
    //When Cancel button in Modify column is clicked
    //
    //
    
    protected void Cancel_Click(object sender, EventArgs e)
    {
        int i = (int)Session["ind"];
        GridView Childgrid = (GridView)(MasterGrid.Rows[i].Cells[1].Controls[1]);

        BindChildGrid();
        ((TextBox)Childgrid.FooterRow.FindControl("TextBox4")).Visible = false;
        ((TextBox)Childgrid.FooterRow.FindControl("TExtBox5")).Visible = false;
        ((TextBox)Childgrid.FooterRow.FindControl("TextBox6")).Visible = false;
        ((LinkButton)Childgrid.FooterRow.FindControl("New")).Visible = true;
        ((LinkButton)Childgrid.FooterRow.FindControl("Add")).Visible = false;
        ((LinkButton)Childgrid.FooterRow.FindControl("Cancel")).Visible = false;
    }
}

Conclusion

I found it very interesting working with grids. We can extend this by further inserting a grid within a grid, although in real use we include various other features like JavaScript, etc. so as to avoid the transfer of bulk again and again over the web. There might be many other ways to do this efficiently, but as I am also at a beginner level, it will be best to start with this. I welcome any queries and responses from your side. Keep on making sincere efforts and you will be a good programmer one day.
Thanking you all,
shibashish mohanty

No comments:

Post a Comment

Please don't spam, spam comments is not allowed here.

.

ShibashishMnty
shibashish mohanty