Dynamic Data Filtering – Table Text Search
Posted by jheyse on October 17th, 2008
I’m pretty happy with the number of people who have been downloading and trying out Dynamic Data Filtering. On average there are about 20 downloads a day with around 50 page views. There has been a lot of good feedback on both the ASP.NET forums and on the CodePlex project site 5over the last week or so. So much actually that I am having trouble keeping up with answering the questions coming in.
One question did catch my eye though. It was a member of the ASP.NET forum asking about being able to search on all of the columns within a given table. [View the Post] Dynamic Data doesn’t support this functionality out of the box, but it is flexible enough to write something yourself to do it. The main things that makes this possible is that a single FilterTemplate can return one ore more DynamicFilterParameters which produce lambda predicates.
In this case, you can accomplish this logic of searching multiple columns in a table by saying where ProductName LIKE [Value] or ProductNumber LIKE [Value] etc… To accomplish this in Dynamic Data Filtering you would utilize the OrExpressionParameter to create a collection of LikeExpressionParameters to do the predicate for each individual column.
I spent a little time creating a simple example which performs this logic. I also implemented a Columns property which allows you to specify using a comma separated list the columns you want searched. If Columns is left null or empty all columns are searched. The aspx for the user control only contains a single asp:TextBox with the Id=TextBox1.
public partial class TableTextSearch : Catalyst.Web.DynamicData.FilterTemplateUserControlBase
{
public string Columns { get; set; }
private MetaTable Table
{
get
{
IDynamicDataSource source = this.FindDataSourceControl();
if (source != null)
return source.GetTable();
return null;
}
}
public override IEnumerable<Parameter> GetWhereParameters(System.Web.DynamicData.IDynamicDataSource dataSource)
{
OrExpressionParameter parameter = new OrExpressionParameter();
string[] cols = null;
if (!string.IsNullOrEmpty(Columns))
{
cols = Columns.Split(',');
for (int i = 0; i < cols.Length; i++)
cols[i] = cols[i].Trim().ToUpper();
}
var columns = (from c in Table.Columns
where cols == null || cols.Length == 0 || cols.Contains(c.Name)
group c by c.TypeCode).ToDictionary(g => g.Key, g => g);
foreach (var column in columns[TypeCode.String])
{
parameter.Parameters.Add(new LikeExpressionParameter()
{
Type = TypeCode.String,
Name = column.Name,
Value = TextBox1.Text,
Like = LikeExpressionParameter.LikeType.Contains
});
}
yield return parameter;
}
public override void LoadQueryStringParameters(System.Collections.Specialized.NameValueCollection parameters)
{
TextBox1.Text = parameters["TableTextSearch"];
}
public override System.Collections.Specialized.NameValueCollection SaveQueryStringParameters()
{
return new NameValueCollection() { { "TableTextSearch", TextBox1.Text } };
}
public override void Clear()
{
TextBox1.Text = string.Empty;
}
}
To implement filters like this one you must use the DynamicFilterForm as opposed to the DynamicFilterRepeater. The reason is that the TableTextSearch filter control is associated with any column/property in particular but the entire table instead. It may make sense to allow the FilterAttribute to be annotated at the class level to address this.
<asp:DynamicFilterForm ID="DynamicFilterForm1" runat="server" DataSourceID="GridDataSource">
<FilterTemplate>
<div>
Search
</div>
<div>
Keyword:
<dd:TableTextSearch runat="server" ID="TableTextSearch" />
<asp:LinkButton ID="LinkButton4" runat="server" CommandName="Search">Search</asp:LinkButton>
<asp:LinkButton ID="LinkButton5" runat="server" CommandName="Clear">Clear</asp:LinkButton>
</div>
</FilterTemplate>
</asp:DynamicFilterForm>
To build upon this example it would be nice to be able to search the DisplayText of foreign columns and potentially non text values. For example if the user entered a string which can be converted to a date, search all DateTime columns.
