Using OpenAccess and Telerik Reporting with SQL Azure

Telerik Reporting is a great reporting package. If you using it, you may be happy to know that you can use Telerik OpenAccess as a data source. Let’s take a look at how to use it with a SQL Azure Database as a back end.

Getting Started

First you need to map your SQL Azure tables to OpenAccess entities. I demonstrated this before on my blog, if you have not used OpenAccess and SQL Azure yet, read this post. (Don’t worry I’ll wait.) What I did for this demo is create a library project called Telerik.Reporting.DAL and mapped all of the Northwind tables in my SQL Azure database to OpenAccess entities in a class library.

Next I created another library project to contain the reports called Telerik.Reporting.RptLib. This solution will contain all of your reports. It is good to create all of the reports inside one isolated solution so you can reuse these reports in an ASP.NET solution as well as other projects like Silverlight. After we created this project, I ran the OpenAccess enable project wizard to get all of the proper references set up to use OpenAccess. In addition, I set a reference to our Telerik.Reporting.DAL project so we can see the entities we just mapped.

Creating a Report

Now it is time to create a report. Just right click on the project and select Add| New Item and choose the Telerik Report Q3 2009 template from the Reporting category.

The Telerik Reporting Wizard will come up. Select the option to build a new report and a new data source. For a data source type, select Business Object from the options available.

Next drill down into the Telerik.Reporting.DAL namespace and then select the Customer entity.

The next few pages of the wizard ask you how to lay out your report and what data fields to choose from. The wizard is self explanatory, so I will not go into detail here, but just choose any style that you like and show some customer fields on the report. I choose to show Customer ID and Company Name in standard report. My report looks pretty basic in design view:

Wiring up the data

We are almost there. Next step is to write a little LINQ code behind your report to wire up the OpenAcces data source with your report. Right click on the design canvas of the report and select “View Code.” That will take you to the code behind. Create a function like the one below that uses the OpenAccess LINQ implementation and return all of the customers (We could more complex LINQ queries here if we wanted to. )

 1: public static List<Customer>
GetCustomers()

 2: {

 3:  //LINQ Query to
fetch all of the Customers via OpenAccess 

 4:  //data context (IObjectScope)

 5:  IObjectScope dat = ObjectScopeProvider1.GetNewObjectScope();

 6:  //Get a ILIst of Customers

 7:  List<Customer> result = (from
c in dat.Extent<Customer>() select c).ToList();

 8:  return result;

 9: }

The function above returns a List of Customers. The IObjectScope in line 5 is the data context and the LINQ statement is in line 7.

But we have one problem. While we set all of the proper references, we are missing a bunch of using statements. You can manually add them here:

 1: using System.Collections.Generic;

 2: using System.Data;

 3: using System.Linq;

 4: using Telerik.OpenAccess;

 5: using Telerik.Reporting.DAL;

Or you can use the new Telerik JustCode tool to organize and fix your using statements:

You need to add one more line of code to your report. Add the following to the report’s construtctor, it will wire up your function as the data source of the report:

 1: DataSource = GetCustomers();

The ReportViewer Control

Now it is time to view the report. To do that create a new ASP.NET application called Telerik.Reporting.Web. Set a reference to the Telerik.Reporting.RptLib project where the report you just created is located. After that drag a Telerik ReportViewer control from your palate to the ASP.NET web form.

All you need to do now is add one line of code to the page load event (or a button click event, etc) to load the CustomerRpt we just built into the ReportViewer.

 1: if (!IsPostBack)
{ ReportViewer1.Report = new CustomerRpt(); }

Next step is to run it and see your report in action!

Enjoy!