ExecuteStoreQuery with TVP parameters
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.
精彩评论