xSQL Schema Compare SDK version 9.5 for SQL Server
SqlSchemaCompare Class
Members  Example 


Represents the main class for comparing and synchronizing two SQL Server databases.
Object Model
SqlSchemaCompare Class
Syntax
'Declaration
 
Public NotInheritable Class SqlSchemaCompare 
public sealed class SqlSchemaCompare 
public __gc __sealed class SqlSchemaCompare 
Example
The following example demonstrates a typical database comparison scenario. It performs the followings:
using xSQL.Schema.Core;
using xSQL.Schema.SqlServer;
using xSQL.SchemaCompare.SqlServer;
            
namespace xSQL.Sdk.SchemaCompare.Samples
{
    class Test
    {
        public static void CompareDatabases()
        {
            SqlServer xServer, yServer;
            SqlDatabase xDatabase, yDatabase;
            SqlSchemaCompare comparer;
            ScriptManager sqlScript;
            SqlTablePair pair;
            
            try
            {
                // create the left SQL Server object using Windows authentication
                xServer = new SqlServer(@"(local)");
                
                // create the right SQL Server using SQL Server authentication
                yServer = new SqlServer(@"(local)", "<user>", "<password>");
                
                // create the left database
                xDatabase = xServer.GetDatabase("Source");
                
                // create the right database
                yDatabase = yServer.GetDatabase("Target");
                
                // create the schema comparer
                comparer = new SqlSchemaCompare(xDatabase, yDatabase);
                
                // exclude some database objects
                comparer.Options.CompareUsers = false;
                comparer.Options.CompareSchemas = false;
                comparer.Options.CompareDatabaseRoles = false;
                comparer.Options.CompareApplicationRoles = false;
                
                // 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);
                
                // we use the generic event EntityPairingFinished to exclude an object from the comparison or
                // the SqlTablePairingFinished event which is raised specifically for database tables.
                // the table-specific event provides slightly better performance since we have to check only tables and not every object in the database
                // objects are usually excluded after the pairing is finished. 
                comparer.SqlTablePairingFinished += new EventHandler<SqlTablePairEventArgs>(comparer_SqlTablePairingFinished);
                //comparer.EntityPairingFinished += new EventHandler<PairBaseEventArgs>(comparer_EntityPairingFinished);
                
                // step 1: read the schema 
                comparer.ReadSchema();
                
                // step 2: pair the database objects
                comparer.PairObjects();
                
                // step 3: compare the database 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 to make it the same as the Source database
                sqlScript = comparer.GetRightDatabaseScript();
                
                if (!sqlScript.IsEmpty())
                {
                    // print the synchronization log
                    Console.Write(sqlScript.GetLog());
                    
                    // print the synchronization script
                    Console.Write(sqlScript.GetScript());
                    
                    // attach event handlers to ScriptManager object to get some progress info during the script execution
                    sqlScript.SchemaScriptExecuting += new EventHandler<SchemaScriptEventArgs>(sqlScript_SchemaScriptExecuting);
                    
                    // execute the sync script
                    sqlScript.Execute();
                    
                    // check for quite errors
                    
                    if (ErrorRepository.Instance.HasErrors())
                    {
                        Console.WriteLine("Some errors occurred during the script execution");
                        Console.Write(ErrorRepository.Instance.GetErrors());
                    }
                }
            }
            
            catch (ConnectionException ex)
            {
                // a connection exception
                Console.Write(ex.ToString());                
            }                
            catch (SchemaException ex)
            {
                // a schema-read exception
                Console.Write(ex.ToString());
            }
            catch (SchemaCompareException ex)
            {
                // an exception that is thrown during the schema compare
                Console.Write(ex.ToString());
            }
            catch (ScriptExecutionException ex)
            {            
                // an exception that is thrown during the script execution
                Console.Write(ex.ToString());
            }
            catch (Exception ex)
            {
                // the fallback exception
                Console.WriteLine("An unexpected error occurred.");
                Console.Write(ex.Message);
            }
        }
        
        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_EntityPairingFinished(object sender, PairBaseEventArgs e)
        {
            if (e.Pair.ContainsMember("employees", SqlEntityTypeEnum.Table))
                e.Pair.Included = false;
        }
        
        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.SqlSchemaCompare

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

SqlSchemaCompare Members
xSQL.SchemaCompare.SqlServer Namespace

 

 


┬ęCopyright 2018 xSQL Software. All Rights Reserved.

Send Feedback