Click here to monitor SSC
  • Av rating:
  • Total votes: 10
  • Total comments: 0
Simon Cooper

Using the Filtering API with the SQL Comparison SDK

17 April 2009

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:

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:

using System;

using RedGate.SQLCompare.Engine;

using RedGate.SQLCompare.Engine.Filter;

 

namespace FilterExample {

    class Program {

        static void Main() {

            // register and compare the two databases

            Database db1 = new Database();

            Database db2 = new Database();

            db1.Register(new ConnectionProperties("(local)", "WidgetDev"), Options.Default);

            db2.Register(new ConnectionProperties("(local)", "WidgetTest"), Options.Default);

 

            Differences diffs = db1.CompareWith(db2, Options.Default);

 

            // FILTER:

            // create a filter to exclude everything

            DifferenceFilter filter = new DifferenceFilter();

 

            // set the filter to include tables that have names starting with 'Widget'

            filter.SetObjectTypeFilter(ObjectType.Table, true, "@NAME LIKE 'Widget%'");

 

            // perform the filter, and display the results

            foreach (Difference d in filter.FilterDifferences(diffs)) {

                Console.WriteLine(d.Name);

            }

 

            Console.ReadKey();

        }

    }

}

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:

 

DifferenceFilter filter = new DifferenceFilter();

filter.SetObjectTypeFilter(ObjectType.Table, true, "@SCHEMA = 'Sales'");

filter.SetObjectTypeInclude(ObjectType.StoredProcedure);

filter.SetObjectTypeInclude(ObjectType.View);

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

bool included = filter.DifferenceIncluded(diff);

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:

(@SCHEMA = 'dbo' AND @NAME LIKE 'tbl%dev%')

OR (@SCHEMA NOT LIKE 'Schema%'

AND NOT (@NAME != 'table1' OR @NAME LIKE 'table1%inc'))

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

DifferenceFilter filter = DifferenceFilterFactory.CreateFilter(false,

@"(@TYPE = 'table' AND @NAME = 'widget')

OR @TYPE = 'view'

OR (@TYPE = 'stored procedure'

AND (@SCHEMA = 'dbo' OR @NAME = 'proc1'))");

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:

OR<Difference> or = new OR<Difference>();

or.Add(new Condition(true, FilterOn.Name, FilterType.LIKE, "tbl%dev"));

or.Add(new Condition(true, FilterOn.Schema, FilterType.NEQ, "dbo"));

DifferenceFilter filter = new DifferenceFilter();

filter.SetObjectTypeFilter(ObjectType.Table, true, or);

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

bool? IFilterCondition<T>.ObjectIncluded(T obj)

 

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.

Simon Cooper

Author profile:

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!

Search for other articles by Simon Cooper

Rate this article:   Avg rating: from a total of 10 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.