2

I have been trying to execute sql scripts from dotnet (C#) but the sql scripts could contain GO statements and I would like to be able to wrap the collection of scripts in a transaction.

I found this question and the accepted answer got me going for handling the GO statements, but if I use a BeginTransaction it throws a InvalidOperationException at the "new ServerConnection" line.

SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(transactionName);
ServerConnection serverConnection = new ServerConnection(connection);

I am running this against a SQL 2005 server.

Community
  • 1
  • 1
benPearce
  • 37,735
  • 14
  • 62
  • 96

3 Answers3

4

I found a way, after trying several combinations, the simplest one worked...

Well this is assuming you are using a TransactionScope object

using (
    var transactionScope = new TransactionScope(TransactionScopeOption.Required,
                                                new TransactionOptions
                                                    {
                                                        IsolationLevel =
                                                            IsolationLevel.ReadCommitted,
                                                        Timeout = TimeSpan.FromMinutes(300)
                                                    }))
{
    sqlServerInstance.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
    sqlServerInstance.ConnectionContext.StatementTimeout = int.MaxValue;

    //This line makes the MAGIC happen =)
    sqlServerInstance.ConnectionContext.SqlConnectionObject.EnlistTransaction(Transaction.Current);
    sqlServerInstance.ConnectionContext.ExecuteNonQuery(scriptContent);
}
Jupaol
  • 21,107
  • 8
  • 68
  • 100
3

Try using the BeginTransaction and CommitTransaction methods on the ServerConnection instance instead. I use this without any problems.

Aaronaught
  • 120,909
  • 25
  • 266
  • 342
1

Try starting the transaction after you've attached the ServerConnection to the SqlConnection's settings.

Also, what are you using ServerConnection for that you can't do from SqlConnection?

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • GO commands in the SQL scripts are not supported from SqlConnection – benPearce Jun 24 '09 at 23:10
  • 2
    Use Regex.Split to split the script on the GO commands. Execute the batches one at a time on the same connection, with the same transaction. – John Saunders Jun 24 '09 at 23:56
  • OK. I think you can make a ServerConnection without a SqlConnection, so I'm not sure you need the SqlConnection, nor do you need to start a transaction on it, since you can start a transaction on the ServerConnection after it is made. – Cade Roux Jun 25 '09 at 03:06