This is a migrated thread and some comments may be shown as answers.

Dynamic Column Generation

5 Answers 152 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Kerry
Top achievements
Rank 1
Kerry asked on 19 Nov 2008, 08:44 PM
Hi, I am new to C#, Telerik and ASP.NET, and I am stuck.  Hopefully someone can help me out.

Synopsis:  I have a Columns Table where I define each column I want to show in the RadGrid (ex. ColumnName, ColumnHeader, ColumnDataType, ColumnFormat, ColumnVisible).  So what I do, is I loop through this table to build my columns at run time, and wish to use the GridBoundColumn to be able to set my Header Display Text and Column Formats based on the values in this table.

I have am close, but am stuck somewhere in the middle of using ASP.NET DataColumn and Telerik's GridDataColumn.

The DataColumn works, but I do not get the ability to format the data (like a phone number or date), or set the header.  The GridBoundColum doesn't seem to work at all.

Please tell me where I am going wrong.  Keep in mind I am new to this, so if there is a better way to do this, I am open to suggestions.

Thanks!!

ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default12.aspx.cs" Inherits="Default12" %> 
 
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml">  
<head id="Head1" runat="server">  
    <title></title>  
    <link href="Assets/CSS/qsf.css" rel="stylesheet" type="text/css" /> 
    <style type="text/css">  
        html, body, form  
        {  
            height: 100%;  
            margin: 0px;  
            padding: 0px;  
            overflow: hidden;</style> 
</head> 
<body> 
      
    <form id="form1" runat="server">  
    <telerik:RadScriptManager ID="ScriptManager1" runat="server" EnableTheming="True">  
    </telerik:RadScriptManager> 
    <div> 
            
        <asp:Button ID="Button4" CssClass="button" Width="150px" Text="Export to Excel" 
            OnClick="Button4_Click" runat="server"></asp:Button> 
        <asp:Button ID="Button2" CssClass="button" Width="150px" Text="Export to Word" OnClick="Button2_Click" 
            runat="server"></asp:Button> 
        <asp:Button ID="Button3" CssClass="button" Width="150px" Text="Export to CSV" OnClick="Button3_Click" 
            runat="server"></asp:Button> 
        <asp:PlaceHolder ID="PlaceHolder1" runat="server"></asp:PlaceHolder> 
        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server" DefaultLoadingPanelID="RadAjaxLoadingPanel1">  
            <AjaxSettings> 
                <telerik:AjaxSetting AjaxControlID="RadGrid1">  
                    <UpdatedControls> 
                        <telerik:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="RadAjaxLoadingPanel1" /> 
                    </UpdatedControls> 
                </telerik:AjaxSetting> 
            </AjaxSettings> 
        </telerik:RadAjaxManager> 
        <telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" Height="75px" 
            Width="275px" Transparency="40" BackColor="white" ForeColor="Gray">  
            <asp:Image ID="Image1" runat="server" AlternateText="Loading..." ImageUrl="~/Assets/Images/loader.gif" 
                Style="margin-top: 100px;" /><br /> 
        </telerik:RadAjaxLoadingPanel> 
          
            <telerik:RadGrid ID="RadGrid1" AllowPaging="True" PageSize="20" OnSortCommand="RadGrid1_SortCommand" 
                OnPageIndexChanged="RadGrid1_PageIndexChanged" OnGroupsChanging="RadGrid1_GroupCommand" 
                runat="server" GridLines="None" ShowGroupPanel="True">  
                <MasterTableView> 
                    <RowIndicatorColumn> 
                        <HeaderStyle Width="20px"></HeaderStyle> 
                    </RowIndicatorColumn> 
                    <ExpandCollapseColumn> 
                        <HeaderStyle Width="20px"></HeaderStyle> 
                    </ExpandCollapseColumn> 
                </MasterTableView> 
                <ClientSettings AllowDragToGroup="True">  
                </ClientSettings> 
                <FilterMenu EnableTheming="True">  
                    <CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation> 
                </FilterMenu> 
            </telerik:RadGrid> 
          
    </div> 
    </form> 
</body> 
</html> 
 

 


C#

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using Telerik.Web.UI;  
using System.Data.SqlClient;  
using System.Configuration;  
using System.Data;  
using System.IO;  
using System.Text;  
 
 
public partial class Default12 : System.Web.UI.Page  
{  
    public static String ConnString = ConfigurationManager.ConnectionStrings["Snarf2ConnectionString"].ConnectionString;  
 
    protected void Page_Load(object sender, EventArgs e)  
    {  
        LoadGridData();  
    }  
 
    private void LoadGridData()  
    {  
        RadGrid1.Skin = "Office2007";  
        RadGrid1.FilterMenu.Skin = "Office2007";  
        RadGrid1.ShowStatusBar = true;  
        RadGrid1.Width = Unit.Percentage(100);  
        RadGrid1.Height = Unit.Percentage(100);  
        RadGrid1.PageSize = 30;  
        RadGrid1.AllowPaging = true;  
        RadGrid1.AllowSorting = true;  
        RadGrid1.AllowFilteringByColumn = true;  
        RadGrid1.GroupingSettings.CaseSensitive = false;  
        RadGrid1.PagerStyle.Mode = GridPagerMode.NextPrevAndNumeric;  
        RadGrid1.PagerStyle.Position = GridPagerPosition.TopAndBottom;  
        RadGrid1.ClientSettings.Selecting.AllowRowSelect = true;  
        RadGrid1.ClientSettings.Scrolling.AllowScroll = true;  
          
        DataTable dt = new DataTable();  
 
        GenerateDataTableColumns(dt);  
        GenerateDataTableRows(dt);  
        RadGrid1.DataSource = dt;  
        RadGrid1.DataBind();  
    }  
 
    private void GenerateDataTableColumns(DataTable dt)  
    {  
 
        SqlConnection con = new SqlConnection(ConnString);  
        con.Open();  
        string thesql = "SELECT * FROM column_mappings WHERE versionID=" + "1" + " AND columnVisible=1 ORDER By columnOrderBy";  
        SqlCommand cmd = new SqlCommand(thesql, con);  
        SqlDataReader reader = cmd.ExecuteReader();  
        string strSelect = null;  
        string strArray = null;  
        int i = 0;  
        Session["strSelect"] = null;  
        Session["strArray"] = null;  
 
        while (reader.Read())  
        {  
 
            /* THIS WORKS, BUT NO FORMAT OR HEADER DISPLAY OPTION
            DataColumn column;
            column = new DataColumn();
            column.DataType = Type.GetType(reader[5].ToString());
            column.ColumnName = reader[4].ToString();
            dt.Columns.Add(column);
             */ 
 
            // THIS DOES NOT WORK WITH DataTable??  
            GridBoundColumn column;  
            column = new GridBoundColumn();  
            column.DataField = reader[2].ToString();  
            column.HeaderText = reader[4].ToString();  
            column.DataFormatString = reader[5].ToString();  
            dt.Columns.Add(column);  
 
            strSelect = strSelect + "" + reader[2].ToString() + ",";  
            strArray = strArray + "" + "reader[" + i + "].ToString()" + ",";  
 
        }  
 
        StringReader reader2 = new StringReader(strSelect);  
        StringBuilder builder = new StringBuilder();  
 
        string line2 = null;  
        while ((line2 = reader2.ReadLine()) != null)  
        {  
            if (line2.Contains(","))  
                builder.AppendLine(line2.Substring(0, line2.LastIndexOf(',')));  
        }  
        Session["strSelect"] = builder.ToString();  
 
        StringReader reader3 = new StringReader(strArray);  
        StringBuilder builder3 = new StringBuilder();  
 
        string line3 = null;  
        while ((line3 = reader3.ReadLine()) != null)  
        {  
            if (line3.Contains(","))  
                builder3.AppendLine(line3.Substring(0, line3.LastIndexOf(',')));  
        }  
        Session["strArray"] = builder3.ToString();  
 
        con.Close();  
 
    }  
 
    private void GenerateDataTableRows(DataTable dt)  
    {  
 
        SqlConnection con = new SqlConnection(ConnString);  
        con.Open();  
 
        string thesql = "SELECT  " + Session["strSelect"] + " FROM snarf_data WHERE formatID=" + "1" + " ORDER BY dataID DESC";  
        SqlCommand cmd = new SqlCommand(thesql, con);  
        SqlDataReader reader = cmd.ExecuteReader();  
 
        while (reader.Read())  
        {  
 
            DataRow dr = dt.NewRow();  
 
            SqlConnection con2 = new SqlConnection(ConnString);  
            con2.Open();  
 
            string thesq2 = "SELECT * FROM column_mappings WHERE versionID=" + "1" + " AND columnVisible=1 ORDER By columnOrderBy";  
            SqlCommand cmd2 = new SqlCommand(thesq2, con2);  
            SqlDataReader reader2 = cmd2.ExecuteReader();  
            int y = 1;  
 
 
            while (reader2.Read())  
            {  
 
                if (Session["showTrans"] != null)  
                {  
                    SqlConnection con3 = new SqlConnection(ConnString);  
                    con3.Open();  
                    string thesql3 = "SELECT * FROM data_mapping WHERE formatID=" + "1" + " AND mappingValue = '" + reader[y - 1].ToString() + "'";  
 
                    SqlCommand cmd3 = new SqlCommand(thesql3, con3);  
                    SqlDataReader reader3 = cmd3.ExecuteReader();  
 
                    dr[reader2[4].ToString()] = reader[y - 1].ToString();  
 
                    while (reader3.Read())  
                    {  
                        if (reader[y - 1].ToString() == reader3[3].ToString())  
                        {  
                            dr[reader2[4].ToString()] = reader3[4].ToString();  
                        }  
 
                    }  
 
                    con3.Close();  
                }  
 
                else 
                {  
 
                    dr[reader2[4].ToString()] = reader[y - 1].ToString();  
                }  
 
                y = y + 1;  
 
            }  
 
            con2.Close();  
            dt.Rows.Add(dr);  
        }  
 
        dt.AcceptChanges();  
        con.Close();  
    }  
    protected void RadGrid1_PageIndexChanged(object source, Telerik.Web.UI.GridPageChangedEventArgs e)  
    {  
        LoadGridData();  
    }  
 
    protected void RadGrid1_SortCommand(object source, Telerik.Web.UI.GridSortCommandEventArgs e)  
    {  
        LoadGridData();  
    }  
 
    protected void RadGrid1_GroupCommand(object source, Telerik.Web.UI.GridGroupsChangingEventArgs e)  
    {  
        LoadGridData();  
    }  
    protected void RadGrid1_FilterCommand(object source, Telerik.Web.UI.GridFilterCommandEventArgs e)  
    {  
        LoadGridData();  
    }  
 
    protected void Button1_Click(object sender, System.EventArgs e)  
    {  
        ConfigureExport();  
        RadGrid1.MasterTableView.ExportToExcel();  
    }  
 
    protected void Button2_Click(object sender, System.EventArgs e)  
    {  
        ConfigureExport();  
        RadGrid1.MasterTableView.ExportToWord();  
    }  
 
    protected void Button3_Click(object sender, System.EventArgs e)  
    {  
        ConfigureExport();  
        RadGrid1.MasterTableView.ExportToCSV();  
    }  
 
    protected void Button4_Click(object sender, System.EventArgs e)  
    {  
        ConfigureExport();  
        RadGrid1.ExportSettings.Excel.Format = Telerik.Web.UI.GridExcelExportFormat.ExcelML;  
        RadGrid1.ExportSettings.ExportOnlyData = true;  
        RadGrid1.MasterTableView.ExportToExcel();  
    }  
 
    protected void RadGrid1_ExcelMLExportRowCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e)  
    {  
        if (e.RowType == Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowType.DataRow)  
        {  
            if (e.Row.Cells[0] != null && ((string)e.Row.Cells[0].Data.DataItem).Contains("U"))  
            {  
                e.Row.Cells[0].StyleValue = "MyCustomStyle";  
            }  
        }  
    }  
 
    protected void RadGrid1_ExcelMLExportStylesCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLStyleCreatedArgs e)  
    {  
        foreach (Telerik.Web.UI.GridExcelBuilder.StyleElement style in e.Styles)  
        {  
            if (style.Id == "headerStyle")  
            {  
                style.FontStyle.Bold = true;  
                style.FontStyle.Color = System.Drawing.Color.Gainsboro;  
                style.InteriorStyle.Color = System.Drawing.Color.Wheat;  
                style.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;  
            }  
            else if (style.Id == "itemStyle")  
            {  
                style.InteriorStyle.Color = System.Drawing.Color.WhiteSmoke;  
                style.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;  
            }  
            else if (style.Id == "alternatingItemStyle")  
            {  
                style.InteriorStyle.Color = System.Drawing.Color.LightGray;  
                style.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;  
            }  
        }  
 
        Telerik.Web.UI.GridExcelBuilder.StyleElement myStyle = new Telerik.Web.UI.GridExcelBuilder.StyleElement("MyCustomStyle");  
        myStyle.FontStyle.Bold = true;  
        myStyle.FontStyle.Italic = true;  
        myStyle.InteriorStyle.Color = System.Drawing.Color.Gray;  
        myStyle.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;  
        e.Styles.Add(myStyle);  
    }  
 
    public void ConfigureExport()  
    {  
        RadGrid1.ExportSettings.ExportOnlyData = true;  
        RadGrid1.ExportSettings.IgnorePaging = true;  
        RadGrid1.ExportSettings.OpenInNewWindow = true;  
    }  
 

5 Answers, 1 is accepted

Sort by
0
Bruno
Top achievements
Rank 2
answered on 19 Nov 2008, 09:25 PM
Kerry,

Your code looks good for the beginner. However I suggest you to notice some details regarding the dynamic column creation on Page_Load.

On PageLoad
On PageInit

Programmatic creation

On Page_Load example:
GridBoundColumn boundColumn; 
boundColumn = new GridBoundColumn(); 

Keep up the good work
0
Kerry
Top achievements
Rank 1
answered on 20 Nov 2008, 03:19 PM
Thanks for the reply Bruno,

But it appears that GridBoundColumn does not work with the method I am using to build my columns and fill my table.  Does anyone have any idea on how to get this to work?  I am 95% there, but I really need to be able to apply column-level formatting to this grid.

Thanks!
0
Daniel
Telerik team
answered on 20 Nov 2008, 07:22 PM
Hello Kerry,

Please confirm that you changed the code as suggested by Bruno.

Using Page_Load event:
Columns and detail tables should be added to the corresponding collection first, before the values for their properties are set. This is important because no ViewState is managed for the object before it has been added to the corresponding collection.
            GridBoundColumn column;   
            column = new GridBoundColumn();   
            dt.Columns.Add(column);  
            column.DataField = reader[2].ToString();   
            column.HeaderText = reader[4].ToString();   
            column.DataFormatString = reader[5].ToString();   
  
            strSelect = strSelect + "" + reader[2].ToString() + ",";   
            strArray = strArray + "" + "reader[" + i + "].ToString()" + ",";   

Best regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.
0
Kerry
Top achievements
Rank 1
answered on 21 Nov 2008, 02:07 PM
I tried putting this is in page_load() , but I still cannot seem to get it to work.  I'm sure I am doing something wrong here.  Is there anyway someone could show me with what I posted where it should go?

Thanks again for the help!
0
Daniel
Telerik team
answered on 24 Nov 2008, 02:59 PM
Hello Kerry,

I created demo project based on your code (modified). Please examine it on your end and let me know whether it helps.

Greetings,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Tags
Grid
Asked by
Kerry
Top achievements
Rank 1
Answers by
Bruno
Top achievements
Rank 2
Kerry
Top achievements
Rank 1
Daniel
Telerik team
Share this question
or