Custom Filter

There are many ways to filter data in a grid, here one of them...

All you need is to create a DataSet connected with a table and a little code!
(in the example I use "AdventureWorks" as database and "Person.CountryRegion" as table)

Let's start!

As usual, we have to write an object to connect a GridView with a source like a dataset/database.

Public Function GetCountryRegion(Optional ByVal CountryRegionCode As String = "%", _
                                   Optional ByVal Name As String = "%") As ds_CountryRegion.CountryRegionDataTable

      Dim cnn As SqlConnection = Nothing
      Dim cmd As SqlCommand = Nothing
      Dim da As SqlDataAdapter = Nothing
      Dim sqlStatement As String

      Try
          sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"

          cnn = New SqlConnection(Me._connectionString)
          cmd = New SqlCommand(sqlStatement, cnn)
          da = New SqlDataAdapter(cmd)

          cnn.Open()

          _ds.CountryRegion.Rows.Clear()
          da.Fill(_ds, "CountryRegion")
      Catch ex As Exception
          'todo: code for exceptions!
      Finally
          GetCountryRegion = _ds.CountryRegion

          If Not (cnn Is Nothing) Then
              cnn.Close()
              cnn.Dispose()
              cnn = Nothing
          End If
          If Not (da Is Nothing) Then
              da.Dispose()
              da = Nothing
          End If
          If Not (cmd Is Nothing) Then
              cmd.Dispose()
              cmd = Nothing
          End If
      End Try

  End Function

As you can see, I use the keyword "Optional" for the parameters and I use "%" as default value.
In T-Sql language "%" stands for "all". If there are no parameters passed, the code reads "all" CountryRegionCode records and "all" Name records.

Another interesting thing is the use of the keyword "like" and the use of "%" at the end of the parameters in the sqlStatement string ("like 'Name%'" and "like 'CountryRegionCode%'").

In this example I can catch all records with the Name that starts with the parameter passed: all records with "Name%" starts with "I%" (Italy, Ireland, Iceland...) (ok I know, it's not easy for me to explain in eng... I'm sorry!)

sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"

When our object is ready and connected with a GridView through the "DataSource" property, we have to use the "Databind" property to associate the data with the GridView.

In the Page_Load event, there are no parameters passed to the DataTable object (in fact the two TextBox values are empty ---> "")

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

      If Not IsPostBack Then
          GetGwCountryRegion()
      End If

  End Sub

But each time that we write something in the TextBox Name or CountryRegionCode and click on "Filter", the DataTable recives parameters and use them for filtering.

That's all!

You can download this my little example here.
You can download and install AdventureWorksDB.msi database here.

Please remember to change in the web.config file, the two values "Data Source=pc01;" with
your sqlserver instance name. (probably "computername" with Sql2005/2008, but if you are using SqlExpress edition your instance name is "computername\SQLExpress")

Enjoy!