Getting DataTable from stored procedures

Thread is closed for posting
2 posts, 0 answers
  1. Ajay
    Ajay avatar
    10 posts
    Member since:
    Apr 2011

    Posted 02 Jun 2011 Link to this post

    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


  2.
  3. Serge
    Admin
    Serge avatar
    375 posts

    Posted 07 Jun 2011 Link to this post

    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. 
Back to Top