Using Stored Procedure Sub Tables in RadGrid

[Paul]

Hello, new to Telerik and have what I think it a pretty basic question.

I've inherited a site where the business wants some more functionality on the page.  One of the items is to make it more reactive.  Telerik looks like it has some great options.  My challenge is, the current page calls a sp that returns multiple unnamed tables.

I can connect the grid to it fine and it binds to the first table (as I understand is the default behavior)  

Can someone please tell me how I can reference the subsequent tables via index?

I think it's something like below to get the 4th table from the SP for example but when I run it, I get:

Type 'System.Web.UI.WebControls.SqlDataSource' does not have a public property named 'DataSource'.

 

can someone please point me in the right direction?

thank you for your help

Paul

 

 

        <asp:SqlDataSource ID="AvailablePlans" DataSource ="[3]" runat="server" ConnectionString="<%$ ConnectionStrings:///////////////// %>" SelectCommand="ListDataForElections" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:Parameter DefaultValue="123456" Name="customer_id" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>

[Attila Antal]

Hi Paul,

Thank you for your interest in the Telerik UI for ASP.NET AJAX.

Before I answer your question, I will use the opportunity and share some details about the Grid.

First, note that there are two ways you can bind data to RadGrid:

1. Using Declarative DataSource controls - Can only control the default behavior, meaning that you do not have control over which table is bound to the Grid.
2. Programmatically using the NeedDataSource event - Since this is done programmatically, you get to choose which table to be bound

Regardless of the Data Binding technique you choose, you can use any means of fetching data from the database. For instance, you can use StoredProcedure, T-SQL, Entity Framework, etc.

When binding to Declarative DataSource controls, the Telerik RadGrid requirement is to define the reference to the DataSource Control through the DataSourceID property.

<telerik:RadGrid ID="RadGrid1" runat="server" DataSourceID="SqlDataSource1">
</telerik:RadGrid>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>

 

Once done, the Grid will request the DataSource control for data. Fetching the data from the database is up to the developer. You can choose to use StoredProcedure or T-SQL.

Here are some examples of using StoredProcedure for SqlDataSource:

• SqlDataSource StoredProcedure example: Pass parameters to StoredProcedure using SqlDataSource in ASP.Net
• Calling a Stored Procedure using SqlDataSource in asp.net

 

Recommended for you

When binding data programmatically using the NeedDataSource event, you will need to fetch the data programmatically.

Instead of using SqlDataSource, you will need to attach the Grid to its NeedDataSource event.

<telerik:RadGrid ID="RadGrid1" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource">
</telerik:RadGrid>

 

In the NeedDataSource event handler, the Grid will expect a DataSource.

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    var grid = (RadGrid)sender;

    grid.DataSource = GetData();
}

 

You can use different types of data sources (DataTable, DataSet, ArrayList, Objects, Sub Objects, Nullable Objects) as long as they have bindable properties. See Bindable Property Types

With that said, the GetData() method I have mentioned above can actually return any type, you choose.

How this method will fetch data from the SQL database? Again, it is your choice. You can choose EntityFramework or a Sql Client using ADO.NET.

Here are a few examples of using StoredProcedure with SQL Server

• Calling Stored procedures in ADO.NET
• Working with SQL Server Stored Procedures and .NET
• The "ins" and "outs" of Using Stored Procedures in C#

 

Here is an idea of the GetData() that would return a DataTable.

private DataTable GetData()
{
    // create a new/empty DataTable object
    var dt = new DataTable();

    // Use ADO.NET with StoredProcedure to fetch the data

    // Fill the DataTable with the returned results

    // Return the DataTable
    return dt;
}

 

Examples to populate DataTable using StoredProcedure:

• How to Populate a DataTable from a Stored Procedure [duplicate]
• Load / Fill / Populate DataTable from Stored Procedure in ASP.Net using C# and VB.Net
• Re: Is there a method filling of DataTable from StoredProcedure? (c#)

 

In your case, the StoredProcedure returns multiple tables, therefore you will need to fetch them into a DataSet. Once you have a set of tables, you can choose which table to return.

private DataTable GetData()
{
    // create a new/empty DataSet object
    var ds = new DataSet();

    // Use ADO.NET with StoredProcedure to fetch the data

    // Fill the DataSet with the returned Tables/results

    // Return only one of the tables (DataTable) from the DataSet.
    return ds.Tables[0];
}

 

This concludes the scenario.

 

Do you have any questions? Let me know!

 

Regards,
Attila Antal
Progress Telerik

[Paul]

Posting publicly too....thank you for such a great walk through and thorough explanation.  it is a huge help for me!