using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Telerik.Web.UI;

public partial class _Default : System.Web.UI.Page
{
    DataTable dt;
    private void Page_Load(object sender, System.EventArgs e)
    {
        if (!IsPostBack)
        {
            dt = GetDataTable("SELECT Country, City, PostalCode FROM Customers");
            this.RadGrid1.MasterTableView.Columns.Clear();
            foreach (DataColumn dataColumn in dt.Columns)
            {
                MyCustomFilteringColumnCS gridColumn = new MyCustomFilteringColumnCS();
                this.RadGrid1.MasterTableView.Columns.Add(gridColumn);
                gridColumn.DataField = dataColumn.ColumnName;
                gridColumn.HeaderText = dataColumn.ColumnName;
            }
        }
    }
    protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
    {
        dt = GetDataTable("SELECT Country, City, PostalCode FROM Customers");
        this.RadGrid1.DataSource = dt;
    }
    protected void RadGrid1_ColumnCreating(object sender, GridColumnCreatingEventArgs e)
    {
        if ((e.ColumnType == typeof(MyCustomFilteringColumnCS).Name))
        {
            e.Column = new MyCustomFilteringColumnCS();
        }
    }
    protected void RadGrid1_ItemCommand(object source, GridCommandEventArgs e)
    {
        if ((e.CommandName == "Filter"))
        {
            foreach (GridColumn column in e.Item.OwnerTableView.Columns)
            {
                column.CurrentFilterValue = string.Empty;
                column.CurrentFilterFunction = GridKnownFunction.NoFilter;
            }
        }
    }
    protected void clrFilters_Click(object sender, EventArgs e)
    {
        foreach (GridColumn column in RadGrid1.MasterTableView.Columns)
        {
            column.CurrentFilterFunction = GridKnownFunction.NoFilter;
            column.CurrentFilterValue = string.Empty;
        }
        RadGrid1.MasterTableView.FilterExpression = string.Empty;
        RadGrid1.MasterTableView.Rebind();
    }
    public static DataTable GetDataTable(string query)
    {
        string ConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(ConnString);
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(query, conn);
        DataTable myDataTable = new DataTable();
        conn.Open();
        try
        {
            adapter.Fill(myDataTable);
        }
        finally
        {
            conn.Close();
        }
        return myDataTable;
    }
}

public class MyCustomFilteringColumnCS : GridBoundColumn
{
    public static string ConnectionString
    {
        get { return ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; }
    }
    //RadGrid will call this method when it initializes the controls inside the filtering item cells
    protected override void SetupFilterControls(TableCell cell)
    {
        base.SetupFilterControls(cell);
        cell.Controls.RemoveAt(0);
        RadComboBox combo = new RadComboBox();
        combo.ID = ("RadComboBox1" + this.UniqueName);
        combo.ShowToggleImage = false;
        combo.Skin = "Office2007";
        combo.EnableLoadOnDemand = true;
        combo.AutoPostBack = true;
        combo.MarkFirstMatch = true;
        combo.Height = Unit.Pixel(100);
        combo.ItemsRequested += this.list_ItemsRequested;
        combo.SelectedIndexChanged += this.list_SelectedIndexChanged;
        cell.Controls.AddAt(0, combo);
        cell.Controls.RemoveAt(1);
    }
    //RadGrid will cal this method when the value should be set to the filtering input control(s)
    protected override void SetCurrentFilterValueToControl(TableCell cell)
    {
        base.SetCurrentFilterValueToControl(cell);
        RadComboBox combo = (RadComboBox)cell.Controls[0];
        if ((this.CurrentFilterValue != string.Empty))
        {
            combo.Text = this.CurrentFilterValue;
        }
    }
    //RadGrid will cal this method when the filtering value should be extracted from the filtering input control(s)
    protected override string GetCurrentFilterValueFromControl(TableCell cell)
    {
        RadComboBox combo = (RadComboBox)cell.Controls[0];
        return combo.Text;
    }
    private void list_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
    {
        ((RadComboBox)o).DataTextField = this.DataField;
        ((RadComboBox)o).DataValueField = this.DataField;

        string filterExpression = ((this as GridBoundColumn).Owner as GridTableView).FilterExpression;
        if (filterExpression != "")
        {
            ((RadComboBox)o).DataSource = GetDataTable("SELECT DISTINCT " + this.UniqueName + " FROM Customers WHERE " + this.UniqueName + " LIKE '" + e.Text + "%' AND " + filterExpression);
        }
        else
        {
            ((RadComboBox)o).DataSource = GetDataTable("SELECT DISTINCT " + this.UniqueName + " FROM Customers WHERE " + this.UniqueName + " LIKE '" + e.Text + "%'");
        }
        
        ((RadComboBox)o).DataBind();
    }
    private void list_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
    {
        GridFilteringItem filterItem = (GridFilteringItem)((RadComboBox)o).NamingContainer;
        if ((this.UniqueName == "Index"))
        {
            //this is filtering for integer column type
            filterItem.FireCommandEvent("Filter", new Pair("EqualTo", this.UniqueName));
        }
        //filtering for string column type
        filterItem.FireCommandEvent("Filter", new Pair("Contains", this.UniqueName));
    }
    public static DataTable GetDataTable(string query)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(query, conn);
        DataTable myDataTable = new DataTable();
        conn.Open();
        try
        {
            adapter.Fill(myDataTable);
        }
        finally
        {
            conn.Close();
        }
        return myDataTable;
    }
}