使用 SqlDataAdapter.Fill(DataTable) 在 .NET 中捕获的存储过程的异常

Question

我有一个从 VB.NET 执行的存储过程。SP 应将记录插入表中，并将集返回到调用应用。返回的集是插入的记录。

如果INSERT失败，则会捕获异常并在 SP 中重新引发，但我从未在应用程序中看到异常。严重性级别为 14，所以我应该看到它。

下面是存储过程：

BEGIN TRY    
    BEGIN TRANSACTION 
        -- Declare local variables
        DECLARE @DefaultCategoryID AS BIGINT = 1                -- 1 = 'Default Category' (which means no category)
        DECLARE @DefaultWeight AS DECIMAL(18,6) = 0
        DECLARE @InsertionDate AS DATETIME2(7) = GETDATE()
        DECLARE @SendToWebsite AS BIT = 0                       -- 0 = 'NO'
        DECLARE @MagentoPartTypeID AS BIGINT = 1                -- For now, this is the only part type we are importing from COPICS ('simple' part type)
        DECLARE @NotUploaded_PartStatusID AS TINYINT = 0        -- 0 = 'Not Uploaded'
        DECLARE @Enabled_PartStatusID AS TINYINT = 1            -- 1 = 'Enabled'
        DECLARE @Disabled_PartStatusID AS TINYINT = 2           -- 2 = 'Disabled'

        -- Get the part numbers that will be inserted (this set will be returned to calling procedure).
        SELECT c.PartNumber
        FROM
            COPICSPartFile c 
            LEFT JOIN Part p on c.PartNumber = p.PartNumber
        WHERE 
            p.PartNumber IS NULL        
        -- Insert new records from COPICSPartFile (records that don't exist - by PartNumber - in Part table)
        INSERT INTO Part
            ([PartNumber]
            ,[ReplacementPartNumber]
            ,[ShortDescription]
            ,[ListPrice]
            ,[PartStatusTypeID]
            ,[Weight]
            ,[CategoryID]
            ,[DateInserted]
            ,[SendToWebsite]
            ,[FileName]
            ,[MagentoPartTypeID]
            ,[PrintNumber])
        SELECT
            c.PartNumber
            ,c.ReplacementPartNumber
            ,c.ShortDescription
            ,c.ListPrice
            ,CASE WHEN c.PartStatusTypeID = @Enabled_PartStatusID THEN @NotUploaded_PartStatusID ELSE @Disabled_PartStatusID END
            ,@DefaultWeight
            ,@DefaultCategoryID
            ,@InsertionDate
            ,@SendToWebsite
            ,@FileName
            ,@MagentoPartTypeID
            ,c.PrintNumber
        FROM
            COPICSPartFile c 
            LEFT JOIN Part p on c.PartNumber = p.PartNumber
        WHERE 
            p.PartNumber IS NULL

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH

这是 .net 代码：

Try
    'Create command
    Dim command As New SqlCommand
    command.CommandType = CommandType.StoredProcedure
    conn = New SqlConnection(m_ConnectionString)
    command.Connection = conn
    command.CommandText = "trxInsertPartFromCOPICSPartFile"
    With command.Parameters
        .AddWithValue("@FileName", fileName)
    End With
    Dim da As New SqlDataAdapter(command)
    Dim dt As New DataTable
    da.Fill(dt)
    If dt.Rows.Count > 0 Then
        Return dt
    Else
        Return Nothing
    End If
Catch ex As SqlException
    Dim myMessage As String = ex.Message
Finally
    If conn.State <> ConnectionState.Closed Then
        conn.Close()
    End If
End Try

当我试图弄清楚为什么我的应用程序中没有捕获异常（重复键）时，我尝试在 INSERT 之前注释掉 SP 中的 SELECT 语句，瞧。应用程序中捕获了来自INSERT的异常。

有人可以向我解释为什么SELECT声明会导致这种情况吗？我知道我可以将SELECT分解为另一个 SP，但如果可能的话，我想将其全部保留为一个原子事务。这是预期行为吗？有没有办法解决它？

谢谢。

Answer 1

异常正在被Fill方法吞噬。不要使用该方法，而是创建一个SqlDataReader，执行command.ExecuteReader()，然后使用读取器通过Load()填充DataTable。这样，错误应该发生在ExecuteReader()方法中，并且应该是可捕获的。然后你不应该需要SqlDataAdapter.

Try
    'Create command
    Dim command As New SqlCommand
    command.CommandType = CommandType.StoredProcedure
    conn = New SqlConnection(m_ConnectionString)
    command.Connection = conn
    command.CommandText = "trxInsertPartFromCOPICSPartFile"
    With command.Parameters
        .AddWithValue("@FileName", fileName)
    End With
    Dim dt As New DataTable
    conn.Open()
    Dim reader As SqlDataReader = command.ExecuteReader()
    dt.Load(reader)
    If dt.Rows.Count > 0 Then
        Return dt
    Else
        Return Nothing
    End If
Catch ex As SqlException
    Dim myMessage As String = ex.Message
Finally
    If conn.State <> ConnectionState.Closed Then
        conn.Close()
    End If
End Try

此外，如果将 SELECT 和 INSERT 合并到一个语句中，您可能会在多个级别上更好。您可以通过 OUTPUT 子句执行此操作，如下所示：

    INSERT INTO Part
        ([PartNumber]
        ,[ReplacementPartNumber]
        ,[ShortDescription]
        ,[ListPrice]
        ,[PartStatusTypeID]
        ,[Weight]
        ,[CategoryID]
        ,[DateInserted]
        ,[SendToWebsite]
        ,[FileName]
        ,[MagentoPartTypeID]
        ,[PrintNumber])
    OUTPUT INSERTED.[PartNumber] -- return the inserted values to the app code
    SELECT
        c.PartNumber
        ,c.ReplacementPartNumber
        ,c.ShortDescription
        ,c.ListPrice
        ,CASE WHEN c.PartStatusTypeID = @Enabled_PartStatusID
                THEN @NotUploaded_PartStatusID
                ELSE @Disabled_PartStatusID END
        ,@DefaultWeight
        ,@DefaultCategoryID
        ,@InsertionDate
        ,@SendToWebsite
        ,@FileName
        ,@MagentoPartTypeID
        ,c.PrintNumber
    FROM
        COPICSPartFile c 
        LEFT JOIN Part p on c.PartNumber = p.PartNumber
    WHERE 
        p.PartNumber IS NULL