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

Using Stored Procedure Sub Tables in RadGrid

2 Answers 25 Views
Ajax
This is a migrated thread and some comments may be shown as answers.
Paul
Top achievements
Rank 1
Paul asked on 17 Apr 2021, 09:42 PM

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>

2 Answers, 1 is accepted

Sort by
0
Accepted
Attila Antal
Telerik team
answered on 21 Apr 2021, 10:42 AM

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:

 

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

 

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:

 

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Paul
Top achievements
Rank 1
answered on 22 Apr 2021, 06:54 PM
Posting publicly too....thank you for such a great walk through and thorough explanation.  it is a huge help for me!
Tags
Ajax
Asked by
Paul
Top achievements
Rank 1
Answers by
Attila Antal
Telerik team
Paul
Top achievements
Rank 1
Share this question
or