SQL Scripts from dotnet with transactions

Question

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.

Answer 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?

Answer 2

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