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

0 comments:

Post a Comment