17 April 2009

Using the Filtering API with the SQL Comparison SDK

Red Gate's SQL Comparison SDK provides a means to compare and synchronize database schemas and data from within your own applications, with a greater degree of control than the command line interfaces. Filters, introduced in V8.0, provide an easy way of limiting which database objects you deal with, and can be created with any arbitrary expression in a SQL-like syntax.

One of the new features in SQL Compare 8.0 is the ability to filter the objects displayed in the comparison results screen by various conditions:

692-rulebuilder.png

In this article, I’ll give an overview of how you can use the filter in your own code.

Filtering Basics

Let’s jump in straight at the deep end, and give a simple example of using the filter on the differences from a comparison:

Here, the bit we’re interested in is below the FILTER: comment. The DifferenceFilter class is the one that manages the filter expressions and performs the filtering. A separate filter can be set for each object type recognised by SQL Compare, as well as a global condition (specified by ObjectType.None). Each object type can be set as unconditionally included, unconditionally excluded, or conditionally included. This corresponds to the three-state checkbox in the filter panel in the SQL Compare UI, and there are methods on the DifferenceFilter class to set each of these filter conditions. For instance, to include all views, stored procedures, and tables that have a schema of ‘Sales’, you would do something like this:

you can also check whether a particular difference is included by the filter, rather than using the FilterDifferences method:

The boolean argument to SetObjectTypeFilter determines if the filter expression is for inclusion or exclusion – if true, objects will be included if they match the filter; if false, objects will be excluded if they match the filter. There are also constructors on the DifferenceFilter class to determine if the filter matches case-sensitively, and if the filter includes or excludes everything by default.

Filter expressions

The filter expressions have a simple SQL-like syntax. Individual clauses are in the form @NAME != 'table', just like in a WHERE clause. The two object properties you can test against are @NAME and @SCHEMA, corresponding to the object name and schema. The operators you can use are ‘=’, ‘!=’, ‘LIKE’, and ‘NOT LIKE’. The = and != test against a simple string value, the LIKE and NOT LIKE clauses test against patterns with the % symbol, with the same meaning as in SQL (Currently, _ is not a regular expression symbol used for matching). These clauses can be strung together with any combination of ANDs, ORs and NOTs, with the same operator precedence as in SQL, and can be arbitrarily complex:

You can also create a DifferenceFilter using a global filter expression, using the DifferenceFilterFactory.CreateFilter method:

The @TYPE property is similar to the @NAME and @SCHEMA, except you can only use ‘=’ and ‘!=’ conditions on it, and it only accepts the full object names as values, for instance 'user defined type' or 'symmetric key'. Given the global expression, the DifferenceFilterFactory will separate the expression into different object types (yes, even if there’s a @TYPE right in the middle of a nest of brackets) and create a DifferenceFilter corresponding to that expression.

IFilterCondition

Alternatively, if you don’t wish to deal with expression strings directly, you can create the object tree for each object type directly:

The relevant classes are:

  • IFilterCondition<T> (the root of the object hierarchy)

  • OR<T>

  • AND<T>

  • NOT<T>

  • Condition

You will notice that all the classes apart from Condition are generic – if you create your own leaf condition for the object type you’re considering, you can use the filter types to filter objects other than Differences.

The main method that does the filtering is

Implementations of this method return a nullable bool indicating if the given object will be included, excluded, or if the filter condition does not apply (for instance, schema conditions on non-schema objects).

You can access the filter conditions in a DifferenceFilter using the DifferenceFilter.GetFilter method and access the Filter property on the ObjectTypeFilter returned. The corresponding string representation can also be returned using the ToString method on a filter condition.

Summary

Filters provide an easy way of limiting which database objects you deal with, and can be created with any arbitrary expression in a SQL-like syntax. Alternatively, you can create the filter expression trees yourself, which is an easier way of integrating with your own front end to the SDK. There are many more ways of using filters than the examples in this article; hopefully this has given you some ideas on using filters in your own code.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 11771 times – thanks for reading.

Tags: , , , ,

  • Rate
    [Total: 10    Average: 4.2/5]
  • Share

Simon joined Red Gate as a software developer in 2007. He has spent most of his time on SQL Compare 7 and 8, where he concentrated on the SQL Compare engine. Other projects have included the new Check for Updates and work on the internal shared libraries. He is currently researching and developing some new products for Red Gate to move into, which he can’t say too much about at the moment!

View all articles by Simon Cooper