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);
        }
    }
}

Read More

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/

Read More