To start with you have to create four essentials stored
procedures that will help build the data layer of this screen. Note that these
stored procedures can be formulated directly within code; however, it is much
more professional to create them using Microsoft SQL Server.
The code for the four stored procedure is presented below.
Listing 1
ALTER PROCEDURE dbo.SelectAllProducts
AS
Select Top 50 * From Products
RETURN @@RowCount
A Procedure to Select Products By Category Id
Listing 2
ALTER PROCEDURE dbo.SelectProductByCategoryId
(@CategoryId as int)
AS
Select * From Products Where CategoryId = @CategoryId
RETURN @@RowCount
A Procedure to Select Product By Name
Listing 3
ALTER PROCEDURE dbo.SelectProductByName
(@ProductName as varchar(50))
AS
Select * From Products Where ProductName Like '%' + @ProductName + '%'
RETURN @@RowCount
A procedure used to populate the category ComboBox
Listing 4:
ALTER PROCEDURE dbo.CbxCategories
AS
Select CategoryID, CategoryName From Categories
RETURN @@RowCount
After having created these four stored procedures, we are
able to proceed with the ASP.NET application. Follow the steps given below.
1.
You need to create a new ASP.NET web application, name it DataGridFilter and place it in the default directory.
2.
In webform1 that is open, drag a DataGrid from the toolbox into it.
3.
Give the DataGrid the professional look by applying AutoFormat from the right click menu.
4.
Now, right click and choose property builder for designing our
DataGrid.
5.
Go to the column's option and clear the check box of Create Columns
automatically at run time.
6.
Add a first bound column. The Header should be Product
Id and the field name ProductID.
7.
Add a second bound column. The Header should be Name
and the field name ProductName.
8.
Add a third bound column. The Header should be Category
ID and the field should be CategoryID.
9.
While still in the property builder, choose the bound column of Name and convert it to a template. Do the same for the Category ID column.
10. Exit
the property builder and start template editing of the Name
column.
11. In
the Header property, insert a text box and name it TxtFilterName.
12. Beside
it, insert a button with the following properties: Text= …,
CommandName=Filter, CommandArgument= TxtFilterName,
ToolTip=Filter By Name, Name=BtnFilterName
13. Move
into template editing of the Category ID column and
insert a drop down list inside the header part and name it CbxFilterCategoryID.
14. Beside
it, insert a button with the following properties: Text= …,
CommandName=Filter, CommandArgument= CbxFilterCategoryId,
ToolTip=Filter By Category, Name=BtnFilterCategory
15. By
now you should have the physical shape of the webform as listed in Figure 2.
Figure 2
Now, we move to the coding part.
First of all, we have to fill the combo box by all available
categories in the database. To do this, we write a fairly simple Visual Basic .NET
code presented in the listing below in the page code section.
A code listing to fill the combo box of category
Listing 5
Public Function BindCategoryID()
Dim ConnStr As String = _
"Data Source=.; Initial Catalog=Northwind; User ID=sa; Password=pass@word1"
Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnStr)
Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("CbxCategories", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myConnection.Open()
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End FunctionTo complete the binding we need to add the following line in
the drop down list in HTML.
DataSource="" DataTextField="CategoryName" DataValueField="CategoryID".
If you run your project, you will have your combo box filled
with the categories.
There are some variables that are going to be used for the
whole form, thus defining them at the top of the web form.
Global Form Variables
Listing 6
Dim MySQLConnection As New SqlClient.SqlConnection
Dim MySQLCommand As New SqlClient.SqlCommand
Dim MyDataAdapter As New SqlClient.SqlDataAdapter
Dim MyDataSet As New Data.DataSet
Next is the fun part. Let us see how to fill data and then
apply filters.
Listing 7
Private Sub SetupData()
MySQLConnection.ConnectionString = _
"Data Source=.; Initial Catalog=Northwind; User ID=sa; Password=pass@word1"
MySQLConnection.Open()
MySQLCommand.Connection = MySQLConnection
MySQLCommand.CommandType = CommandType.StoredProcedure
MySQLCommand.CommandText = "SelectAllProducts"
MyDataAdapter.SelectCommand = MySQLCommand
MyDataAdapter.Fill(MyDataSet, "Products")
DataGrid1.DataSource = MyDataSet
DataGrid1.DataMember = "Products"
MySQLConnection.Close()
End Sub
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
If Not Me.IsPostBack Then
Call SetupData()
DataGrid1.DataBind()
End If
End Sub
To apply the filter when the user clicks the button next to
the filter part, just add the following code.
Listing 8
Private Sub DataGrid1_ItemCommand(ByVal Source As Object, _
ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _
Handles DataGrid1.ItemCommand
Select Case e.CommandName
Case "Filter"
Dim FirstName As String
Dim TxtFilter As TextBox, CbxFilter As DropDownList, FilterValue As String
Try
If Left(e.CommandArgument, 3) = "Cbx" Then
CbxFilter = CType(e.Item.FindControl(e.CommandArgument), DropDownList)
FilterValue = CbxFilter.SelectedValue
Else
TxtFilter = CType(e.Item.FindControl(e.CommandArgument), TextBox)
FilterValue = TxtFilter.Text
End If
If FilterValue Is Nothing Then
Call SetupData()
ElseIf FilterValue = String.Empty Then
Call SetupData()
Else
If e.CommandArgument = "CbxFilterCategoryId" Then
MySQLConnection.ConnectionString =_
"Data Source=.; Initial Catalog=Northwind; User ID=sa; Password=pass@word1"
MySQLConnection.Open()
MySQLCommand.Connection = MySQLConnection
MySQLCommand.CommandType = CommandType.StoredProcedure
MySQLCommand.Parameters.Add("@CategoryId", FilterValue)
MySQLCommand.CommandText = "SelectProductByCategoryID"
MyDataAdapter.SelectCommand = MySQLCommand
MyDataAdapter.Fill(MyDataSet, "Products")
DataGrid1.DataSource = MyDataSet
DataGrid1.DataMember = "Products"
MySQLConnection.Close()
ElseIf e.CommandArgument = "TxtFilterName" Then
MySQLConnection.ConnectionString =_
"Data Source=.; Initial Catalog=Northwind; User ID=sa; Password=pass@word1"
MySQLConnection.Open()
MySQLCommand.Connection = MySQLConnection
MySQLCommand.CommandType = CommandType.StoredProcedure
MySQLCommand.Parameters.Add("@ProductName", FilterValue)
MySQLCommand.CommandText = "SelectProductByName"
MyDataAdapter.SelectCommand = MySQLCommand
MyDataAdapter.Fill(MyDataSet, "Products")
DataGrid1.DataSource = MyDataSet
DataGrid1.DataMember = "Products"
MySQLConnection.Close()
End If
End If
Catch ex As Exception
Finally
DataGrid1.DataBind()
End Try
End Select
End SubWe reached the end and the webpage is now fully working. Go
and experiment with your project and have fun. Just remember, to cancel the
filter you should click the button of the name when the text box related to it is
empty.
