Thursday, February 6, 2014

How to script a SQL Server database object using xSQL Schema SDK

Using the xSQL Schema Compare SDK it is very easy to generate the CREATE, DROP and ALTER (when alter is supported) scripts for any database object. The following example shows how to generate the CREATE script for the table Employees from the AdventureWorks database:

using xSQL.Schema.Core;
using xSQL.Schema.SqlServer;
using xSQL.SchemaCompare.SqlServer;

namespace xSQL.Sdk.SchemaCompare.Examples
{
    class Scripting
    {
        /// <summary>

        /// This method reads the schema of the database AdventureWorks and scripts the table Employee.
        /// </summary>
        public static void Script()
        {
            SqlServer server;
            SqlDatabase database;
            SqlTable table;
            ScriptingOptions options;
            try
            {
                //--create the SQL Server object

                server = new SqlServer(@"(local)");

                //--create the database object

                database = server.GetDatabase("AdventureWorks");

                //--attach an event handler to database.SchemaOperation event in order to get progress information during the schema read

                database.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);

                //--read the database schema

                database.ReadSchema();

                //--create scripting options;

                options = new ScriptingOptions();
                options.CreateScript = true;
                options.DropScript = false;
                options.AlterScript = false;

                //--locate and script the Employee table

                table = database.SqlTables["HumanResources", "Employee"];
                if (table != null)
                    Console.Write(table.GetScript(options));
            }
            catch (Exception ex)
            {
                Console.WriteLine(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);
        }
    }
}

Tuesday, February 4, 2014

Step by step - compare and sync two sql database schemas in your .NET app using xSQL SDK

Step 1: reference the three namespaces required by xSQL Schema Compare SDK
      using xSQL.Schema.Core;
      using xSQL.Schema.SqlServer;
      using xSQL.SchemaCompare.SqlServer;


Step 2: Create the SqlServer objects for the left and right SQL Server
      SqlServer xServer, yServer;
      // create the left SQL Server object - let's assume you are using Windows authentication

      xServer = new SqlServer(@"(local)\LeftServer");
      // create the right SQL Server - let's assume you are using SQL Server authentication

      yServer = new SqlServer(@"(local)\RightServer", "<user>", "<password>");

Step 3: Create database objects for the two databases being compared
      SqlDatabase xDatabase, yDatabase;
      // create the left database

      xDatabase = xServer.GetDatabase("Source");
      // create the right database

      yDatabase = yServer.GetDatabase("Target");

Step 4: Create the schema comparer object
      SqlSchemaCompare comparer;
      comparer = new SqlSchemaCompare(xDatabase, yDatabase);


Step 5: (optional) Change the comparison options if you need to
      comparer.Options.CompareUsers = false;
      comparer.Options.CompareSchemas = false;
      comparer.Options.CompareDatabaseRoles = false;
      comparer.Options.CompareApplicationRoles = false;


Step 6: (optional/recommended) it is a good practice to attach event handlers to some of the schema events in order to get progress information during the comparison.
      comparer.LeftDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
      comparer.RightDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
      comparer.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);


      private static void database_SchemaOperation(object sender, SchemaOperationEventArgs e)
      {
            //--exclude verbose messages             if (e.Message.MessageType != OperationMessageTypeEnum.Verbose)
                  Console.WriteLine(e.Message.Text);
      }


Step 7: Compare the schemas of the two databases
      // 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 8: Generate the synchronization script for the target database and execute it (let's assume here that the right database is the target - you can substitute left for right if the left database is your target)
      // 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

            status = sqlScript.Execute();
            // check the execution and print any errors

            if (status == ScriptExecutionStatusEnum.Succeeded)
            {
                  Console.WriteLine("Database synchronization finished successfully");
            }
            else if (status == ScriptExecutionStatusEnum.Canceled)
            {
                  Console.WriteLine("Database synchronization was canceled");
            }
            else
            {
                  // check for errors

                  if (ErrorRepository.Instance.HasErrors())
                  {
                        Console.WriteLine("Some errors occurred during the script execution");
                        Console.Write(ErrorRepository.Instance.GetErrors());
                  }
            }
      }

      private static void sqlScript_SchemaScriptExecuting(object sender, SchemaScriptEventArgs e)
      {
            Console.WriteLine("{0} {1}", DateTime.Now.ToString("HH:mm:ss"), e.Script);
      }


Step 9: Catching exception. All exceptions are listed here for clarity, but you can reduce this section by catching just the top-level Exception
      catch (ConnectionException ex)
      {
            // a connection exception

            Console.Write(ex.ToString());
      }
      catch (SchemaException ex)
      {
            // a schema-read exception

            Console.Write(ex.ToString());
      }
      catch (SchemaCompareException ex)
      {
            // a schema compare exception

            Console.Write(ex.ToString());
      }
      catch (ScriptExecutionException ex)
      {
            // a script execution exception

            Console.Write(ex.ToString());
            Console.WriteLine("Script fragments that failed:");
            foreach (string err in ex.Errors)
                  Console.WriteLine(err);
      }
      catch (Exception ex)
      {
            // a generic exception

            Console.WriteLine("An unexpected error occurred.");
            Console.Write(ex.Message);
      }


You are done - your app can now compare and synchronize the schemas of two databases and it took you 5 minutes! And here it is, all of it in one place to make it easier for you to copy and paste:

using xSQL.Schema.Core;
using xSQL.Schema.SqlServer;
using xSQL.SchemaCompare.SqlServer;

namespace xSQL.Sdk.SchemaCompare.Examples
{
    class Examples
    {

        /// <summary>

        /// This example demonstrates a typical database comparison scenario.
        /// </summary>
        public static void SimpleCompare()
        {
            SqlServer xServer, yServer;
            SqlDatabase xDatabase, yDatabase;
            SqlSchemaCompare comparer;
            ScriptManager sqlScript;
            ScriptExecutionStatusEnum status;
            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);

                // 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

                    status = sqlScript.Execute();

                    // check the execution and print any errors

                    if (status == ScriptExecutionStatusEnum.Succeeded)
                    {
                        Console.WriteLine("Database synchronization finished successfully");
                    }
                    else if (status == ScriptExecutionStatusEnum.Canceled)
                    {
                        Console.WriteLine("Database synchronization was canceled");
                    }
                    else
                    {
                        // check for quiet 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)
            {
                // a schema compare exception

                Console.Write(ex.ToString());
            }
            catch (ScriptExecutionException ex)
            {
                // a script execution exception

                Console.Write(ex.ToString());
                Console.WriteLine("Script fragments that failed:");
                foreach (string err in ex.Errors)
                    Console.WriteLine(err);
            }
            catch (Exception ex)
            {
                // a generic 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);
        }
    }
}

//


You can download xSQL Schema Compare SDK from: http://www.xsql.com/download/sdk/sql_server_schema_compare/