NAnt task with Red-Gate SQL Compare

• Posted: November 11, 2008
• |
• Author: Vorleak Chy
• |
• Filed under: Tools
• |
• Tags: .Net, C#, CI, NAnt, Tools

It can be an easy way to do if you have a database with schema changes. You have to convert a database to scripts so that you can commit to source control by comparing script files. Or you get update from source control as scripts and synchronize to your local database.

Most code development these days involves using a “local development” model. In other words, the code is created on the developers’ local machines, and the shared source control database is the integration point and provides the mechanism for sharing new code. The automated delivery processes then provide the isolated continuous integration process as already specified. Database development is not necessarily handled in the same way, but it can be. The actual model used affects the requirements for handling database integration.

Red-Gate have a very good product called SQL Compare which can compare two database versions and produce a script to convert one version to the other and a SQL Toolkit that allows you to automate the process.

NAnt task help you a lot of saving time instead of doing manual work everyday by creating custom task for your own which I include in here.

Here is an example of the configuration:

This is a build file which will load SyncNAntTask.dll as a custom task and have two databases need to synchronize with script files.

<?xml version="1.0" ?>
<project name="SyncDatabase">
  <property name="schemaRoot.dir" value="..\database\schema\" />
  <property name="dbServer" value="localhost" />
  <property name="dbUserId" value="sa" />
  <property name="dbPassword" value="123" />
  <loadtasks assembly="..\src\SyncNAntTask\bin\Debug\SyncNAntTask.dll"/>
  <syncDatabase>
    <databases>
      <database folder="${schemaRoot.dir}db1" server="${dbServer}" name="db1" userId="${dbUserId}" password="${dbPassword}" />
      <database folder="${schemaRoot.dir}db2" server="${dbServer}" name="db2" userId="${dbUserId}" password="${dbPassword}" />
     </databases>
   </syncDatabase>
</project>

There are three .cs files for the SyncNAntTask.dll which I have to separate.

It’s quite simple for database element with five attributes.

using NAnt.Core;
using NAnt.Core.Attributes;

namespace SyncNAntTask
{
    [ElementName("database")]
    public class DatabaseInfo : Element
    {
        [TaskAttribute("folder", Required = true)]
        public string Folder { get; set; }

        [TaskAttribute("server", Required = true)]
        public string Server { get; set; }

        [TaskAttribute("name", Required = true)]
        public new string Name { get; set; }

        [TaskAttribute("userId", Required = true)]
        public string UserId { get; set; }

        [TaskAttribute("password", Required = true)]
        public string Password { get; set; }
    }
}

It’s for syncDatabase task which contains collection of database elements.

using NAnt.Core;
using NAnt.Core.Attributes;
using RedGate.SQLCompare.Engine;
using SyncNAntTask;

namespace SyncNAntTask
{
    [TaskName("syncDatabase")]
    public class SynchronizeDatabaseTask : Task
    {
        private DatabaseInfo[] databaseInfos;

        [BuildElementCollection("databases", "database")]
        public DatabaseInfo[] DbInfos
        {
            get { return databaseInfos; }
            set { databaseInfos = value; }
        }

        protected override void ExecuteTask()
        {
            foreach (DatabaseInfo dbInfo in databaseInfos)
            {
                SynchronizeScripts.Synchronize(dbInfo.Folder,
                                               new ConnectionProperties(dbInfo.Server, dbInfo.Name, dbInfo.UserId,
                                                                        dbInfo.Password));
            }
        }
    }
}

It is static class to run a task to do synchronizing between script files and database.

using System;
using RedGate.SQL.Shared;
using RedGate.SQLCompare.Engine;

namespace SyncNAntTask
{
    public static class SynchronizeScripts
    {
        public static void Synchronize(string scriptFolder, ConnectionProperties targetConnectionProperties)
        {
            using (Database sourceDatabaseScripts = new Database(), targetDatabase = new Database())
            {
                // Establish the schema from the scripts stored in the sourceDatabaseScripts scripts folder
                // Passing in null for the database information parameter causes SQL Compare to read the
                // XML file supplied in the folder.
                sourceDatabaseScripts.Register(scriptFolder, null, Options.Default);

                // Read the schema for the targetDatabase database
                targetDatabase.Register(targetConnectionProperties, Options.Default);

                // Compare the database against the scripts.
                // Comparing in this order makes the targetDatabase the second database
                Differences sourceDatabaseScriptsVStargetDatabase =
                    sourceDatabaseScripts.CompareWith(targetDatabase, Options.Default);

                // Select all of the differences for synchronization
                foreach (Difference difference in sourceDatabaseScriptsVStargetDatabase)
                {
                    difference.Selected = IsIncludeObject(difference);
                }

                // Calculate the work to do using sensible default options
                // The targetDatabase is to be updated, so the runOnTwo parameter is true
                var work = new Work();
                work.BuildFromDifferences(sourceDatabaseScriptsVStargetDatabase, Options.Default, true);

                // We can now access the messages and warnings
                Console.WriteLine("Messages:");

                foreach (Message message in work.Messages)
                {
                    Console.WriteLine(message.Text);
                }

                Console.WriteLine("Warnings:");

                foreach (Message message in work.Warnings)
                {
                    Console.WriteLine(message.Text);
                }

                // Disposing the execution block when it's not needed any more is important to ensure
                // that all the temporary files are cleaned up
                using (ExecutionBlock block = work.ExecutionBlock)
                {
                    // Display the SQL used to synchronize
                    Console.WriteLine("SQL to synchronize:");
                    Console.WriteLine(block.GetString());

                    // Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
                    var executor = new BlockExecutor();
                    executor.ExecuteBlock(block, targetConnectionProperties.ServerName,
                                          targetConnectionProperties.DatabaseName, false,
                                          targetConnectionProperties.UserName, targetConnectionProperties.Password);
                }
            }
        }

        private static bool IsIncludeObject(Difference difference)
        {
            if (difference.DatabaseObjectType != ObjectType.User &&
                difference.DatabaseObjectType != ObjectType.Role &&
                difference.DatabaseObjectType != ObjectType.Queue &&
                difference.DatabaseObjectType != ObjectType.Service &&
                MeetExcludeSpecialCase(difference) == false)
            {
                return true;
            }

            return false;
        }

        private static bool MeetExcludeSpecialCase(Difference difference)
        {
            if ((difference.DatabaseObjectType == ObjectType.Table &&
                 difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql")) ||
                (difference.DatabaseObjectType == ObjectType.StoredProcedure &&
                 difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql")) ||
                (difference.DatabaseObjectType == ObjectType.StoredProcedure &&
                 difference.Name.ToLower().StartsWith("[dbo].[sqlquery")))
            {
                return true;
            }

            return false;
        }
    }
}

That’s all for this, do you have another way? please let me know!

• (0) Comments
• Permalink
• RSS 2.0