SqlDataAdapter未填充DataTable [英] SqlDataAdapter not filling DataTable

问题描述

我遇到一个问题，当执行特定的SQL Server存储过程时， SqlDataAdapter 没有填充 DataTable ，尽管事实是我有许多类似的函数执行几乎相同的存储过程，但它们都能正常工作。

I am having a problem where a SqlDataAdapter is not filling a DataTable when executing a particular SQL Server stored procedure, despite the fact I have many similar functions executing almost identical stored procedures that all work correctly.

这是运行存储过程并返回填充的<$ c的函数$ c> DataTable ;

Here is the function that runs the stored procedure and returns the filled DataTable;

public DataTable getAssetClassifications(int? pLevel)
{            
    // Create dataTable to hold data
    var dt = new DataTable();

    // Initialize connection
    using (connection = new SqlConnection(connectionString))
    using (SqlCommand command = connection.CreateCommand())
    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
    {
        // Open connection
        connection.Open();

        // Set connection properties
        command.CommandText = "BR_Manage_Primary_Classification_GetItemsAssets";
        command.CommandType = CommandType.StoredProcedure;

        // Add params
        command.Parameters.AddWithValue("@pLevel", pLevel);

        // Create return value parameter
        SqlParameter returnValue = new SqlParameter();
        returnValue.Direction = ParameterDirection.ReturnValue;

        // Add return value to command
        command.Parameters.Add(returnValue);

        // Execute command
        command.ExecuteNonQuery();

        // Fill DataTable
        adapter.Fill(dt);

        // Get result
        var result = returnValue.Value;

        Console.WriteLine("Get asset classifications result: " + result.ToString());

        if (result.Equals(0))
        {
            return dt;
        }
    }

    return null;       
}

运行 pLevel = null 将返回null，应返回382行记录集。通过调试，我可以看到 adapter.fill（dt）返回了382，如它应有的那样，但是尚未填充数据表 dt。

Running this with pLevel = null will return null where it should be return a 382 row recordset. From debugging I can see that 'adapter.fill(dt)' returns 382, as it should, yet the DataTable 'dt' is not filled.

存储过程如下所示：

CREATE PROCEDURE BR_Manage_Primary_Classification_GetItemsAssets
/*  
TODO:   Allow for subsets based on tree branches
*/
    @pLevel AS INT = NULL
AS
BEGIN
/*      
    Description:    Independently returns asset classifications

        exec BR_Manage_Primary_Classification_GetItemsAssets

    Outputs:    None

    NOTE: This BR doesn't write, so it doesn't audit but it does error log the call to the DAL.

*/
    SET xact_abort, nocount on
    DECLARE @StoredProcedureName AS SYSNAME = quotename(object_schema_name(@@procid))+'.'+quotename(object_name(@@procid))
    DECLARE @Parameters AS NVARCHAR(1000) = 'Parameters: '
    DECLARE @LocalError AS INT
    DECLARE @LocalErrorMessage AS NVARCHAR(2048) 
    DECLARE @NewAuditID AS INT = NULL

BEGIN TRY
    BEGIN TRANSACTION
        EXEC DAL_Primary_Classification_GetItemsAssets
    COMMIT TRANSACTION

    -- Return SUCCESS
    RETURN 0
END TRY
BEGIN CATCH
    -- On fail close any open transactions and write to Error Log
    SET @LocalError = @@ERROR
    SET @LocalErrorMessage = ERROR_MESSAGE()

    IF @@trancount > 0 
       ROLLBACK TRANSACTION

    -- Actual error logging
    EXEC DAL_System_LogError null, @StoredProcedureName, @LocalError, @LocalErrorMessage, @Parameters, null, null, null

    -- Return Error
    RETURN 1
END CATCH
END

这称为DAL：

CREATE PROCEDURE [dbo].[DAL_Primary_Classification_GetItemsAssets]
as
begin
    /*
    Description:    Returns all assets

            exec [DAL_Primary_Classification_GetItemsAssets] 

            exec DAL_Primary_Classification_GetItems 3, 4 

    Outputs:    None

*/
set nocount on;

select  c.id as rule_id,
        c.[Description] as rule_description,
        c.Comment as rule_comment,
        l91.id as L91_IDm,
        l91.name as L91_Name,
        l91.[Description] as L91_Description,
        l91.Comment as L91_Comment,
        l92.id as L92_ID,
        l92.name as L92_Name,
        l92.[Description] as L92_Description,
        l92.Comment as L92_Comment

from    Classifications_Assets as c
            left outer join Class_L9_1 as l91 on c.Class_L9_1_ID = l91.ID       and l91.Deleted = 0
            left outer join Class_L9_2 as l92 on c.Class_L9_2_ID = l92.ID     and l92.Deleted = 0
where   c.Deleted = 0
end

很抱歉，我们将不胜感激！

Sorry for the wall of text, any help would be greatly appreciated!

推荐答案

我想我在其他答案上误解了你。

I think I misunderstood you on my other answer.

我看到你在打电话 ExecuteNonQuery 之前 adapter.Fill（dt）。

I see you're calling ExecuteNonQuery before adapter.Fill(dt).

是否需要调用 ExecuteNonQuery 在那里？

您可以在没有它的情况下进行测试吗？

Can you test without it?

这篇关于SqlDataAdapter未填充DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！