Hi roon3y,
I have created sample which full-fill your requirement. Here i have filter gridview with 3 dropdownlist. So you need to add as much possible. For this you need to modify the code aswell.
The filter process is done using the procedure.
For this sample I have used of NorthWind database that you can download using the link given below.
Download Northwind Database
SQL
CREATE PROCEDURE GetCustomerByMultipleParameter
@Address VARCHAR(500) = NULL
,@City VARCHAR(500) = NULL
,@Country VARCHAR(500) = NULL
AS
BEGIN
SELECT CustomerID,ContactName,Address,City,Country
FROM Customers
WHERE (Address = @Address OR @Address IS NULL)
AND (City = @City OR @City IS NULL)
AND (Country = @Country OR @Country IS NULL)
END
HTML
<div>
<table>
<tr>
<td>
Address
</td>
<td>
City
</td>
<td>
Country
</td>
</tr>
<tr>
<td>
<asp:DropDownList runat="server" ID="ddlAddress" AppendDataBoundItems="true" AutoPostBack="true"
OnSelectedIndexChanged="Filter">
<asp:ListItem Text="Select" Value="0" />
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlCity" runat="server" AppendDataBoundItems="true" AutoPostBack="true"
OnSelectedIndexChanged="Filter">
<asp:ListItem Text="Select" Value="0" />
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlCountry" runat="server" AppendDataBoundItems="true" AutoPostBack="true"
OnSelectedIndexChanged="Filter">
<asp:ListItem Text="Select" Value="0" />
</asp:DropDownList>
</td>
</tr>
</table>
<br />
<asp:GridView runat="server" ID="gvCustomers" AutoGenerateColumns="false" AllowPaging="true"
PageSize="10" OnPageIndexChanging="PageIndexChanging">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer Id" />
<asp:BoundField DataField="ContactName" HeaderText="Name" />
<asp:BoundField DataField="Address" HeaderText="Address" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
</div>
C#
string str = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateDropDownList(ddlAddress, "Address", "Address", "SELECT DISTINCT Address FROM Customers");
PopulateDropDownList(ddlCity, "City", "City", "SELECT DISTINCT City FROM Customers");
PopulateDropDownList(ddlCountry, "Country", "Country", "SELECT DISTINCT Country FROM Customers");
BindGrid("SELECT CustomerID,ContactName,Address,City,Country FROM Customers", CommandType.Text, new List<SqlParameter>().ToArray());
}
}
protected void Filter(object sender, EventArgs e)
{
List<SqlParameter> sqlParameter = new List<SqlParameter>();
if (ddlAddress.SelectedIndex > 0)
{
sqlParameter.Add(new SqlParameter("@Address", ddlAddress.SelectedItem.Text.Trim()));
}
if (ddlCity.SelectedIndex > 0)
{
sqlParameter.Add(new SqlParameter("@City", ddlCity.SelectedItem.Text.Trim()));
}
if (ddlCountry.SelectedIndex > 0)
{
sqlParameter.Add(new SqlParameter("@Country", ddlCountry.SelectedItem.Text.Trim()));
}
BindGrid("GetCustomerByMultipleParameter", CommandType.StoredProcedure, sqlParameter.ToArray());
}
private void BindGrid(string query, CommandType commandType, SqlParameter[] parameters)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandType = commandType;
if (parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
}
private void PopulateDropDownList(DropDownList ddl, string textField, string valueField, string query)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
ddl.DataSource = dt;
ddl.DataTextField = textField;
ddl.DataValueField = valueField;
ddl.DataBind();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvCustomers.PageIndex = e.NewPageIndex;
BindGrid("SELECT CustomerID,ContactName,Address,City,Country FROM Customers", CommandType.Text, new List<SqlParameter>().ToArray());
}
VB.Net
Private str As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
PopulateDropDownList(ddlAddress, "Address", "Address", "SELECT DISTINCT Address FROM Customers")
PopulateDropDownList(ddlCity, "City", "City", "SELECT DISTINCT City FROM Customers")
PopulateDropDownList(ddlCountry, "Country", "Country", "SELECT DISTINCT Country FROM Customers")
BindGrid("SELECT CustomerID,ContactName,Address,City,Country FROM Customers", CommandType.Text, New List(Of SqlParameter)().ToArray())
End If
End Sub
Protected Sub Filter(sender As Object, e As EventArgs)
Dim sqlParameter As New List(Of SqlParameter)()
If ddlAddress.SelectedIndex > 0 Then
sqlParameter.Add(New SqlParameter("@Address", ddlAddress.SelectedItem.Text.Trim()))
End If
If ddlCity.SelectedIndex > 0 Then
sqlParameter.Add(New SqlParameter("@City", ddlCity.SelectedItem.Text.Trim()))
End If
If ddlCountry.SelectedIndex > 0 Then
sqlParameter.Add(New SqlParameter("@Country", ddlCountry.SelectedItem.Text.Trim()))
End If
BindGrid("GetCustomerByMultipleParameter", CommandType.StoredProcedure, sqlParameter.ToArray())
End Sub
Private Sub BindGrid(query As String, commandType As CommandType, parameters As SqlParameter())
Dim dt As New DataTable()
Dim con As New SqlConnection(str)
Dim cmd As New SqlCommand(query, con)
cmd.CommandType = commandType
If parameters.Length > 0 Then
cmd.Parameters.AddRange(parameters)
End If
Dim sda As New SqlDataAdapter(cmd)
sda.Fill(dt)
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Sub
Private Sub PopulateDropDownList(ddl As DropDownList, textField As String, valueField As String, query As String)
Dim dt As New DataTable()
Dim con As New SqlConnection(str)
Dim cmd As New SqlCommand(query, con)
Dim sda As New SqlDataAdapter(cmd)
sda.Fill(dt)
ddl.DataSource = dt
ddl.DataTextField = textField
ddl.DataValueField = valueField
ddl.DataBind()
End Sub
Protected Sub PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
gvCustomers.PageIndex = e.NewPageIndex
BindGrid("SELECT CustomerID,ContactName,Address,City,Country FROM Customers", CommandType.Text, New List(Of SqlParameter)().ToArray())
End Sub
Screenshot
