C# 如果 MSDTC 被禁用，您如何绕过 TransactionScope 内的多个数据库连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/761031/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you get around multiple database connections inside a TransactionScope if MSDTC is disabled?
提问by Joseph
I have a web application that issues requests to 3 databases in the DAL. I'm writing some integration tests to make sure that the overall functionality round trip actually does what i expect it to do. This is completely separate from my unit tests, just fyi.
我有一个向 DAL 中的 3 个数据库发出请求的 Web 应用程序。我正在编写一些集成测试，以确保整体功能往返实际上符合我的预期。这与我的单元测试完全分开，仅供参考。
The way I was intending to write these tests were something to the effect of this
我打算编写这些测试的方式是这样的
[Test]
public void WorkflowExampleTest()
{
(using var transaction = new TransactionScope())
{
Presenter.ProcessWorkflow();
}
}
The Presenter in this case has already been set up. The problem comes into play inside the ProcessWorkflow method because it calls various Repositories which in turn access different databases, and my sql server box does not have MSDTC enabled, so I get an error whenever I try to either create a new sql connection, or try to change a cached connection's database to target a different one.
在这种情况下，Presenter 已经设置好了。该问题在 ProcessWorkflow 方法中发挥作用，因为它调用各种存储库，这些存储库依次访问不同的数据库，而我的 sql server 盒没有启用 MSDTC，因此每当我尝试创建新的 sql 连接或尝试时，我都会收到错误消息更改缓存连接的数据库以定位不同的数据库。
For brevity the Presenter resembles something like:
为简洁起见，Presenter 类似于：
public void ProcessWorkflow()
{
LogRepository.LogSomethingInLogDatabase();
var l_results = ProcessRepository.DoSomeWorkOnProcessDatabase();
ResultsRepository.IssueResultstoResultsDatabase(l_results);
}
I've attempted numerous things to solve this problem.
我已经尝试了很多方法来解决这个问题。
- Caching one active connection at all times and changing the target database
- Caching one active connection for each target database (this was kind of useless because pooling should do this for me, but I wanted to see if I got different results)
- Adding additional TransactionScopes inside each repository so that they have their own transactions using the TransactionScopeOption "RequiresNew"
- 始终缓存一个活动连接并更改目标数据库
- 为每个目标数据库缓存一个活动连接（这有点没用，因为池应该为我做这件事，但我想看看我是否得到了不同的结果）
- 在每个存储库中添加额外的 TransactionScope，以便它们使用 TransactionScopeOption "RequiresNew" 拥有自己的事务
My 3rd attempt on the list looks something like this:
我对列表的第三次尝试看起来像这样：
public void LogSomethingInLogDatabase()
{
using (var transaction =
new TransactionScope(TransactionScopeOption.RequiresNew))
{
//do some database work
transaction.Complete();
}
}
And actually the 3rd thing I tried actually got the unit tests to work, but all the transactions that completed actually HIT my database! So that was an utter failure, since the entire point is to NOT effect my database.
实际上，我尝试的第三件事实际上使单元测试起作用了，但是所有完成的事务实际上都命中了我的数据库！所以这是一个彻底的失败，因为重点是不影响我的数据库。
My question therefore is, what other options are out there to accomplish what I'm trying to do given the constraints I've laid out?
因此，我的问题是，考虑到我的限制，还有哪些其他选择可以完成我正在尝试做的事情？
EDIT:
编辑：
This is what "//do some database work" would look like
这就是“//做一些数据库工作”的样子
using (var l_context = new DataContext(TargetDatabaseEnum.SomeDatabase))
{
//use a SqlCommand here
//use a SqlDataAdapter inside the SqlCommand
//etc.
}
and the DataContext itself looks something like this
和 DataContext 本身看起来像这样
public class DataContext : IDisposable
{
static int References { get; set; }
static SqlConnection Connection { get; set; }
TargetDatabaseEnum OriginalDatabase { get; set; }
public DataContext(TargetDatabaseEnum database)
{
if (Connection == null)
Connection = new SqlConnection();
if (Connection.Database != DatabaseInfo.GetDatabaseName(database))
{
OriginalDatabase =
DatabaseInfo.GetDatabaseEnum(Connection.Database);
Connection.ChangeDatabase(
DatabaseInfo.GetDatabaseName(database));
}
if (Connection.State == ConnectionState.Closed)
{
Connection.Open() //<- ERROR HAPPENS HERE
}
ConnectionReferences++;
}
public void Dispose()
{
if (Connection.State == ConnectionState.Open)
{
Connection.ChangeDatabase(
DatabaseInfo.GetDatabaseName(OriginalDatabase));
}
if (Connection != null && --ConnectionReferences <= 0)
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
Connection.Dispose();
}
}
}
采纳答案by Joseph
Ok, I found a way around this issue. The only reason I'm doing it this way is because I couldn't find ANY other way to fix this problem, and because it's in my integration tests, so I'm not concerned about this having adverse effects in production code.
好的，我找到了解决这个问题的方法。我这样做的唯一原因是我找不到任何其他方法来解决这个问题，而且因为它在我的集成测试中，所以我不担心这会对生产代码产生不利影响。
I had to add a property to my DataContext to act as a flag to keep track of whether or not to dispose of the connection object when my DataContext is being disposed. This way, the connection is kept alive throughout the entire transaction scope, and therefore no longer bothers DTC
我必须向我的 DataContext 添加一个属性作为一个标志来跟踪在我的 DataContext 被处置时是否处置连接对象。这样，连接在整个事务范围内保持活动状态，因此不再困扰 DTC
Here's sample of my new Dispose:
这是我的新 Dispose 示例：
internal static bool SupressConnectionDispose { get; set; }
public void Dispose()
{
if (Connection.State == ConnectionState.Open)
{
Connection.ChangeDatabase(
DatabaseInfo.GetDatabaseName(OriginalDatabase));
}
if (Connection != null
&& --ConnectionReferences <= 0
&& !SuppressConnectionDispose)
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
Connection.Dispose();
}
}
this allows my integration tests to take the form of:
这允许我的集成测试采用以下形式：
[Test]
public void WorkflowExampleTest()
{
(using var transaction = new TransactionScope())
{
DataContext.SuppressConnectionDispose = true;
Presenter.ProcessWorkflow();
}
}
I would not recommend utilizing this in production code, but for integration tests I think it is appropriate. Also keep in mind this only works for connections where the server is always the same, as well as the user.
我不建议在生产代码中使用它，但对于集成测试，我认为这是合适的。还要记住，这仅适用于服务器和用户始终相同的连接。
I hope this helps anyone else who runs into the same problem I had.
我希望这可以帮助遇到同样问题的其他人。
回答by laktak
If you don't want to use MSDTC you can use SQL transactions directly.
如果不想使用 MSDTC，可以直接使用 SQL 事务。
See SqlConnection.BeginTransaction().
见SqlConnection.BeginTransaction()。
回答by Eduardo
Set Enlist=falseon connection string to avoid auto enlistment on transaction.
Manually enlist connection as participants in transaction scope. (http://msdn.microsoft.com/en-us/library/ms172153%28v=VS.80%29.aspx)
在连接字符串上设置Enlist=false以避免自动登记事务。
手动将连接登记为事务范围内的参与者。(http://msdn.microsoft.com/en-us/library/ms172153%28v=VS.80%29.aspx)
