实体模型 - 添加函数导入 - 存储过程不返回任何列 [英] Entity Model - Add Function Import - stored procedure returns no columns
问题描述
我正在使用visual studio 2010和Entity Framework(EF)。我正在使用存储过程从数据库中检索数据并通过实体模型绑定到Gridview。当我在Sql Query中执行此存储过程时,它返回动态生成
的列。即,执行(@PivotTableSQL)。 @PivotTableSQL包含动态生成的select语句。以下是我的存储过程。
I am using visual studio 2010 and with Entity Framework(EF). I am retriving data from database and binding into Gridview thru Entity Model, using stored procedures. when i execute this Stored procedure in Sql Query, its returns columns which is generated dynamically. ie., Execute (@PivotTableSQL). @PivotTableSQL contains dynamically generated select statement. Below is my Stored procedure.
但是,在创建实体模型浏览器窗口时 - >添加功能导入窗口 - >复杂 - >获取列信息,无法识别结果集列。请给我一个解决方案,如何添加功能导入和获取列信息(创建
复杂类型)或任何其他替代解决方案。这个阻止了我的进一步发展。提前致谢。
But, while creating the Entity model browser window -> Add Function import window -> Complex --> Get Columns Information, the resultset columns cannot be identified. Please give me a solution, how to add function import and Get Column information(create complex Type) or any other alternate solution detaily. This one stopped my further development. Thanks in advance.
alter PROCEDURE spGetQuestGrid
(
@QID as int
)
$
AS
BEGIN
SET NOCOUNT OFF
SET FMTONLY OFF
 声明@optId VARCHAR(10)
  DECLARE @PivotColumnHeaders VARCHAR(MAX)
  DECLARE @PivotTableSQL NVARCHAR(MAX)
设置@optId =(从QuestOptions中选择DISTINCT Optid,其中QID = @QID)
  SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + '[' +铸造(标题为varchar)+ ']',
    &NBSP ; '['+ cast(Caption as varchar)+']')
FROM OptionsDetail其中OptId = @optId
 
  SET @PivotTableSQL ="SELECT * FROM(选择 标题,Optid 从OptionsDetail)作为数据表
PIVOT
(min(Optid)FOR Caption IN('+ @PivotColumnHeaders +'))as Colltable'
执行(@PivotTableSQL)
alter PROCEDURE spGetQuestGrid
(
@QID as int
)
AS
BEGIN
SET NOCOUNT OFF
SET FMTONLY OFF
Declare @optId varchar(10)
DECLARE @PivotColumnHeaders VARCHAR(MAX)
DECLARE @PivotTableSQL NVARCHAR(MAX)
set @optId = (select DISTINCT Optid from QuestOptions where QID = @QID)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,
'[' + cast(Caption as varchar)+ ']')
FROM OptionsDetail where OptId = @optId
SET @PivotTableSQL = 'select * from (select Caption,Optid from OptionsDetail ) as datatable
PIVOT
( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as Colltable'
Execute (@PivotTableSQL)
推荐答案
Hello Kanjay,
Hello Kanjay,
感谢您的主题!
要在Visual Studio 2010中将存储过程导入为函数,您可以打开
模型浏览器并双击陈旧模型中的存储过程。这将打开一个新对话框,请点击
获取列信息,然后c 创建一个新的复杂类型返回。在您的方案中,因为您"select * from ..."您可以随机获取列信息,例如C1,C2,C3等...
To import the stored procedure as function in Visual Studio 2010, you could open Model Browser and double clicck the stored procedure in Store model. This will open a new dialog box, please click get column information and then create a new complex type to return. In your scenario, since you "select * from..." you may get the column information randomly, for example, C1, C2, C3, etc...
请试一试,让我知道结果!
最好的问候
Please have a try and let me know the result!
Best regards
这篇关于实体模型 - 添加函数导入 - 存储过程不返回任何列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!