Telerik Reporting: Connecting to Stored Procedures

[OBSOLETE. Instead use the approach suggested in How to: Connect to a SQL Server Database Using Stored Procedure help article.]

I’ve blogged in the past about the many ways you can connect your reports to data. Because the developers who build Telerik’s Reporting Tool wanted to make it as flexible as possible, they designed it to have the ability to bind to any object that implements one of the four supported interfaces: IEnumerable, IListSource, ITypedList, and IDbDataAdapter. What does this mean for you? No matter where your data comes from – plain SQL queries, stored procedures, web services, etc – you can bind it to a Telerik report.

Okay, I realize that showing an example is much more effective than just talking about about how easy it is to bind data to your reports. So I’m going to walk you through connecting a report to the stored procedures in your database.

First, create a class library project in Visual Studio and add a Telerik Report item to your project. When you add this item, you’ll be greeted with the Telerik Report Wizard. Go ahead and cancel out of it as we’ll be designing our report manually this time.

For this example, I’m going to connect to the SalesByCategory stored procedure in Northwind. This stored procedure will return rows with two fields each, ProductName and TotalPurchase. Go ahead and use your report items to create a simple report that binds to these two fields. Here’s what I created:

Next, we’ll need to a way to retrieve data from the database using the stored procedure. We could write ADO.NET code, but I prefer to take the easier route and create a DataSet item to my report. To do so, right-click on your project and select Add | New Item… From the Add New Item dialog select a DataSet item, name it SalesDataSet.xsd and click Add.

When the new DataSet is generated, you’ll be greeted with a blank design surface. Drag a TableAdapter onto the design surface from your Visual Studio Toolbox. This will launch the TableAdapter Configuration Wizard. On the first page of the wizard, select the connection string you want to use to connect to the Northwind database. If one is not already set up and available in the drop down list, click New Connection… to create a new one.

Click Next. On the following page, select the Use existing stored procedures radio button to choose your command type.

Click Next. You’ll be given the option to choose four different stored procedures for CRUD operations. From the Select drop down list, choose the SalesByCategory stored procedure.

Click Next. One the following page you can choose your own method names for methods that will be generated to fill a DataTable or return a DataTable. For the Return a DataTable method name, enter GetSalesByCategory.

Click Finish. Now that we’ve set up a DataSet with which we can call our stored procedure, it’s time to set up our report to bind to the data from the DataSet. Since the stored procedure requires two parameters, we need to add a way to dynamically request these values from the user of the report. With Telerik Reporting we can use Report Parameters to satisfy this requirement.

To add two new report parameters to the report, go to your report properties in the Visual Studio Properties window and select the ellipses next to the Report Parameters property.

You will be greeted with the ReportParameter Collection Editor. Add two new parameters, with a Name and Text of Category and Year, each of type String. Set the AllowBlank property to False and the Visible property to True. Click OK.

Now that we have two parameters that we can pass to our stored procedure, the last thing we must do is call the stored procedure and bind the results to the report. We can do this by taking advantage of the report’s NeedDataSource event. To add an event handler to this event, go the to report’s events in the Visual Studio Properties window and double-click on the NeedDataSource event. This will add the event handler automatically to your code-behind.

In the newly-generated event handler, add the following code to retrieve the values from our report parameters, pass them to the stored procedure and bind the results to the report.

private void AnnualSalesReport_NeedDataSource(object sender, EventArgs e)

{

 string category = (string)this.ReportParameters["Category"].Value;

 string year = (string)this.ReportParameters["Year"].Value;

    SalesTableAdapter adapter = new SalesTableAdapter();

    SalesDataSet.SalesDataTable data = adapter.GetSalesByCategory(category, year);

    ((Telerik.Reporting.Processing.Report)sender).DataSource = data.AsDataView();

}

Now you can preview the results in the report designer by clicking on the Preview tab.

Conclusion

Binding to any data source is easy with Telerik Reporting if you remember one thing: the objects that are returned from your data source must match one of the supported interfaces.

If you’d like to see more examples of using stored procedures with Telerik reports, check out this Knowledge Base article on the topic.

[Source: C#]