Getting DataTable from stored procedures

[Ajay]

I have a stored procedure that uses a dynamic pivot table returns a dataset as follows.
I want to use ORM entity objects to get a data table from the returned value to populate MVC grid.
Is that possible?
Regards
ajay

Entity object (using wizard to import SP)

public object[] ProcGetMonthlyConsumption(string pPeriodStart, string pPeriodEnd)
        {
            SqlParameter parameterPPeriodStart = new SqlParameter("pPeriodStart", OpenAccessType.Varchar);
            SqlParameter parameterPPeriodEnd = new SqlParameter("pPeriodEnd", OpenAccessType.Varchar);

            List<SqlParameter> sqlParameters = new List<SqlParameter>()
            {
                parameterPPeriodStart, parameterPPeriodEnd
            };

            object[] queryResult = this.ExecuteStoredProcedure<object>("'procGetMonthlyConsumption' ?,?", sqlParameters ,pPeriodStart, pPeriodEnd);
            return queryResult;
        }

SPC:

-- =============================================

-- Author:      <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[procGetMonthlyConsumption]

(

@pPeriodStart nvarchar(4)='2009',

@pPeriodEnd nvarchar(4)='2014'

)

    -- Add the parameters for the stored procedure here

     

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

SET NOCOUNT ON;

SET NOCOUNT ON;

DECLARE @listCol VARCHAR(2000)

DECLARE @query VARCHAR(4000)

 

SELECT  @listCol = STUFF(( SELECT DISTINCT

                                '],[' + ltrim(convert(nvarchar(3),BillStartPeriod,107))

                        FROM    dbo.table_energy_consumption

                        --where '],[' + ltrim(convert(nvarchar(4),BillStartPeriod,120))='],[' + @pBillPeriod

                        ORDER BY '],[' + ltrim(convert(nvarchar(3),BillStartPeriod,107))

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']'

              

-- @pBillPeriod varchar(4)='2011'

 

SET @query =

'SELECT * FROM

      (SELECT ConsumerId,LeaseID, ltrim(convert(nvarchar(3),BillStartPeriod,107)) ConsumptionMonth, BillPeriodConsumption,ltrim(convert(nvarchar(4),BillStartPeriod,120)) as Saal

            FROM dbo.table_energy_consumption

            where  YEAR(BillStartPeriod) >= '+@pPeriodStart+ '

            and YEAR(BillEndPeriod) <='+@pPeriodEnd+ '

            )  src

             

PIVOT (SUM(BillPeriodConsumption) FOR ConsumptionMonth

IN ('+@listCol+') ) AS pvt order by Saal asc,Consumerid asc'

  

EXECUTE (@query)

 

RETURN

END




[Serge]

Hi Ajay,

 You can actually get objects materialized with the ExecuteStoredProcedure method. The best way to achieve this is to do a similar method (just copy paste it) into a partial class, change the name and the generic type that is passed to the ExecuteStoredProcedure method and of course change the return type of the method.

Keep in mind though that this method can only handle materialization of classes that you already have defined in your model, in other words you need to have the shape predefined in the model. Once you get a strongly types set of objects you shouldn't have any problems binding that to a MVC grid.

I hope this is helpful and please do not hesitate to let us know if you need further assistance. 

Best wishes,
Serge
the Telerik team