如何从linq中的存储过程中获取值 [英] How to get Values from stored procedure in linq

问题描述

Hi Team，

这里我在linq中从动态创建的程序中获取数据时遇到了问题。

它返回0



程序是在现有表格的数据透视数据的帮助下创建的。



这是存储过程。

  ALTER   proc  [dbo]。[usp_tblProperty] 
  AS  
  DECLARE   @ PivotColumnHeaders   VARCHAR （MAX）
  SELECT   @PivotColumnHeaders  = 
  COALESCE （
  @ PivotColumnHeaders  + < span class =code-string>' ，[' + cast（propertyName  as   varchar （ 55 ））+ ' ]'，
 '  [' + cast（propertyName  as   varchar （ 55 ））+ ' ]' 
）
  FROM  tblproperty 
  print   @ PivotColumnHeaders  
  DECLARE   @ PivotTableSQL   NVARCHAR （MAX）
  SET  < span class =code-sdkkeyword> @ PivotTableSQL  = N '  
 SELECT * 
 FROM（
 SELECT 
 EntityObjectId，tblorganisationentity.OrganisationName，tblproperty.propertyname a s propertyname，value 
 FROM tblpropertyvalue inner join tblProperty 
 on tblpropertyvalue.propertyid = tblproperty.propertyid 
 inner join tblorganisationentity 
 on tblorganisationentity.organisationId = tblpropertyvalue.entityobjectid 
 
）AS PivotData 
 PIVOT（
 max（value）
 FOR propertyname IN（
' +  @ PivotColumnHeaders  + '  
）
）AS PivotTable 
' 
 打印  @ PivotTableSQL  
  EXECUTE （ @ PivotTableSQL ）

OutPut of Procedure是如下所示数据：



OrganisationId OrganisationName SAdd1 lSubscriberId



F649AD83-CA02-43AC-9A5E-FD6B18 3415F1 3M Norge AS 0 3M









在C＃Page时我尝试使用linq访问数据它返回整数值，我无法从程序访问proerties。



此处C＃中的代码：

  var  elno = objEfoDataContext.usp_tblProperty（）; 
 elno  return   0  

解决方案

它的解决方案是你可以创建内部查询的视图。



DECLARE @PivotColumnHeaders VARCHAR（MAX）

SELECT @PivotColumnHeaders =

COALESCE（

@PivotColumnHeaders +'，['+ cast（propertyName as varchar（55））+']'，

'['+ cast（propertyName as varchar（55））+']'

）

FROM tblproperty

print @ PivotColumnHeaders

DECLARE @PivotTableSQL NVARCHAR（MAX）

SET @PivotTableSQL = N'

创建视图abc

as

SELECT *

FROM（

SELECT

EntityObjectId，tblorganisationentity.OrganisationName， tblproperty.propertyname as propertyname，value

FROM tblpropertyvalue inner join tblProperty

on tblpropertyvalue.propertyid = tblpropert y.propertyid

内部联合tblorganisationentity

on tblorganisationentity.organisationId = tblpropertyvalue.entityobjectid



）AS PivotData

PIVOT（

max（value）

FOR propertyname IN（

'+ @PivotColumnHeaders +'

）

）AS PivotTable

'



现在你可以访问视图

select * from abc

要读取存储过程的结果Linq你需要为proc填充创建一个列表：

 使用（objEfoDataContext dc =  new  objEfoDataContext（））
 {
 
 List< usp_tblproperty> myresult =  new  List< usp_tblproperty>（）; 
 myresult = dc.usp_tblPropertyResult（）。tolist（）; 
  //   myresult将包含存储过程中的数据 
} < /   usp_tblproperty  >  < /   usp_tblproperty  >  

喜。这里解决了你的问题..

 DataClasses2DataContext db = new DataClasses2DataContext（）; 
 
 IEnumerable< sp_SonYapilanlarResult> sp_gelen =（IEnumerable< sp_SonYapilanlarResult>）db.sp_SonYapilanlar（）; 
 
 List< sp_SonYapilanlarResult> listemeARTI = new List< sp_SonYapilanlarResult>（）; 
 List< sp_SonYapilanlarResult> listemeSIFIR = new List< sp_SonYapilanlarResult>（）; 
 List< sp_SonYapilanlarResult> listemeEKSI = new List< sp_SonYapilanlarResult>（）; 
 foreach（sp_gelen中的sp_SonYapilanlarResult gelenItem）
 {
 if（gelenItem.Delta> 0）listemeARTI.Add（gelenItem）; 
 if（gelenItem.Delta == 0）listemeSIFIR.Add（gelenItem）; 
 if（gelenItem.Delta< 0）listemeEKSI.Add（gelenItem）; 
} 
 
 GridView1.DataSource = listemeARTI; 
 GridView1.DataBind（）; 
 
 GridView2.DataSource = listemeSIFIR; 
 GridView2.DataBind（）; 
 
 GridView3.DataSource = listemeEKSI; 
 GridView3.DataBind（）; 

Hi Team,
Here I got issue while fetching data from dynamic created procedure in linq.
it returns 0

Procedure is created with the help of Pivot data from existing table.

Here is Stored Procedure.

ALTER proc [dbo].[usp_tblProperty]
AS
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(propertyName as varchar(55)) + ']',
    '[' + cast(propertyName as varchar(55))+ ']'
  )
FROM tblproperty
print @PivotColumnHeaders
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT *
  FROM (
    SELECT
     EntityObjectId,tblorganisationentity.OrganisationName,tblproperty.propertyname as propertyname,value
    FROM tblpropertyvalue inner join tblProperty
    on tblpropertyvalue.propertyid = tblproperty.propertyid
    inner join tblorganisationentity 
    on tblorganisationentity.organisationId = tblpropertyvalue.entityobjectid
   
  ) AS PivotData
  PIVOT (
    max(value)
    FOR propertyname IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
Print @PivotTableSQL
EXECUTE(@PivotTableSQL)

OutPut of Procedure is something like below mention data:

OrganisationId OrganisationName SAdd1 lSubscriberId

F649AD83-CA02-43AC-9A5E-FD6B183415F1 3M Norge A.S 0 3M




In C# Page when i try to access data with linq it return integer value and i am unable to access proerties from procedure.

Here Code in C# :

var elno = objEfoDataContext.usp_tblProperty();
elno return 0

解决方案

Its Solution is that you can create view of inner query.

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(propertyName as varchar(55)) + ']',
'[' + cast(propertyName as varchar(55))+ ']'
)
FROM tblproperty
print @PivotColumnHeaders
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
create view abc
as
SELECT *
FROM (
SELECT
EntityObjectId,tblorganisationentity.OrganisationName,tblproperty.propertyname as propertyname,value
FROM tblpropertyvalue inner join tblProperty
on tblpropertyvalue.propertyid = tblproperty.propertyid
inner join tblorganisationentity
on tblorganisationentity.organisationId = tblpropertyvalue.entityobjectid

) AS PivotData
PIVOT (
max(value)
FOR propertyname IN (
' + @PivotColumnHeaders + '
)
) AS PivotTable
'

Now you can access view as
"select * from abc"

Hi,

To read the results of a stored procedure in Linq you need to create a list for the proc to populate:

using(objEfoDataContext dc = new objEfoDataContext())
{

List<usp_tblproperty> myresult = new List<usp_tblproperty>();
myresult= dc.usp_tblPropertyResult().tolist();
//myresult will have the data from your stored procedure
}</usp_tblproperty></usp_tblproperty>

hi. here is solved your problem..

DataClasses2DataContext db = new DataClasses2DataContext();

        IEnumerable<sp_SonYapilanlarResult> sp_gelen = (IEnumerable<sp_SonYapilanlarResult>)db.sp_SonYapilanlar();

        List<sp_SonYapilanlarResult> listemeARTI = new List<sp_SonYapilanlarResult>();
        List<sp_SonYapilanlarResult> listemeSIFIR = new List<sp_SonYapilanlarResult>();
        List<sp_SonYapilanlarResult> listemeEKSI = new List<sp_SonYapilanlarResult>();
        foreach (sp_SonYapilanlarResult gelenItem in sp_gelen)
        {
            if (gelenItem.Delta > 0)                listemeARTI.Add(gelenItem);
            if (gelenItem.Delta == 0)                listemeSIFIR.Add(gelenItem);
            if (gelenItem.Delta < 0)                listemeEKSI.Add(gelenItem);
        }

        GridView1.DataSource = listemeARTI;
        GridView1.DataBind();

        GridView2.DataSource = listemeSIFIR;
        GridView2.DataBind();

        GridView3.DataSource = listemeEKSI;
        GridView3.DataBind();

这篇关于如何从linq中的存储过程中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！