ADO.NET Data Services (formerly Astoria) is one of the new technologies released as part of v3.5 SP1 of the .NET Framework. If you haven’t tried this new addition to the framework, I highly suggest you give it a spin. It’s very cool and allows you to quickly and easily set up RESTful WCF services with which your application can interact with the database. If you’re interested in learning more, MSDN has several great resources to help you get started.

One of the cool things about Telerik is that the developers are always mindful of building products that can be easily integrated with existing and future technologies. With the deluge of new technologies spilling out of Microsoft, it would be silly to come up with a custom framework that may or may not be flexible enough to adapt as newer and more powerful capabilities emerge in .NET. That’s why products like Telerik Reporting can be used with cutting edge technologies like ADO.NET Data Services right out of the box.

I won’t go into any details about the inner workings of ADO.NET Data Services or the Entity Framework in this post. The goal here is to show a step-by-step guide to binding reports to data from ADO.NET Data Services. I encourage you to discover the details on your own.

A Quick Reminder about Data Binding

The important thing to remember about Telerik Reporting (and I can’t stress this enough) is that the manner with which data is served to your reports is completely irrelevant. Since Telerik reports bind to CLR objects, you must simply ensure that the data served to Telerik reports is in the correct format. So with that simple fact in mind, it becomes clear that using a new technology like ADO.NET Data Services (or any other technology for that matter) is not only possible but very easy. Just keep in mind that if the retrieved data is returned, or can be converted into, a supported type, it can be bound to a Telerik report.

Creating the Data and Service Layer

To get started we need to create a data layer to retrieve data from the database, and a services layer to serve up our data. First, create a Class Library project and an ASP.NET Web Application project in Visual Studio called Data and Services, respectively. Remove all generated files as we’ll be adding everything from scratch. Right-click on the Data project and select Add | New Item… Select the ADO.NET Entity Data Model template, name is NorthwindEntities.edmx and click Add. To set up your new entity model, you’ll be greeted with the Entity Data Model Wizard. Select Generate from database and click Next.

1On the next page of the wizard, select the connection which you will use to connect to the Northwind database. If you do not already have a connection set up in Visual Studio, you can use the New Connection… dialog to create one. Rename the entity connection settings to NorthwindEntitiesConnection and click Next.

2On the next page of the wizard you will choose the database objects you’d like to model. For this example, we’ll simply model the Products and Categories tables of the Northwind database. To do so, expand the Tables node in the treeview and click those tables. Click Finish.

3

Once the models are generated, you’ll be greeted with them in the Entity Designer. You can stop here, but since I’m not thrilled about my entities given plural names (e.g. “Products” instead of “Product”) I recommend going to each of their properties and changing their names to be singular, and their EntitySet names to be plural. After that is done, it’s time to move on to the data service.

4Luckily, this is the easiest step in the process. Right-click on the Services project and select Add | New Item… Select the ADO.NET Data Service template and name it ProductService.svc.

5Add a reference to the  Data project by right-clicking on the References folder and selecting Add Reference… Navigate to the Projects tab and select the Data project, then click OK. Now armed with a reference to the data layer, you need to make two simple edits to ProductService.svc.cs. First, add the data source class name to the class signature as the DataService type from which ProductService inherits. Next, uncomment the call to config.SetEntitySetAccessRule() and replace the first argument with the entity set we want to read, Products. Create an equivalent call to the same method, passing in the Categories entity set. Since our report only read and display data, we can leave the EntitySetRights as AllRead. Here is what the entire class should look like:

using System.Data.Services;
using Telerik.Examples.Data;
 
namespace Telerik.Examples.Services
{
 public class ProductService : DataService<NorthwindEntitiesConnection>
    {
 public static void InitializeService(IDataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("Products", EntitySetRights.AllRead);
            config.SetEntitySetAccessRule("Categories", EntitySetRights.AllRead);
        }
    }
}

An important thing to note before trying to run and test your service is that you must first copy the connection string generated in the Data project to the Web.config of the Services project. You could easily avoid this by combining the data and services layer into a single project, but I like to keep a clear separation of concerns in my projects (even in demos!) since it is a good coding practice. Here is what the connection string should look like (it may differ slightly, depending on your specific SQL Server connection details):

<connectionStrings>
 <add name="NorthwindEntitiesConnection" 
 connectionString="metadata=res://*/NorthwindEntities.csdl|res://*/NorthwindEntities.ssdl|res://*/NorthwindEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True&quot;" 
 providerName="System.Data.EntityClient" />
</connectionStrings>

Before moving on, you can run the service in the browser to verify it works correctly.

6Creating a Report

Creating your report is the easy part. There are two approaches you can take to building the report: use the wizard or add report items by hand and bind them to data fields manually. Since we’re using a web service to serve data to the report, we’ll need to use some trickery if we want to set up the report using the Report Wizard. Okay, so it’s really not “trickery” per se, but more like a workaround. Simply create a new data source which points to the same database as your web service, pick the settings and style you want for your report, and you’ll have a report ready to go in no time.

Don’t forget to add some style to the report to spruce it up a bit, and set format rules for your report items that are bound to date or numeric data (e.g. format the UnitPrice field as Currency). Here’s what my report looks like after running the wizard and tweaking things a bit:

7

The final thing I want to do, before I head into the code to bind my report to the web service data, is to add a report parameter to the report, which I’ll use to filter the data requested from the web service. To add a new report parameter, go to your report properties and click on the ellipses next to the ReportParameters property. The ReportParameter Collection editor will appear, which you can use to add a single parameter with the following properties – Name: Category; Type: Integer; UI.AllowBlank: False; UI.AvailableValues.DataMember: CategoryID; UI.AvailableValues.DisplayMember: CategoryName; UI.AvailableValues.ValueMember: CategoryID; UI.MultiValue: True; UI.Text: Category; UI.Visible: True.

8I purposefully didn’t set the available values using the ReportParameter Collection Editor because I intend to set those later using data from the web service. If you’re interested in learning more about report parameters, I have blogged about the topic and the online documentation is superb!

Binding Data to the Report

Finally we are ready to call our web service and bind data to the report. Jump into the code-behind of your report and follow along. The first thing I want to do is add a reference to the ADO.NET service we created earlier. Right-click on my References folder and click Add Service Reference…

9In the Add Service Reference dialog that appears, click Discover to quickly locate the service we created. If you already have a service set up and deployed to another address, you can enter it into the Address textbox and click Go. Click on the service you want to reference, give it a namespace of Services and click OK. This will generate entity classes in your Reports project that we can use when we retrieve data from the service.

10Next, add a DataContext property in your report code-behind which returns an instance of the NorthwindEntitiesConnection object which we will use to call the service. To instantiate a new instance of this object, you must pass in a Uri object which contains the address of the service.

private NorthwindEntitiesConnection _dataContext;
public NorthwindEntitiesConnection DataContext
{
    get
    {
 if (this._dataContext == null)
 this._dataContext = new NorthwindEntitiesConnection(new Uri("http://localhost/Services/Reporting/ProductService.svc/"));
 return this._dataContext;
    }
}

In the report constructor you can call the web service to retrieve a list of categories which will be used to populate the Category report parameter’s available values. Recall that we set the DisplayMember and ValueMember properties of our report parameter earlier so, if we simply pass in a list of Category objects, the parameter will be bound to those properties automatically. Since we specified in the report parameter’s AllowBlank and AllowNull properties that a value must be selected, we should also assign a value to the parameter at this time.

public ProductReport()
{
    InitializeComponent();
    var parameter = this.ReportParameters["Category"];
    parameter.UI.AvailableValues.DataSource = this.DataContext.Categories.ToList();
    parameter.Value = this.DataContext.Categories.ToList<Category>().Select(c => c.CategoryID).ToList<int>();
}

Finally, add an event handler for the report’s NeedDataSource event, during which we’ll retrieve filtered data from the web service using our report parameter.

private void ProductReport_NeedDataSource(object sender, EventArgs e)
{
    var categoryIDs = (ArrayList)this.ReportParameters["Category"].Value;
    var productList = new List<Product>();
 for (int i = 0; i < categoryIDs.Count; i++)
    {
        var categoryID = Convert.ToInt32(categoryIDs[i]);
        var products = this.DataContext.Products.Where(p => p.Category.CategoryID == categoryID);
        productList.AddRange(products);
    }
    ((Telerik.Reporting.Processing.Report)sender).DataSource = productList;
}

You might be curious why I chose to iterate through the list of categories in the report parameter value, calling the web service for each one and appending the results to a collection. The reason is that, due to the limitations of ADO.NET Data Services, you can’t use methods like Contains to filter data in your LINQ queries.

Here is the complete code for your report:

namespace Telerik.Examples.Reports
{
 using System;
 using System.Collections;
 using System.Collections.Generic;
 using System.Linq;
 using Telerik.Examples.Reports.Services;
 
 public partial class ProductReport : Telerik.Reporting.Report
    {
 private NorthwindEntitiesConnection _dataContext;
 public NorthwindEntitiesConnection DataContext
        {
            get
            {
 if (this._dataContext == null)
 this._dataContext = new NorthwindEntitiesConnection(new Uri("http://localhost/Services/Reporting/ProductService.svc/"));
 return this._dataContext;
            }
        }
 
 public ProductReport()
        {
            InitializeComponent();
            var parameter = this.ReportParameters["Category"];
            parameter.UI.AvailableValues.DataSource = this.DataContext.Categories.ToList();
            parameter.Value = this.DataContext.Categories.ToList<Category>().Select(c => c.CategoryID).ToList<int>();
        }
 
 private void ProductReport_NeedDataSource(object sender, EventArgs e)
        {
            var categoryIDs = (ArrayList)this.ReportParameters["Category"].Value;
            var productList = new List<Product>();
 for (int i = 0; i < categoryIDs.Count; i++)
            {
                var categoryID = Convert.ToInt32(categoryIDs[i]);
                var products = this.DataContext.Products.Where(p => p.Category.CategoryID == categoryID);
                productList.AddRange(products);
            }
            ((Telerik.Reporting.Processing.Report)sender).DataSource = productList;
        }
    }
}

Now if you head back to the report designer and select the Preview tab at the top, you can see what your new report looks like once it is populated with data from the web service. Notice in the dropdown at the top of the page that you can select one or more categories to filter your report data.

11Conclusion

So there you have it. It’s pretty easy to bind a Telerik report to ADO.NET Data Services. As you’ve probably deduced from this demo, you can easily change out your backend without needing to modify your reports at all. For example, we could take the Northwind Beverages report created in this demo and copy it to a WinForms project that serves up data from a local database using LINQ to SQL. By only changing a couple of lines of code in the report code-behind, we’d be good to go. Just recently I blogged about using stored procedures to populate your report with data.

An important thing to note about Telerik Reporting is that we still consider a “codeless” approach to reporting to be the best solution. The developers on the Telerik Reporting team have gone to great lengths to ensure that you can create rich reports without having to write a single line of code. So while it is easy to jump into the code and use Telerik Reporting with just about any data access technology, it is even easier to use the built-in wizards and designer to create your reports.

Hopefully this simple demo has demonstrated the flexibility and ease of use provided by Telerik Reporting. I encourage you to take this example further and discover how you can adapt it to your own reporting needs.

[Source Code]


Related Posts

Comments