Setting datasource with filtering-functionality by code

[Henrik]

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

[Princy]

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.

[Henrik]

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

[Princy]

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.

[Henrik]

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

[Princy]

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.

[Henrik]

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

[Princy]

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.

[Henrik]

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

//h

[Alex]

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 + "%'");

[Sampath]

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 :) 

[Kostadin]

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