GridView with Dynamic Columns
The ASP.NET GridView control can be a really useful control for displaying and editing tabular data, but if you try to make it work with MySQL or a cross-tab query you’re likely to run into problems.
I wanted to create a grid showing the data from a cross-tab but also allow users to edit the data. If you’ve never used one before, a cross tabulation is a query which maps repeating rows to columns instead. This means that a column in a displayed result might not actually exist as a column in a database table. If you just need to display the data and aren’t bothered about editing it, the GridView works out of the box and will automatically generate the columns for you – but what if you need to display some columns but not others?
In my example, there are three static columns and a user specified number of dynamic ones. The static columns are coded in the .aspx page but the dynamic ones are generated by an AutoFieldGenerator which takes selections from a ListBox into account. The columns in the crosstab are populated from the selected ListBox items so I can pass the selected items to the AutoFieldGenerator and have it create template columns which I know are present as columns in the crosstab.
<% <asp:GridView ID="grdTranslations" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="false" OnRowDatabound="row_DataBound" OnRowCommand="grd_RowCommand" OnPageIndexChanging="grd_PageIndexChanging" OnDataBound="grd_DataBound" PageSize="15"> <Columns> <asp:TemplateField HeaderText="Bundle Name"> <ItemTemplate> <asp:Label ID="lblBundleName" runat="server" Text='<%# Eval("bundlename") %>' EnableViewState="False"></asp:Label> <asp:DropDownList ID="cboBundleName" visible="false" runat="server"> </asp:DropDownList> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Element Name"> <ItemTemplate> <asp:Label ID="lblElementName" runat="server" Text='<%# Eval("elementname") %>' EnableViewState="False"></asp:Label> <asp:TextBox ID="txtElementName" runat="server" Text='<%# Eval("elementname") %>' Visible="false" EnableViewState="False"></asp:TextBox> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Actions"> <ItemTemplate> <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="edittranslation" CssClass="uniformbutton" EnableViewState="False" /> <asp:DropDownList ID="cboTranslationID" visible="false" runat="server" DataSourceID="dsTranslationID" DataTextField="display" DataValueField="translationid" AppendDataBoundItems="true" SelectedValue='<%# Eval("translationid") %>'> <asp:ListItem Text="New Translation" Value="-1"></asp:ListItem> </asp:DropDownList> <asp:Button ID="btnSave" Visible="false" runat="server" Text="Save" CommandName="savetranslation" CommandArgument='<%# Eval("id") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> %>
The dynamic columns are added on every postback by setting the AutoColumnGenerator property of the GridView. The source for the column generator and template are available here and here. The item template is just a label and a textbox which databind themselves to the column in the DataSource with the column name given in the GridViewTemplate constructor.
Seem simple so far? Good, because getting the data out of the textboxes isn’t so easy. The problem exists because once you start adding columns dynamically, the GridView doesn’t seem to recognize that extra columns were added to it when the page posts back, so there’s no way to access the textbox controls in those columns. In my example, there’s an edit button and a save button in cell 3. When the edit button is clicked the page posts back and there’s a method which resets the column generator, re-databinds the GridView then finds the index of row where the edit button was clicked and changes the visibility of the edit controls.
protected void grd_RowCommand(object sender, GridViewCommandEventArgs e) { GridViewRow row; // The row which has clicked was the undatabound one, so use it's index to lookup the new, databound row grdTranslations.DataBind(); row = (e.CommandSource as WebControl).NamingContainer as GridViewRow; // this is the row before dynamic columns were added, only controls coded in the .aspx page are accessible as only the static columns are present. string selectedTranslationID = (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList).SelectedValue; switch(e.CommandName) { case "edittranslation": GridViewRow newRow = grdTranslations.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex]; // use the row's index to lookup the instance of the row in the new, databound GridView with dynamic columns ShowEditControls(row); break; case "savetranslation": // 'Save' button clicked row = (e.CommandSource as WebControl).NamingContainer as GridViewRow; SaveTranslation(row); // the new, databound row isn't much use as it just contains the values as the exist when the server sent them break; } } private void ShowEditControls(GridViewRow row) { (row.Cells[1].FindControl("cboBundleName") as DropDownList).Visible = true; (row.Cells[2].FindControl("txtElementName") as TextBox).Visible = true; (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList).Visible = true; if(row.RowType == DataControlRowType.DataRow) { (row.Cells[1].FindControl("lblBundleName") as Label).Visible = false; (row.Cells[2].FindControl("lblElementName") as Label).Visible = false; (row.Cells[editButtonColumNo].FindControl("btnEdit") as Button).Visible = false; (row.Cells[editButtonColumNo].FindControl("btnSave") as Button).Visible = true; for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) { row.Cells[i].Controls[0].Visible = false; row.Cells[i].Controls[1].Visible = true; } } }
So all of the labels are hidden and instead, textboxes and combo boxes are shown to allow the user to edit the record. To record the user’s input when the save button is clicked, the GridView isn’t much help. The only way I could find of getting the values back out from the textboxes was to loop through Request.Form and compare the keys against the UniqueIDs of the textboxes I needed.
... string bundleName = (oldRow.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue, elementName = (oldRow.Cells[2].FindControl("txtElementName") as TextBox).Text, elementComment = (oldRow.Cells[2].FindControl("txtElementComment") as TextBox).Text, translationID = (oldRow.Cells[3].FindControl("cboTranslationID") as DropDownList).SelectedValue, translationComment = (oldRow.Cells[3].FindControl("txtTranslationComment") as TextBox).Text; for(int i = editButtonColumNo + 1; i < newRow.Cells.Count; i++) { string textBoxID = newRow.Cells[i].Controls[1].UniqueID; foreach(string key in Request.Form.AllKeys) { if(key == textBoxID) { cmd.Parameters["@trans"].Value = Request.Form[key]; cmd.Parameters["@lang"].Value = textBoxID.Substring(textBoxID.LastIndexOf("$") + 1); // the ID of the textbox has $country-CODE appended to it cmd.ExecuteNonQuery(); break; } } }
In my particular scenario, I needed the names of the columns as parameters for a query, which is why I stored the column names in the textbox IDs during databinding on the texbox controls in GridViewTemplate. This might not be the nicest or most efficient method of extracting the values again, but it does the job. Even with 12 dynamic textboxes and a few hidden fields the total size of the Request.Form collection was 31 keys, which won’t hamper performance too much.
It is very nice,please provide screen shot for reference
Unless you have a very wide monitor, that’s probably not going to work. My particular GridView usually has about 15 columns. It looks otherwise like a normal GridView.