具有TVP参数的ExecuteStoreQuery

问题描述

我有一个存储过程在我的数据库中获取一个表值参数，一个包含一个整数Id列的IdTable对象的列表。

我有一个实体数据库模型，并希望执行以下操作...

  ProjectEntities projectEntities = new ProjectEntities（）; 
 
 DataTable stationIds = new DataTable（）; 
 stationIds.Columns.Add（Id）; 
 stationIds.Rows.Add（1）; 
 stationIds.Rows.Add（2）; 
 
 SqlParameter参数= new SqlParameter（@ stationIds，stationIds）; 
 parameter.TypeName =IdTable; 
 
 var parameters = new object [] {parameter}; 
 
 var results = projectEntities .ExecuteStoreQuery< ProjectSummary>（exec ProjectSummary，参数）; 
 
 var count = results.Count（）; 
  

当它返回一堆ProjectSummary实体时，运行并返回任何结果。

当我在SQL Profiler中配置文件时，我得到以下

  declare @ p3 IdTable 
 insert into @ p3 values（N'1'）
 insert into @ p3 values（N'2'）
 
 exec sp_executesql N'exec ProjectSummary'，N'@ stationIds [IdTable] READONLY'，@ stationIds = @ p3 
  

如果我声明存储过程为

  ALTER PROCEDURE [dbo]。[ProjectSummary] 
 @stationIds [dbo]。[IdTable] READONLY 
 AS 
 BEGIN 
  - 添加了SET NOCOUNT，以防止额外的结果集从
  - 干扰SELECT语句。 
 SET NOCOUNT ON; 
 SELECT * FROM @stationIds 
 ... 
  

然后我得不到结果返回看来，TVP参数看起来就是空的。

如果我手动执行

  declare @ p3 IdTable 
 insert into @ p3 values（N'1'）
 insert into @ p3 values（N'2'）
 
 EXEC [ProjectSummary] 
 @stationIds = @ p3 
 
 GO 
  

我得到从SELECT查询返回的值1和2。

所以，当我运行ExecuteStoreCommand时，看起来我想使用EXEC而不是SP_EXECUTESQL。给出上面的代码示例，我该怎么做？

解决方案

结果是ExecuteStoreQuery调用不正确，应该是

  SqlParameter stations = new SqlParameter {ParameterName =p0，Value = ids，TypeName =[dbo]。[IdTable] ，SqlDbType = SqlDbType.Structured}; 
 
 var parameters = new object [] {stations}; 
 
 var results = projectEntities.ExecuteStoreQuery< ProjectSummary>（exec ProjectSummary @ p0，参数）; 
  

所以我需要命名参数，并将@ p0添加到exec命令。

I have a stored procedure in my database that takes a table value parameter, a list of IdTable objects which contain a single integer Id column.

I have an entity model for the database and want to do the following...

ProjectEntities projectEntities = new ProjectEntities ();

DataTable stationIds = new DataTable();
stationIds.Columns.Add("Id");
stationIds.Rows.Add(1);
stationIds.Rows.Add(2);

SqlParameter parameter = new SqlParameter("@stationIds",stationIds);
parameter.TypeName = "IdTable";

var parameters = new object[] {parameter};

var results = projectEntities .ExecuteStoreQuery<ProjectSummary>("exec ProjectSummary", parameters);

var count = results.Count();

This runs and returns no results, when it should return a bunch of ProjectSummary entities.

When I profile this in SQL Profiler, I get the following

declare @p3 IdTable
insert into @p3 values(N'1')
insert into @p3 values(N'2')

exec sp_executesql N'exec ProjectSummary',N'@stationIds [IdTable] READONLY',@stationIds=@p3

If I declare the stored procedure to be

ALTER PROCEDURE [dbo].[ProjectSummary]
    @stationIds  [dbo].[IdTable] READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
SELECT * FROM @stationIds
...

Then I get not results back, it looks like the TVP parameter is coming through empty.

Where as if I manually execute

declare @p3 IdTable
insert into @p3 values(N'1')
insert into @p3 values(N'2')

EXEC    [ProjectSummary]
        @stationIds = @p3

GO

I get the values 1 and 2 returned from the SELECT query.

So, it looks like I want to use EXEC rather than SP_EXECUTESQL when I run ExecuteStoreCommand. Given the code example above, how on earth do I do that?

解决方案

Turns out the ExecuteStoreQuery call was incorrect, it should be

SqlParameter stations = new SqlParameter { ParameterName = "p0", Value = ids, TypeName = "[dbo].[IdTable]", SqlDbType = SqlDbType.Structured };

var parameters = new object[] { stations };

var results = projectEntities.ExecuteStoreQuery<ProjectSummary>("exec ProjectSummary @p0", parameters);

So I needed to name parameter and add the @p0 to the exec command.

这篇关于具有TVP参数的ExecuteStoreQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！