c# - 使用 ExecuteNonQuery 运行存储过程不是创建我的表，而是在 SSMS 中执行 sp 时创建表

Question

一些注意事项:

1. ExecuteNonQuery 返回 -1
2. ExecuteNonQuery 将删除表 (@droptable)，但不会创建新表 (@code)
3. @code 查询的长度为 10265 个字符
4. 存储过程在 SSMS 中运行良好，并在表中返回 22 行

对于为什么 C# 的 ExecuteNonQuery 函数似乎没有执行存储过程的“exec(@code)”部分，有什么想法吗？

ALTER procedure [dbo].[sp_create_EditControlResultsPivot] 
as
begin
    declare @t nvarchar (250); 
    set @t = 'editControlResults'

    declare @newtable nvarchar(250); 
    set @newtable = 'dbo.' + @t + 'Pivot'

    declare @nonPivotColumn1 nvarchar(250); 
    set @nonPivotColumn1 = 'num'

    declare @nonPivotColumn2 nvarchar(25); 
    set @nonPivotColumn2 = 'File_Name'

    declare @droptable nvarchar(max); 
    set @droptable =  
'if EXISTS (select * from sys.objects where object_id = object_id(N''' + @newtable + '''))
begin drop table ' + @newtable + ' end
'

    declare @i int 
    set @i = 1;

    declare @itemList nvarchar(max);
    declare @code nvarchar(max);

    while @i <= (
        select COUNT(*) 
        from sys.columns c 
        left join sys.tables t on c.object_id = t.object_id 
        where 1=1 
          and c.name not like @nonPivotColumn1 
          and c.name not like @nonPivotColumn2
          and t.name = @t
    ) 
    begin

    set @itemList = @itemList + ', ' +
    (
        select col from 
        (
            select c.name as col, ROW_NUMBER () over (order by c.name) as num from
            sys.columns c left join sys.tables t on c.object_id = t.object_id 
            where 1=1
            and c.name not like @nonPivotColumn1
            and c.name not like @nonPivotColumn2
            and t.name = @t 
        ) sub where num = @i
    )
    set @i = @i + 1
  end

  set @itemList = (select substring(@itemList, 2, LEN(@itemList)))

  set @code = '
  SELECT ' + @nonpivotcolumn2 + ', Item
  into ' + @newtable + '
FROM
(SELECT ' + @nonpivotcolumn2 + ', ' + @itemList + '
FROM ' + @t + ') sub
UNPIVOT
(Value FOR Item IN (' + @itemList + ')
) AS sub
where Value = ''true''
'

exec(@droptable)
exec(@code);
--print(len(@code))
END
--exec sp_create_EditControlResultsPivot

Answer 1

ExecuteNonQuery 方法返回受影响的行数，而应使用 ExecuteReader 方法。

SqlCommand.ExecuteReader Method

从 ExecuteNonQuery 返回数据的唯一方法是通过输出参数。