Sorting and Filtering Dynamically

If a query returns a large set of records, the results will be more useful if the user can define the order in which the rows are returned and which rows should be filtered out.

Adding a Sort Order to a Dynamic Query

To sort a dynamic query, just append the ORDER BY clause followed by a list of columns in the order indicated by the user. If the user wants to get the information in descending order, also append the DESC keyword.

In the following code, the order selections are stored in a special collection called SortInfo, a custom class designed to store all the information needed for each order selection.

Public Class SortInfo     Public SchemaName As String     Public TableName As String     Public ColumnName As String     Public SortOrder As SortOrder     Public SortPosition As Integer End Class

To create the order list, the code goes through this list, adding the definition into a StringBuilder, and in case some ordering is defined, adds the ORDER TO clause at the beginning of the StringBuilder content.

For Each o As System.Collections.Generic.KeyValuePair( _             Of Integer, SortInfo) In OrderList     With sbOrderBy         If .Length <> 0 Then            .Append(",")         End If            .AppendFormat("[{0}].[{1}]", o.Value.TableName, _                 o.Value.ColumnName)         If o.Value.SortOrder = SortOrder.Descending Then            .Append(" DESC")         End If     End With Next If sbOrderBy.Length > 0 Then    sbOrderBy.Insert(0, " ORDER BY ") End If

Filtering a Dynamic Query

Adding filtering to a query is a bit more complicated than adding a sort order. You have to take into account the many different operators and datatypes. The results, however, are worth the effort. Again, our starting point is the base syntax of the WHERE clause filtering expression in a SELECT statement:

SELECT <Field_List> FROM <Table_Name> WHERE <Condition_List> ORDER BY <Order_List>

The condition list has to be constructed using the following guidelines:

• Use the <ColumnName> <compare operator> <value> pattern.

• Match the value datatype with the column datatype.

• Add additional filters to the Condition_List using the AND or OR keywords.

You have to implement a user interface for the user to specify values and comparison operators for each filter. The goal is to take the user's input and create a string with the filter conditions that you can then insert into the dynamic query.

A Full-Fledged Dynamic Query Sample Application

The following examples are based on The Dynamic Queries sample application included in the Filters folder in the Chapter 7 sample files. The Dynamic Queries sample application is a fleshed-out version of the application you've created so far in this chapter. It includes a ListView control that shows all the selections made by the user in order to construct a dynamic query, as shown in Figure 7-1. To try this yourself, perform the procedure below.

Figure 7-1. ListView control with information about the query the user wants to create.

Run the Dynamic Queries Sample Application

1.	Double-click the Ch07.sln file in the Chapter07\Filters folder to open the project in Visual Studio.
2.	Choose Start Debugging from the Debug menu. Select your server and click the OK button. Visual Studio builds and runs the project, displaying an initial form, as shown here:
3.	In the frmPpal form, click the Data dropdown list on the toolbar, then select Table | Sales | Customer.
4.	Select the CustomerID, TerritoryID, and AccountNumber items in the listbox and click the right-arrow icon in the middle of the form.
5.	Click the Build Query button on the toolbar. Your screen now displays the fields you've selected in the upper right and the corresponding SQL query in the lower right, like this:
6.	Right-click in the CustomerID row and choose Order | Ascending from the context menu.
7.	Right-click again in the CustomerID row and choose Filter from the context menu. In the Filter dialog box, choose Less_Than in the dropdown list and enter 20 in the textbox. Click the OK button.
8.	Again click the Build Query button on the toolbar. The dynamic query now looks like this: SELECT [CustomerID],[TerritoryID],[AccountNumber] FROM Sales.Customer WHERE [CustomerID] < 20 ORDER BY [Customer].[CustomerID]
9.	Click the Execute Query button to execute the dynamic query. The screen will display the results:

How the Sample Application Builds Its Filter String

The application stores the different values shown in the upper right of the form in a ListView control, named lvUseFields, as shown in Table 7-1.

Table 7-1. Fields in the lvUseFields ListView

Property	Contains
Text	Schema name
SubItems(1)	Table name
SubItems(2)	Field name
SubItems(3)	Name (Alias for the result column)
SubItems(4)	Order position
SubItems(5)	Filter operator
SubItems(6)	Filter value

When you click the Build Query button on the toolbar, the following code builds the dynamic query using the contents of the lvUseFields ListView control. Pay particular attention to the section of code shown in bold, which demonstrates how to build the WHERE clause for filtering the dynamic query.

Private Sub tsbGen_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles tsbGen.Click     Dim baseSQL As String = "SELECT {0} from {1}"     'List of Fields to show     Dim sbFields As New System.Text.StringBuilder     'List of columns order     Dim sbOrderBy As New System.Text.StringBuilder("")     'Filters     Dim sbFilter As New System.Text.StringBuilder("")     With sbFields         For Each el As ListViewItem In lvUseFields.Items             If .Length <> 0 Then                 .Append(",")             End If             'Add the Column in the list of fields             .AppendFormat("[{0}]", _                  el.SubItems(UseFieldsColumnsEnum.Field).Text)             'If the Name is different than the column name, add the Alias             If el.SubItems(UseFieldsColumnsEnum.Name).Text <> _                 el.SubItems(UseFieldsColumnsEnum.Field).Text Then                 .AppendFormat(" AS [{0}]", _                     el.SubItems(UseFieldsColumnsEnum.Name).Text)             End If             'If there is a filter...             If el.SubItems(UseFieldsColumnsEnum.Filter).Text <> "" Then                 With sbFilter                     If .Length > 0 Then                         .Append(" AND ")                     End If                     'Add the column name to the list of filters                     .AppendFormat("[{0}]", el.SubItems( _                         UseFieldsColumnsEnum.Field).Text)                     'Add the operator                     Select Case CType([Enum].Parse( _                         GetType(FilterTypeEnum), _                         el.SubItems(UseFieldsColumnsEnum.Filter _                         ).Text.Replace(" ", "_")), FilterTypeEnum)                         Case FilterTypeEnum.Equal                             .Append(" = ")                         Case FilterTypeEnum.Not_Equal                             .Append(" <> ")                         Case FilterTypeEnum.Greather_Than                             .Append(" >")                         Case FilterTypeEnum.Less_Than                             .Append(" < ")                         Case FilterTypeEnum.Like                             .Append(" LIKE ")                         Case FilterTypeEnum.Between                             .Append(" BETWEEN ")                     End Select                     'Get the Data type from the columns definition                     Dim Typename As String = _                         tableColumns.Select(String.Format("COLUMN_NAME='{0}'", _                         el.SubItems(UseFieldsColumnsEnum.Field).Text))(0).Item( _                         "DATA_TYPE").ToString                     'When the data type is one with characters values,                     'the value must be enclosed by apostrophes                     If Typename.ToUpper.IndexOf("CHAR") > -1 _                         OrElse Typename.ToUpper.IndexOf("TEXT") > -1 Then                         .Append("'")                     End If                     .Append(el.SubItems(UseFieldsColumnsEnum.Filter_Value).Text)                     'If the operator is Like, append the wildcard '%'                     If CType([Enum].Parse(GetType(FilterTypeEnum), _                     el.SubItems(UseFieldsColumnsEnum.Filter).Text.Replace( _                         " ", "_")), FilterTypeEnum) = FilterTypeEnum.Like Then                         .Append("%")                     End If                     If Typename.ToUpper.IndexOf("CHAR") > -1 _                     OrElse Typename.ToUpper.IndexOf("TEXT") > -1 Then                         .Append("'")                     End If                 End With             End If         Next     End With     'Add the Order     For Each o As System.Collections.Generic.KeyValuePair( _                 Of Integer, SortInfo) In OrderList         With sbOrderBy             If .Length <> 0 Then                 .Append(",")             End If             .AppendFormat("[{0}].[{1}]", o.Value.TableName, o.Value.ColumnName)             If o.Value.SortOrder = SortOrder.Descending Then                 .Append(" DESC")             End If         End With     Next     If sbOrderBy.Length > 0 Then         sbOrderBy.Insert(0, " ORDER BY ")     End If     If sbFilter.Length > 0 Then         sbFilter.Insert(0, " WHERE ")     End If     'The query string has to be: SELECT columns FROM table,     'then WHERE and finally ORDER     txtsql.Text = _         String.Format(baseSQL, _         sbFields.ToString, _         String.Format("{0}.{1}", SchemaName, TableName)) & _         sbFilter.ToString & " " & sbOrderBy.ToString End Sub

Considerations for Formatting a Filter String

As you build the filter, for each column whose datatype is one that contains characters (char, nchar, varchar, nvarchar, text, or ntext), the values compared to that column must be enclosed by apostrophes (').

When filtering a smalldatetime or datetime datatype, your code depends on the country and cultural settings for your user's machine. Can you assume that the date "11/10/05" is November 10, 2005, or is it October 11, 2005, or could it be October 5, 2011? This can be a real problem in any application, but it is even more significant issue if you are constructing an application for the Web. In that case, you must consider users from all around the world.

The simplest solution can be instructing the user about how to enter dates. It would be better to have a solution so that users don't have to be trained. SQL Server uses the date formats defined by the cultural settings in the server's host operating system. Nevertheless, it always recognizes the ISO format, which matches the following pattern: yyyy-mm-dd.

If you use the CultureInfo setting on the user's machine, you can get a DateTime value. Use that value to create the appropriate comparison value in your filter. In addition, just to be sure you are sending the appropriate string to SQL Server, you can also use the CONVERT function. Here's how to do all this:

Dim theDate As Date = DateTime.Parse(el.SubItems(1).Text, _     System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat) sparam = String.Format("Convert(datetime,'{0}-{1}-{2}',120)", _      theDate.Year, _      theDate.Month, _      theDate.Day)

The tsbGen_Click procedure generates the following dynamic query using the fields shown in Figure 7-1.

SELECT [Name],     [ProductNumber],     [Color],     [ListPrice],     [Size],     [Weight],     [Style] FROM Production.Product WHERE [ListPrice] >100 ORDER BY [Product].[Name]