防止 T-SQL 语句提交应用程序启动的事务
我正在编写一个程序，该程序使用 ADO.NET 的 SqlCommand 来执行许多用户提供的批量 T-SQL 语句。
我的应用程序打开一个事务，其中执行所有语句，以确保如果执行任何错误，则全部语句都会回滚。
我遇到的问题是，用户提供的 SQL 中放置不当的 COMMIT（没有匹配的 BEGIN TRAN）将提交我最重要的事务，并使我无法回滚。
有人知道如何阻止用户的 SQL 干扰我的事务，但仍允许他们开始/提交/回滚自己的嵌套事务吗？
I am writing a program that uses ADO.NET's SqlCommand to execute a number of user provided batches of T-SQL statements.
My application opens a transaction in which all of the statements are executed to ensure that if there is an error executing any, the whole lot are rolled back.
The problem I have come across is that a badly placed COMMIT (without a matching BEGIN TRAN) in the user provided SQL will commit my all-important transaction and leave me unable to roll back.
Does anybody have any ideas how I can stop the user's SQL from messing with my transaction but still allow them to BEGIN/COMMIT/ROLLBACK their own nested transactions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
用户的 SQL 需要更正 - 如果它放置了错误的 COMMIT（或 ROLLBACK），那么我会将其归类为需要修复的错误。我不太了解您的场景的具体情况，但我认为这确实是您需要做的。如果您发现自己正在尝试实施解决方法，那么就应该敲响警钟，因为它只会使系统变得更加复杂/更难以维护。
The user's SQL needs to be corrected - if it has badly placed COMMITs (or ROLLBACKs), then I'd class that as a bug that needs to be fixed. I don't know much about the specifics of your scenario, but I think this is really what you need to do. If you find yourself trying to implement a workaround, that's when alarm bells should start ringing as it will just make the system more complex/harder to maintain.
您可以在分布式事务中加入SqlCommand。这些不能通过数据库中的 COMMIT 语句提交。请参阅 Connection 对象的 EnlistDistributedTransaction 方法。
分布式事务引入了一类全新的性能和可靠性问题。如果您可以像 AdaTheDev 建议的那样修复 T-SQL 批处理，那将是一个更好的选择。
You could enlist the SqlCommand in a distributed transaction. Those cannot be committed with a COMMIT statement in the database. See the EnlistDistributedTransaction method of the Connection object.
Distributed transactions introduce a whole new class of performance and reliability issues. If you can fix the T-SQL batches like AdaTheDev suggests, that would be a much better option.
您能否以编程方式检查加载到 SqlCommand 中的每个 T-SQL 语句中的 COMMIT 一词，以便您可以标记运行该 T-SQL 时的潜在问题。
也许将它们归档到某个地方以便手动查看。
Could you programmatically check each T-SQL statments that get loaded into the SqlCommand for the word COMMIT, so that you could then flag up potential issues with running that T-SQL.
Maybe archive them somewhere to be manually looked at.