This is a migrated thread and some comments may be shown as answers.
Getting DataTable from stored procedures
1 Answer 88 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Ajay
Top achievements
Rank 1
Ajay asked on 02 Jun 2011, 09:49 AM
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


1 Answer, 1 is accepted

Sort by
0
Serge
Telerik team
answered on 07 Jun 2011, 04:42 PM
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
Want to use Telerik OpenAccess with SQL Azure? Download the trial version today. 
Tags
Data Access Free Edition
Asked by
Ajay
Top achievements
Rank 1
Answers by
Serge
Telerik team
Share this question
or