SQL Server CLR 集成在当前事务中登记

Question

我尝试使用 SQL Server 中的 CLR 集成来处理对外部文件的访问，而不是在内部将它们存储为 BLOB。我正在尝试找出需要遵循的模式，以使我的代码加入当前的 SQL 事务。我想我会从最简单的场景开始，删除现有行，因为插入/更新场景会更复杂。

[SqlProcedure]
public static void DeleteStoredImages(SqlInt64 DocumentID)
{
    if (DocumentID.IsNull)
        return;

    using (var conn = new SqlConnection("context connection=true"))
    {
        conn.Open();

        string FaceFileName, RearFileName;
        int Offset, Length;
        GetFileLocation(conn, DocumentID.Value, true,
            out FaceFileName, out Offset, out Length);
        GetFileLocation(conn, DocumentID.Value, false,
            out RearFileName, out Offset, out Length);

        new DeleteTransaction().Enlist(FaceFileName, RearFileName);

        using (var comm = conn.CreateCommand())
        {
            comm.CommandText = "DELETE FROM ImagesStore WHERE DocumentID = " + DocumentID.Value;
            comm.ExecuteNonQuery();
        }
    }
}

private class DeleteTransaction : IEnlistmentNotification
{
    public string FaceFileName { get; set; }
    public string RearFileName { get; set; }
    public void Enlist(string FaceFileName, string RearFileName)
    {
        this.FaceFileName = FaceFileName;
        this.RearFileName = RearFileName;
        var trans = Transaction.Current;
        if (trans == null)
            Commit(null);
        else
            trans.EnlistVolatile(this, EnlistmentOptions.None);
    }
    public void Commit(Enlistment enlistment)
    {
        if (FaceFileName != null && File.Exists(FaceFileName))
        {
            File.Delete(FaceFileName);
        }
        if (RearFileName != null && File.Exists(RearFileName))
        {
            File.Delete(RearFileName);
        }
    }

    public void InDoubt(Enlistment enlistment)
    {
    }

    public void Prepare(PreparingEnlistment preparingEnlistment)
    {
        preparingEnlistment.Prepared();
    }

    public void Rollback(Enlistment enlistment)
    {
    }
}

当我实际尝试运行此程序时，出现以下异常：

A .NET Framework error occurred during execution of user defined routine or aggregate 'DeleteStoredImages': 
System.Transactions.TransactionException: The operation is not valid for the state of the transaction. ---> System.Transactions.TransactionPromotionException: MSDTC on server 'BD009' is unavailable. ---> System.Data.SqlClient.SqlException: MSDTC on server 'BD009' is unavailable.
System.Data.SqlClient.SqlException: 
   at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
   at System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
System.Transactions.TransactionPromotionException: 
   at System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
System.Transactions.TransactionException: 
   at System.Transactions.TransactionState.EnlistVolatile(InternalTransaction tx, IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions, Transaction atomicTransaction)
   at System.Transactions.TransactionStateSubordinateActive.EnlistVolatile(InternalTransaction tx, IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions, Transaction atomicTransaction)
   at System.Transactions.Transaction.EnlistVolatile(IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions)
   at ExternalImages.StoredProcedures.DeleteTransaction.Enlist(String FaceFileName, String RearFileName)
   at ExternalImages.StoredProcedures.DeleteStoredImages(SqlInt64 DocumentID)
. User transaction, if any, will be rolled back.
The statement has been terminated.

任何人都可以解释我做错了什么，或者向我指出如何正确执行此操作的示例吗？

Answer 1

您现在希望已经解决了这个问题，但万一其他人也遇到类似的问题：您收到的错误消息表明您需要在 BD009分布式事务协调器 服务code> 机器（大概是你自己的机器）。

Answer 2

@Aasmund 关于分布式事务协调器的回答可能会解决所述问题，但这仍然使您处于非理想状态：您正在绑定一个事务，这会锁定 ImagesStore表（即使它只是一个RowLock），到两个文件系统操作？并且您需要在此函数之外BEGIN 和COMMIT 事务（因为所提供的代码中未处理该事务）。

我会将这两部分分开：

• 第 1 步：从表中删除该行

  然后，如果没有错误，

• 步骤 2：删除文件

如果步骤 1 成功，但步骤 2 无论出于何种原因失败，请执行以下一项或两项操作：

• 返回错误状态代码并跟踪哪个 DocumentID在尝试删除状态表中的文件时出错。您可以使用它来手动删除文件和/或调试发生错误的原因。

• 创建一个可以定期运行以查找和删除未引用文件的进程。