存储过程返回int结果集,而不是 [英] Stored procedure returns int instead of result set
问题描述
我有一个包含动态选择一个存储过程。事情是这样的:
ALTER PROCEDURE [DBO] [usp_GetTestRecords]
- @ P1 INT = 0,
- @ P2 INT = 0
@groupId为nvarchar(10)= 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query NVARCHAR(最大值)
设置@query =SELECT * FROM CUSTOMERS其中id ='+ @groupId
/ *这实际上包含了动态支点select语句* /
EXECUTE(@query);
端
在SSMS存储过程运行良好,并显示结果集。
在C#中使用实体框架它显示了返回一个 INT
而不是的IEnumerable的
?
私人无效LoadTestRecords()
{
TestRecordsDBEntities的DataContext =新TestRecordsDBEntities();
字符串ID = ddlGroupId.SelectedValue;
名单,LT; TestRecord>清单= dataContext.usp_GetTestRecords(ID); //这部分不工作返回int
GridView1.DataSource =清单;
}
有关生成的函数 usp_GetTestRecords
公共虚拟INT usp_GetTestRecords(字符串的groupId)
{
VAR groupIdParameter =的groupId!= NULL?
新ObjectParameter(的groupId的groupId):
新ObjectParameter(的groupId,typeof运算(字符串));
回报率((IObjectContextAdapter)本).ObjectContext.ExecuteFunction(usp_GetTestRecords,groupIdParameter);
}
实体框架不能告诉你的存储过程返回。我已经成功创建一个从你的SELECT语句反映了数据表变量。只要插入到表变量然后做一个选择从该表变量。 EF应该把它捡起来。
I have a stored procedure that contains dynamic select. Something like this:
ALTER PROCEDURE [dbo].[usp_GetTestRecords]
--@p1 int = 0,
--@p2 int = 0
@groupId nvarchar(10) = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query NVARCHAR(max)
SET @query = 'SELECT * FROM CUSTOMERS WHERE Id = ' + @groupId
/* This actually contains a dynamic pivot select statement */
EXECUTE(@query);
END
In SSMS the stored procedure runs fine and shows result set.
In C# using Entity Framework it shows returning an int
instead of IEnumerable
?
private void LoadTestRecords()
{
TestRecordsDBEntities dataContext = new TestRecordsDBEntities();
string id = ddlGroupId.SelectedValue;
List<TestRecord> list = dataContext.usp_GetTestRecords(id); //This part doesn't work returns int
GridView1.DataSource = list;
}
Generated function for usp_GetTestRecords
public virtual int usp_GetTestRecords(string groupId)
{
var groupIdParameter = groupId != null ?
new ObjectParameter("groupId", groupId) :
new ObjectParameter("groupId", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("usp_GetTestRecords", groupIdParameter);
}
Entity Framework can't tell what your stored procedure is returning. I've had success creating a table variable that mirrors the data from your SELECT statement. Just insert into the table variable then do a select from that table variable. EF should pick it up.
这篇关于存储过程返回int结果集,而不是的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!