Using the ReportViewer with ADO.NET Data Services and Entity Framework

Using the ReportViewer with ADO.NET Data Services and Entity Framework

  • Comments 17

The Winforms ReportViewer in Visual Studio Pro will allow you define client reports with a variety of data sources. If you’re not familiar with creating client-side reports using the ReportViewer, take a look at these videos:

(The ReportViewer can also display server-based reports a la SQL Reporting Services and has a whole boatload of features that I’m not going to talk about here. For more information on this freely redistributable control, please read ReportViewer Controls (Visual Studio) in the MSDN library.)

ADO.NET Data Services was released with Visual Studio 2008 SP1 and is basically a framework for exposing your data models via RESTful web services. So if you are building a remote CRUD data access layer then this is a technology that will make that super-simple, especially if you just want to expose a few data entities as read-only report sources. (I’ve written a lot about how to use ADO.NET Data Services in a variety of ways so check out these posts if you’re interested.) Entity Framework was released at the same time and you can use EF as the data model behind your ADO.NET Data Service and expose it easily. If you’ve never created one before, check out this quick start here.

What’s the Problem?

If you’ve ever tried to use the ReportViewer to design a client-side report with an ADO.NET Data Service data source (or use an Entity Data Model directly) you may be disappointed. If you try to design a new report, the wizard may crash Visual Studio when selecting an entity. Yikes!

Let me show you what I mean, but don’t try this at home. I have an ADO.NET Data Service based on the Northwind database like the one I created here and in the quick start. I then added a Windows Forms client project to the same solution (File –> Add –> New Project, then select Windows Forms App). Now open Form1 in the client and from the toolbox under the Reporting tab, drop the Microsoft ReportViewer control onto the form and from the smart tag select “Design a New Report”.

 image

This will open the Report Wizard. The first thing I need to do is tell it what data source to use, so I’ll select Service and click Next. This will open the Add Service Reference dialog box where I can add the reference to my data service and Visual Studio will generate the proxy objects for me. In this example my service is in the same solution as the client so I can just click the Discover button and Visual Studio will see it. I named it NorthwindService. Click OK then click Finish.

At this point I’m brought back to the Report Wizard:

image

Hmmmm…. I’m a little lost at this point because -- didn’t I just select the data source? Why don’t I see it?

Maybe I should have added the service reference first from the Solution Explorer instead of through the Report Wizard? You can do that and you’ll get to the same screen at this point.

So okay, I’ll click the Add Data Source button that’s calling to me. Granted, I like clicking buttons that have the ellipsis (…) on them because I’m always curious to see what’s under them, but this seems redundant.

This opens the same dialog as before but this time I’ll choose Object instead of Service. I’m choosing object this time because I’m going to try and bind to the client proxy types that were generated for me when I added the service reference. After selecting Object, click Next and expand the service namespace.

image

For this report I want to just display a list of Customers in the system so I’ll select Customers and then click Next.

Then… then…… are you ready?… you better sit down for this…. click Finish……. wait for it…. wait for it….. not responding…. uh ohhhh…..

Visual Studio has encountered a problem and needs to close. Darn!

image

Now that was fun.

How Do I Fix this?

Unfortunately the Report Wizard in Visual Studio 2008 doesn’t know how to work with many-to-many relationships and that’s exactly what we have defined in our EF Entity Data Model which is what is backing our ADO.NET Data Service. Customers have many CustomerDemographics and vice versa in Northwind. You can actually define a couple classes yourself manually and create a many-to-many association between them:

Public Class Class1

    Private _class2 As List(Of Class2)
    Public ReadOnly Property Class2() As List(Of Class2)
        Get
            Return _class2
        End Get
    End Property

End Class

Public Class Class2

    Private _class1 As List(Of Class1)
    Public ReadOnly Property Class1() As List(Of Class1)
        Get
            Return _class1
        End Get
    End Property

End Class

Try to use either class above as the data source of the report and it will crash too. Woah! So when the report designer tries to read these associations I’m guessing it gets into an infinite loop walking the association references back and forth and then it crashes. So it’s not an error with the generated proxy objects per se, the root cause is our object model. If I would have chosen Categories instead, it would have worked.

So what do we do? We could create another layer of classes without the associations on both sides if we wanted and then use those types instead but then we’d have to write code that IMO is pretty useless, just to shape the data again how we want on the client.

My recommendation is to create a separate data model for your reports that doesn’t have many-to-many associations at all. You’re going to end up with a lot of different looking entities anyways for your reports because they will most likely pull from multiple database Views and/or stored procedures. You’ll always be displaying one side of the many-to-many relationship on paper anyways.

Creating a Reporting Data Model

So let’s go back to the data service project and add a new Entity Data Model to the web project called NorthwindReport. This time I’ll just select the Customers, Orders and Order Details and all the Views defined in the database.

image

Next I’ll add the ADO.NET Data Service and call it NorthwindReport and configure it so that we’re only allowing read-only access to our data by setting the entity access rule like so:

Imports System.Data.Services
Imports System.Linq
Imports System.ServiceModel.Web

Public Class NorthwindReport
    Inherits DataService(Of NorthwindReportEntities)

    Public Shared Sub InitializeService(ByVal config As IDataServiceConfiguration)
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead)
    End Sub
End Class

Now back on the client we can add the service reference to this service instead. After we add the service reference we can run the Report Wizard again by selecting “Design a New Report” smart tag on the ReportViewer control like I showed in the beginning. Select Object as the data source (or Service if you didn’t add the service reference first, then you’ll have to click “Add Data Sources…” again and then select Object) and you should see the list of entities exposed in the NorthwindReport data model. Now when we select Customer, we don’t have a problem.

Design the report how you like (see the documentation for more details) by clicking through the wizard and then go back to the smart tag on the ReportViewer and you should see the report you just created in the dropdown. Next go to the code-behind for the form and in the Load handler we just set the BindingSource.DataSource property to the results of the ADO.NET Data Service LINQ query:

Imports NorthwindClient.NorthwindReportService

Public Class Form1

    Private Sub Form1_Load() Handles MyBase.Load
        'TODO: put in My.Settings
        Dim uri As New Uri("http://localhost:1933/NorthwindReport.svc/")
        'Create the service reference
        Dim db As New NorthwindReportEntities(uri)

        'Set the report's DataSource to the results of the query
        Me.BindingSource.DataSource = From c In db.Customers _
                                      Where c.Country = "USA" _
                                      Order By c.CompanyName

        Me.ReportViewer1.RefreshReport()
    End Sub
End Class

Now hit F5 to run this baby and you’ll see the report pull the data from the service and display in the form:

image

Recap & Resources

ADO.NET Data Services are a great way to quickly and easily expose data on the web, especially if it’s just read-only data used for remote reporting clients. The key to using the ReportViewer with ADO.NET Data Services (or an Entity Data Model directly, or even your own object model) is to make sure there are no many-to-many associations on the entities. Here are the resources you need to get started with ReportViewer and ADO.NET Data Services:

Enjoy!

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • Hi

    in last time i learn connect too database by your Moves and now i do DAL(DatAccessLayer) by you method but i want create a report by Crystal and connect via Ado.net . in crystal there are a tree for connect by ado.net but it don't work how can i connect to database via crystal report by ado (i didn't use dataset.)

    thanks

  • Hi Massi,

    I am facing same issue in visual studio 2012. When I try to add data source my vs getting closed. This was working fine with vs 2010 .Is there any change in report development in vs 2012 ? only difference that I feel in vs 2008/ 2012 is that when I click choose data sources in 2008 this attach sqldatasource with report viewer but in vs 2012 this add datasource object and then vs crash. any suggestion

    I am trying to create report using vs 2012 , sql server 2008 r2  

Page 2 of 2 (17 items) 12