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.

Enjoy!

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.

More to come.

In ASP.NET, you can utilize all of this new functionality by using the SqlParameter class with any data source control, including the .netTiers generated ones.

CustomerID:
<asp:TextBox ID="txtCustomerID" Text="A*" runat="server" />
<br /><br />

<asp:GridView ID="GridView1" DataSourceID="dsCustomers" runat="server" />

<data:CustomersDataSource ID="dsCustomers" SelectMethod="Find" runat="server">
   <Parameters>
      <data:SqlParameter Name="Parameters">
         <Filters>
            <data:CustomersFilter Column="CustomerID" ControlID="txtCustomerID" />
         </Filters>
      </data:SqlParameter>
   </Parameters>
</data:CustomersDataSource>

I have emphasized two important properties in the code above.  First, the SelectMethod is set to Find so that the method which accepts a SqlFilterParameter object is executed, as in the first example.  Second, the Name property of the SqlParameter is set to Parameters.  This is very important when using the typed data source controls as this will allow the collection to be auto-detected and passed to the underlying method correctly.

One or more Filters may be specified for a SqlParameter.  The CustomersFilter object in this example gives you full intellisense for the Column property, which is the list of values from the CustomersColumn enumeration.

Done.

Thank you very much Bobby, I can't find words to say how cool is this!!! In the past I've been writing String.Format :) Man! Уou are genius!! ;)

Bobby

Firstly, a BIG thanks to everyone involved in this little gem being added - should make things a whole lot neater/easier.

Secondly, I've just tried to create a filter on a nullable column using the following assuming that it would work fine but it appears that when the Append() method is called, it checks for an empty string and if the string is empty it just ignores this append (i.e. nothing gets added to the query string). 

        Dim query1 As New ContactFilterBuilder
        query1.Append(ContactColumn.Homephone, Nothing)
        Debug.Print(query1.ToString)

I know that querying for Nulls is a bit of a pain at the moment but it is something that a lot of people use fairly regularly so just wanted to make a suggestion (or a couple of different options really).

First option - instead of dropping the Append if the string is empty could the code add an "entity.columnName Is Null" clause to the query instead?

Alternatively, don't change the above behaviour, but do check for the string value being "Null" and then add an "entity.columnName Is Null" clause?

Either of the 2 above options should enable people to return records that contain Nulls if they need to.

Cheers
Martin

Alex wrote:

Thank you very much Bobby, I can't find words to say how cool is this!!! In the past I've been writing String.Format :) Man! Уou are genius!! ;)

I agree .....things getting better and better.

Hi

First: Very nice/useful post!
Second: How can I expand this to use a DropDownList instead of a Textbox? I tried it but I can't get it to work! If you could kindly post a sample code I would appreciate it!

Thx

Thanks for the suggestions.  I have added a couple of new methods to the query builder classes.  Try using AppendIsNull and/or AppendIsNotNull to see if they work for you.  These have been checked into SVN rev 296.

Thanks.

A DropDownList control should work just like a TextBox.  If you are still having trouble, can you post some sample code so I can see what you are trying to do?

Thanks.

Cheers Bobby - I'll take a butchers at the new methods over the weekend (assuming my 'other half' doesn't have a list of chores a mile long for me!).

Thanks again

Martin

Hi bdiaz

I got it working. I searched some more and found another post here that ilustrated how to do it with a DropDownList. I don't remember the post but here is the code I used to test it (with the Northwind DB):

<data:CategoriesDataSource ID="ddlCategory" runat="server" SelectMethod="GetAll">
</data:CategoriesDataSource>

<asp:DropDownList AppendDataBoundItems="true" ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="ddlCategory" DataTextField="CategoryName" DataValueField="CategoryID">
   <asp:ListItem Value="" Selected="True">All</asp:ListItem>
</asp:DropDownList>

<asp:GridView ID="GridView2" DataKeyNames="ProductID" PageSize="10" AllowPaging="true" AllowSorting="true" DataSourceID="dsProducts" runat="server" AutoGenerateColumns="False" >
   <Columns>
      <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
      <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" SortExpression="CategoryID" />
   </Columns>
</asp:GridView>

<data:ProductsDataSource ID="dsProducts" SelectMethod="GetPaged" EnablePaging="true" EnableSorting="true" runat="server">
   <Parameters>
      <data:SqlParameter Name="WhereClause" UseParameterizedFilters="false">
         <Filters>
            <data:ProductsFilter Column="CategoryID" ControlID="DropDownList1" />
         </Filters>
      </data:SqlParameter>
   </Parameters>
</data:ProductsDataSource>

Cheers

Hi bdiaz,

Thanks much your useful post. I'm working with SQLStringBuilder in Net-tiers framework 0.9.2 and  I could not build a filter query with Greater operand !! . Ex: I need to select from Products where quality >= 4.
I've tried some ways but it did not work.I've found some methods in net-tiers generated classes but no results. Now, I'm using a bad way: create my query and append it to filter query.

My way looks like:

function string CreateQuery()
{
    SqlStringBuilder filter = new SqlStringBuilder();
    filter.Append(column1, value1);
    filter.Append(...);

    string myQuery  = " and Quality >= 4 ";
    return  filter.ToString() + myQuery;
}

Is there another way  to utilize net-tiers SQL builder classes to generate this ?  Please tell me.
Thank you so much,

Hieu Vu.
 

Do you have the following methods in your SqlStringBuilder class?  If not, you would need to get the latest release of the templates.

AppendGreaterThan
AppendGreaterThanOrEqual
AppendLessThan
AppendLessThanOrEqual

Thanks.

Hello guys,

 I have been looking to use the Utility classes for my current project, but have no idea where to find them.

 In particular i am looking for the Strongly Typed Filter Parameters

 

Any pointers would be great