How to get Values from stored procedure in linq

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

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.

SQL

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# :

C#

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

Posted 19-Apr-12 0:08am

sunandandutt

Updated 13-Apr-18 12:35pm

Prasad_Kulkarni

v3

Add a Solution

3 solutions

Solution 1

Hi,

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

C#

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>

Posted 22-Apr-12 13:21pm

milenalukic

Solution 3

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();

Posted 13-Apr-18 12:36pm

AYDIN_CANDAN

Comments

Maciej Los 14-Apr-18 14:14pm

6 years too late ;(

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sunandandutt · Accepted Answer · 2012-05-13T22:50:00

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"