Wednesday, March 18, 2009

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!

0 Comments:

2012 | aspnet code by Michele | don't try this at home
Mirko Iodice Notageek
Vladimir Carrer Carrer web log