netTiers New Query Builder Classes

 The .netTiers team has been busy getting ready for the beta 2 release, but we still found a little time to squeeze in some new utility classes to help developers query their data.  As of this initial post, the majority of the implementation has been done in the data access layer, but will be added to the component layer very soon!  Here is a brief list of a few of the recent additions:

SqlExpressionParser - a search term parser
SqlStringBuilder - a filter expression builder (uses SqlExpressionParser internally)

SqlFilterBuilder - a generic filter expression builder (uses entity column enumerations)
EntityFilterBuilder - a strongly typed filter expression builder

ParameterizedSqlExpressionParser - parses search terms into parameterized expressions
ParameterizedSqlFilterBuilder - a generic parameterized expression builder (uses entity column enumerations)
EntityParameterBuilder - a strongly typed parameterized expression builder

SqlFilterParameter - represents information needed for a database command parameter
SqlFilterParameterCollection - a parameterizd query string and a collection of SqlFilterParameter objects

SqlParameter - a subclass of System.Web.UI.WebControls.Parameter that will allow an ASP.NET developer to use the ParameterizedSqlFilterBuilder (default) or SqlFilterBuilder along with any data source control.
EntityFilter - used along with the SqlParameter to bind filter input controls to a data source control.

Some usage examples to follow.

After generating against the Northwind database, I will demonstrate the use of the CustomersParameterBuilder class.  First the code:

CustomersParameterBuilder query1 = new CustomersParameterBuilder();
query1.Append(CustomersColumn.CustomerID, "A%");
query1.Append(CustomersColumn.City, "London, Berlin");

TList<Customers> list1 = DataRepository.CustomersProvider.Find(query1.GetParameters());

Console.WriteLine("Query1 = {0}", query1);
Console.WriteLine("Count1 = {0}", list1.Count);

Then the result:

Query1 = (CustomerID LIKE @Param0) AND (City = @Param1 OR City = @Param2)
Count1 = 2

Along with the filter expression, query1 also has a collection of SqlFilterParameter objects that hold the name, type and value of each named parameter.

This collection, which is returned by calling query1.GetParameters(), is passed into a new Find method overload that dynamically generates a paramaterized SQL statement, applies the necessary command parameters, then executes the query.

The SqlStringBuilder class, along with all of its sub-classes, contain several variations to the Append method to allow you to define simple or complex queries.  Also, notice that the Append method used in this example accepts the use of wild card characters.

///

Similar to the previous example, I will show you how you can create a non-parameterized query for those times when one is not necessary.

The code:

CustomersFilterBuilder query2 = new CustomersFilterBuilder();
query2.Append(CustomersColumn.CustomerID, "A*");
query2.Append(CustomersColumn.City, "London, Berlin");

int count = 0;
TList<Customers> list2 = DataRepository.CustomersProvider.GetPaged(query2.ToString(), null, 0, 100, out count);

Console.WriteLine("Query2 = {0}", query2);
Console.WriteLine("Count2 = {0}", list2.Count);

The result:

Query2 = (CustomerID LIKE 'A%') AND (City = 'London' OR City = 'Berlin')
Count2 = 2

This time around we used the CustomersFilterBuilder class along with the GetPaged method.  The generated filter expression will contain the actual values Appended to the query.  Also, be aware that '*' characters will be treated as wild cards.

/

CustomersFilterBuilder searchQuery = new CustomersFilterBuilder();
        searchQuery.BeginGroup(string.Empty);
        searchQuery.AppendEquals(string.Empty, CustomersColumn.CustomerID, "Param0");
        searchQuery.AppendEquals("AND", CustomersColumn.City, "Param1");
        searchQuery.EndGroup();
        searchQuery.BeginGroup("OR");
        searchQuery.AppendEquals(string.Empty, CustomersColumn.CustomerID, "Param2");
        searchQuery.AppendEquals("AND", CustomersColumn.City, "Param3");
        searchQuery.EndGroup();

 <data:SqlParameter Name="Parameters">
                    <Filters>
                        <data:CustomersExpressionBuilder Column="CustomerID" DefaultValue="Param0" BuilderExpression="AppendEquals"  GroupState="Begin" />
                        <data:CustomersExpressionBuilder Junction="AND" Column="City" DefaultValue="Param1" BuilderExpression="AppendEquals" GroupState="End"  />                       
                        <data:CustomersExpressionBuilder Column="CustomerID" DefaultValue="Param2" BuilderExpression="AppendEquals"  GroupState="OrBegin" />
                        <data:CustomersExpressionBuilder Junction="AND" Column="City" DefaultValue="Param3" BuilderExpression="AppendEquals" GroupState="End"  />
                    </Filters>
                </data:SqlParameter>