Single Database

rsavage

How can I get the RedGate.SQLDataCompare.Engine to to generate the raw SQL commands for a single database?

I am basically trying to re-create the functionality like included in the SQLPackager utility where it can generate the schema and default data for a single database.

I was able to acomplish this goal for the database schema using the RedGate.SQLCompare.Engine via the 'ScriptObject' class and then enumerating thoguh each of the TABLES, STORED PROCS, VIEWS, etc of a single database connection.

My goal is to create a custom application that can generate the default database structure and default data scripts, then also have the ability to create the difference scripts as future database updates are released for my product. I was looking to use the RedGate libraries in my database tool, but not distribute them. I only want to distribute the raw database SQL scripts.

Thank You,
Robert Savage
AMX Corporation
Applications Engineer

Brian Donahue

Hello Robert,

What you want to do sounds an awful lot like the way that Packager works. It does use the Data Compare and SQL Compare engines to compare your schema and data against those of a blank database. That is how you can generate a whole new database script because all objects and data need to be created rather than altered or updated in order to get an empty database to look like your populated one. Here is the sample code:

using System;
using RedGate.SQL.Shared;
using RedGate.SQLPackager.Engine;
using System.IO;

namespace ConsoleApplication1
{
	class Class1
	{
		static void Main()
		{
			string serverName=".";
			string databaseName="WidgetDev";

			//get the execution block representing the schema
			RedGate.SQLCompare.Engine.Database database=new RedGate.SQLCompare.Engine.Database();
			database.Register(new RedGate.SQLCompare.Engine.ConnectionProperties(serverName, databaseName), RedGate.SQLCompare.Engine.Options.Default);
			
			//compare it with a null database to get a creation script
			RedGate.SQLCompare.Engine.Differences schema=database.CompareWith(null, RedGate.SQLCompare.Engine.Options.Default);

			//select all the differences

			foreach (RedGate.SQLCompare.Engine.Difference difference in schema)
			{
				difference.Selected=true;
			}

			RedGate.SQLCompare.Engine.Work work=new RedGate.SQLCompare.Engine.Work();
			
			//get the script
			work.BuildFromDifferences(schema, RedGate.SQLCompare.Engine.Options.Default, true);
			ExecutionBlock schemaBlock=work.ExecutionBlock;

			//now get the data script

			RedGate.SQLDataCompare.Engine.SqlProvider provider=new RedGate.SQLDataCompare.Engine.SqlProvider();
			RedGate.SQLDataCompare.Engine.Database database2=provider.GetDatabase(new RedGate.SQLDataCompare.Engine.SqlConnectionProperties(serverName, databaseName));
			RedGate.SQLDataCompare.Engine.ComparisonSession session=new RedGate.SQLDataCompare.Engine.ComparisonSession();

			RedGate.SQLDataCompare.Engine.TableComparisonSettings tablesToCompare=new RedGate.SQLDataCompare.Engine.TableComparisonSettings();

			foreach (RedGate.SQLDataCompare.Engine.Table table in database2.Tables)
			{
				//since we are always inserting data there is no need to define a primary key
				tablesToCompare.Add(new RedGate.SQLDataCompare.Engine.TableComparisonSetting(table.FullyQualifiedName, table.Fields, null));
			}

			session.CompareDatabases(database2, null, tablesToCompare);
			ExecutionBlock dataBlock=provider.GetMigrationSQL(session, true);
		}
	}
}

rsavage

Thanks Brian, that was exactly what I was looking for.

Now, 1 additional question ......

Do the RedGate components provide a method to execute this generated SQL script against a connected database? The script format looks like MS SQL Query Analyzer compatible format. If the RedGate components do not provide this type of method, do you have an suggections on how to programatically automate the execution of this script. The standard .NET data command object (SqlCommand::ExecuteNonQuery()) does not accept this script directly.

Deploying simple text based database script files (that my software can automatically detect and install) and leveraging RedGate in my custom developer tool to create these scripts is my ultimate goal.

Thank you for you help,
Robert

Brian Donahue

Hello Robert,

Yes -- once you get a populated ExecutionBlock object, you can use the RedGate.SQL.Shared.ExecuteBlock() method to execute the SQL. Please heve a look at the Toolkit help for more information about the type and number of arguments that this will accept.

The reason that you can't run the script with a SqlCommand is because of the batch separators (GO) that are inserted between every block of SQL. The scripts that are produced by SQL Toolkit are meant to be run in Query Analyzer and the batch separator is specific to that application.