xSQL Schema Compare SDK for SQL Server version 12
SchemaFilter Class
Members  Example 


Represents a schema filter.
Object Model
SchemaFilter Class
Syntax
'Declaration
 
<System.ObsoleteAttribute("Schema filters have been depreciated. Use the entity filters instead")>
Public NotInheritable Class SchemaFilter 
   Implements xSQL.Schema.Core.IKeyedEntity(Of SqlEntityTypeEnum) 
[System.ObsoleteAttribute("Schema filters have been depreciated. Use the entity filters instead")]
public sealed class SchemaFilter : xSQL.Schema.Core.IKeyedEntity<SqlEntityTypeEnum>  
[System.ObsoleteAttribute("Schema filters have been depreciated. Use the entity filters instead")]
public __gc __sealed class SchemaFilter : public xSQL.Schema.Core.IKeyedEntity<SqlEntityTypeEnum>  
Remarks
A schema filter provides for including or excluding database objects based on some predefined conditions. You can choose, for example, to exclude tables the name of which start with "tmp", include views that belong to the schema HumanResource and more.

A schema filter is a collection of filter conditions. Each conditions is evaluated against the name of an object and the result is combined with AND or OR depending on the schema filter operator.

Schema filters are not supported for the following database objects:

Example
The following example shows how to create and apply a schema filter. The filter in the code excludes from the comparison all tables the name of which starts with "tmp"
using xSQL.Schema.Core;
using xSQL.Schema.SqlServer;
using xSQL.SchemaCompare.SqlServer;
            
namespace xSQL.Sdk.SchemaCompare.Samples
{
    class Test
    {
        public static void CompareWithSchemaFilters()
        {
            SqlServer server;
            SqlDatabase xDatabase, yDatabase;
            SqlSchemaCompare comparer;
            ScriptManager sqlScript;
            SchemaFilter filter;
            
            try
            {
            
                // create the SQL Server object
                server = new SqlServer(@"(local)");
                
                // create the left database
                xDatabase = server.GetDatabase("Source");
                
                // create the right database
                yDatabase = server.GetDatabase("Target");
                
                // create the schema comparer
                comparer = new SqlSchemaCompare(xDatabase, yDatabase);
                
                // create a filter that excludes all tables starting with "tmp"
                filter = new SchemaFilter(SqlEntityTypeEnum.Table);
                filter.SchemaFilterCriteria.Add(new SchemaFilterCriteria("tmp", SchemaFilterTypeEnum.StartingWith, false));
                // add the filter to the comparer filter collection
                comparer.SchemaFilters.Add(filter);
                
                // attach event handlers to these events in order to get some progress information during the schema read and compare
                comparer.LeftDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
                comparer.RightDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
                comparer.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
                
                // step 1: read the schema 
                comparer.ReadSchema();
                
                // step 2: pair the database objects
                comparer.PairObjects();
                
                // step 3: compare the schema
                comparer.Compare();
                
                // check for errors that could have occurred during the schema compare. 
                // some errors are handled quietly and do not stop the process, those that are critical throw exceptions
                // quiet errors are collected and stored into the ErrorRepository object
                if (ErrorRepository.Instance.HasErrors())
                {
                    Console.WriteLine("Some errors occurred during the database compare");
                    Console.Write(ErrorRepository.Instance.GetErrors());
                }
                
                // check the database status; exit if no schema differences are found.
                if (comparer.SqlDatabasePair.ComparisonStatus == ComparisonStatusEnum.Equal)
                    return;
                    
                // step 4: get the T-SQL script intended for the right database; that is the script that should be executed 
                // on Target database in order to make it the same as the Source database
                sqlScript = comparer.GetRightDatabaseScript();
                
                if (!sqlScript.IsEmpty())
                {
                    //--use the event to get some progress during the script execution
                    sqlScript.SchemaScriptExecuting += new EventHandler<SchemaScriptEventArgs>(sqlScript_SchemaScriptExecuting);
                    
                    //--execute the synchronization script 
                    sqlScript.Execute();
                    
                    //--check for silent errors
                    if (ErrorRepository.Instance.HasErrors())
                    {
                        Console.WriteLine("Some errors occurred during the script execution.");
                        Console.Write(ErrorRepository.Instance.GetErrors());
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
        }
        
        private static void database_SchemaOperation(object sender, SchemaOperationEventArgs e)
        { 
            //--exclude verbose messages
            if (e.Message.MessageType != OperationMessageTypeEnum.Verbose)
                Console.WriteLine(e.Message.Text);
        }
            
        private static void sqlScript_SchemaScriptExecuting(object sender, SchemaScriptEventArgs e)
        {
            Console.WriteLine("{0} {1}", DateTime.Now.ToString("HH:mm:ss"), e.Script);
        }
        
        private static void comparer_SqlTablePairingFinished(object sender, SqlTablePairEventArgs e)
        {
            if (e.Pair.ContainsMember("employees", SqlEntityTypeEnum.Table))
                e.Pair.Included = false;
        }
    }
}
Inheritance Hierarchy

System.Object
   xSQL.SchemaCompare.SqlServer.SchemaFilter

Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

See Also

Reference

SchemaFilter Members
xSQL.SchemaCompare.SqlServer Namespace

 

 


©Copyright 2022 xSQL Software. All Rights Reserved.

Send Feedback