Getting DataTable from stored procedures

This is a migrated thread and some comments may be shown as answers.

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

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

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.