Imports Microsoft.VisualBasic
Imports System.Data
Imports Telerik.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI
Imports System
Imports System.Data.OleDb

Namespace RadGrid

    ' <summary>
    ' Custom column that shows filtering dropdown instead of textbox
    ' </summary>
    ' 
    Public Class MyCustomFilteringColumn
        Inherits GridBoundColumn

        Public Shared ReadOnly Property connectionString() As String
            Get
                Dim dbPath As String = System.Web.HttpContext.Current.Server.MapPath("~\App_Data\Nwind.mdb")
                Return ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbPath)
            End Get
        End Property

        'RadGrid will cal this method when it initializes the controls inside the filtering item cells
        Protected Overrides Sub SetupFilterControls(ByVal cell As TableCell)
            MyBase.SetupFilterControls(cell)
            cell.Controls.RemoveAt(0)
            Dim combo As RadComboBox = New RadComboBox
            combo.ID = ("RadComboBox1" + Me.DataField)
            combo.ShowToggleImage = False
            combo.Skin = "Office2007"
            combo.EnableLoadOnDemand = True
            combo.AutoPostBack = True
            combo.MarkFirstMatch = True
            combo.Height = Unit.Pixel(100)
            AddHandler combo.ItemsRequested, AddressOf Me.list_ItemsRequested
            AddHandler combo.SelectedIndexChanged, AddressOf Me.list_SelectedIndexChanged
            cell.Controls.AddAt(0, combo)
            cell.Controls.RemoveAt(1)
        End Sub

        'RadGrid will cal this method when the value should be set to the filtering input control(s)
        Protected Overrides Sub SetCurrentFilterValueToControl(ByVal cell As TableCell)
            MyBase.SetCurrentFilterValueToControl(cell)
            Dim combo As RadComboBox = CType(cell.Controls(0), RadComboBox)
            If (Me.CurrentFilterValue <> String.Empty) Then
                combo.Text = Me.CurrentFilterValue
            End If
        End Sub

        'RadGrid will cal this method when the filtering value should be extracted from the filtering input control(s)
        Protected Overrides Function GetCurrentFilterValueFromControl(ByVal cell As TableCell) As String
            Dim combo As RadComboBox = CType(cell.Controls(0), RadComboBox)
            Return combo.Text
        End Function

        Private Sub list_ItemsRequested(ByVal o As Object, ByVal e As RadComboBoxItemsRequestedEventArgs)
            CType(o, RadComboBox).DataTextField = Me.DataField
            CType(o, RadComboBox).DataValueField = Me.DataField
            CType(o, RadComboBox).DataSource = GetDataTable("SELECT DISTINCT " & Me.UniqueName & " FROM Customers WHERE " & Me.UniqueName & " LIKE '" & e.Text & "%'")
            CType(o, RadComboBox).DataBind()
        End Sub

        Private Sub list_SelectedIndexChanged(ByVal o As Object, ByVal e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs)
            Dim filterItem As GridFilteringItem = CType(CType(o, RadComboBox).NamingContainer, GridFilteringItem)
            If (Me.UniqueName = "Index") Then
                'this is filtering for integer column type 
                filterItem.FireCommandEvent("Filter", New Pair("EqualTo", Me.UniqueName))
            End If
            'filtering for string column type
            filterItem.FireCommandEvent("Filter", New Pair("Contains", Me.UniqueName))
        End Sub

        Public Shared Function GetDataTable(ByVal query As String) As DataTable
            Dim MyOleDbConnection As OleDbConnection = New OleDbConnection(connectionString)
            Dim MyOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
            MyOleDbDataAdapter.SelectCommand = New OleDbCommand(query, MyOleDbConnection)
            Dim myDataTable As DataTable = New DataTable
            MyOleDbConnection.Open()
            Try
                MyOleDbDataAdapter.Fill(myDataTable)
            Finally
                MyOleDbConnection.Close()
            End Try
            Return myDataTable
        End Function
    End Class
End Namespace
