T-SQL and AddBatch

chuckice

I forgot to login for that previous post...wanted to inform that I am an existing customer. Thank you.

Brian Donahue

Hi Chuck,

AddBatch isn't meant to be used to construct or add SQL to an ExecutionBlock, so I'd recommend using ADO to run your own SQL commands before or after running the ExecutionBlock produced by SQL Compare Engine.

Here is some example code from Microsoft that shows how to run SQL transactions in .net, get the result of the queries, and roll the transaction back if necessary.

public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open();

SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;

// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback("SampleTransaction");
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}

chuckice

Brian Donahue wrote:

Hi Chuck,

AddBatch isn't meant to be used to construct or add SQL to an ExecutionBlock, so I'd recommend using ADO to run your own SQL commands before or after running the ExecutionBlock produced by SQL Compare Engine.

Here is some example code from Microsoft that shows how to run SQL transactions in .net, get the result of the queries, and roll the transaction back if necessary.

public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open();

SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;

// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback("SampleTransaction");
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}

Unfortunately I know how to do this thru ADO the issue is we have massive SQL merge scripts and it's not feasible via ADO. We purchased the toolkit with the assumption that it specifically handled this case. Is there no way to merge a SQL script into an existing DB using the toolkit? If not do you have any other recommendations using the toolkit other than ADO? It's really not feasible to take 100's of lines of SQL and convert them to ADO.NET. Thank you.

Brian Donahue

Hi Chuck,

It's not a problem at all to run a SQL script that SQL Compare produces, it's just a matter of giving the executionblock to the ExecuteBlock method in RedGate.SQL.Shared. It's when you want to add your very own custom scripts that you need to run them some other way. ExecuteBlock is only meant for RedGate ExecutionBlock objects.

chuckice

And are you saying that I have to have an existing schema in order to get to an execution block?

Brian Donahue

Hi Chuck,

You need to run a comparison between two database objects to get an ExecutionBlock. I believe if you set the second database object to null, you'd get a script that would allow you to re-create an entire database rather than merging changes to an existing database.