从DOTNET与事务SQL脚本 [英] SQL Scripts from dotnet with transactions
问题描述
我一直在试图执行SQL脚本从DOTNET(C#),但SQL脚本可以包含GO语句,我想能够包装在一个交易脚本的集合。
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.
我发现<一href="http://stackoverflow.com/questions/40814/how-do-i-execute-a-large-sql-script-with-go-commands-from-c">this问题和接受的答案让我去办理了GO语句,但是如果我使用的BeginTransaction它抛出一个 InvalidOperationException异常在新ServerConnection行。
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);
我正在这对SQL 2005服务器。
I am running this against a SQL 2005 server.
推荐答案
我找到了一种方法,尝试几种组合后,最简单的工作......
I found a way, after trying several combinations, the simplest one worked...
嗯,这是你用的是的TransactionScope
对象假设
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);
}
这篇关于从DOTNET与事务SQL脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!