Setting datasource with filtering-functionality by code

12 posts, 0 answers
  1. Henrik
    Henrik avatar
    30 posts
    Member since:
    Dec 2010

    Posted 22 Dec 2010 Link to this post

    Hi all!
    I am new into Telerik and I think I need a boost here to see íf I can use RadGrid for my project:

    Now to what I have done:
    I need to set a datasource for my grid wich I today do like :
    DataGrid1.DataSource = GetTheTable()
    DataGrid1.DataBind()

    No problem so far and items seen in grid.

    Now ... I want to implement Filtering for my Colums in the bound table. I have read that this can't be done when using simple databinding. Correct?

    Do I have to first specify my columns and then bind my datasource?

    Now ... how to do this manually by code? Anyone can give me an example?

    //h
  2. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 22 Dec 2010 Link to this post

    Hello Henrik,

    If you are using any advanced feature in grid(like filtering), then a better approach is using "AdvancedData binding" using NeedDataSource event. You need to attach NeedDataSource event to RadGrid. In the eventhandler set the DataSource property of RadGrid to a valid data source object.

    C#:
    protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            DataGrid1.DataSource = GetTheTable()
        }

     Please refer the following documentation and demo which explains more on this.
    Advanced Data-binding (using NeedDataSource event)
    Grid / Advanced Data Binding

    Thanks,
    Princy.

  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Henrik
    Henrik avatar
    30 posts
    Member since:
    Dec 2010

    Posted 22 Dec 2010 Link to this post

    hrmmz I don't really understand how to use this in my app ...
    I have a combo of TableNames .. When i select TableName I need to set datasource of grid ...

    NeedDataSource seem to be an event or Am i wrong here?

    //h

  5. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 23 Dec 2010 Link to this post

    Hello Henrik,

    Here is a sample code snippet to achieve your requirement. In SelectedIndexChanged event of RadComboBox call the Rebind() method which in turn call the NeedDataSource event. In the NeedDataSource event populate the grid based on the selected value of RadComboBox.

    ASPX:
    <telerik:RadComboBox runat="server" ID="RadComboBox1" AutoPostBack="true"
            OnSelectedIndexChanged="RadComboBox1_SelectedIndexChanged">
        <Items>
            <telerik:RadComboBoxItem Text="Employees" Value="Table1" />
            <telerik:RadComboBoxItem Text="Table_3" Value="Table2" />
        </Items>
    </telerik:RadComboBox>
    <telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="True"  
            OnNeedDataSource="RadGrid1_NeedDataSource">
        <MasterTableView>
        </MasterTableView>
    </telerik:RadGrid>

    C#:
    protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
        {
        //based on SelectedValue of comboBox populate RadGrid
            if (RadComboBox1.SelectedValue == "Table1")
                GetTheTable1();
            else
                GetTheTable2();
        }
        private void GetTheTable1()
        {
            //populate datatable with 'Table1'
            RadGrid1.DataSource = datatable;
        }
        private void GetTheTable2()
        {
            //populate datatable with 'Table2'
            RadGrid1.DataSource = dt;
        }
        protected void RadComboBox1_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            RadGrid1.Rebind(); // will call NeedDataSource event
        }

    Thanks,
    Princy.
  6. Henrik
    Henrik avatar
    30 posts
    Member since:
    Dec 2010

    Posted 23 Dec 2010 Link to this post

    Thanks!
    Worked like a charm :D
    Now I start see how RadGrid works different from my old components

    BTW follow up question. Is there a way to remove the filter button?

    //h

  7. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 23 Dec 2010 Link to this post

    Hello Henrik,

    You can set the 'ShowFilterIcon' property as 'False' to hide the Filter icon.

    ASPX:
    <telerik:GridBoundColumn UniqueName="EmployeeID" DataField="EmployeeID" ShowFilterIcon="false">
     </telerik:GridBoundColumn>

    Thanks,
    Princy.
  8. Henrik
    Henrik avatar
    30 posts
    Member since:
    Dec 2010

    Posted 23 Dec 2010 Link to this post

    GREAT Finally I am starting to get anywhere ... It all has to do with learning new control ...

    Well I do this now
    Protected Sub TableGrid_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles TableGrid.DataBound
        For Each Column As Telerik.Web.UI.GridColumn In TableGrid.MasterTableView.AutoGeneratedColumns
            Column.AutoPostBackOnFilter = True
            Column.CurrentFilterFunction = Telerik.Web.UI.GridKnownFunction.Contains
            Column.ShowFilterIcon = False
        Next
    End Sub

    However removing of filter is only done after my first sort or filter then these things are set. Maybe I shouldn't use the DataBound-event for this? Shall I use NeedDataSource for these settings to just after I have set the datasourcer? Or what is good practice?

    Thanks for all your help ..
    //h
  9. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 24 Dec 2010 Link to this post

    Hello Henrik,

    Since you are using AutoGeneratedColumn, you can try the following code snippet in ColumnCreated event to hide the filter icon.

    VB.NET:
    Protected Sub RadGrid1_ColumnCreated(sender As Object, e As GridColumnCreatedEventArgs)
        e.Column.ShowFilterIcon = False
    End Sub

    Thanks,
    Princy.
  10. Henrik
    Henrik avatar
    30 posts
    Member since:
    Dec 2010

    Posted 24 Dec 2010 Link to this post

    Soooo Simple ...
    Thanks again!
    Now I have made everything I wanted to do and got the boost I wanted to get.

    //h
  11. Alex
    Alex avatar
    23 posts
    Member since:
    Dec 2012

    Posted 27 Mar 2013 Link to this post

    How would this be done programmatically? In your example you are hard-coding your drop-down options. If the drop-down menu items were bound to a database, how would this example change? I am currently battling this scenario and having a lot of trouble. Maybe I am making it harder than it has to be .... so, please, please help.

    If I am using GetTheTable1(); in your example, how would I filter based on the query pulled and the drop-down.text?

    String ConnString2 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                SqlConnection conn2 = new SqlConnection(ConnString2);
                SqlDataAdapter adapter2 = new SqlDataAdapter();
                adapter2.SelectCommand = new SqlCommand("SELECT top 1000 name,id, title,number FROM dummytable", conn2);

    string combotext = RadComboBox2.SelectedValue.ToString();
    Select("name LIKE '%" + combotext + "%'");

  12. Sampath
    Sampath avatar
    12 posts
    Member since:
    Mar 2013

    Posted 27 May 2015 in reply to Princy Link to this post

    Hi,

    I have followed the same technique to display a set of data in my RadGrid, I was able to remove the filter icon and set the current filter function to contains from my code. 

    But when I try to filter the data in the radgrid, nothing happens. 

    Here is the code in my aspx page,

     

    <%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="time-and-action-ganttchart-details.aspx.cs" Inherits="time_and_action_ganttchart_details" %>
     
    <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
        <title>Genesis | Time And Action</title>
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="headstyles" runat="Server">
    </asp:Content>
    <asp:Content ID="Content3" ContentPlaceHolderID="pagehead" runat="Server">
        <h1>Time And Action <small>time and action plan</small></h1>
    </asp:Content>
    <asp:Content ID="Content4" ContentPlaceHolderID="body" runat="Server">
        <!-- BEGIN PAGE BREADCRUMB -->
        <ul class="page-breadcrumb breadcrumb">
            <li>
                <a href="#">Home</a><i class="fa fa-circle"></i>
            </li>
            <li class="active">time and action plan
            </li>
        </ul>
        <!-- END PAGE BREADCRUMB -->
        <!-- BEGIN PAGE CONTENT INNER -->
        <div class="row">
            <div class="col-md-12">
                <!-- BEGIN SAMPLE FORM PORTLET-->
                <div class="portlet light ">
                    <div class="portlet-title">
                        <div class="caption caption-md">
                            <i class="icon-bar-chart theme-font-color hide"></i>
                            <span class="caption-subject theme-font-color bold uppercase">Time And Action Plan</span>
                        </div>
                        <div class="actions">
                            <a class="btn btn-circle btn-icon-only btn-default fullscreen" href="javascript:;"></a>
                        </div>
                    </div>
                    <div class="portlet-body">
                        <div class="table-responsive">
                            <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
                            <asp:ImageButton ID="ImageButton1" runat="server" OnClick="ImageButton1_Click" ImageUrl="~/images/Office-Excel-icon.png" Width="4%" Height="4%" ToolTip="Export to Excel" />
                            <telerik:RadGrid ID="RadGrid1" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource" AllowSorting="True" AllowPaging="True" ShowGroupPanel="True" GroupingSettings-CaseSensitive="false"
                                AllowFilteringByColumn="True" CellSpacing="-1" GridLines="Both" Skin="Metro" CssClass="rad_header_style" Width="100%" OnColumnCreated="RadGrid1_ColumnCreated" OnDataBound="RadGrid1_DataBound">                         
                            </telerik:RadGrid>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <!-- END PAGE CONTENT -->
    </asp:Content>
    <asp:Content ID="Content5" ContentPlaceHolderID="footer" runat="Server">
        <!-- BEGIN PAGE LEVEL PLUGINS -->
        <script type="text/javascript" src="../../assets/global/plugins/fancybox/source/jquery.fancybox.pack.js"></script>
     
        <script src="../../assets/global/scripts/metronic.js" type="text/javascript"></script>
        <script src="../../assets/admin/layout4/scripts/layout.js" type="text/javascript"></script>
        <script src="../../assets/admin/layout4/scripts/demo.js" type="text/javascript"></script>
        <script>
            jQuery(document).ready(function () {
                Metronic.init(); // init metronic core components
                Layout.init(); // init current layout
                Demo.init(); // init demo features
            });
        </script>
        <!-- END PAGE LEVEL PLUGINS -->
    </asp:Content>

    This is how I bound the data from code behind,

     

    using System;
    using System.Data;
    using System.Linq;
    using Telerik.Web.UI;
     
    public partial class time_and_action_ganttchart_details : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
     
        protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
        {
            short techpackID = 93; // short.Parse(Request.QueryString[0]);
     
            DataTable dt = new DataTable();
     
            dt.Columns.Add("Customer", typeof(string));
            dt.Columns.Add("Account", typeof(string));
            dt.Columns.Add("Season", typeof(string));
            dt.Columns.Add("Program", typeof(string));
            dt.Columns.Add("Tech Pack Name", typeof(string));
            dt.Columns.Add("Design Ref", typeof(string));
            dt.Columns.Add("Stroke", typeof(string));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Assortment Name", typeof(string));
            dt.Columns.Add("Component", typeof(string));
            dt.Columns.Add("RM Description", typeof(string));
            dt.Columns.Add("Supplier", typeof(string));
            dt.Columns.Add("CSP", typeof(string));
            dt.Columns.Add("Mode", typeof(string));
            dt.Columns.Add("RM Color Code", typeof(string));
            dt.Columns.Add("RM Color Name", typeof(string));
            dt.Columns.Add("Silhoutte", typeof(string));
            dt.Columns.Add("Garment Number", typeof(string));
     
            using (PDLC.Data.PDLCEntities context = new PDLC.Data.PDLCEntities())
            {
                var version = context.V_TimeAndAction_TechPackLeadTime.Where(x => x.TP_ID == techpackID).Select(s => new { s.VersionID }).Max(p => p.VersionID);
     
                var distinctEvents = context.V_TimeAndAction_TechPackLeadTime.Where(x => x.TP_ID == techpackID && x.VersionID == version).Select(s => new { s.EventText, s.EventOrder }).Distinct().OrderBy(o => o.EventOrder);
     
                foreach (var eventname in distinctEvents)
                {
                    dt.Columns.Add(eventname.EventText, typeof(string));
                    dt.Columns.Add("Actual " + eventname.EventText, typeof(string));
                    dt.Columns.Add("Committed " + eventname.EventText, typeof(string));
                }
     
                var assortmentIdList = context.V_TimeAndAction_TechPackLeadTime.Where(x => x.TP_ID == techpackID && x.VersionID == version).Select(s => new { s.AS_ID }).Distinct();
     
                foreach (var assortmentId in assortmentIdList)
                {
                    var rawMaterialList = context.V_TimeAndAction_TechPackLeadTime.Where(x => x.TP_ID == techpackID && x.VersionID == version && x.AS_ID == assortmentId.AS_ID).Select(s => new { s.RawMaterialID }).OrderBy(o => o.RawMaterialID).Distinct();
     
                    foreach (var rawmaterial in rawMaterialList)
                    {
                        DataRow dr = dt.NewRow();
                        var headerDetails = context.V_TimeAndAction_TechPackLeadTime.Where(x => x.TP_ID == techpackID && x.VersionID == version && x.AS_ID == assortmentId.AS_ID && x.RawMaterialID == rawmaterial.RawMaterialID).Select(s => new
                        {
                            s.Cust_Name,
                            s.Account_Name,
                            s.Season_Name,
                            s.ProgramName,
                            s.Design_Ref,
                            s.Stroke,
                            s.StyleTypeName,
                            s.Component_Name,
                            s.Description60Digit,
                            s.sales_office_name,
                            s.Color_Code,
                            s.Color_Name,
                            s.ShapeName,
                            s.GMT_ID,
                            s.TP_Name,
                            s.Assortment_name
                        }).Distinct().First();
     
                        dr["Customer"] = headerDetails.Cust_Name;
                        dr["Account"] = headerDetails.Account_Name;
                        dr["Season"] = headerDetails.Season_Name;
                        dr["Program"] = headerDetails.ProgramName;
                        dr["Tech Pack Name"] = headerDetails.TP_Name;
                        dr["Design Ref"] = headerDetails.Design_Ref;
                        dr["Stroke"] = headerDetails.Stroke;
                        dr["Type"] = headerDetails.StyleTypeName;
                        dr["Assortment Name"] = headerDetails.Assortment_name;
                        dr["Component"] = headerDetails.Component_Name;
                        dr["RM Description"] = headerDetails.Description60Digit;
                        dr["Supplier"] = headerDetails.sales_office_name;
                        dr["CSP"] = "CSP";
                        dr["Mode"] = "Mode";
                        dr["RM Color Code"] = headerDetails.Color_Code;
                        dr["RM Color Name"] = headerDetails.Color_Name;
                        dr["Silhoutte"] = headerDetails.ShapeName;
                        dr["Garment Number"] = headerDetails.GMT_ID;
     
                        var eventIdList = context.V_TimeAndAction_TechPackLeadTime.Where(x => x.TP_ID == techpackID && x.VersionID == version && x.AS_ID == assortmentId.AS_ID && x.RawMaterialID == rawmaterial.RawMaterialID).Select(s => new { s.EventID, s.EventOrder, s.EventText }).Distinct().OrderBy(o => o.EventOrder);
     
                        foreach (var eventId in eventIdList)
                        {
                            var timeandactiondates = context.V_TimeAndAction_TechPackLeadTime.Where(x => x.TP_ID == techpackID && x.VersionID == version && x.AS_ID == assortmentId.AS_ID && x.RawMaterialID == rawmaterial.RawMaterialID && x.EventID == eventId.EventID).Select(s => new { s.TimeAndActionES, s.ActualDate, s.CommittedDate }).Distinct().FirstOrDefault();
                            DateTime timeandactiones = (DateTime)timeandactiondates.TimeAndActionES;
                            dr[eventId.EventText] = timeandactiones.ToShortDateString();
                            if (timeandactiondates.ActualDate != null)
                            {
                                DateTime timeandactionactual = (DateTime)timeandactiondates.ActualDate;
                                dr["Actual " + eventId.EventText] = timeandactionactual.ToShortDateString();
                            }
                            else
                            {
                                dr["Actual " + eventId.EventText] = timeandactiondates.ActualDate;
                            }
                            if (timeandactiondates.CommittedDate != null)
                            {
                                DateTime timeandactualcommitted = (DateTime)timeandactiondates.CommittedDate;
                                dr["Committed " + eventId.EventText] = timeandactualcommitted.ToShortDateString();
                            }
                            else
                            {
                                dr["Committed " + eventId.EventText] = timeandactiondates.CommittedDate;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            RadGrid1.DataSource = dt;
        }
     
        protected void ImageButton1_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        {
            RadGrid1.Rebind();
            RadGrid1.ExportSettings.ExportOnlyData = true;
            RadGrid1.ExportSettings.IgnorePaging = true;
            RadGrid1.ExportSettings.OpenInNewWindow = true;
            RadGrid1.ExportSettings.UseItemStyles = true;
            RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;
            RadGrid1.ExportSettings.FileName = "T-and-A " + DateTime.Today.ToString("yyyy-MM-dd");
            RadGrid1.MasterTableView.ExportToExcel();
        }
     
        protected void RadGrid1_ColumnCreated(object sender, GridColumnCreatedEventArgs e)
        {
            e.Column.ShowFilterIcon = false;
        }
     
        protected void RadGrid1_DataBound(object sender, EventArgs e)
        {
            foreach (GridColumn column in RadGrid1.MasterTableView.AutoGeneratedColumns)
            {
                column.AutoPostBackOnFilter = true;
                column.CurrentFilterFunction = GridKnownFunction.Contains;
                column.ShowFilterIcon = false;
            }
        }
    }

    Any help is much appreciated. Thank you :) 

  13. Kostadin
    Admin
    Kostadin avatar
    1713 posts

    Posted 01 Jun 2015 Link to this post

    Hello Sampath,

    Could you please try setting the filter only on initial load by adding a check whether the page is not postback and also add the filter function on PreRender event handler as demonstrated in the following help article?

    Regards,
    Kostadin
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017