Entity Framework Reporting

For many years the mantra for implementing business logic in your line of business application has been:
“don’t put it in the database, don’t put it in the user interface”.  In other words, apply the layers design pattern if at all possible,
together with implementing the Model-View-ViewModel (MVVM) or Model-View-Controller (MVC) pattern. Technologies like Entity Framework
help us convert data in the database to .NET objects and add logic. Life is good.

Then it is time to create a report. Traditionally reports are run against the database and any self respecting reporting technology
will to this day still offer you the option of building a report by querying directly against the database. Out the door goes the reuse of
your .NET based business logic, right? No need to fear, Visual Studio offers a solution. Starting with Visual Studio 2005 Microsoft started
shipping the ReportViewerControl with Visual Studio. Where SQL Server Reporting Services is full fledged reporting solution, with it’s own
server, scheduling engine, user interface, the ReportViewerControl is only a small part of the food chain. The ReportViewerControl will render
a report defined by an RDLC file against the data you feed into it. The data can still come from a database, but also from a WCF Service, any .NET object or SharePoint.

Let’s look at a sample. The sample will work on the AdventureWorks2008R2 database which can be downloaded from CodePlex. I’ve then
created two views: CustomerView and OrderView. These views limit the data to Massachusetts and join a couple of table to make for more demo-friendly data.

The following script will add the two views that we’ll be using:

1: USE  [AdventureWorks2008R2] 

2: GO 

3: 

4: IF   EXISTS (SELECT  *  FROM  sys.views  WHERE  object_id  =  OBJECT_ID()) 

5: DROP  VIEW  [dbo].[CustomerView] 

6: GO 

7: 

8: IF  NOT  EXISTS (SELECT  *  FROM  sys.views  WHERE  object_id  =  OBJECT_ID()) 

9: EXEC  dbo.sp_executesql @statement  =  < VIEW> 

10: AS 

11: SELECT DISTINCT Sales.Customer.AccountNumber, Person.Person.LastName,

       Person.Person.FirstName, Sales.Store.Name AS StoreName, 

       Sales.Customer.CustomerID 

12: FROM Sales.Customer INNER JOIN 

13:       Person.Person ON Sales.Customer.PersonID = Person.Person.BusinessEntityID INNER JOIN 

14:       Sales.Store ON Sales.Customer.StoreID = Sales.Store.BusinessEntityID INNER JOIN 

15:       dbo.OrderView ON Sales.Customer.CustomerID = dbo.OrderView.CustomerID 

16:

17: GO 

18: 

19: IF   EXISTS (SELECT  *  FROM  sys.views  WHERE  object_id  =  OBJECT_ID()) 

20: DROP  VIEW  [dbo].[OrderView] 

21: GO 

22: 

23: IF  NOT  EXISTS (SELECT  *  FROM  sys.views  WHERE  object_id  =  OBJECT_ID()) 

24: EXEC  dbo.sp_executesql @statement  =  <> 

25: AS 

26: SELECT Sales.SalesOrderHeader.SalesOrderID, Sales.SalesOrderHeader.CustomerID, 

       Production.Product.Name AS ProductName, Sales.SalesOrderDetail.OrderQty,  

27:       Sales.SalesOrderDetail.UnitPrice, Sales.SalesOrderDetail.UnitPriceDiscount, 

          Sales.SalesOrderDetail.LineTotal, Person.Address.AddressLine1,  

28:       Person.Address.AddressLine2, Person.Address.City, Person.Address.PostalCode, 

          Person.StateProvince.StateProvinceCode, Person.Address.SpatialLocation 

29: FROM Person.StateProvince INNER JOIN 

30:       Person.Address ON Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID AND  

31:       Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID INNER JOIN 

32:       Sales.SalesOrderDetail INNER JOIN 

33:       Sales.SalesOrderHeader ON  

          Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID INNER JOIN 

34:       Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID ON  

35:       Person.Address.AddressID = Sales.SalesOrderHeader.ShipToAddressID 

36: WHERE (Person.StateProvince.StateProvinceCode ='') 

37:   

38: GO

39: 

Next step is to create a WCF service application, add an Entity Framework model and drag the two views onto the model:

Next we’ll implement two methods to use the Entity Framework model to select the data and return 

a list of CustomerView or OrderView objects. Note: Normally you would not select all the contents 

in a view, but since we know that the number of rows in our views are already limited in numbers there is no problem here.

1: using  System;

2: using  System.Collections.Generic;

3: using  System.Linq;

4: using  System.Runtime.Serialization;

5: using  System.ServiceModel;

6: using  System.ServiceModel.Web;

7: using  System.Text;

8: 

9: namespace  AdventureServices

10: {

11:     public  class  AdventureService  : IAdventureService 

12:     {

13: 

14:         #region  IAdventureService Members

15: 

16:         public  List <CustomerView > GetReportCustomerData()

17:         {

18:             using  ( AdventureEntities  db = new  AdventureEntities () )

19:             {

20:                 var  query =  from  customer in  db.CustomerViews select  customer;

21:                 return  query.ToList();

22:             }

23:         }

24: 

25:         public  List <OrderView > GetReportOrderData()

26:         {

27:             using  ( AdventureEntities  db = new  AdventureEntities () )

28:             {

29:                 var  query = from  customer in  db.OrderViews select  customer;

30:                 return  query.ToList();

31:             }

32:         }

33: 

34:         #endregion 

35:     }

36: }

37: 

38: 

Next step is to create a report client. We can use any Windows or ASP.NET application and add start using the ReportViewerControl,
but Visual Studio also offers a report application template.
Very useful for quick demos:
 

Create the project (skip the wizard), then delete the Report1.rdlc. Add service reference to you AdventureServices and then add a new
report using the Report Wizard (on my machine I’ve had poor luck adding the service reference as part of the wizard steps.
 

On the first screen of the wizard give the dataset a name (CustomerDataSet), select the service reference as a datasource and pick CustomerView as the available dataset.
 

On the next screen drag the fields we want to display to the ‘Value’ grid. More complex grouping per row and column is also possible.
 

Since we’re doing a very basic report the next screen offers no selectable options, although we’re starting to see part of our report.
 

Next we pick a style. There are a couple to choose from.
 

We click finish to close the wizard.  Our report looks like this:
 

Use the designer and the tool box to enhance the report just a little:
 

Now the next step is to make sure our form will display this report. Go to the Form1 designer, select the ReportViewerControl and
look for the smart tag in the top right hand corner of the control. Activate the smart tag and you’ll see that you have to option to select a report.
Select the report you’ve just created:
 

Notice how at the bottom of the forms designer there now is a design time control:
 

The design time binding source allows us to feed data into the report. So far the ‘links’ that we created to the service have only been used
to pull in the schema of the data to be used. The actual data needs to be fed into the report when the form is run. For this we implement
a call to our AdventureService:
 

1: using  System;

2: using  System.Collections.Generic;

3: using  System.ComponentModel;

4: using  System.Data;

5: using  System.Drawing;

6: using  System.Text;

7: using  System.Windows.Forms;

8: 

9: namespace  AdventureReports

10: {

11:     public  partial  class  Form1  : Form 

12:     {

13:         public  Form1()

14:         {

15:             InitializeComponent();

16:         }

17: 

18:         private  void  Form1_Load(object  sender, EventArgs  e)

19:         {

20:             using  ( AdventureServiceReference.AdventureServiceClient  client = new  AdventureServiceReference.AdventureServiceClient () )

21:             {

22:                 this .CustomerViewBindingSource.DataSource = client.GetReportCustomerData();

23:             }

24:             this .reportViewer1.RefreshReport();

25:         }

26:     }

27: }

28: 

Note: Even though our service does not take any parameters to filter the data I hope you can see that it would only take a small amount of
coding to add a couple of fields to the form and pass any kind of selection to the service. I leave the actual implementation of that up to you,
when you’re building your ‘real’ report.