How to add TemplateField to a gridview in the code behind?

Question

I have a DropDownList which has a list of tables. Under it there is GridView. Based on the table selected from the drop down list box, I will populate the GridView dynamically. Since the tables could have different column names, I need to create the template field for the GridView dynamically.

Following is my bind method. I have two problems:

1. I couldn’t wrap the binding part in if (!IsPostBack) since the GridView is populated based on the selection of the DropDownList, so everytime I change the selection, the columns will be duplicated.
2. And I don’t have any data, I think I need to set ItemTemplate of the tField (TemplateField), but how do I do that?

My bind method

private void BindGridView()
{
    DataSet ds = new DataSet();

    try
    {
        ds = …
        if (ds.Tables.Count > 0)
        {

            foreach (DataColumn dc in ds.Tables[0].Columns)
            {
                TemplateField tField = new TemplateField();
                tField.HeaderText = dc.ColumnName;
                GridView2.Columns.Add(tField);
            }    

            GridView2.DataSource = ds.Tables[0];
            GridView2.DataBind();
        }
        else
        {
            …
        }
    }
    catch (Exception ex)
    {
        …    
    }
}

Answer 1

There are various steps that should be taken care of:

STEP I:: Create a class inheriting the ITemplate interface. Override the method InstantiateIn() of the ITemplate interface.

STEP II:

Define a constructor for your class that takes a ListItemType object as its parameter.

STEP III::

If the Control being added to the container's ControlCollection has to be bound to some DataSource Column, then register the handler for the OnDataBinding event. When the event occurs, retrieve the text from the data source and assign it to your control. For Example, hyprLnk_DataBinding event is defined for Binding Data to your controls created inside ItemTemplate.

public class TemplateGenerator : ITemplate // Class inheriting ITemplate
{
    ListItemType type;
    string columnName;  

    public TemplateGenerator(ListItemType t, string cN)
    {           
       type = t;    
       columnName= cN;    
    }

    // Override InstantiateIn() method
    void ITemplate.InstantiateIn(System.Web.UI.Control container)
    {    
        switch (type)
        {
            case ListItemType.Item:    
               HyperLink hyprLnk = new HyperLink();
               hyprLnk.Target = "_blank"; //Optional.
               hyprLnk.DataBinding+=new EventHandler(hyprLnk_DataBinding);
               container.Controls.Add(hyprLnk);
            break;      
        }
    } 

    // The DataBinding event of your controls
    void hyprLnk_DataBinding(object sender, EventArgs e)
    {    
        HyperLink hyprlnk = (HyperLink)sender;
        GridViewRow container = (GridViewRow)hyprlnk.NamingContainer;
        object bindValue = DataBinder.Eval(container.DataItem,columnName);
        // Adding check in case Column allows null values
        if (bindValue != DBNull.Value) 
        {
            hyprlnk.Text = bindValue.ToString();
            hyprlnk.NavigateUrl = "http://www.google.com";
        }
    }
}

That's all. Above was just a sample to create ItemTemplate dynamically for GridView and add controls to the Item Template.

Now, Below is the function that will actually carry out the calls to create Template Columns dynamically. You can call this function when required for e.g. from your DropDownList event Handler.

protected void GenerateGridViewColumnsDynamically()
{
    // Create the TemplateField 
    TemplateField firstName = new TemplateField();
    firstName.HeaderText = "First_Name"; 
    firstName.ItemTemplate = new TemplateGenerator(ListItemType.Item, "FirstName");

    // Showing boundField example just for more context
    BoundField lastName = new BoundField();
    lastName.DataField = "LastName";
    lastName.HeaderText = "Last_Name";

    // Add the Columns now
    MyGridView.Columns.Add(firstName);
    MyGridView.Columns.Add(lastName);
}

NOTE:: FirstName and LastName are the Columns whose Names are passed to the constructor of your custom class: TemplateGenerator.

Answer 2

I have done the same functionality as below with custom paging(using storedProc) for 100+ million records in many tables, update, delete and insert also:

CREATE PROCEDURE [dbo].[sp_Mk]
    @PageIndex  INT,
    @PageSize INT,
    @tableName nvarchar(255),
    @totalRow INT Output
AS
BEGIN 
DECLARE @sql NVARCHAR(MAX)
Declare @anotherSql NVARCHAR(1000)
DECLARE @ParamDefinition NVARCHAR(500)

--DECLARE @totalRow INT
Set @sql = 'WITH TempResult AS( SELECT *  FROM '+@tableName+'), TempCount AS ( SELECT COUNT(*) AS MaxRows FROM TempResult )
SELECT * FROM TempResult, TempCount ORDER BY (Select Null)
    OFFSET '+CONVERT(VARCHAR(20),(@PageIndex-1)*@PageSize) +' ROWS FETCH NEXT '+CONVERT(VARCHAR(20),@PageSize)+' ROWS ONLY'

PRINT @SQL
EXECUTE sp_executesql @SQL
Set @anotherSql=N'SELECT COUNT(*) as totalRow FROM '+@tableName
SET @ParamDefinition = N'@totalRowOutPut INT  OUTPUT'
--PRINT @anotherSql
Execute sp_executesql @anotherSql, 
@ParamDefinition, 
--@tableNameInput=@tableName, 
@totalRowOutPut=@totalRow OUTPUT
End




<asp:GridView CssClass="table-striped header-fixed" ID="grdDynamic" runat="server" AutoGenerateColumns="True" ShowHeaderWhenEmpty="true" AutoGenerateEditButton="true" AutoGenerateDeleteButton="true" 
            OnRowEditing="OnRowEditing_grdDynamic" OnRowUpdating="OnRowUpdating_grdDynamic" OnRowCancelingEdit="OnRowCancelingEdit_grdDynamic" OnRowDeleting="OnRowDeleting_grdDynamic" OnRowDataBound="OnRowDataBound_grdDynamic">
            </asp:GridView><br/>
            <asp:linkbutton id="AddButton" runat="server" commandname="Add" text="Insert: " OnClick="AddNewButton_Click" /><br/>
            <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" CssClass="pagination-ys" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater><asp:HiddenField runat="server" id="hdnPageIndex" Value="1"></asp:HiddenField>





SqlConnectionStringBuilder builder;
        int pageSize = 100;
        protected void Page_Load(object sender, EventArgs e)
        {
            builder = new SqlConnectionStringBuilder(connectionString);
            if (!IsPostBack)
            {
                using (SqlConnection connObj = new SqlConnection(connectionString))
                {
                    connObj.Open();

                    using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='" + builder.InitialCatalog + "' AND TABLE_NAME Not In('AspNetUsers') Order By TABLE_NAME", connObj))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        ddlTableNames.DataSource = ds;
                        ddlTableNames.DataBind();
                        ddlTableNames.Items.Insert(0, new ListItem("Select Table", String.Empty));
                    }
                }
            }
            //}
            //else if(ddlTableNames.Visible) ddlTableNames.Visible = false;
        }

        protected void ddlTableNames_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlTableNames.SelectedValue != "")
            {
                grdDynamic.Visible = true;
                this.BindGrid(ddlTableNames.SelectedValue, Convert.ToInt32(hdnPageIndex.Value));
            }
            else if (grdDynamic.Visible == true) grdDynamic.Visible = false;
        }

        private void BindGrid(string selectedTable, int pageIndex, bool addNewRow=false)
        {
            using (SqlConnection connObj = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("sp_Mk", connObj))
                {
                    int recordCount=0;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                    cmd.Parameters.AddWithValue("@PageSize", pageSize);
                    cmd.Parameters.AddWithValue("@tableName", ddlTableNames.SelectedValue);
                    SqlParameter totalRow = new SqlParameter("@totalRow", SqlDbType.Int, 4);
                    totalRow.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(totalRow);
                    connObj.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    grdDynamic.DataSource = ds.Tables[0];
                    if (addNewRow) ds.Tables[0].Rows.Add();
                    recordCount = Convert.ToInt32(ds.Tables[1].Rows[0].ItemArray[0]);
                    grdDynamic.DataBind();

                    connObj.Close();
                    if (totalRow.Value != DBNull.Value)
                    {

                    }
                    this.PopulatePager(recordCount, pageIndex);
                }
            }
        }

        private void PopulatePager(int recordCount, int currentPage)
        {
            double dblPageCount = (double)((decimal)recordCount / pageSize);
            int pageCount = (int)Math.Ceiling(dblPageCount);
            List<ListItem> pages = new List<ListItem>();
            if (pageCount > 0)
            {
                pages.Add(new ListItem("First", "1", currentPage > 1));
                for (int i = 1; i <= pageCount; i++)
                {
                    ListItem item=new ListItem(i.ToString(), i.ToString(), i != currentPage);
                    if (i == currentPage) item.Attributes.Add("style", "color:red;");
                    pages.Add(item);
                }
                pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
            }
            rptPager.DataSource = pages;
            rptPager.DataBind();
        }

        protected void Page_Changed(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            hdnPageIndex.Value = pageIndex.ToString();
            this.BindGrid(ddlTableNames.SelectedValue, pageIndex);
        }

        protected void OnRowEditing_grdDynamic(object sender, GridViewEditEventArgs e)
        {
            grdDynamic.EditIndex = e.NewEditIndex;
            this.BindGrid(ddlTableNames.SelectedValue, Convert.ToInt32(hdnPageIndex.Value));
        }

        protected void OnRowUpdating_grdDynamic(object sender, GridViewUpdateEventArgs e)
        {
            GridViewRow row = grdDynamic.Rows[e.RowIndex];
            string updateStatement = string.Empty;
            for (int x = 0; x < row.Cells.Count; x++) updateStatement = updateStatement + grdDynamic.DataKeys[e.RowIndex].Values[x] + " = " + grdDynamic.DataKeys[e.RowIndex].Values[x] + ", ";
            //int recordId = Convert.ToInt32(grdDynamic.DataKeys[e.RowIndex].Values[0]);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                //using (SqlCommand cmd = new SqlCommand("UPDATE "+selectedTable"+ SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"))
                {
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            grdDynamic.EditIndex = -1;
            this.BindGrid(ddlTableNames.SelectedValue, Convert.ToInt32(hdnPageIndex.Value));
        }

        protected void OnRowCancelingEdit_grdDynamic(object sender, EventArgs e)
        {
            grdDynamic.EditIndex = -1;
            this.BindGrid(ddlTableNames.SelectedValue, Convert.ToInt32(hdnPageIndex.Value));
        }

        protected void OnRowDeleting_grdDynamic(object sender, GridViewDeleteEventArgs e)
        {
            int recordId = Convert.ToInt32(grdDynamic.DataKeys[e.RowIndex].Values[0]);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("DELETE FROM " + ddlTableNames.SelectedValue + " WHERE RecordId = @recordId"))
                {
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            this.BindGrid(ddlTableNames.SelectedValue, Convert.ToInt32(hdnPageIndex.Value));
        }

        protected void btnGo_Click(object sender, EventArgs e)
        {   int myInt;
            if(txtPageSize.Text!=null && txtPageSize.Text !=string.Empty)
                if(int.TryParse(txtPageSize.Text, out myInt)) pageSize = myInt;
            hdnPageIndex.Value = "1";
            this.BindGrid(ddlTableNames.SelectedValue, 1);
        }

        protected void AddNewButton_Click(object sender, EventArgs e)
        {
            hdnPageIndex.Value="1";
            this.BindGrid(ddlTableNames.SelectedValue, Convert.ToInt32(hdnPageIndex.Value), true);
        }

        protected void OnRowDataBound_grdDynamic(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != grdDynamic.EditIndex)
            {
                (e.Row.Cells[0].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');";
            }
        }

Hope it helps: