Create database or Simple Sql execution Example please

pspeltz

Hi. I am new to this toolkit and have been searching a couple hours now through the API docs and examples for how to *Create* a database from a snapshot using the toolkit.

Our use case is this:
If the DB exists,
synch it with the snapshot
else
create it with the snapshot
end if

The synch part is well demonstated in the examples. However, i am either missing it or there is no example of creating a database using the toolkit.
For that matter, I cant find an example to execute simple sql statements.
Can anyone point me in the right direction or give me an example of how to do this?

Thanks.

JonathanWatts

Hi there,

Sorry but at the current time there is no method to create a database within the SQL Compare API or the UI. If you wish to create a database you will have to do it within your own code, then call the SQL Compare API to synchronise the source snapshot with the newly created empty target database.

> "For that matter, I cant find an example to execute simple sql statements."

Sorry, but I am not quite with you here, can you expand on this? Are you attempting to execute your own custom SQL via the API or trying to execute the SQL Compare synchronisation script?

Regards,

Jonathan

pspeltz

Hi. Thank you for the help, Jonathan.

Jonathan Watts wrote:

Hi there,

> "For that matter, I cant find an example to execute simple sql statements."

Sorry, but I am not quite with you here, can you expand on this? Are you attempting to execute your own custom SQL via the API or trying to execute the SQL Compare synchronisation script?

Regards,

Jonathan

Yes. I would like to execute arbitrary sql scripts, statements through the API. We use SQLDmo now. I was hoping not to have to use two different APIs. Our 2003 code converted to 2005 has name clashes with RedGgate SqlServer and SqlDMO.SqlServer names.

From reading the docs on Batch which say a batch is just a set of sql scripts and BatchExecutor which executes scripts in batches, I was hoping there would be an api to build a batch of sql scripts, say for example a simple script-- "Create Database NewDB", and then execute that batch. Does that make sense? Could i put that in a text file and execute it through the API?

I could be misunderstanding but it seems like most of the API is already there except the content of the SQL scripts executed are limited to database compare and synchronization. A Work obj has a BuildFromDifferences to get the sql you need to execute for synching. it would be nice to have a BuildFromDatabase or something to get sql you need in case the database you are trying to synchronize does not exist. The sql would create the enitre db just like the snapshot you were trying to synch with.

thanks for help.

Peter

JonathanWatts

Peter,

Sorry about the delay in replying, I have been on holiday.

At the moment it is not possible to insert custom scripts into SQL Compare batches. It is something we may think about doing in future, but only at the begin / end of a SQL Compare script.

Nor is there a "Create Database NewDB" command that can be added to a batch. This is something we would encourage toolkit users to do themselves with DMO.

Regards,

Jonathan

aperregatturv

I was able to execute SQL Script and a snapshot to create a new database.

if (File.Exists(ScriptFile))
{
RedGate.SQLCompare.Engine.Database dbExecute = new RedGate.SQLCompare.Engine.Database();
dbExecute.Status += new StatusEventHandler(StatusCallBack);
if (NTAUTHENTICATE)
{
dbExecute.Register(new ConnectionProperties(DBSERVER, DBName), Options.Default);
}
else
{
dbExecute.Register(new ConnectionProperties(DBSERVER, DBName, USERID, PASSWORD), Options.Default);
}
RedGate.SQL.Shared.BlockExecutor b = new BlockExecutor();
ExecutionBlock blk = new ExecutionBlock();
StreamReader sr = new StreamReader(ScriptFile);
string data = sr.ReadToEnd();
if (Script == WinApp.Database.Resource.CREATEDB)
{
string s = data.Replace("<#DBNAME>", CREATEDBNAME);
string d = s.Replace("<#DBFILEPATH>", DBPATH);
data = d;
}
string newdata = data.Replace("GO", " ");
blk.AddBatch(newdata);
if (NTAUTHENTICATE)
{
b.ExecuteBlock(blk, DBSERVER, DBName, true, "", "");
}
else
{
b.ExecuteBlock(blk, DBSERVER, DBName, false, USERID, PASSWORD);
}
blk.Dispose();
dbExecute.Dispose();
sr.Dispose();

The above code will create a new database first. Its a SQL Script file that has a create database command with addition option.

the following code uses snapshot file to sync the newly empty created database with all the tables/views/stored procedures....

if (File.Exists(ScriptFile))
{
try
{
if (breturnvalue)
{
RedGate.SQLCompare.Engine.Database db1 = new RedGate.SQLCompare.Engine.Database();
RedGate.SQLCompare.Engine.Database db2 = new RedGate.SQLCompare.Engine.Database();
db1.LoadFromDisk(ScriptFile);
if (NTAUTHENTICATE)
{ db2.Register(new ConnectionProperties(DBSERVER, DatabaseName), Options.Default); }
else
{ db2.Register(new ConnectionProperties(DBSERVER, DatabaseName, USERID, PASSWORD), Options.Default); }

Differences diffs = db1.CompareWith(db2, Options.Default);
foreach (Difference diff in diffs)
{ diff.Selected = true; }

Work wrk = new Work();
wrk.BuildFromDifferences(diffs, Options.Default, true);

ExecutionBlock block = wrk.ExecutionBlock;
try
{
RedGate.SQL.Shared.BlockExecutor b = new BlockExecutor();
if (NTAUTHENTICATE)
{ b.ExecuteBlock(block, DBSERVER, DatabaseName, true, "", ""); }
else
{ b.ExecuteBlock(block, DBSERVER, DatabaseName, false, USERID, PASSWORD); }
}
finally
{
block.Dispose();
}
db1.Dispose();
db2.Dispose();
}
}
catch
{
}

Hope this helps.

Arun